src/org/openbravo/erpCommon/ad_actionButton/CreateFrom_Invoice_data.xsql
author RM packaging bot <staff.rm@openbravo.com>
Thu, 14 Sep 2017 12:03:42 +0000
changeset 32424 56162cb1470e
parent 31822 2933c75ab713
parent 32418 7c9f843207d8
child 32448 1509bbd77a82
child 32729 7bfe29345cb5
permissions -rw-r--r--
Merge temporary head for 3.0PR17Q2.3
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@9085
     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@6702
    14
 * The Initial Developer of the Original Code is Openbravo SLU 
markmm82@31822
    15
 * All portions are Copyright (C) 2001-2017 Openbravo SLU 
carlos@0
    16
 * All Rights Reserved. 
naiara@16816
    17
 * Contributor(s):  Cheli Pineda__________________________.
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="CreateFromInvoiceData" package="org.openbravo.erpCommon.ad_actionButton">
carlos@0
    26
  <SqlMethod name="selectFromPO" type="preparedStatement" return="multiple">
carlos@0
    27
    <SqlMethodComment></SqlMethodComment>
carlos@0
    28
    <Sql>
carlos@0
    29
      <![CDATA[
carlos@0
    30
        SELECT o.C_ORDER_ID as ID, Ad_Column_Identifier(to_char('C_Order'), to_char(o.c_order_id), to_char(?)) AS NAME, o.DocumentNo AS NAMEORDER,
david@3296
    31
        '' AS NAMESHIPMENT,
javier@15726
    32
        l.QtyOrdered-SUM(COALESCE(m.Qty,0))-COALESCE((SELECT SUM(COALESCE(CI.QtyINVOICED, 0))  
javier@15726
    33
       FROM C_ORDERLINE CO LEFT JOIN C_INVOICELINE CI ON CI.C_ORDERLINE_ID = CO.C_ORDERLINE_ID
javier@15726
    34
       WHERE  CI.C_INVOICE_ID=? AND CO.C_ORDERLINE_ID = l.c_orderline_id
javier@15726
    35
       GROUP BY CI.C_orderline_id , CO.QtyORDERED),0) AS QTY, 
jon@16238
    36
       l.C_UOM_ID,uom.UOMSymbol, l.M_Product_ID,Ad_Column_Identifier(to_char('M_Product'), to_char(l.m_product_id), to_char(?)) AS RELATION_NAME, 
david@30927
    37
        l.C_OrderLine_ID,l.Line, l.ad_org_id, '' as STDPRECISION, 
carlos@0
    38
        '' as M_InOutLine_ID, '' AS PriceActual, '' AS PriceList, '' AS PriceLimit, '' AS Description, '' as PriceStd,
david@30927
    39
        '' AS QUANTITYORDER, l.M_Product_UOM_ID, '' AS M_ATTRIBUTESETINSTANCE_ID, '' AS M_Offer_ID, '' AS PricePrecision,
mikel@18829
    40
        l.taxbaseamt, l.CancelPriceAD, '' AS Rate, '' AS gross_unit_price, '' AS grosspricelist, '' AS grosspricestd,
pandeeswari@18864
    41
        COALESCE(l.A_Asset_ID, o.A_Asset_ID) AS A_Asset_ID, COALESCE(l.C_Project_ID, o.C_Project_ID) AS C_Project_ID,
pandeeswari@18864
    42
        COALESCE(l.C_Costcenter_ID, o.C_Costcenter_ID) AS C_Costcenter_ID,
david@30807
    43
        COALESCE(l.User1_ID, o.User1_ID) AS User1_ID, COALESCE(l.User2_ID, o.User2_ID) AS User2_ID, l.explode, '' as isOrder, CASE WHEN l.Bom_parent_id is null then 'Y' else 'N' END as isparent, l.bom_parent_id,
david@30807
    44
        (CASE WHEN L.C_AUM IS NULL THEN M_GET_DEFAULT_AUM_FOR_DOCUMENT(L.M_PRODUCT_ID, o.C_DOCTYPE_ID) ELSE l.C_AUM END) AS C_AUM, 
david@30918
    45
        l.AUMQTY-SUM(COALESCE(M_GET_CONVERTED_AUMQTY(l.m_product_id, m.Qty, l.c_aum),0))-COALESCE((SELECT SUM(COALESCE(M_GET_CONVERTED_AUMQTY(l.m_product_id, CI.QtyINVOICED, l.c_aum), 0))  
david@30918
    46
                                                                                                FROM C_ORDERLINE CO LEFT JOIN C_INVOICELINE CI ON CI.C_ORDERLINE_ID = CO.C_ORDERLINE_ID
david@30918
    47
                                                                                                WHERE  CI.C_INVOICE_ID=? AND CO.C_ORDERLINE_ID = l.c_orderline_id
david@30918
    48
                                                                                                GROUP BY CI.C_orderline_id , CO.QtyORDERED),0) AS AUMQTY, o.C_DOCTYPE_ID, 
david@30807
    49
        (CASE WHEN L.C_AUM IS NOT NULL THEN (SELECT UOMSYMBOL FROM C_UOM WHERE C_UOM_ID = L.C_AUM) ELSE (SELECT UOMSYMBOL FROM C_UOM WHERE C_UOM_ID = M_GET_DEFAULT_AUM_FOR_DOCUMENT(L.M_PRODUCT_ID, o.C_DOCTYPE_ID)) END) AS AUMSYMBOL,
nonofce@30897
    50
        'none' AS AUMVISIBLE
carlos@0
    51
        FROM C_ORDERLINE l left join M_MATCHPO m on  l.C_OrderLine_ID=m.C_OrderLine_ID
carlos@0
    52
                                                 AND m.C_InvoiceLine_ID IS NOT NULL, 
carlos@0
    53
        C_ORDER o, C_UOM uom,  M_PRODUCT p
carlos@0
    54
        WHERE o.C_ORDER_ID = l.C_ORDER_ID 
carlos@0
    55
        AND o.IsSOTrx='N' 
carlos@0
    56
        AND o.DocStatus IN ('CL','CO') 
carlos@0
    57
        AND o.AD_Client_ID IN ('1') 
carlos@0
    58
        AND o.AD_Org_ID IN ('1') 
carlos@0
    59
        AND l.C_UOM_ID=uom.C_UOM_ID
carlos@0
    60
        AND l.M_Product_ID=p.M_Product_ID
juanpablo@1605
    61
        AND l.C_Order_ID = ? 
javier@15726
    62
        AND NOT EXISTS (SELECT 1
javier@15732
    63
                       FROM C_ORDERLINE CO LEFT JOIN C_INVOICELINE CI ON CI.C_ORDERLINE_ID = CO.C_ORDERLINE_ID left join M_MATCHPO m on  CO.C_OrderLine_ID=m.C_OrderLine_ID
javier@15732
    64
                                                 AND m.C_InvoiceLine_ID IS NOT NULL
javier@15732
    65
                       WHERE CO.C_ORDERLINE_ID = l.c_orderline_id AND CI.C_INVOICE_ID=? 
javier@15726
    66
                       GROUP BY CI.C_orderline_id , CO.QtyORDERED
david@19206
    67
                       HAVING (SUM(COALESCE(CI.QtyINVOICED, 0))-(COALESCE(CO.QtyORDERED,0)-SUM(COALESCE(m.Qty,0)))) >= 0 )   
carlos@0
    68
        GROUP BY o.C_ORDER_ID, o.DocumentNo, o.DateOrdered, o.GrandTotal, l.QtyOrdered,l.C_UOM_ID,
pandeeswari@18817
    69
        uom.UOMSymbol,l.M_Product_ID,p.NAME,l.Line,l.C_OrderLine_ID, l.ad_org_id, l.taxbaseamt,l.CancelPriceAD,
pandeeswari@18864
    70
        COALESCE(l.A_Asset_ID, o.A_Asset_ID), COALESCE(l.C_Project_ID, o.C_Project_ID), COALESCE(l.C_Costcenter_ID, o.C_Costcenter_ID),
david@30807
    71
        COALESCE(l.User1_ID, o.User1_ID), COALESCE(l.User2_ID, o.User2_ID), l.explode, l.bom_parent_id, l.M_Product_UOM_ID, l.AUMQTY, o.C_DOCTYPE_ID, c_aum
naiara@19828
    72
        HAVING ( (l.explode='Y') OR ((l.QtyOrdered-SUM(COALESCE(m.Qty,0))) <> 0)) 
carlos@0
    73
        ORDER BY NAME, l.Line
carlos@0
    74
      ]]>
carlos@0
    75
    </Sql>
carlos@0
    76
    <Field name="rownum" value="count"/>
carlos@0
    77
    <Parameter name="language"/>
naiara@16671
    78
    <Parameter name="piId"/>
jon@16238
    79
    <Parameter name="language"/>
david@30918
    80
    <Parameter name="piId"/>
carlos@0
    81
    <Parameter name="adUserClient" type="replace" optional="true" after="o.AD_Client_ID IN (" text="'1'"/>
carlos@0
    82
    <Parameter name="adOrgClient" type="replace" optional="true" after="o.AD_Org_ID IN (" text="'1'"/>
carlos@0
    83
    <Parameter name="poId"/>
javier@15726
    84
    <Parameter name="piId"/>
carlos@0
    85
  </SqlMethod>
carlos@0
    86
  <SqlMethod name="selectFromPOCombo" type="preparedStatement" return="multiple">
carlos@0
    87
    <SqlMethodComment></SqlMethodComment>
carlos@0
    88
    <Sql>
carlos@0
    89
      <![CDATA[
carlos@0
    90
        SELECT o.C_ORDER_ID as ID, Ad_Column_Identifier(to_char('C_Order'), to_char(o.c_order_id), to_char(?)) AS NAME 
gorkaion@17357
    91
        FROM C_ORDER o JOIN m_pricelist pl ON o.m_pricelist_id = pl.m_pricelist_id
carlos@0
    92
        WHERE o.IsSOTrx='N' 
carlos@0
    93
        AND o.DocStatus IN ('CL','CO') 
carlos@0
    94
        AND o.AD_Client_ID IN ('1') 
carlos@0
    95
        AND o.AD_Org_ID IN ('1')
juanpablo@1605
    96
        AND o.C_BPartner_ID = ?
gorkaion@17357
    97
        AND pl.istaxincluded = ?
reinaldo@25191
    98
        AND o.InvoiceRule <> 'N'
carlos@0
    99
        AND EXISTS (SELECT 1 
carlos@0
   100
                     FROM C_ORDERLINE l left join M_MATCHPO m on  l.C_OrderLine_ID=m.C_OrderLine_ID
carlos@0
   101
                                                              and m.C_InvoiceLine_ID IS NOT NULL  
carlos@0
   102
                    WHERE l.C_ORDER_ID = o.C_ORDER_ID  
carlos@0
   103
                    GROUP BY l.QtyOrdered,l.C_OrderLine_ID 
carlos@0
   104
                    HAVING (l.QtyOrdered-SUM(COALESCE(m.Qty,0))) <> 0)
carlos@0
   105
        ORDER BY NAME
carlos@0
   106
      ]]>
carlos@0
   107
    </Sql>
carlos@0
   108
    <Parameter name="language"/>
carlos@0
   109
    <Parameter name="adUserClient" type="replace" optional="true" after="o.AD_Client_ID IN (" text="'1'"/>
carlos@0
   110
    <Parameter name="adOrgClient" type="replace" optional="true" after="o.AD_Org_ID IN (" text="'1'"/>
carlos@0
   111
    <Parameter name="cBpartnerId"/>
gorkaion@17357
   112
    <Parameter name="isTaxIncluded"/>
carlos@0
   113
  </SqlMethod>
carlos@0
   114
  <SqlMethod name="selectFromPOSOTrx" type="preparedStatement" return="multiple">
carlos@0
   115
    <SqlMethodComment></SqlMethodComment>
carlos@0
   116
    <Sql>
carlos@0
   117
      <![CDATA[
carlos@0
   118
        SELECT o.C_ORDER_ID as ID, Ad_Column_Identifier(to_char('C_Order'), to_char(o.c_order_id), to_char(?)) AS NAME,o.DocumentNo AS NAMEORDER,
jon@16238
   119
        l.QtyOrdered-COALESCE(l.QTYINVOICED,0) AS QTY, l.C_UOM_ID,uom.UOMSymbol, l.M_Product_ID,
jon@16238
   120
	Ad_Column_Identifier(to_char('M_Product'), to_char(l.m_product_id), to_char(?)) AS RELATION_NAME, 
pandeeswari@18864
   121
        l.C_OrderLine_ID,l.Line, l.ad_Org_id, l.taxbaseamt,
pandeeswari@18864
   122
        COALESCE(l.A_Asset_ID, o.A_Asset_ID) AS A_Asset_ID, COALESCE(l.C_Project_ID, o.C_Project_ID) AS C_Project_ID, COALESCE(l.C_Costcenter_ID, o.C_Costcenter_ID) AS C_Costcenter_ID,
naiara@19828
   123
        COALESCE(l.User1_ID, o.User1_ID) AS User1_ID, COALESCE(l.User2_ID, o.User2_ID) AS User2_ID, l.explode, l.Bom_parent_id, 
david@30807
   124
         CASE WHEN l.Bom_parent_id is null then 'Y' else 'N' END as isparent,
david@30807
   125
        (CASE WHEN L.C_AUM IS NULL THEN M_GET_DEFAULT_AUM_FOR_DOCUMENT(L.M_PRODUCT_ID, o.C_DOCTYPE_ID) ELSE l.C_AUM END) AS C_AUM, 
david@30918
   126
        l.AUMQTY - SUM(COALESCE(M_GET_CONVERTED_AUMQTY(l.m_product_id, l.QTYINVOICED, l.c_aum), 0)) AS AUMQTY, o.C_DOCTYPE_ID, 
nonofce@30897
   127
        (CASE WHEN L.C_AUM IS NOT NULL THEN (SELECT UOMSYMBOL FROM C_UOM WHERE C_UOM_ID = L.C_AUM) ELSE (SELECT UOMSYMBOL FROM C_UOM WHERE C_UOM_ID = M_GET_DEFAULT_AUM_FOR_DOCUMENT(L.M_PRODUCT_ID, o.C_DOCTYPE_ID)) END) AS AUMSYMBOL,
nonofce@30897
   128
        'none' AS AUMVISIBLE
carlos@0
   129
        FROM C_INVOICE_CANDIDATE_V ic, C_UOM uom, C_ORDER o, C_ORDERLINE l, M_PRODUCT p 
carlos@0
   130
        WHERE l.C_UOM_ID=uom.C_UOM_ID
carlos@0
   131
        AND o.C_ORDER_ID = ic.C_ORDER_ID 
carlos@0
   132
        AND o.C_ORDER_ID=l.C_ORDER_ID 
carlos@0
   133
        AND ic.AD_Client_ID IN ('1') 
carlos@0
   134
        AND ic.AD_Org_ID IN ('1') 
carlos@0
   135
        AND l.M_Product_ID=p.M_Product_ID 
juanpablo@1605
   136
        AND ic.C_Order_ID = ? 
carlos@0
   137
        GROUP BY o.C_ORDER_ID, o.DocumentNo, o.DateOrdered, o.GrandTotal, l.QtyOrdered,
pandeeswari@18864
   138
        l.C_UOM_ID,uom.UOMSymbol,l.M_Product_ID,p.NAME,l.Line,l.C_OrderLine_ID, l.QTYINVOICED, l.ad_org_id, l.taxbaseamt,
pandeeswari@18864
   139
        COALESCE(l.A_Asset_ID, o.A_Asset_ID), COALESCE(l.C_Project_ID, o.C_Project_ID), COALESCE(l.C_Costcenter_ID, o.C_Costcenter_ID),
david@30807
   140
        COALESCE(l.User1_ID, o.User1_ID), COALESCE(l.User2_ID, o.User2_ID), l.explode, l.Bom_parent_id, c_aum, l.AUMQTY, o.C_DOCTYPE_ID
carlos@0
   141
        ORDER BY NAME, l.Line
carlos@0
   142
      ]]>
carlos@0
   143
    </Sql>
carlos@0
   144
    <Field name="rownum" value="count"/>
carlos@0
   145
    <Parameter name="language"/>
jon@16238
   146
    <Parameter name="language"/>
carlos@0
   147
    <Parameter name="adUserClient" type="replace" optional="true" after="ic.AD_Client_ID IN (" text="'1'"/>
carlos@0
   148
    <Parameter name="adOrgClient" type="replace" optional="true" after="ic.AD_Org_ID IN (" text="'1'"/>
carlos@0
   149
    <Parameter name="poId"/>
carlos@0
   150
  </SqlMethod>
carlos@0
   151
  <SqlMethod name="selectFromPOSOTrxCombo" type="preparedStatement" return="multiple">
carlos@0
   152
    <SqlMethodComment></SqlMethodComment>
carlos@0
   153
    <Sql>
carlos@0
   154
      <![CDATA[
carlos@0
   155
        SELECT o.C_ORDER_ID as ID, Ad_Column_Identifier(to_char('C_Order'), to_char(o.c_order_id), to_char(?)) AS NAME 
gorkaion@17357
   156
        FROM C_INVOICE_CANDIDATE_V ic,
gorkaion@17357
   157
             C_ORDER o JOIN m_pricelist pl ON o.m_pricelist_id = pl.m_pricelist_id
carlos@0
   158
        WHERE o.C_ORDER_ID = ic.C_ORDER_ID 
carlos@0
   159
        AND ic.AD_Client_ID IN ('1') 
carlos@0
   160
        AND ic.AD_Org_ID IN ('1') 
gorkaion@17357
   161
        AND ic.C_BPartner_ID = ?
gorkaion@17357
   162
        AND pl.istaxincluded = ? 
harikrishnan@7999
   163
        AND o.C_Order_Id = ic.C_Order_Id
atul@30803
   164
        AND ((ic.term = 'D' AND ic.qtydelivered <>0) OR (ic.term = 'I' AND exists (SELECT 1 FROM C_ORDERLINE ol WHERE ol.C_ORDER_ID = o.C_ORDER_ID group by ol.c_orderline_id having SUM(ol.QTYORDERED)-SUM(ol.QTYINVOICED)<>0)) OR (ic.term IN ('O','S') AND (ic.qtyordered = ic.qtydelivered) ) )
carlos@0
   165
        GROUP BY o.C_ORDER_ID, o.DocumentNo, o.DateOrdered, o.GrandTotal 
carlos@0
   166
        ORDER BY NAME
carlos@0
   167
      ]]>
carlos@0
   168
    </Sql>
carlos@0
   169
    <Parameter name="language"/>
carlos@0
   170
    <Parameter name="adUserClient" type="replace" optional="true" after="ic.AD_Client_ID IN (" text="'1'"/>
carlos@0
   171
    <Parameter name="adOrgClient" type="replace" optional="true" after="ic.AD_Org_ID IN (" text="'1'"/>
carlos@0
   172
    <Parameter name="cBpartnerId"/>
gorkaion@17357
   173
    <Parameter name="isTaxIncluded"/>
carlos@0
   174
  </SqlMethod>
carlos@0
   175
  <SqlMethod name="selectFromPOTrl" type="preparedStatement" return="multiple">
carlos@0
   176
    <SqlMethodComment></SqlMethodComment>
carlos@0
   177
    <Sql>
carlos@0
   178
      <![CDATA[
carlos@0
   179
        SELECT o.C_ORDER_ID as ID, Ad_Column_Identifier(to_char('C_Order'), to_char(o.c_order_id), to_char(?)) AS NAME, o.DocumentNo AS NAMEORDER,
javier@15726
   180
        l.QtyOrdered-SUM(COALESCE(m.Qty,0))-
javier@15726
   181
        COALESCE((SELECT SUM(COALESCE(CI.QtyINVOICED, 0))  
javier@15726
   182
        FROM C_ORDERLINE CO LEFT JOIN C_INVOICELINE CI ON CI.C_ORDERLINE_ID = CO.C_ORDERLINE_ID
javier@15726
   183
        WHERE  CI.C_INVOICE_ID=? AND CO.C_ORDERLINE_ID = l.c_orderline_id
javier@15726
   184
        GROUP BY CI.C_orderline_id , CO.QtyORDERED),0) AS QTY, 
javier@15726
   185
        l.C_UOM_ID, COALESCE(uomt.UOMSYMBOL, uom.UOMSymbol) AS UOMSymbol, 
pandeeswari@18864
   186
        l.M_Product_ID,Ad_Column_Identifier(to_char('M_Product'), to_char(l.m_product_id), to_char(?)) AS RELATION_NAME, l.C_OrderLine_ID,l.Line, l.taxbaseamt,
pandeeswari@18864
   187
        COALESCE(l.A_Asset_ID, o.A_Asset_ID) AS A_Asset_ID, COALESCE(l.C_Project_ID, o.C_Project_ID) AS C_Project_ID, COALESCE(l.C_Costcenter_ID, o.C_Costcenter_ID) AS C_Costcenter_ID,
david@30807
   188
        COALESCE(l.User1_ID, o.User1_ID) AS User1_ID, COALESCE(l.User2_ID, o.User2_ID) AS User2_ID,
david@30807
   189
        (CASE WHEN L.C_AUM IS NULL THEN M_GET_DEFAULT_AUM_FOR_DOCUMENT(L.M_PRODUCT_ID, o.C_DOCTYPE_ID) ELSE l.C_AUM END) AS C_AUM, 
david@30918
   190
        l.AUMQTY-SUM(COALESCE(M_GET_CONVERTED_AUMQTY(l.m_product_id, m.Qty, l.c_aum),0))-COALESCE((SELECT SUM(COALESCE(M_GET_CONVERTED_AUMQTY(l.m_product_id, CI.QtyINVOICED, l.c_aum), 0))  
david@30918
   191
                                                                                                FROM C_ORDERLINE CO LEFT JOIN C_INVOICELINE CI ON CI.C_ORDERLINE_ID = CO.C_ORDERLINE_ID
david@30918
   192
                                                                                                WHERE  CI.C_INVOICE_ID=? AND CO.C_ORDERLINE_ID = l.c_orderline_id
david@30918
   193
                                                                                                GROUP BY CI.C_orderline_id , CO.QtyORDERED),0) AS AUMQTY, o.C_DOCTYPE_ID, 
nonofce@30897
   194
        (CASE WHEN L.C_AUM IS NOT NULL THEN (SELECT UOMSYMBOL FROM C_UOM WHERE C_UOM_ID = L.C_AUM) ELSE (SELECT UOMSYMBOL FROM C_UOM WHERE C_UOM_ID = M_GET_DEFAULT_AUM_FOR_DOCUMENT(L.M_PRODUCT_ID, o.C_DOCTYPE_ID)) END) AS AUMSYMBOL,
nonofce@30897
   195
        'none' AS AUMVISIBLE
carlos@0
   196
        FROM C_UOM uom left join  C_UOM_TRL uomt on uom.C_UOM_ID = uomt.C_UOM_ID
carlos@0
   197
                                                and uomt.AD_Language = ?,
carlos@0
   198
             C_ORDERLINE l left join M_MATCHPO m on l.C_OrderLine_ID=m.C_OrderLine_ID
carlos@0
   199
                                                and m.C_InvoiceLine_ID IS NOT NULL,
carlos@0
   200
        C_ORDER o, M_PRODUCT p
carlos@0
   201
        WHERE o.C_ORDER_ID = l.C_ORDER_ID 
carlos@0
   202
        AND o.IsSOTrx='N' 
carlos@0
   203
        AND o.DocStatus IN ('CL','CO') 
carlos@0
   204
        AND o.AD_Client_ID IN ('1') 
carlos@0
   205
        AND o.AD_Org_ID IN ('1') 
carlos@0
   206
        AND l.C_UOM_ID=uom.C_UOM_ID 
carlos@0
   207
        AND l.M_Product_ID=p.M_Product_ID  
juanpablo@1605
   208
        AND l.C_Order_ID = ? 
javier@15726
   209
        AND NOT EXISTS (SELECT 1
javier@15732
   210
                       FROM C_ORDERLINE CO LEFT JOIN C_INVOICELINE CI ON CI.C_ORDERLINE_ID = CO.C_ORDERLINE_ID left join M_MATCHPO m on  CO.C_OrderLine_ID=m.C_OrderLine_ID
javier@15732
   211
                                                 AND m.C_InvoiceLine_ID IS NOT NULL
javier@15732
   212
                       WHERE CO.C_ORDERLINE_ID = l.c_orderline_id AND CI.C_INVOICE_ID=? 
javier@15726
   213
                       GROUP BY CI.C_orderline_id , CO.QtyORDERED
javier@15732
   214
                       HAVING (SUM(COALESCE(CI.QtyINVOICED, 0))-(COALESCE(CO.QtyORDERED,0)-SUM(COALESCE(m.Qty,0)))) >= 0 )  
carlos@0
   215
        GROUP BY o.C_ORDER_ID, o.DocumentNo, o.DateOrdered, o.GrandTotal, l.QtyOrdered,l.C_UOM_ID,
eduardo@6416
   216
        uom.UOMSymbol, uomt.UOMSYMBOL,l.M_Product_ID,p.NAME,l.Line,l.C_OrderLine_ID,
pandeeswari@18864
   217
        l.taxbaseamt, COALESCE(l.A_Asset_ID, o.A_Asset_ID), COALESCE(l.C_Project_ID, o.C_Project_ID), COALESCE(l.C_Costcenter_ID, o.C_Costcenter_ID),
david@30807
   218
        COALESCE(l.User1_ID, o.User1_ID), COALESCE(l.User2_ID, o.User2_ID), C_AUM, l.AUMQTY, o.C_DOCTYPE_ID
carlos@0
   219
        HAVING (l.QtyOrdered-SUM(COALESCE(m.Qty,0))) <> 0
carlos@0
   220
        ORDER BY NAME, l.Line
carlos@0
   221
      ]]>
carlos@0
   222
    </Sql>
carlos@0
   223
    <Field name="rownum" value="count"/>
carlos@0
   224
    <Parameter name="adLanguage"/>
javier@15726
   225
    <Parameter name="piId"/>
jon@16238
   226
    <Parameter name="adLanguage"/>
david@30918
   227
    <Parameter name="piId"/>
jon@16238
   228
    <Parameter name="adLanguage"/>
carlos@0
   229
    <Parameter name="adUserClient" type="replace" optional="true" after="o.AD_Client_ID IN (" text="'1'"/>
carlos@0
   230
    <Parameter name="adOrgClient" type="replace" optional="true" after="o.AD_Org_ID IN (" text="'1'"/>
carlos@0
   231
    <Parameter name="poId"/>
javier@15726
   232
    <Parameter name="piId"/>
carlos@0
   233
  </SqlMethod>
carlos@0
   234
  <SqlMethod name="selectFromPOTrlSOTrx" type="preparedStatement" return="multiple">
carlos@0
   235
    <SqlMethodComment></SqlMethodComment>
carlos@0
   236
    <Sql>
carlos@0
   237
      <![CDATA[
carlos@0
   238
        SELECT o.C_ORDER_ID as ID, Ad_Column_Identifier(to_char('C_Order'), to_char(o.c_order_id), to_char(?) ) AS NAME,o.DocumentNo AS NAMEORDER,
carlos@0
   239
        l.QtyOrdered-COALESCE(l.QTYINVOICED,0) AS QTY, l.C_UOM_ID, COALESCE(uomt.UOMSymbol, uom.UOMSymbol) AS UOMSymbol, 
pandeeswari@18864
   240
        l.M_Product_ID,Ad_Column_Identifier(to_char('M_Product'), to_char(l.m_product_id), to_char(?)) AS RELATION_NAME, l.C_OrderLine_ID,l.Line, l.taxbaseamt,
pandeeswari@18864
   241
        COALESCE(l.A_Asset_ID, o.A_Asset_ID) AS A_Asset_ID, COALESCE(l.C_Project_ID, o.C_Project_ID) AS C_Project_ID, COALESCE(l.C_Costcenter_ID, o.C_Costcenter_ID) AS C_Costcenter_ID,
david@30807
   242
        COALESCE(l.User1_ID, o.User1_ID) AS User1_ID, COALESCE(l.User2_ID, o.User2_ID) AS User2_ID,
david@30807
   243
        (CASE WHEN L.C_AUM IS NULL THEN M_GET_DEFAULT_AUM_FOR_DOCUMENT(L.M_PRODUCT_ID, o.C_DOCTYPE_ID) ELSE l.C_AUM END) AS C_AUM, 
david@30918
   244
        l.AUMQTY - SUM(COALESCE(M_GET_CONVERTED_AUMQTY(l.m_product_id, l.QTYINVOICED, l.c_aum), 0)) AS AUMQTY, o.C_DOCTYPE_ID, 
nonofce@30897
   245
        (CASE WHEN L.C_AUM IS NOT NULL THEN (SELECT UOMSYMBOL FROM C_UOM WHERE C_UOM_ID = L.C_AUM) ELSE (SELECT UOMSYMBOL FROM C_UOM WHERE C_UOM_ID = M_GET_DEFAULT_AUM_FOR_DOCUMENT(L.M_PRODUCT_ID, o.C_DOCTYPE_ID)) END) AS AUMSYMBOL,
nonofce@30897
   246
        'none' AS AUMVISIBLE
carlos@0
   247
        FROM C_UOM uom left join C_UOM_TRL uomt on uom.C_UOM_ID = uomt.C_UOM_ID
carlos@0
   248
                                               and  uomt.AD_Language = ?, 
carlos@0
   249
        C_INVOICE_CANDIDATE_V ic,   C_ORDER o, C_ORDERLINE l, M_PRODUCT p 
carlos@0
   250
        WHERE l.C_UOM_ID=uom.C_UOM_ID
carlos@0
   251
        AND o.C_ORDER_ID = ic.C_ORDER_ID 
carlos@0
   252
        AND o.C_ORDER_ID=l.C_ORDER_ID 
carlos@0
   253
        AND ic.AD_Client_ID IN ('1') 
carlos@0
   254
        AND ic.AD_Org_ID IN ('1') 
carlos@0
   255
        AND l.M_Product_ID=p.M_Product_ID
juanpablo@1605
   256
        AND ic.C_Order_ID = ? 
carlos@0
   257
        GROUP BY o.C_ORDER_ID, o.DocumentNo, o.DateOrdered, o.GrandTotal, l.QtyOrdered,
eduardo@6416
   258
        l.C_UOM_ID,uom.UOMSymbol, uomt.UOMSymbol,l.M_Product_ID,p.NAME,l.Line,l.C_OrderLine_ID, l.QTYINVOICED,
pandeeswari@18864
   259
        l.taxbaseamt, COALESCE(l.A_Asset_ID, o.A_Asset_ID), COALESCE(l.C_Project_ID, o.C_Project_ID), COALESCE(l.C_Costcenter_ID, o.C_Costcenter_ID),
david@30807
   260
        COALESCE(l.User1_ID, o.User1_ID), COALESCE(l.User2_ID, o.User2_ID), l.AUMQTY, o.C_DOCTYPE_ID, C_AUM
carlos@0
   261
        ORDER BY NAME, l.Line
carlos@0
   262
      ]]>
carlos@0
   263
    </Sql>
carlos@0
   264
    <Field name="rownum" value="count"/>
carlos@0
   265
    <Parameter name="adLanguage"/>
jon@16238
   266
    <Parameter name="adLanguage"/>
carlos@0
   267
    <Parameter name="adUserClient" type="replace" optional="true" after="ic.AD_Client_ID IN (" text="'1'"/>
carlos@0
   268
    <Parameter name="adOrgClient" type="replace" optional="true" after="ic.AD_Org_ID IN (" text="'1'"/>
carlos@0
   269
    <Parameter name="adLanguage"/>
carlos@0
   270
    <Parameter name="poId"/>
carlos@0
   271
  </SqlMethod>
carlos@0
   272
  
carlos@0
   273
  <SqlMethod name="selectFromPOUpdate" type="preparedStatement" connection="true" return="multiple">
carlos@0
   274
    <SqlMethodComment></SqlMethodComment>
carlos@0
   275
    <Sql>
carlos@0
   276
      <![CDATA[
carlos@0
   277
      SELECT (CASE WHEN B.M_InOutLine_ID IS NULL THEN A.QTY ELSE B.QTY END) AS ID, 
jon@16083
   278
      A.C_UOM_ID,uom.UOMSymbol, A.M_Product_ID,Ad_Column_Identifier(to_char('M_Product'), to_char(A.m_product_id), to_char(?)) AS NAME, 
david@30927
   279
      A.C_OrderLine_ID,A.Line, uom.stdprecision AS stdprecision, cur.priceprecision, 
carlos@0
   280
      B.M_InOutLine_ID, A.Description, (CASE WHEN B.M_InOutLine_ID IS NULL THEN A.quantityOrder ELSE B.quantityOrder END) AS quantityOrder, 
eduardo@6416
   281
      (CASE WHEN B.M_InOutLine_ID IS NULL THEN A.M_Product_UOM_ID ELSE B.M_Product_UOM_ID END) AS M_Product_UOM_ID, A.M_ATTRIBUTESETINSTANCE_ID, A.ad_org_id,
david@30807
   282
      A.taxbaseamt, A.a_asset_id, A.c_project_id, A.c_costcenter_id, A.user1_id, A.user2_id, A.explode, 'Y' as isOrder,
david@30807
   283
      (CASE WHEN B.C_AUM IS NULL THEN A.C_AUM ELSE B.C_AUM END) AS C_AUM, (CASE WHEN B.AUMQTY IS NULL THEN A.AUMQTY ELSE B.AUMQTY END) AS AUMQTY, A.C_DOCTYPE_ID
harikrishnan@4494
   284
      FROM c_currency cur,C_UOM uom, M_PRODUCT p, 
pandeeswari@18864
   285
      (SELECT COALESCE(l.A_Asset_ID, o.A_Asset_ID) AS A_Asset_ID, COALESCE(l.C_Project_ID, o.C_Project_ID) AS C_Project_ID, COALESCE(l.C_Costcenter_ID, o.C_Costcenter_ID) AS C_Costcenter_ID,
naiara@19828
   286
       COALESCE(l.User1_ID, o.User1_ID) AS User1_ID, COALESCE(l.User2_ID, o.User2_ID) AS User2_ID, l.explode,l.ad_org_id,l.C_CURRENCY_ID,l.C_UOM_ID, l.M_Product_ID, l.Line, l.Description, l.C_OrderLine_ID, (l.QtyOrdered-SUM(COALESCE(mp.Qty,0))-
javier@15726
   287
       COALESCE((SELECT SUM(COALESCE(CI.QtyINVOICED, 0))  
javier@15726
   288
       FROM C_ORDERLINE CO LEFT JOIN C_INVOICELINE CI ON CI.C_ORDERLINE_ID = CO.C_ORDERLINE_ID
javier@15726
   289
       WHERE  CI.C_INVOICE_ID=? AND CO.C_ORDERLINE_ID = l.c_orderline_id
javier@15726
   290
       GROUP BY CI.C_orderline_id , CO.QtyORDERED),0)) AS QTY,
javier@15726
   291
      l.quantityOrder*C_DIVIDE((l.QtyOrdered-SUM(COALESCE(mp.Qty,0))-COALESCE((SELECT SUM(COALESCE(CI.QtyINVOICED, 0))  
javier@15726
   292
       FROM C_ORDERLINE CO LEFT JOIN C_INVOICELINE CI ON CI.C_ORDERLINE_ID = CO.C_ORDERLINE_ID
javier@15726
   293
       WHERE  CI.C_INVOICE_ID=? AND CO.C_ORDERLINE_ID = l.c_orderline_id
javier@15726
   294
       GROUP BY CI.C_orderline_id , CO.QtyORDERED),0)),l.QtyOrdered) as quantityOrder, 
david@30807
   295
       l.M_Product_UOM_ID, l.M_ATTRIBUTESETINSTANCE_ID, l.taxbaseamt, l.C_AUM, l.AUMQTY, o.C_DOCTYPE_ID
carlos@0
   296
        FROM C_ORDERLINE l left join M_MATCHPO mp on l.C_OrderLine_ID=mp.C_OrderLine_ID
carlos@0
   297
                                                  and mp.C_InvoiceLine_ID IS NOT NULL
pandeeswari@18864
   298
        LEFT JOIN C_Order o ON l.C_Order_ID = o.C_Order_ID
harikrishnan@4494
   299
        GROUP BY l.ad_org_id,l.C_CURRENCY_ID,l.C_UOM_ID, l.M_Product_ID, l.Line, l.Description, l.C_OrderLine_ID, l.QtyOrdered, l.quantityOrder, l.M_Product_UOM_ID,
pandeeswari@18864
   300
        l.M_ATTRIBUTESETINSTANCE_ID, l.taxbaseamt, COALESCE(l.A_Asset_ID, o.A_Asset_ID), COALESCE(l.C_Project_ID, o.C_Project_ID), COALESCE(l.C_Costcenter_ID, o.C_Costcenter_ID),
david@30807
   301
        COALESCE(l.User1_ID, o.User1_ID), COALESCE(l.User2_ID, o.User2_ID), l.explode, o.C_DOCTYPE_ID, l.c_aum, l.aumqty, o.c_doctype_id) A left join 
carlos@0
   302
      (SELECT il.M_InOutLine_ID, il.C_OrderLine_ID, (il.MovementQty-SUM(COALESCE(mi.Qty,0))) AS QTY,
david@30807
   303
      il.quantityOrder*C_DIVIDE((il.MovementQty-SUM(COALESCE(mi.Qty,0))),(il.MovementQty)) as quantityOrder, il.M_Product_UOM_ID,
david@30807
   304
      il.C_AUM, il.AUMQTY, io.C_DOCTYPE_ID
gorkaion@158
   305
        FROM M_INOUTLINE il left join M_MATCHINV mi on il.M_InOutLine_ID=mi.M_InOutLine_ID,
gorkaion@158
   306
             M_INOUT io
carlos@0
   307
        WHERE il.C_OrderLine_ID IS NOT NULL
gorkaion@158
   308
        AND io.M_InOut_ID = il.M_InOut_ID
gorkaion@158
   309
        AND io.Processed = 'Y'
markmm82@31822
   310
        AND io.docstatus <> 'VO'
david@30807
   311
        GROUP BY il.M_InOutLine_ID, il.C_OrderLine_ID, il.MovementQty, il.quantityOrder, il.M_Product_UOM_ID, io.C_DOCTYPE_ID, il.C_AUM, il.AUMQTY
carlos@0
   312
        HAVING (il.MovementQty-SUM(COALESCE(mi.Qty,0))) <> 0) B on A.C_OrderLine_ID=B.C_OrderLine_ID
carlos@0
   313
      WHERE A.C_UOM_ID=uom.C_UOM_ID
harikrishnan@4494
   314
      AND cur.C_CURRENCY_ID=A.C_CURRENCY_ID
carlos@0
   315
      AND A.M_Product_ID=p.M_Product_ID
carlos@0
   316
      ORDER BY A.Line
carlos@0
   317
      ]]>
carlos@0
   318
    </Sql>
jon@16083
   319
    <Parameter name="adLanguage"/>
javier@15726
   320
    <Parameter name="piId"/>
javier@15726
   321
    <Parameter name="piId"/>
carlos@0
   322
    <Parameter name="cOrderlineId" optional="true" type="argument" after="AND A.M_Product_ID=p.M_Product_ID" text=" AND A.C_OrderLine_ID IN "/>
carlos@0
   323
  </SqlMethod>
carlos@0
   324
  <SqlMethod name="selectFromPOUpdateSOTrx" type="preparedStatement" connection="true" return="multiple">
carlos@0
   325
    <SqlMethodComment></SqlMethodComment>
carlos@0
   326
    <Sql>
carlos@0
   327
      <![CDATA[
carlos@0
   328
        SELECT (CASE WHEN il.M_INOUTLINE_ID IS NULL THEN (l.QtyOrdered-COALESCE(l.QTYINVOICED ,0)) ELSE il.MOVEMENTQTY END) AS ID, 
jon@16083
   329
        l.C_UOM_ID,uom.UOMSymbol, l.M_Product_ID,Ad_Column_Identifier(to_char('M_Product'), to_char(l.m_product_id), to_char(?)) AS NAME, 
david@30927
   330
        l.C_OrderLine_ID,l.Line, max(uom.stdprecision) AS stdprecision, cur.priceprecision, il.M_InOutLine_ID as M_InOutLine_ID, l.Description,
gorkaion@1787
   331
        (CASE WHEN il.M_INOUTLINE_ID IS NULL THEN l.quantityOrder*C_DIVIDE((l.QtyOrdered-COALESCE(l.QTYINVOICED ,0)),(l.QtyOrdered)) ELSE il.quantityOrder END) AS quantityOrder, 
pandeeswari@22817
   332
        (CASE WHEN il.M_INOUTLINE_ID IS NULL THEN l.M_Product_UOM_ID ELSE il.M_Product_UOM_ID END) AS M_Product_UOM_ID, il.M_ATTRIBUTESETINSTANCE_ID, l.ad_org_id,
pandeeswari@18864
   333
        l.taxbaseamt, COALESCE(l.A_Asset_ID, o.A_Asset_ID) as A_Asset_ID, COALESCE(l.C_Project_ID, o.C_Project_ID) as C_Project_ID,
david@30807
   334
        COALESCE(l.C_Costcenter_ID, o.C_Costcenter_ID) as C_Costcenter_ID, COALESCE(l.User1_ID, o.User1_ID) as User1_ID, COALESCE(l.User2_ID, o.User2_ID) as User2_ID, l.explode, 'Y' as isOrder,
david@30807
   335
        l.C_AUM, l.AUMQTY, o.C_DOCTYPE_ID
gorkaion@158
   336
        FROM C_ORDERLINE l left join (SELECT M_InOutLine.* FROM M_InOutLine, M_InOut
gorkaion@158
   337
                                      WHERE M_InOut.M_InOut_ID = M_InOutLine.M_InOut_ID
gorkaion@158
   338
                                      AND M_InOutLine.IsInvoiced = 'N'
gorkaion@158
   339
                                      AND M_InOut.Processed='Y') il  on l.C_OrderLine_ID = il.C_OrderLine_ID,
pandeeswari@18864
   340
        C_UOM uom,  M_PRODUCT p  ,c_currency cur, c_order o
harikrishnan@4494
   341
        WHERE cur.C_CURRENCY_ID=l.C_CURRENCY_ID AND l.C_UOM_ID=uom.C_UOM_ID
pandeeswari@18864
   342
        AND l.C_Order_ID=o.C_Order_ID
carlos@0
   343
        AND l.M_Product_ID=p.M_Product_ID 
carlos@0
   344
        GROUP BY l.QtyOrdered,l.qtydelivered,l.C_UOM_ID,uom.UOMSymbol,l.M_Product_ID,p.NAME,l.Line,l.C_OrderLine_ID, 
carlos@0
   345
        l.QTYINVOICED, il.M_InOutLine_ID, il.MovementQty, l.Description, l.quantityOrder, il.quantityOrder, 
david@30927
   346
        l.M_Product_UOM_ID, il.M_Product_UOM_ID, il.M_ATTRIBUTESETINSTANCE_ID, l.ad_org_id, cur.priceprecision,
pandeeswari@18864
   347
        l.taxbaseamt, COALESCE(l.A_Asset_ID, o.A_Asset_ID), COALESCE(l.C_Project_ID, o.C_Project_ID), COALESCE(l.C_Costcenter_ID, o.C_Costcenter_ID),
david@30807
   348
        COALESCE(l.User1_ID, o.User1_ID), COALESCE(l.User2_ID, o.User2_ID), l.explode, o.C_DOCTYPE_ID, l.C_AUM, l.AUMQTY
naiara@19828
   349
        HAVING ( (l.explode='Y') OR ((l.QtyOrdered-COALESCE(l.QTYINVOICED ,0)) <> 0)) 
carlos@0
   350
        ORDER BY l.Line
carlos@0
   351
      ]]>
carlos@0
   352
    </Sql>
carlos@0
   353
    <Field name="rownum" value="count"/>
jon@16083
   354
    <Parameter name="adLanguage"/>
carlos@0
   355
    <Parameter name="cOrderlineId" optional="true" type="argument" after=" AND l.M_Product_ID=p.M_Product_ID " text=" AND l.C_OrderLine_ID IN "/>
carlos@0
   356
  </SqlMethod>
carlos@0
   357
  <SqlMethod name="selectFromShipment" type="preparedStatement" return="multiple">
carlos@0
   358
    <SqlMethodComment></SqlMethodComment>
carlos@0
   359
    <Sql>
carlos@0
   360
      <![CDATA[
carlos@0
   361
        SELECT s.M_InOut_ID AS ID, Ad_Column_Identifier(to_char('M_InOut'), to_char(s.M_InOut_ID), to_char(?)) AS NAME, 
carlos@0
   362
        s.DocumentNo AS NAMESHIPMENT, 
jon@16238
   363
        (l.MovementQty-SUM(COALESCE(mi.Qty,0))) AS QTY, l.C_UOM_ID,uom.UOMSymbol, l.M_Product_ID,Ad_Column_Identifier(to_char('M_Product'), to_char(l.m_product_id), to_char(?))||' '||(CASE WHEN ma.serno IS NOT NULL THEN TO_CHAR('('||' '||ma.serno||' '||')') ELSE TO_CHAR('')  END) AS RELATION_NAME, 
pandeeswari@18864
   364
        l.M_InOutLine_ID,l.Line, l.M_InOutLine_ID as C_OrderLine_ID,
pandeeswari@18864
   365
        COALESCE(l.A_Asset_ID, s.A_Asset_ID) AS A_Asset_ID, COALESCE(l.C_Project_ID, s.C_Project_ID) AS C_Project_ID, 
pandeeswari@18864
   366
        COALESCE(l.C_Costcenter_ID, s.C_Costcenter_ID) AS C_Costcenter_ID,
naiara@19828
   367
        COALESCE(l.User1_ID, s.User1_ID) AS User1_ID, COALESCE(l.User2_ID, s.User2_ID) AS User2_ID, l.explode, l.Bom_parent_id, 
david@30807
   368
        CASE WHEN l.Bom_parent_id is null then 'Y' else 'N' END as isparent,
david@30807
   369
        (CASE WHEN L.C_AUM IS NULL THEN M_GET_DEFAULT_AUM_FOR_DOCUMENT(L.M_PRODUCT_ID, s.C_DOCTYPE_ID) ELSE l.C_AUM END) AS C_AUM, 
david@30918
   370
        l.AUMQTY - SUM(COALESCE(M_GET_CONVERTED_AUMQTY(l.m_product_id, mi.Qty, l.c_aum), 0)) AS AUMQTY, s.C_DOCTYPE_ID,  
nonofce@30897
   371
        (CASE WHEN L.C_AUM IS NOT NULL THEN (SELECT UOMSYMBOL FROM C_UOM WHERE C_UOM_ID = L.C_AUM) ELSE (SELECT UOMSYMBOL FROM C_UOM WHERE C_UOM_ID = M_GET_DEFAULT_AUM_FOR_DOCUMENT(L.M_PRODUCT_ID, s.C_DOCTYPE_ID)) END) AS AUMSYMBOL,
nonofce@30897
   372
        'none' AS AUMVISIBLE
carlos@0
   373
        FROM M_INOUTLINE l left join M_ATTRIBUTESETINSTANCE ma on l.M_ATTRIBUTESETINSTANCE_ID = ma.M_ATTRIBUTESETINSTANCE_ID
gorkaion@17357
   374
                           left join M_MATCHINV mi             on l.M_InOutLine_ID=mi.M_InOutLine_ID
gorkaion@17357
   375
                           left join c_orderline ol on l.c_orderline_id = ol.c_orderline_id
gorkaion@17357
   376
                           left join c_order o on ol.c_order_id = o.c_order_id
gorkaion@17357
   377
                           left join m_pricelist pl on pl.m_pricelist_id = o.m_pricelist_id,
carlos@0
   378
        M_INOUT s, C_UOM uom,  M_PRODUCT p
carlos@0
   379
        WHERE s.M_INOUT_ID = l.M_INOUT_ID 
carlos@0
   380
        AND s.IsSOTrx='N' 
carlos@0
   381
        AND s.DocStatus IN ('CL','CO') 
carlos@0
   382
        AND s.AD_Client_ID IN ('1') 
carlos@0
   383
        AND s.AD_Org_ID IN ('1') 
carlos@0
   384
        AND l.C_UOM_ID=uom.C_UOM_ID 
carlos@0
   385
        AND l.M_Product_ID=p.M_Product_ID 
juanpablo@1605
   386
        AND l.M_InOut_ID=? 
gorkaion@17357
   387
        AND (l.c_orderline_id is null OR pl.istaxincluded = ?)
carlos@0
   388
        GROUP BY s.M_InOut_ID, s.DocumentNo, s.MovementDate, l.MovementQty,l.C_UOM_ID,uom.UOMSymbol,
pandeeswari@18864
   389
        l.M_Product_ID,ma.serno,p.NAME, l.M_InOutLine_ID,l.Line,l.C_OrderLine_ID,
pandeeswari@18864
   390
        COALESCE(l.A_Asset_ID, s.A_Asset_ID), COALESCE(l.C_Project_ID, s.C_Project_ID), COALESCE(l.C_Costcenter_ID, s.C_Costcenter_ID),
david@30807
   391
        COALESCE(l.User1_ID, s.User1_ID), COALESCE(l.User2_ID, s.User2_ID), l.explode, l.bom_parent_id, l.C_AUM, l.AUMQTY, s.C_DOCTYPE_ID
naiara@19828
   392
        HAVING  (((l.MovementQty-SUM(COALESCE(mi.Qty,0))) <> 0) OR (l.explode='Y'))
carlos@0
   393
        ORDER BY NAME, l.Line
carlos@0
   394
      ]]>
carlos@0
   395
    </Sql>
carlos@0
   396
    <Field name="rownum" value="count"/>
carlos@0
   397
    <Parameter name="adLanguage"/>
jon@16238
   398
    <Parameter name="adLanguage"/>
carlos@0
   399
    <Parameter name="adUserClient" type="replace" optional="true" after="s.AD_Client_ID IN (" text="'1'"/>
carlos@0
   400
    <Parameter name="adOrgClient" type="replace" optional="true" after="s.AD_Org_ID IN (" text="'1'"/>
carlos@0
   401
    <Parameter name="mInoutId"/>
gorkaion@17357
   402
    <Parameter name="isTaxIncluded"/>
carlos@0
   403
  </SqlMethod>
carlos@0
   404
  <SqlMethod name="selectFromShipmentCombo" type="preparedStatement" return="multiple">
carlos@0
   405
    <SqlMethodComment></SqlMethodComment>
carlos@0
   406
    <Sql>
carlos@0
   407
      <![CDATA[
carlos@0
   408
        SELECT s.M_InOut_ID AS ID, Ad_Column_Identifier(to_char('M_InOut'), to_char(s.M_InOut_ID), to_char(?)) AS NAME 
carlos@0
   409
        FROM M_INOUT s 
carlos@0
   410
        WHERE s.IsSOTrx='N' 
carlos@0
   411
        AND s.DocStatus IN ('CL','CO') 
david@1686
   412
        AND s.ISLOGISTIC <> 'Y'
carlos@0
   413
        AND s.AD_Client_ID IN ('1') 
carlos@0
   414
        AND s.AD_Org_ID IN ('1') 
juanpablo@1605
   415
        AND s.C_BPartner_ID = ? 
carlos@0
   416
        AND EXISTS (SELECT 1 
gorkaion@17357
   417
                     FROM M_INOUTLINE l
gorkaion@17357
   418
                           left join M_MATCHINV mi on l.M_InOutLine_ID=mi.M_InOutLine_ID
gorkaion@17357
   419
                           left join c_orderline ol on l.c_orderline_id = ol.c_orderline_id
staff@26037
   420
                           left join c_order o on ol.c_order_id = o.c_order_id AND o.InvoiceRule <> 'N'
gorkaion@17357
   421
                           left join m_pricelist pl on pl.m_pricelist_id = o.m_pricelist_id
carlos@0
   422
                      WHERE l.M_INOUT_ID = s.M_INOUT_ID 
gorkaion@17357
   423
                        AND (l.c_orderline_id is null OR pl.istaxincluded = ?)
carlos@0
   424
                      GROUP BY l.MovementQty, l.M_InOutLine_ID 
carlos@0
   425
                      HAVING  (l.MovementQty-SUM(COALESCE(mi.Qty,0))) <> 0)
carlos@0
   426
        ORDER BY NAME
carlos@0
   427
      ]]>
carlos@0
   428
    </Sql>
carlos@0
   429
    <Field name="rownum" value="count"/>
carlos@0
   430
    <Parameter name="adLanguage"/>
carlos@0
   431
    <Parameter name="adUserClient" type="replace" optional="true" after="s.AD_Client_ID IN (" text="'1'"/>
carlos@0
   432
    <Parameter name="adOrgClient" type="replace" optional="true" after="s.AD_Org_ID IN (" text="'1'"/>
carlos@0
   433
    <Parameter name="cBpartnerId"/>
gorkaion@17357
   434
    <Parameter name="isTaxIncluded"/>
carlos@0
   435
  </SqlMethod>
carlos@0
   436
  <SqlMethod name="selectFromShipmentSOTrx" type="preparedStatement" return="multiple">
carlos@0
   437
    <SqlMethodComment></SqlMethodComment>
carlos@0
   438
    <Sql>
carlos@0
   439
      <![CDATA[
carlos@0
   440
        SELECT s.M_InOut_ID AS ID, Ad_Column_Identifier(to_char('M_InOut'), to_char(s.M_InOut_ID), to_char(?)) AS NAME,
carlos@0
   441
        s.DocumentNo AS NAMESHIPMENT,
staff@19527
   442
        (l.MovementQty - sum(COALESCE(CASE WHEN i.docstatus = 'CO' THEN il.qtyinvoiced ELSE 0 END, 0))) AS QTY, l.C_UOM_ID,uom.UOMSymbol,
staff@19527
   443
        l.M_Product_ID,Ad_Column_Identifier(to_char('M_Product'), to_char(l.m_product_id), to_char(?))||' '||(CASE WHEN ma.serno IS NOT NULL THEN TO_CHAR('('||' '||ma.serno||' '||')') ELSE TO_CHAR('') END) AS RELATION_NAME, l.M_InOutLine_ID,l.Line,
mikel@18949
   444
        l.M_InOutLine_ID as C_OrderLine_ID,
pandeeswari@18864
   445
        COALESCE(l.A_Asset_ID, s.A_Asset_ID) AS A_Asset_ID, COALESCE(l.C_Project_ID, s.C_Project_ID) AS C_Project_ID, 
pandeeswari@18864
   446
        COALESCE(l.C_Costcenter_ID, s.C_Costcenter_ID) AS C_Costcenter_ID,
naiara@19828
   447
        COALESCE(l.User1_ID, s.User1_ID) AS User1_ID, COALESCE(l.User2_ID, s.User2_ID) AS User2_ID, l.explode, l.Bom_parent_id, 
david@30807
   448
        CASE WHEN l.Bom_parent_id is null then 'Y' else 'N' END as isparent,
david@30807
   449
        (CASE WHEN L.C_AUM IS NULL THEN M_GET_DEFAULT_AUM_FOR_DOCUMENT(L.M_PRODUCT_ID, s.C_DOCTYPE_ID) ELSE l.C_AUM END) AS C_AUM, 
david@30918
   450
        l.AUMQTY - SUM(COALESCE(CASE WHEN i.docstatus = 'CO' THEN M_GET_CONVERTED_AUMQTY(l.m_product_id, il.qtyinvoiced, l.c_aum) ELSE 0 END, 0)) AS AUMQTY, s.C_DOCTYPE_ID, 
nonofce@30897
   451
        (CASE WHEN L.C_AUM IS NOT NULL THEN (SELECT UOMSYMBOL FROM C_UOM WHERE C_UOM_ID = L.C_AUM) ELSE (SELECT UOMSYMBOL FROM C_UOM WHERE C_UOM_ID = M_GET_DEFAULT_AUM_FOR_DOCUMENT(L.M_PRODUCT_ID, s.C_DOCTYPE_ID)) END) AS AUMSYMBOL,
nonofce@30897
   452
        'none' AS AUMVISIBLE        
gorkaion@17357
   453
        FROM M_INOUTLINE l
gorkaion@17357
   454
                left join M_ATTRIBUTESETINSTANCE ma on l.M_ATTRIBUTESETINSTANCE_ID = ma.M_ATTRIBUTESETINSTANCE_ID
gorkaion@17357
   455
                left join c_orderline ol on l.c_orderline_id = ol.c_orderline_id
gorkaion@17357
   456
                left join c_order o on ol.c_order_id = o.c_order_id
staff@19527
   457
                left join m_pricelist pl on pl.m_pricelist_id = o.m_pricelist_id
staff@19527
   458
                left join c_invoiceline il on l.m_inoutline_id = il.m_inoutline_id
staff@19527
   459
                left join c_invoice i on il.c_invoice_id = i.c_invoice_id,
carlos@0
   460
        M_INOUT s, C_UOM uom, M_PRODUCT p 
carlos@0
   461
        WHERE s.M_INOUT_ID = l.M_INOUT_ID
carlos@0
   462
        AND s.IsSOTrx='Y' 
carlos@0
   463
        AND s.DocStatus IN ('CL','CO') 
carlos@0
   464
        AND s.AD_Client_ID IN ('1') 
carlos@0
   465
        AND s.AD_Org_ID IN ('1') 
carlos@0
   466
        AND l.C_UOM_ID=uom.C_UOM_ID 
carlos@0
   467
        AND l.M_Product_ID=p.M_Product_ID 
juanpablo@1605
   468
        AND l.M_InOut_ID=?
gorkaion@17357
   469
        AND (l.c_orderline_id is null OR pl.istaxincluded = ?)
carlos@0
   470
        GROUP BY s.M_InOut_ID, s.DocumentNo, s.MovementDate, l.MovementQty,l.C_UOM_ID,uom.UOMSymbol,
pandeeswari@18864
   471
        l.M_Product_ID,p.NAME, ma.serno, l.M_InOutLine_ID,l.Line,l.C_OrderLine_ID,
pandeeswari@18864
   472
        COALESCE(l.A_Asset_ID, s.A_Asset_ID), COALESCE(l.C_Project_ID, s.C_Project_ID), COALESCE(l.C_Costcenter_ID, s.C_Costcenter_ID),
david@30807
   473
        COALESCE(l.User1_ID, s.User1_ID), COALESCE(l.User2_ID, s.User2_ID), l.explode, l.Bom_parent_id, l.C_AUM, l.AUMQTY, s.C_DOCTYPE_ID
staff@19527
   474
        HAVING (l.movementqty >= 0 AND l.movementqty > SUM(COALESCE(CASE WHEN i.docstatus = 'CO' THEN il.qtyinvoiced ELSE 0 END, 0)))
staff@19527
   475
           OR (l.movementqty < 0 AND l.movementqty < SUM(COALESCE(CASE WHEN i.docstatus = 'CO' THEN il.qtyinvoiced ELSE 0 END, 0)))
naiara@19828
   476
           OR (l.explode='Y')
carlos@0
   477
        ORDER BY NAME, l.Line
carlos@0
   478
      ]]>
carlos@0
   479
    </Sql>
carlos@0
   480
    <Field name="rownum" value="count"/>
carlos@0
   481
    <Parameter name="adLanguage"/>
jon@16238
   482
    <Parameter name="adLanguage"/>
carlos@0
   483
    <Parameter name="adUserClient" type="replace" optional="true" after="s.AD_Client_ID IN (" text="'1'"/>
carlos@0
   484
    <Parameter name="adOrgClient" type="replace" optional="true" after="s.AD_Org_ID IN (" text="'1'"/>
carlos@0
   485
    <Parameter name="mInoutId"/>
gorkaion@17357
   486
    <Parameter name="isTaxIncluded"/>
staff@19527
   487
  </SqlMethod>
carlos@0
   488
  <SqlMethod name="selectFromShipmentSOTrxCombo" type="preparedStatement" return="multiple">
carlos@0
   489
    <SqlMethodComment></SqlMethodComment>
carlos@0
   490
    <Sql>
carlos@0
   491
      <![CDATA[
carlos@0
   492
        SELECT s.M_InOut_ID AS ID, Ad_Column_Identifier(to_char('M_InOut'), to_char(s.M_InOut_ID), to_char(?)) AS NAME 
carlos@0
   493
        FROM M_INOUT s 
carlos@0
   494
        WHERE s.IsSOTrx='Y' 
david@1686
   495
        AND s.ISLOGISTIC <> 'Y'
carlos@0
   496
        AND s.DocStatus IN ('CL','CO') 
carlos@0
   497
        AND s.AD_Client_ID IN ('1') 
carlos@0
   498
        AND s.AD_Org_ID IN ('1')
juanpablo@1605
   499
        AND s.C_BPartner_ID = ?  
carlos@0
   500
        AND EXISTS (SELECT 1 FROM M_INOUTLINE l 
gorkaion@17357
   501
                           left join c_orderline ol on l.c_orderline_id = ol.c_orderline_id
gorkaion@17357
   502
                           left join c_order o on ol.c_order_id = o.c_order_id
gorkaion@17357
   503
                           left join m_pricelist pl on pl.m_pricelist_id = o.m_pricelist_id
staff@19527
   504
                           left join c_invoiceline il on l.m_inoutline_id = il.m_inoutline_id
staff@19527
   505
                           left join c_invoice i on il.c_invoice_id = i.c_invoice_id
gorkaion@17357
   506
                    WHERE l.M_INOUT_ID = s.M_INOUT_ID
gorkaion@17357
   507
                    AND (l.c_orderline_id is null OR pl.istaxincluded = ?)
staff@19527
   508
                    GROUP BY l.m_inoutline_id, l.movementqty
staff@19527
   509
                    HAVING ( l.movementqty >= 0 AND l.movementqty > Sum(COALESCE(CASE WHEN i.docstatus = 'CO' THEN il.qtyinvoiced ELSE 0 END, 0)) )
staff@19527
   510
                      OR ( l.movementqty < 0 AND l.movementqty < Sum(COALESCE(CASE WHEN i.docstatus = 'CO' THEN il.qtyinvoiced ELSE 0 END, 0)) )
staff@19527
   511
                    )
atul@30803
   512
        AND NOT EXISTS (SELECT 1 FROM C_ORDER o WHERE o.C_ORDER_ID = s.C_ORDER_ID AND o.invoicerule = 'N')
javier@15683
   513
        AND NOT EXISTS (SELECT 1
javier@15163
   514
        FROM C_ORDER o , C_ORDERLINE ol
javier@15163
   515
        WHERE o.C_ORDER_ID = s.C_ORDER_ID
javier@15163
   516
        AND o.C_ORDER_ID = ol.C_ORDER_ID
javier@15164
   517
        GROUP BY o.INVOICERULE
javier@15683
   518
        HAVING (o.INVOICERULE = 'O' AND SUM(ol.qtyordered) <> SUM(ol.qtydelivered)))
carlos@0
   519
        ORDER BY NAME
carlos@0
   520
      ]]>
carlos@0
   521
    </Sql>
carlos@0
   522
    <Parameter name="adLanguage"/>
carlos@0
   523
    <Parameter name="adUserClient" type="replace" optional="true" after="s.AD_Client_ID IN (" text="'1'"/>
carlos@0
   524
    <Parameter name="adOrgClient" type="replace" optional="true" after="s.AD_Org_ID IN (" text="'1'"/>
carlos@0
   525
    <Parameter name="cBpartnerId"/>
gorkaion@17357
   526
    <Parameter name="isTaxIncluded"/>
carlos@0
   527
  </SqlMethod>
carlos@0
   528
  <SqlMethod name="selectFromShipmentTrl" type="preparedStatement" return="multiple">
carlos@0
   529
    <SqlMethodComment></SqlMethodComment>
carlos@0
   530
    <Sql>
carlos@0
   531
      <![CDATA[
carlos@0
   532
      SELECT s.M_InOut_ID AS ID, Ad_Column_Identifier(to_char('M_InOut'), to_char(s.M_InOut_ID), to_char(?)) AS NAME,
carlos@0
   533
      s.DocumentNo AS NAMESHIPMENT,
carlos@0
   534
        (l.MovementQty-SUM(COALESCE(mi.Qty,0))) AS QTY, l.C_UOM_ID, COALESCE(uomt.UOMSymbol, uom.UOMSymbol) AS UOMSymbol, 
pandeeswari@18817
   535
        l.M_Product_ID,Ad_Column_Identifier(to_char('M_Product'), to_char(l.m_product_id), to_char(?))||' '||(CASE WHEN ma.serno IS NOT NULL THEN TO_CHAR('('||' '||ma.serno||' '||')') ELSE TO_CHAR('')  END) AS RELATION_NAME, l.M_InOutLine_ID,l.Line, l.M_InOutLine_ID as C_OrderLine_ID,
pandeeswari@18864
   536
        COALESCE(l.A_Asset_ID, s.A_Asset_ID) AS A_Asset_ID, COALESCE(l.C_Project_ID, s.C_Project_ID) AS C_Project_ID, 
pandeeswari@18864
   537
        COALESCE(l.C_Costcenter_ID, s.C_Costcenter_ID) AS C_Costcenter_ID,
david@30807
   538
        COALESCE(l.User1_ID, s.User1_ID) AS User1_ID, COALESCE(l.User2_ID, s.User2_ID) AS User2_ID,
david@30807
   539
        (CASE WHEN L.C_AUM IS NULL THEN M_GET_DEFAULT_AUM_FOR_DOCUMENT(L.M_PRODUCT_ID, s.C_DOCTYPE_ID) ELSE l.C_AUM END) AS C_AUM, 
david@30918
   540
        l.AUMQTY - SUM(COALESCE(M_GET_CONVERTED_AUMQTY(l.m_product_id, mi.Qty, l.c_aum), 0)) AS AUMQTY, s.C_DOCTYPE_ID,  
nonofce@30897
   541
        (CASE WHEN L.C_AUM IS NOT NULL THEN (SELECT UOMSYMBOL FROM C_UOM WHERE C_UOM_ID = L.C_AUM) ELSE (SELECT UOMSYMBOL FROM C_UOM WHERE C_UOM_ID = M_GET_DEFAULT_AUM_FOR_DOCUMENT(L.M_PRODUCT_ID, s.C_DOCTYPE_ID)) END) AS AUMSYMBOL,
nonofce@30897
   542
        'none' AS AUMVISIBLE
gorkaion@17357
   543
        FROM M_INOUTLINE l
gorkaion@17357
   544
                left join M_ATTRIBUTESETINSTANCE ma on l.M_ATTRIBUTESETINSTANCE_ID = ma.M_ATTRIBUTESETINSTANCE_ID
gorkaion@17357
   545
                left join M_MATCHINV mi             on l.M_InOutLine_ID=mi.M_InOutLine_ID
gorkaion@17357
   546
                left join c_orderline ol on l.c_orderline_id = ol.c_orderline_id
gorkaion@17357
   547
                left join c_order o on ol.c_order_id = o.c_order_id
gorkaion@17357
   548
                left join m_pricelist pl on pl.m_pricelist_id = o.m_pricelist_id,
gorkaion@17357
   549
             C_UOM uom
gorkaion@17357
   550
                left join C_UOM_TRL uomt on uom.C_UOM_ID = uomt.C_UOM_ID and uomt.AD_Language = ?,
carlos@0
   551
        M_INOUT s,   M_PRODUCT p
carlos@0
   552
        WHERE s.M_INOUT_ID = l.M_INOUT_ID 
carlos@0
   553
        AND s.IsSOTrx='N' 
carlos@0
   554
        AND s.DocStatus IN ('CL','CO') 
carlos@0
   555
        AND s.AD_Client_ID IN ('1') 
carlos@0
   556
        AND s.AD_Org_ID IN ('1') 
carlos@0
   557
        AND l.C_UOM_ID=uom.C_UOM_ID 
carlos@0
   558
        AND l.M_Product_ID=p.M_Product_ID 
juanpablo@1605
   559
        AND l.M_InOut_ID=? 
gorkaion@17357
   560
        AND (l.c_orderline_id is null OR pl.istaxincluded = ?)
carlos@0
   561
        GROUP BY s.M_InOut_ID, s.DocumentNo, s.MovementDate, l.MovementQty,l.C_UOM_ID,uom.UOMSymbol, uomt.UOMSymbol,
pandeeswari@18823
   562
        l.M_Product_ID,p.NAME, ma.serno, l.M_InOutLine_ID,l.Line,l.C_OrderLine_ID,
pandeeswari@18864
   563
        COALESCE(l.A_Asset_ID, s.A_Asset_ID), COALESCE(l.C_Project_ID, s.C_Project_ID), COALESCE(l.C_Costcenter_ID, s.C_Costcenter_ID),
david@30807
   564
        COALESCE(l.User1_ID, s.User1_ID), COALESCE(l.User2_ID, s.User2_ID), l.C_AUM, l.AUMQTY, s.C_DOCTYPE_ID
carlos@0
   565
        HAVING  (l.MovementQty-SUM(COALESCE(mi.Qty,0))) <> 0
carlos@0
   566
        ORDER BY NAME, l.Line
carlos@0
   567
      ]]>
carlos@0
   568
    </Sql>
carlos@0
   569
    <Field name="rownum" value="count"/>
carlos@0
   570
    <Parameter name="adLanguage"/>
jon@16238
   571
    <Parameter name="adLanguage"/>
jon@16238
   572
    <Parameter name="adLanguage"/>
carlos@0
   573
    <Parameter name="adUserClient" type="replace" optional="true" after="s.AD_Client_ID IN (" text="'1'"/>
carlos@0
   574
    <Parameter name="adOrgClient" type="replace" optional="true" after="s.AD_Org_ID IN (" text="'1'"/>
carlos@0
   575
    <Parameter name="mInoutId"/>
gorkaion@17357
   576
    <Parameter name="isTaxIncluded"/>
ioritz@18528
   577
  </SqlMethod>  
carlos@0
   578
  <SqlMethod name="selectFromShipmentTrlSOTrx" type="preparedStatement" return="multiple">
carlos@0
   579
    <SqlMethodComment></SqlMethodComment>
carlos@0
   580
    <Sql>
carlos@0
   581
      <![CDATA[
carlos@0
   582
        SELECT s.M_InOut_ID AS ID, Ad_Column_Identifier(to_char('M_InOut'), to_char(s.M_InOut_ID), to_char(?)) AS NAME,
ioritz@19044
   583
        s.DocumentNo AS NAMESHIPMENT,
staff@19527
   584
        (l.MovementQty - SUM(COALESCE(CASE WHEN i.docstatus = 'CO' THEN il.qtyinvoiced ELSE 0 END, 0))) AS QTY, l.C_UOM_ID,COALESCE(uomt.UOMSymbol, uom.UOMSymbol) AS UOMSymbol, 
mikel@18910
   585
        l.M_Product_ID,Ad_Column_Identifier(to_char('M_Product'), to_char(l.m_product_id), to_char(?))||' '|| (CASE WHEN ma.serno IS NOT NULL THEN TO_CHAR('('||' '||ma.serno||' '||')') ELSE TO_CHAR('')  END) AS RELATION_NAME, l.M_InOutLine_ID,l.Line, l.M_InOutLine_ID as C_OrderLine_ID, 
pandeeswari@18864
   586
        COALESCE(l.A_Asset_ID, s.A_Asset_ID) AS A_Asset_ID, COALESCE(l.C_Project_ID, s.C_Project_ID) AS C_Project_ID, 
pandeeswari@18864
   587
        COALESCE(l.C_Costcenter_ID, s.C_Costcenter_ID) AS C_Costcenter_ID,
david@30807
   588
        COALESCE(l.User1_ID, s.User1_ID) AS User1_ID, COALESCE(l.User2_ID, s.User2_ID) AS User2_ID,
david@30807
   589
        (CASE WHEN L.C_AUM IS NULL THEN M_GET_DEFAULT_AUM_FOR_DOCUMENT(L.M_PRODUCT_ID, s.C_DOCTYPE_ID) ELSE l.C_AUM END) AS C_AUM, 
david@30918
   590
        l.AUMQTY - SUM(COALESCE(CASE WHEN i.docstatus = 'CO' THEN M_GET_CONVERTED_AUMQTY(l.m_product_id, il.qtyinvoiced, l.c_aum) ELSE 0 END, 0)) AS AUMQTY, s.C_DOCTYPE_ID,  
nonofce@30897
   591
        (CASE WHEN L.C_AUM IS NOT NULL THEN (SELECT UOMSYMBOL FROM C_UOM WHERE C_UOM_ID = L.C_AUM) ELSE (SELECT UOMSYMBOL FROM C_UOM WHERE C_UOM_ID = M_GET_DEFAULT_AUM_FOR_DOCUMENT(L.M_PRODUCT_ID, s.C_DOCTYPE_ID)) END) AS AUMSYMBOL,
nonofce@30897
   592
        'none' AS AUMVISIBLE
gorkaion@17357
   593
        FROM M_INOUTLINE l
gorkaion@17357
   594
              left join M_ATTRIBUTESETINSTANCE ma on l.M_ATTRIBUTESETINSTANCE_ID = ma.M_ATTRIBUTESETINSTANCE_ID
gorkaion@17357
   595
              left join c_orderline ol on l.c_orderline_id = ol.c_orderline_id
gorkaion@17357
   596
              left join c_order o on ol.c_order_id = o.c_order_id
staff@19527
   597
              left join m_pricelist pl on pl.m_pricelist_id = o.m_pricelist_id
staff@19527
   598
              left join c_invoiceline il on l.m_inoutline_id = il.m_inoutline_id
staff@19527
   599
              left join c_invoice i on il.c_invoice_id = i.c_invoice_id,
carlos@0
   600
             C_UOM uom     left join C_UOM_TRL uomt            on uom.C_UOM_ID = uomt.C_UOM_ID
carlos@0
   601
                                                              and uomt.AD_Language = ? ,
carlos@0
   602
        M_INOUT s,    M_PRODUCT p 
carlos@0
   603
        WHERE s.M_INOUT_ID = l.M_INOUT_ID
carlos@0
   604
        AND s.IsSOTrx='Y' 
carlos@0
   605
        AND s.DocStatus IN ('CL','CO') 
carlos@0
   606
        AND s.AD_Client_ID IN ('1') 
carlos@0
   607
        AND s.AD_Org_ID IN ('1') 
carlos@0
   608
        AND l.C_UOM_ID=uom.C_UOM_ID 
carlos@0
   609
        AND l.M_Product_ID=p.M_Product_ID 
juanpablo@1605
   610
        AND l.M_InOut_ID=? 
gorkaion@17357
   611
        AND (l.c_orderline_id is null OR pl.istaxincluded = ?)
carlos@0
   612
        GROUP BY s.M_InOut_ID, s.DocumentNo, s.MovementDate, l.MovementQty,l.C_UOM_ID,uom.UOMSymbol, uomt.UOMSymbol,
pandeeswari@18823
   613
        l.M_Product_ID,p.NAME,ma.serno, l.M_InOutLine_ID,l.Line,l.C_OrderLine_ID,
pandeeswari@18864
   614
        COALESCE(l.A_Asset_ID, s.A_Asset_ID), COALESCE(l.C_Project_ID, s.C_Project_ID), COALESCE(l.C_Costcenter_ID, s.C_Costcenter_ID),
david@30807
   615
        COALESCE(l.User1_ID, s.User1_ID), COALESCE(l.User2_ID, s.User2_ID), l.C_AUM, l.AUMQTY, s.C_DOCTYPE_ID
staff@19527
   616
        HAVING (l.movementqty >= 0 AND l.movementqty > SUM(COALESCE(CASE WHEN i.docstatus = 'CO' THEN il.qtyinvoiced ELSE 0 END, 0)))
staff@19527
   617
          OR (l.movementqty < 0 AND l.movementqty < SUM(COALESCE(CASE WHEN i.docstatus = 'CO' THEN il.qtyinvoiced ELSE 0 END, 0)))
carlos@0
   618
        ORDER BY NAME, l.Line
carlos@0
   619
      ]]>
carlos@0
   620
    </Sql>
carlos@0
   621
    <Field name="rownum" value="count"/>
carlos@0
   622
    <Parameter name="adLanguage"/>
jon@16238
   623
    <Parameter name="adLanguage"/>
jon@16238
   624
    <Parameter name="adLanguage"/>
carlos@0
   625
    <Parameter name="adUserClient" type="replace" optional="true" after="s.AD_Client_ID IN (" text="'1'"/>
carlos@0
   626
    <Parameter name="adOrgClient" type="replace" optional="true" after="s.AD_Org_ID IN (" text="'1'"/>
carlos@0
   627
    <Parameter name="mInoutId"/>
gorkaion@17357
   628
    <Parameter name="isTaxIncluded"/>
carlos@0
   629
  </SqlMethod>
carlos@0
   630
  <SqlMethod name="selectFromShipmentUpdate" type="preparedStatement" connection="true" return="multiple">
carlos@0
   631
    <SqlMethodComment></SqlMethodComment>
carlos@0
   632
    <Sql>
carlos@0
   633
      <![CDATA[
jon@16083
   634
      SELECT (l.MovementQty-SUM(COALESCE(mi.Qty,0))) AS ID, l.C_UOM_ID,uom.UOMSymbol, l.M_Product_ID,Ad_Column_Identifier(to_char('M_Product'), to_char(l.m_product_id), to_char(?)) AS NAME, 
jon@16083
   635
      l.M_InOutLine_ID,l.Line, l.C_OrderLine_ID, max(uom.STDPRECISION) as stdprecision, l.Description, l.quantityOrder*C_DIVIDE((l.MovementQty-SUM(COALESCE(mi.Qty,0))),l.MovementQty) as quantityOrder, 
pandeeswari@18817
   636
      l.M_Product_UOM_ID, l.M_ATTRIBUTESETINSTANCE_ID, l.ad_org_id, COALESCE(co.taxbaseamt,0) * C_DIVIDE((l.MovementQty-SUM(COALESCE(mi.Qty,0))),COALESCE(co.qtyordered,1)) as taxbaseamt,
pandeeswari@18864
   637
      COALESCE(l.A_Asset_ID, s.A_Asset_ID) AS A_Asset_ID, COALESCE(l.C_Project_ID, s.C_Project_ID) AS C_Project_ID, 
pandeeswari@18864
   638
      COALESCE(l.C_Costcenter_ID, s.C_Costcenter_ID) AS C_Costcenter_ID,
naiara@19828
   639
      COALESCE(l.User1_ID, s.User1_ID) AS User1_ID, COALESCE(l.User2_ID, s.User2_ID) AS User2_ID,
david@30807
   640
      l.explode, 'N' as isOrder,
david@30918
   641
      l.C_AUM, l.AUMQTY - SUM(COALESCE(M_GET_CONVERTED_AUMQTY(l.m_product_id, mi.Qty, l.c_aum), 0)) AS AUMQTY, s.C_DOCTYPE_ID
javier@15844
   642
      FROM M_INOUTLINE l left join M_MATCHINV mi on l.M_InOutLine_ID=mi.M_InOutLine_ID
javier@15844
   643
            left join C_ORDERLINE co ON co.C_ORDERLINE_ID=l.C_ORDERLINE_ID,
pandeeswari@18864
   644
      C_UOM uom,  M_PRODUCT p, M_InOut s
pandeeswari@18864
   645
      WHERE l.M_InOut_ID = s.M_InOut_ID
pandeeswari@18864
   646
      AND l.C_UOM_ID=uom.C_UOM_ID
carlos@0
   647
      AND l.M_Product_ID=p.M_Product_ID
pandeeswari@18823
   648
      GROUP BY l.MovementQty,l.C_UOM_ID,uom.UOMSymbol,l.M_Product_ID,p.NAME, l.M_InOutLine_ID,l.Line,l.C_OrderLine_ID, l.Description,l.quantityOrder, l.M_Product_UOM_ID, l.M_ATTRIBUTESETINSTANCE_ID, l.ad_org_id, co.taxbaseamt,co.qtyordered,
pandeeswari@18864
   649
      COALESCE(l.A_Asset_ID, s.A_Asset_ID), COALESCE(l.C_Project_ID, s.C_Project_ID), COALESCE(l.C_Costcenter_ID, s.C_Costcenter_ID),
david@30807
   650
      COALESCE(l.User1_ID, s.User1_ID), COALESCE(l.User2_ID, s.User2_ID),l.explode, s.C_DOCTYPE_ID, l.C_AUM, l.AUMQTY
carlos@0
   651
      ORDER BY l.Line
carlos@0
   652
      ]]>
carlos@0
   653
    </Sql>
jon@16083
   654
    <Parameter name="adLanguage"/>
david@7243
   655
    <Parameter name="cInOutLineId" optional="true" type="argument" after="AND l.M_Product_ID=p.M_Product_ID" text=" AND l.M_InOutLine_ID IN "/>
carlos@0
   656
  </SqlMethod>
carlos@0
   657
  <SqlMethod name="selectFromShipmentUpdateSOTrx" type="preparedStatement" connection="true" return="multiple">
carlos@0
   658
    <SqlMethodComment></SqlMethodComment>
carlos@0
   659
    <Sql>
carlos@0
   660
      <![CDATA[
staff@19527
   661
        SELECT (l.MovementQty - sum(COALESCE(CASE WHEN i.docstatus = 'CO' THEN il.qtyinvoiced ELSE 0 END, 0))) AS ID,
staff@19527
   662
        l.C_UOM_ID,uom.UOMSymbol, l.M_Product_ID,Ad_Column_Identifier(to_char('M_Product'), to_char(l.m_product_id), to_char(?)) AS NAME, l.M_InOutLine_ID,l.Line,
markmm82@27990
   663
        l.C_OrderLine_ID, max(uom.STDPRECISION) as stdprecision, l.Description,
markmm82@27990
   664
		(l.quantityOrder - sum(COALESCE(CASE WHEN i.docstatus = 'CO' THEN il.quantityorder ELSE 0 END, 0))) AS quantityOrder, 
markmm82@27990
   665
		l.M_Product_UOM_ID,
pandeeswari@18817
   666
        l.M_ATTRIBUTESETINSTANCE_ID, l.ad_org_id,
pandeeswari@18864
   667
        COALESCE(l.A_Asset_ID, s.A_Asset_ID) AS A_Asset_ID, COALESCE(l.C_Project_ID, s.C_Project_ID) AS C_Project_ID, 
pandeeswari@18864
   668
        COALESCE(l.C_Costcenter_ID, s.C_Costcenter_ID) AS C_Costcenter_ID,
naiara@19828
   669
        COALESCE(l.User1_ID, s.User1_ID) AS User1_ID, COALESCE(l.User2_ID, s.User2_ID) AS User2_ID, 
david@30807
   670
        l.explode, 'N' as isOrder,
david@30918
   671
        l.C_AUM, l.AUMQTY - SUM(COALESCE(CASE WHEN i.docstatus = 'CO' THEN M_GET_CONVERTED_AUMQTY(l.m_product_id, il.qtyinvoiced, l.c_aum) ELSE 0 END, 0)) AS AUMQTY, s.C_DOCTYPE_ID
staff@19527
   672
        FROM C_UOM uom, M_INOUTLINE l
staff@19527
   673
          left join c_invoiceline il on l.m_inoutline_id = il.m_inoutline_id
staff@19527
   674
          left join c_invoice i on il.c_invoice_id = i.c_invoice_id 
staff@19527
   675
        , M_PRODUCT p , M_InOut s
pandeeswari@18864
   676
        WHERE l.M_InOut_ID = s.M_InOut_ID
pandeeswari@18864
   677
        AND l.C_UOM_ID=uom.C_UOM_ID
carlos@0
   678
        AND l.M_Product_ID=p.M_Product_ID 
ioritz@19044
   679
        GROUP BY l.MovementQty,l.C_UOM_ID,uom.UOMSymbol,l.M_Product_ID,p.NAME, l.M_InOutLine_ID,l.Line,l.C_OrderLine_ID, l.Description, l.quantityOrder, l.M_Product_UOM_ID, l.M_ATTRIBUTESETINSTANCE_ID, l.ad_org_id, l.m_inout_id,
mikel@18949
   680
        COALESCE(l.A_Asset_ID, s.A_Asset_ID), COALESCE(l.C_Project_ID, s.C_Project_ID), COALESCE(l.C_Costcenter_ID, s.C_Costcenter_ID),
david@30807
   681
        COALESCE(l.User1_ID, s.User1_ID), COALESCE(l.User2_ID, s.User2_ID), l.explode, s.C_DOCTYPE_ID, l.C_AUM, l.AUMQTY
staff@19527
   682
        HAVING (l.movementqty >= 0 AND l.movementqty > SUM(COALESCE(CASE WHEN i.docstatus = 'CO' THEN il.qtyinvoiced ELSE 0 END, 0)))
staff@19527
   683
          OR (l.movementqty < 0 AND l.movementqty < SUM(COALESCE(CASE WHEN i.docstatus = 'CO' THEN il.qtyinvoiced ELSE 0 END, 0)))
naiara@19828
   684
          OR(l.explode='Y') 
carlos@0
   685
        ORDER BY l.Line
carlos@0
   686
      ]]>
carlos@0
   687
    </Sql>
carlos@0
   688
    <Field name="rownum" value="count"/>
jon@16083
   689
    <Parameter name="adLanguage"/>
ioritz@19044
   690
    <Parameter name="cOrderlineId" optional="true" type="argument" after="AND l.M_Product_ID=p.M_Product_ID " text=" AND l.M_InOutLine_ID IN "/>
carlos@0
   691
  </SqlMethod>
carlos@0
   692
  <SqlMethod name="set" type="constant" return="multiple">
carlos@0
   693
      <SqlMethodComment></SqlMethodComment>
carlos@0
   694
      <Sql></Sql>
carlos@0
   695
  </SqlMethod>
carlos@0
   696
  <SqlMethod name="selectPriceList" type="preparedStatement" connection="true" return="multiple">
carlos@0
   697
    <SqlMethodComment></SqlMethodComment>
carlos@0
   698
    <Sql>
carlos@0
   699
      <![CDATA[
david@30927
   700
      SELECT plv.M_PriceList_Version_ID AS ID, cur.Priceprecision
rafael@5919
   701
      FROM M_PRICELIST pl, M_PRICELIST_VERSION plv, C_CURRENCY cur
rafael@5919
   702
      WHERE pl.M_PriceList_ID = plv.M_PriceList_ID 
rafael@5919
   703
      AND pl.C_Currency_ID = cur.C_Currency_ID
rafael@5919
   704
      AND plv.IsActive='Y'
juanpablo@1605
   705
      AND plv.ValidFrom <= TO_DATE(?) AND pl.M_PriceList_ID=? 
carlos@0
   706
      ORDER BY plv.ValidFrom DESC
carlos@0
   707
      ]]>
carlos@0
   708
    </Sql>
carlos@0
   709
    <Parameter name="validfrom"/>
carlos@0
   710
    <Parameter name="mPricelistId"/>
carlos@0
   711
  </SqlMethod>
carlos@0
   712
carlos@0
   713
  <SqlMethod name="selectPrices" type="preparedStatement" connection="true" return="multiple">
carlos@0
   714
    <SqlMethodComment></SqlMethodComment>
carlos@0
   715
    <Sql>
carlos@0
   716
      <![CDATA[
gorkaion@17357
   717
      SELECT PriceActual, PriceList, PriceLimit, Description,
gorkaion@17357
   718
          '' AS C_ORDERLINE_ID, PriceStd, CancelPriceAD,
david@18197
   719
          gross_unit_price, COALESCE(grosspricelist,0) AS grosspricelist, COALESCE(grosspricestd,0) AS grosspricestd
carlos@0
   720
      FROM C_OrderLine 
juanpablo@1605
   721
      WHERE C_OrderLine_ID=?
carlos@0
   722
      ]]>
carlos@0
   723
    </Sql>
carlos@0
   724
    <Parameter name="cOrderlineId"/>
carlos@0
   725
  </SqlMethod>
carlos@0
   726
  <SqlMethod name="selectBOM" type="preparedStatement" connection="true" return="multiple">
carlos@0
   727
    <SqlMethodComment></SqlMethodComment>
carlos@0
   728
    <Sql>
carlos@0
   729
      <![CDATA[
gorkaion@1787
   730
      SELECT M_BOM_PriceStd(pp.M_Product_ID,pp.M_PriceList_Version_ID) AS PriceStd,
gorkaion@1787
   731
      M_BOM_PriceList(pp.M_Product_ID,pp.M_PriceList_Version_ID) AS PriceList,
gorkaion@1787
   732
      M_BOM_PriceLimit(pp.M_Product_ID,pp.M_PriceList_Version_ID) AS PriceLimit, 
gorkaion@1787
   733
      M_GET_OFFER_PRICE(M_GET_OFFER(to_date(?), ?, pp.M_Product_ID), M_BOM_PriceStd(pp.M_Product_ID,pp.M_PriceList_Version_ID)) AS PriceActual, 
juanpablo@1605
   734
      M_GET_OFFER(to_date(?), ?, pp.M_Product_ID) AS M_Offer_ID
carlos@0
   735
      FROM M_PRODUCTPRICE pp 
juanpablo@1605
   736
      WHERE pp.M_Product_ID=? 
juanpablo@1605
   737
      AND pp.M_PriceList_Version_ID=? 
carlos@0
   738
      ]]>
carlos@0
   739
    </Sql>
carlos@0
   740
    <Parameter name="dateinvoiced"/>
carlos@0
   741
    <Parameter name="cBpartnerId"/>
carlos@0
   742
    <Parameter name="dateinvoiced"/>
carlos@0
   743
    <Parameter name="cBpartnerId"/>
carlos@0
   744
    <Parameter name="mProductId"/>
carlos@0
   745
    <Parameter name="mPricelistVersionId"/>
carlos@0
   746
  </SqlMethod>
carlos@0
   747
  <SqlMethod name="insert" type="preparedStatement" connection="true" return="rowCount">
carlos@0
   748
    <SqlMethodComment></SqlMethodComment>
carlos@0
   749
    <Sql>
juanpablo@1605
   750
      INSERT INTO C_INVOICELINE 
juanpablo@1605
   751
        (C_InvoiceLine_ID,C_Invoice_ID, AD_Client_ID,AD_Org_ID,IsActive,
juanpablo@1605
   752
        Created,CreatedBy,Updated,UpdatedBy, 
juanpablo@1605
   753
        C_OrderLine_ID,M_InOutLine_ID, Line,Description, M_Product_ID,
juanpablo@1605
   754
        C_UOM_ID,QtyInvoiced, PriceList,
juanpablo@1605
   755
        PriceActual,PriceLimit, LineNetAmt,
eduardo@18445
   756
        ChargeAmt,C_Tax_ID, taxamt , QuantityOrder, M_Product_UOM_ID,
gorkaion@17357
   757
        M_Attributesetinstance_ID, PriceStd, taxbaseamt, excludeforwithholding,
eduardo@18445
   758
        gross_unit_price, line_gross_amount, grosspricelist, grosspricestd,
mikel@18839
   759
        isdeferred, defplantype, periodnumber, c_period_id,
naiara@19828
   760
        a_asset_id, c_project_id, c_costcenter_id, user1_id, user2_id, explode, 
david@30807
   761
        BOM_Parent_id,
david@30807
   762
        C_AUM, AUMQTY
eduardo@18445
   763
        )
juanpablo@1605
   764
      VALUES 
juanpablo@1605
   765
        (?, ?, ?, ?, 'Y', 
juanpablo@1605
   766
         now(), ?, now(), ?, 
juanpablo@1605
   767
         ?, ?, (SELECT (COALESCE(Max(Line),0))+10 FROM C_InvoiceLine WHERE C_Invoice_ID=?), ?, ?, 
juanpablo@1605
   768
         ?, TO_NUMBER(?), TO_NUMBER(?), 
naiara@16816
   769
         TO_NUMBER(?), TO_NUMBER(?), TO_NUMBER(?),
naiara@16816
   770
         0, ?, TO_NUMBER(?), TO_NUMBER(?), ?, 
gorkaion@17357
   771
         ?, TO_NUMBER(?), TO_NUMBER(?), 'N',
eduardo@18445
   772
         TO_NUMBER(?), TO_NUMBER(?), TO_NUMBER(?), TO_NUMBER(?),
mikel@18839
   773
         ?, ?, TO_NUMBER(?), ?,
naiara@19828
   774
         ?, ?, ?, ?, ?, ?,
naiara@19828
   775
         (CASE to_char(?) WHEN 'Y' THEN (SELECT c_invoiceline_id 
naiara@19828
   776
                                FROM c_invoiceline 
naiara@19828
   777
                                WHERE c_invoice_id=? 
naiara@19828
   778
                                   and c_orderline_id = (SELECT BOM_parent_id 
naiara@19828
   779
                                                         FROM c_orderline 
naiara@19828
   780
                                                         WHERE c_orderline_id = ?))
naiara@19828
   781
          ELSE (SELECT c_invoiceline_id 
naiara@19828
   782
                FROM c_invoiceline 
naiara@19828
   783
                WHERE c_invoice_id=? 
naiara@19828
   784
                and m_inoutline_id = (SELECT BOM_parent_id 
naiara@19828
   785
                                      FROM m_inoutline 
naiara@19828
   786
                                      WHERE m_inoutline_id = ?)) END
david@30807
   787
         ), ?, TO_NUMBER(?)
naiara@19828
   788
        )
carlos@0
   789
    </Sql>
carlos@0
   790
    <Parameter name="cInvoicelineId"/>
carlos@0
   791
    <Parameter name="cInvoiceId"/>
carlos@0
   792
    <Parameter name="adClientId"/>
carlos@0
   793
    <Parameter name="adOrgId"/>
carlos@0
   794
    <Parameter name="adUserId"/>
carlos@0
   795
    <Parameter name="adUserId"/>
carlos@0
   796
    <Parameter name="cOrderlineId"/>
carlos@0
   797
    <Parameter name="mInoutlineId"/>
carlos@0
   798
    <Parameter name="cInvoiceId"/>
carlos@0
   799
    <Parameter name="description"/>
carlos@0
   800
    <Parameter name="mProductId"/>
carlos@0
   801
    <Parameter name="cUomId"/>
carlos@0
   802
    <Parameter name="qtyinvoiced"/>
carlos@0
   803
    <Parameter name="pricelist"/>
carlos@0
   804
    <Parameter name="priceactual"/>
carlos@0
   805
    <Parameter name="pricelimit"/>
carlos@0
   806
    <Parameter name="linenetamt"/>
carlos@0
   807
    <Parameter name="cTaxId"/>
naiara@16816
   808
    <Parameter name="taxAmt"/>
carlos@0
   809
    <Parameter name="quantityOrder"/>
carlos@0
   810
    <Parameter name="mProductUomId"/>
carlos@0
   811
    <Parameter name="mAttributesetinstanceId"/>
carlos@0
   812
    <Parameter name="pricestd"/>
eduardo@6416
   813
    <Parameter name="taxBaseAmt"/>
gorkaion@17357
   814
    <Parameter name="grossUnitPrice"/>
gorkaion@17357
   815
    <Parameter name="grossAmount"/>
david@18197
   816
    <Parameter name="priceListGross"/>
david@18197
   817
    <Parameter name="priceStdGross"/>
eduardo@18445
   818
    <Parameter name="isDeferredRevenue"/>
eduardo@18445
   819
    <Parameter name="revplantype"/>
eduardo@18445
   820
    <Parameter name="periodnumber"/>
eduardo@18445
   821
    <Parameter name="cPeriodId"/>
pandeeswari@18817
   822
    <Parameter name="aAssetId"/>
pandeeswari@18817
   823
    <Parameter name="cProjectId"/>
pandeeswari@18817
   824
    <Parameter name="cCostcenterId"/>
eduardo@18848
   825
    <Parameter name="user1Id"/>
eduardo@18849
   826
    <Parameter name="user2Id"/>
naiara@19828
   827
    <Parameter name="explode"/>
naiara@19828
   828
    <Parameter name="isOrder"/>
naiara@19828
   829
    <Parameter name="cInvoiceId"/>
naiara@19828
   830
    <Parameter name="cOrderlineId"/>
naiara@19828
   831
    <Parameter name="cInvoiceId"/>
naiara@19828
   832
    <Parameter name="mInoutlineId"/>
david@30807
   833
    <Parameter name="cAum"/>
david@30807
   834
    <Parameter name="aumQty"/>
pandeeswari@18817
   835
  </SqlMethod>
pandeeswari@18817
   836
  <SqlMethod name="insertAcctDimension" type="preparedStatement" connection="true" return="rowCount">
pandeeswari@18817
   837
    <SqlMethodComment>Copies the order line accounting dimensions to invoice lines</SqlMethodComment>
pandeeswari@18817
   838
    <Sql>
pandeeswari@18817
   839
      INSERT INTO C_INVOICELINE_ACCTDIMENSION
pandeeswari@18817
   840
        (C_InvoiceLine_AcctDimension_ID,C_InvoiceLine_ID, AD_Client_ID,AD_Org_ID,IsActive,
pandeeswari@18817
   841
        Created,CreatedBy,Updated,UpdatedBy,
pandeeswari@18817
   842
        C_Project_ID, C_Campaign_ID, User1_ID,
pandeeswari@18817
   843
        User2_ID, C_Activity_ID, C_Costcenter_ID,
pandeeswari@18817
   844
        C_BPartner_ID, M_Product_ID, A_Asset_ID, Amt
pandeeswari@18817
   845
        )
pandeeswari@18817
   846
        (SELECT GET_UUID(), ?, ?, ?, 'Y',
pandeeswari@18817
   847
         now(), ?, now(), ?, C_Project_ID, C_Campaign_ID,
eduardo@18849
   848
         User1_ID, User2_ID, C_Activity_ID, C_Costcenter_Id,
pandeeswari@18817
   849
         C_BPartner_ID, M_Product_ID, A_Asset_ID, Amt
pandeeswari@18817
   850
         FROM C_OrderLine_AcctDimension
pandeeswari@18817
   851
         WHERE C_OrderLine_ID = ?
pandeeswari@18817
   852
         )
pandeeswari@18817
   853
    </Sql>
pandeeswari@18817
   854
    <Parameter name="cInvoicelineId"/>
pandeeswari@18817
   855
    <Parameter name="adClientId"/>
pandeeswari@18817
   856
    <Parameter name="adOrgId"/>
pandeeswari@18817
   857
    <Parameter name="adUserId"/>
pandeeswari@18817
   858
    <Parameter name="adUserId"/>
pandeeswari@18817
   859
    <Parameter name="cOrderlineId"/>
pandeeswari@18817
   860
  </SqlMethod>
pandeeswari@18817
   861
  <SqlMethod name="insertShipmentAcctDimension" type="preparedStatement" connection="true" return="rowCount">
pandeeswari@18817
   862
    <SqlMethodComment>Copies the in/out line accounting dimensions to invoice lines</SqlMethodComment>
pandeeswari@18817
   863
    <Sql>
pandeeswari@18817
   864
      INSERT INTO C_INVOICELINE_ACCTDIMENSION
pandeeswari@18817
   865
        (C_InvoiceLine_AcctDimension_ID,C_InvoiceLine_ID, AD_Client_ID,AD_Org_ID,IsActive,
pandeeswari@18817
   866
        Created,CreatedBy,Updated,UpdatedBy,
pandeeswari@18817
   867
        C_Project_ID, C_Campaign_ID, User1_ID,
pandeeswari@18817
   868
        User2_ID, C_Activity_ID, C_Costcenter_ID,
pandeeswari@18817
   869
        C_BPartner_ID, M_Product_ID, A_Asset_ID,
pandeeswari@18817
   870
        Amt
pandeeswari@18817
   871
        )
pandeeswari@18817
   872
        (SELECT GET_UUID(), ?, ?, ?, 'Y',
pandeeswari@18817
   873
         now(), ?, now(), ?, C_Project_ID, C_Campaign_ID,
eduardo@18849
   874
         User1_ID, User2_ID, C_Activity_ID, C_Costcenter_Id,
pandeeswari@18817
   875
         C_BPartner_ID, M_Product_ID, A_Asset_ID,
pandeeswari@18817
   876
         (Quantity*(SELECT PriceActual FROM C_InvoiceLine WHERE C_InvoiceLine_ID = ?))
pandeeswari@18817
   877
         FROM M_InOutLine_AcctDimension
pandeeswari@18817
   878
         WHERE M_InOutLine_ID = ?
pandeeswari@18817
   879
         )
pandeeswari@18817
   880
    </Sql>
pandeeswari@18817
   881
    <Parameter name="cInvoicelineId"/>
pandeeswari@18817
   882
    <Parameter name="adClientId"/>
pandeeswari@18817
   883
    <Parameter name="adOrgId"/>
pandeeswari@18817
   884
    <Parameter name="adUserId"/>
pandeeswari@18817
   885
    <Parameter name="adUserId"/>
pandeeswari@18817
   886
    <Parameter name="cInvoicelineId"/>
pandeeswari@18817
   887
    <Parameter name="mInoutlineId"/>
carlos@0
   888
  </SqlMethod>
carlos@0
   889
  <SqlMethod name="updateC_Order_ID" type="preparedStatement" connection="true" return="rowCount">
carlos@0
   890
    <SqlMethodComment></SqlMethodComment>
carlos@0
   891
    <Sql><![CDATA[
juanpablo@1605
   892
      UPDATE C_Invoice SET C_Order_id = ?
juanpablo@1605
   893
      WHERE C_Invoice_ID = ?
carlos@0
   894
    ]]></Sql>
carlos@0
   895
    <Parameter name="cOrderId"/>
carlos@0
   896
    <Parameter name="cInvoiceId"/>
carlos@0
   897
  </SqlMethod>
carlos@0
   898
  <SqlMethod name="deleteC_Order_ID" type="preparedStatement" connection="true" return="rowCount">
carlos@0
   899
    <SqlMethodComment></SqlMethodComment>
carlos@0
   900
    <Sql> <![CDATA[
carlos@0
   901
      UPDATE C_INVOICE SET C_Order_ID=NULL
juanpablo@1605
   902
      WHERE C_Invoice_ID=?
juanpablo@1605
   903
      AND C_Order_ID IS NOT NULL AND C_Order_ID <> ?
carlos@0
   904
    ]]></Sql>
carlos@0
   905
    <Parameter name="cInvoiceId"/>
carlos@0
   906
    <Parameter name="cOrderId"/>
carlos@0
   907
  </SqlMethod>
carlos@0
   908
  <SqlMethod name="selectProject" type="preparedStatement" return="String" default="">
carlos@0
   909
    <SqlMethodComment></SqlMethodComment>
carlos@0
   910
    <Sql><![CDATA[
carlos@0
   911
      SELECT C_Project_ID
carlos@0
   912
      FROM C_INVOICE
juanpablo@1605
   913
      WHERE C_INVOICE_ID = ? 
carlos@0
   914
    ]]></Sql>
carlos@0
   915
    <Parameter name="cInvoiceId"/>
carlos@0
   916
  </SqlMethod>
carlos@0
   917
  <SqlMethod name="getTax" type="preparedStatement" return="String" default="">
carlos@0
   918
    <SqlMethodComment></SqlMethodComment>
carlos@0
   919
    <Sql><![CDATA[
carlos@0
   920
      SELECT C_Tax_ID
carlos@0
   921
      FROM C_ORDERLINE
juanpablo@1605
   922
      WHERE C_ORDERLINE_ID = ? 
carlos@0
   923
    ]]></Sql>
carlos@0
   924
    <Parameter name="cOrderLineId"/>
carlos@0
   925
  </SqlMethod>
harikrishnan@7218
   926
  <SqlMethod name="getOffersPriceInvoice" type="preparedStatement" return="String" default="0">
harikrishnan@7218
   927
    <SqlMethodComment></SqlMethodComment>
harikrishnan@7218
   928
    <Sql>
harikrishnan@7218
   929
      SELECT ROUND(M_GET_OFFERS_PRICE(TO_DATE(?),?,?,TO_NUMBER(?), TO_NUMBER(?), ?),
harikrishnan@7218
   930
                    (SELECT PRICEPRECISION 
harikrishnan@7218
   931
                       FROM C_CURRENCY C,
harikrishnan@7218
   932
                            C_INVOICE  I 
harikrishnan@7218
   933
                      WHERE C.C_CURRENCY_ID = I.C_CURRENCY_ID
harikrishnan@7218
   934
                        AND I.C_INVOICE_ID  = ?)) AS TOTAL FROM DUAL
harikrishnan@7218
   935
    </Sql>
harikrishnan@7218
   936
    <Parameter name="dateordered"/>
harikrishnan@7218
   937
    <Parameter name="cBpartnerId"/>
harikrishnan@7218
   938
    <Parameter name="mProductId"/>
harikrishnan@7218
   939
    <Parameter name="pricestd"/>
harikrishnan@7218
   940
    <Parameter name="qty"/>
harikrishnan@7218
   941
    <Parameter name="pricelist"/>
harikrishnan@7218
   942
    <Parameter name="invoiceid"/>
harikrishnan@7218
   943
  </SqlMethod>
naiara@16816
   944
  <SqlMethod name="selectTaxRate" type="preparedStatement" return="String" default="">
naiara@16816
   945
    <SqlMethodComment></SqlMethodComment>
naiara@16816
   946
    <Sql>
naiara@16816
   947
      SELECT Rate
naiara@16816
   948
      FROM C_TAX
naiara@16816
   949
      WHERE C_Tax_ID = ?
naiara@16816
   950
    </Sql>
naiara@16816
   951
    <Parameter name="cTaxId"/>
naiara@16816
   952
  </SqlMethod>
sandra@25582
   953
    <SqlMethod name="selectPrepaymentAmt" type="preparedStatement" return="String" default="">
sandra@25582
   954
    <SqlMethodComment></SqlMethodComment>
sandra@25582
   955
    <Sql>
sandra@25582
   956
     select paidamt from FIN_Payment_Schedule  ps
sandra@25582
   957
     where ps.C_Order_ID=?
sandra@25582
   958
    </Sql>
sandra@25582
   959
    <Parameter name="cOrderId"/>
sandra@25582
   960
  </SqlMethod>
sandra@25641
   961
    <SqlMethod name="selectInvoicePrepaymentAmt" type="preparedStatement" return="String" default="">
sandra@25641
   962
    <SqlMethodComment></SqlMethodComment>
sandra@25641
   963
    <Sql>
sandra@25641
   964
     select prepaymentamt from c_invoice 
sandra@25641
   965
     where c_invoice_id=?
sandra@25641
   966
    </Sql>
sandra@25641
   967
    <Parameter name="cOrderId"/>
sandra@25641
   968
  </SqlMethod>
sandra@25582
   969
  <SqlMethod name="updatePrepaymentAmt" type="preparedStatement" connection="true" return="rowCount">
sandra@25582
   970
    <SqlMethodComment></SqlMethodComment>
sandra@25582
   971
    <Sql><![CDATA[
sandra@25641
   972
      UPDATE C_Invoice SET prepaymentamt =  TO_NUMBER(?)
sandra@25582
   973
      WHERE C_Invoice_ID = ?
sandra@25582
   974
    ]]></Sql>
sandra@25582
   975
    <Parameter name="prepaymentamt"/>
sandra@25582
   976
    <Parameter name="cInvoiceId"/>
sandra@25582
   977
  </SqlMethod>
carlos@0
   978
</SqlClass>