src/org/openbravo/erpCommon/ad_actionButton/CreateFrom_Invoice_data.xsql
author RM packaging bot <staff.rm@openbravo.com>
Tue, 06 Mar 2012 19:42:39 +0000
changeset 15687 0f4211de14eb
parent 15669 6de349321d77
parent 15683 a77ef04bc81c
child 15750 bc82095c4e7c
permissions -rw-r--r--
Merge temporary head for 3.0MP8.1
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 
ggi@6702
    15
 * All portions are Copyright (C) 2001-2010 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="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,
carlos@0
    32
        l.QtyOrdered-SUM(COALESCE(m.Qty,0)) AS QTY, l.C_UOM_ID,uom.UOMSymbol, l.M_Product_ID,p.NAME AS RELATION_NAME, 
carlos@0
    33
        l.C_OrderLine_ID,l.Line, l.ad_org_id, '' as STDPRECISION, 
carlos@0
    34
        '' as M_InOutLine_ID, '' AS PriceActual, '' AS PriceList, '' AS PriceLimit, '' AS Description, '' as PriceStd,
eduardo@6416
    35
        '' AS QUANTITYORDER, '' AS M_Product_UOM_ID, '' AS M_ATTRIBUTESETINSTANCE_ID, '' AS M_Offer_ID, '' AS PricePrecision,
harikrishnan@7575
    36
        l.taxbaseamt, l.CancelPriceAD
carlos@0
    37
        FROM C_ORDERLINE l left join M_MATCHPO m on  l.C_OrderLine_ID=m.C_OrderLine_ID
carlos@0
    38
                                                 AND m.C_InvoiceLine_ID IS NOT NULL, 
carlos@0
    39
        C_ORDER o, C_UOM uom,  M_PRODUCT p
carlos@0
    40
        WHERE o.C_ORDER_ID = l.C_ORDER_ID 
carlos@0
    41
        AND o.IsSOTrx='N' 
carlos@0
    42
        AND o.DocStatus IN ('CL','CO') 
carlos@0
    43
        AND o.AD_Client_ID IN ('1') 
carlos@0
    44
        AND o.AD_Org_ID IN ('1') 
carlos@0
    45
        AND l.C_UOM_ID=uom.C_UOM_ID
carlos@0
    46
        AND l.M_Product_ID=p.M_Product_ID
juanpablo@1605
    47
        AND l.C_Order_ID = ? 
carlos@0
    48
        GROUP BY o.C_ORDER_ID, o.DocumentNo, o.DateOrdered, o.GrandTotal, l.QtyOrdered,l.C_UOM_ID,
harikrishnan@7575
    49
        uom.UOMSymbol,l.M_Product_ID,p.NAME,l.Line,l.C_OrderLine_ID, l.ad_org_id, l.taxbaseamt,l.CancelPriceAD
carlos@0
    50
        HAVING (l.QtyOrdered-SUM(COALESCE(m.Qty,0))) <> 0
carlos@0
    51
        ORDER BY NAME, l.Line
carlos@0
    52
      ]]>
carlos@0
    53
    </Sql>
carlos@0
    54
    <Field name="rownum" value="count"/>
carlos@0
    55
    <Parameter name="language"/>
carlos@0
    56
    <Parameter name="adUserClient" type="replace" optional="true" after="o.AD_Client_ID IN (" text="'1'"/>
carlos@0
    57
    <Parameter name="adOrgClient" type="replace" optional="true" after="o.AD_Org_ID IN (" text="'1'"/>
carlos@0
    58
    <Parameter name="poId"/>
carlos@0
    59
  </SqlMethod>
carlos@0
    60
  <SqlMethod name="selectFromPOCombo" type="preparedStatement" return="multiple">
carlos@0
    61
    <SqlMethodComment></SqlMethodComment>
carlos@0
    62
    <Sql>
carlos@0
    63
      <![CDATA[
carlos@0
    64
        SELECT o.C_ORDER_ID as ID, Ad_Column_Identifier(to_char('C_Order'), to_char(o.c_order_id), to_char(?)) AS NAME 
carlos@0
    65
        FROM C_ORDER o 
carlos@0
    66
        WHERE o.IsSOTrx='N' 
carlos@0
    67
        AND o.DocStatus IN ('CL','CO') 
carlos@0
    68
        AND o.AD_Client_ID IN ('1') 
carlos@0
    69
        AND o.AD_Org_ID IN ('1')
juanpablo@1605
    70
        AND o.C_BPartner_ID = ?
carlos@0
    71
        AND EXISTS (SELECT 1 
carlos@0
    72
                     FROM C_ORDERLINE l left join M_MATCHPO m on  l.C_OrderLine_ID=m.C_OrderLine_ID
carlos@0
    73
                                                              and m.C_InvoiceLine_ID IS NOT NULL  
carlos@0
    74
                    WHERE l.C_ORDER_ID = o.C_ORDER_ID  
carlos@0
    75
                    GROUP BY l.QtyOrdered,l.C_OrderLine_ID 
carlos@0
    76
                    HAVING (l.QtyOrdered-SUM(COALESCE(m.Qty,0))) <> 0)
carlos@0
    77
        ORDER BY NAME
carlos@0
    78
      ]]>
carlos@0
    79
    </Sql>
carlos@0
    80
    <Parameter name="language"/>
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="cBpartnerId"/>
carlos@0
    84
  </SqlMethod>
carlos@0
    85
  <SqlMethod name="selectFromPOSOTrx" type="preparedStatement" return="multiple">
carlos@0
    86
    <SqlMethodComment></SqlMethodComment>
carlos@0
    87
    <Sql>
carlos@0
    88
      <![CDATA[
carlos@0
    89
        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
    90
        l.QtyOrdered-COALESCE(l.QTYINVOICED,0) AS QTY, l.C_UOM_ID,uom.UOMSymbol, l.M_Product_ID,p.NAME AS RELATION_NAME, 
eduardo@6416
    91
        l.C_OrderLine_ID,l.Line, l.ad_Org_id, l.taxbaseamt
carlos@0
    92
        FROM C_INVOICE_CANDIDATE_V ic, C_UOM uom, C_ORDER o, C_ORDERLINE l, M_PRODUCT p 
carlos@0
    93
        WHERE l.C_UOM_ID=uom.C_UOM_ID
carlos@0
    94
        AND o.C_ORDER_ID = ic.C_ORDER_ID 
carlos@0
    95
        AND o.C_ORDER_ID=l.C_ORDER_ID 
carlos@0
    96
        AND ic.AD_Client_ID IN ('1') 
carlos@0
    97
        AND ic.AD_Org_ID IN ('1') 
carlos@0
    98
        AND l.M_Product_ID=p.M_Product_ID 
juanpablo@1605
    99
        AND ic.C_Order_ID = ? 
carlos@0
   100
        GROUP BY o.C_ORDER_ID, o.DocumentNo, o.DateOrdered, o.GrandTotal, l.QtyOrdered,
eduardo@6416
   101
        l.C_UOM_ID,uom.UOMSymbol,l.M_Product_ID,p.NAME,l.Line,l.C_OrderLine_ID, l.QTYINVOICED, l.ad_org_id,
eduardo@6416
   102
        l.taxbaseamt
carlos@0
   103
        ORDER BY NAME, l.Line
carlos@0
   104
      ]]>
carlos@0
   105
    </Sql>
carlos@0
   106
    <Field name="rownum" value="count"/>
carlos@0
   107
    <Parameter name="language"/>
carlos@0
   108
    <Parameter name="adUserClient" type="replace" optional="true" after="ic.AD_Client_ID IN (" text="'1'"/>
carlos@0
   109
    <Parameter name="adOrgClient" type="replace" optional="true" after="ic.AD_Org_ID IN (" text="'1'"/>
carlos@0
   110
    <Parameter name="poId"/>
carlos@0
   111
  </SqlMethod>
carlos@0
   112
  <SqlMethod name="selectFromPOSOTrxCombo" type="preparedStatement" return="multiple">
carlos@0
   113
    <SqlMethodComment></SqlMethodComment>
carlos@0
   114
    <Sql>
carlos@0
   115
      <![CDATA[
carlos@0
   116
        SELECT o.C_ORDER_ID as ID, Ad_Column_Identifier(to_char('C_Order'), to_char(o.c_order_id), to_char(?)) AS NAME 
harikrishnan@7999
   117
        FROM C_INVOICE_CANDIDATE_V ic, C_ORDER o
carlos@0
   118
        WHERE o.C_ORDER_ID = ic.C_ORDER_ID 
carlos@0
   119
        AND ic.AD_Client_ID IN ('1') 
carlos@0
   120
        AND ic.AD_Org_ID IN ('1') 
juanpablo@1605
   121
        AND ic.C_BPartner_ID = ? 
harikrishnan@7999
   122
        AND o.C_Order_Id = ic.C_Order_Id
harikrishnan@7999
   123
        AND ((ic.term = 'D' AND ic.qtydelivered <>0) OR (ic.term = 'I' AND (SELECT SUM(ol.QTYORDERED)-SUM(ol.QTYINVOICED) FROM C_ORDERLINE ol WHERE ol.C_ORDER_ID = o.C_ORDER_ID) <> 0) OR (ic.term <> 'N' AND (ic.term IN ('O','S') AND (ic.qtyordered = ic.qtydelivered) )) )
carlos@0
   124
        GROUP BY o.C_ORDER_ID, o.DocumentNo, o.DateOrdered, o.GrandTotal 
carlos@0
   125
        ORDER BY NAME
carlos@0
   126
      ]]>
carlos@0
   127
    </Sql>
carlos@0
   128
    <Parameter name="language"/>
carlos@0
   129
    <Parameter name="adUserClient" type="replace" optional="true" after="ic.AD_Client_ID IN (" text="'1'"/>
carlos@0
   130
    <Parameter name="adOrgClient" type="replace" optional="true" after="ic.AD_Org_ID IN (" text="'1'"/>
carlos@0
   131
    <Parameter name="cBpartnerId"/>
carlos@0
   132
  </SqlMethod>
carlos@0
   133
  <SqlMethod name="selectFromPOTrl" type="preparedStatement" return="multiple">
carlos@0
   134
    <SqlMethodComment></SqlMethodComment>
carlos@0
   135
    <Sql>
carlos@0
   136
      <![CDATA[
carlos@0
   137
        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
   138
        l.QtyOrdered-SUM(COALESCE(m.Qty,0)) AS QTY, l.C_UOM_ID, COALESCE(uomt.UOMSYMBOL, uom.UOMSymbol) AS UOMSymbol, 
eduardo@6416
   139
        l.M_Product_ID,p.NAME AS RELATION_NAME, l.C_OrderLine_ID,l.Line, l.taxbaseamt 
carlos@0
   140
        FROM C_UOM uom left join  C_UOM_TRL uomt on uom.C_UOM_ID = uomt.C_UOM_ID
carlos@0
   141
                                                and uomt.AD_Language = ?,
carlos@0
   142
             C_ORDERLINE l left join M_MATCHPO m on l.C_OrderLine_ID=m.C_OrderLine_ID
carlos@0
   143
                                                and m.C_InvoiceLine_ID IS NOT NULL,
carlos@0
   144
        C_ORDER o, M_PRODUCT p
carlos@0
   145
        WHERE o.C_ORDER_ID = l.C_ORDER_ID 
carlos@0
   146
        AND o.IsSOTrx='N' 
carlos@0
   147
        AND o.DocStatus IN ('CL','CO') 
carlos@0
   148
        AND o.AD_Client_ID IN ('1') 
carlos@0
   149
        AND o.AD_Org_ID IN ('1') 
carlos@0
   150
        AND l.C_UOM_ID=uom.C_UOM_ID 
carlos@0
   151
        AND l.M_Product_ID=p.M_Product_ID  
juanpablo@1605
   152
        AND l.C_Order_ID = ? 
carlos@0
   153
        GROUP BY o.C_ORDER_ID, o.DocumentNo, o.DateOrdered, o.GrandTotal, l.QtyOrdered,l.C_UOM_ID,
eduardo@6416
   154
        uom.UOMSymbol, uomt.UOMSYMBOL,l.M_Product_ID,p.NAME,l.Line,l.C_OrderLine_ID,
eduardo@6416
   155
        l.taxbaseamt 
carlos@0
   156
        HAVING (l.QtyOrdered-SUM(COALESCE(m.Qty,0))) <> 0
carlos@0
   157
        ORDER BY NAME, l.Line
carlos@0
   158
      ]]>
carlos@0
   159
    </Sql>
carlos@0
   160
    <Field name="rownum" value="count"/>
carlos@0
   161
    <Parameter name="adLanguage"/>
carlos@0
   162
    <Parameter name="adUserClient" type="replace" optional="true" after="o.AD_Client_ID IN (" text="'1'"/>
carlos@0
   163
    <Parameter name="adOrgClient" type="replace" optional="true" after="o.AD_Org_ID IN (" text="'1'"/>
carlos@0
   164
    <Parameter name="adLanguage"/>
carlos@0
   165
    <Parameter name="poId"/>
carlos@0
   166
  </SqlMethod>
carlos@0
   167
  <SqlMethod name="selectFromPOTrlSOTrx" type="preparedStatement" return="multiple">
carlos@0
   168
    <SqlMethodComment></SqlMethodComment>
carlos@0
   169
    <Sql>
carlos@0
   170
      <![CDATA[
carlos@0
   171
        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
   172
        l.QtyOrdered-COALESCE(l.QTYINVOICED,0) AS QTY, l.C_UOM_ID, COALESCE(uomt.UOMSymbol, uom.UOMSymbol) AS UOMSymbol, 
eduardo@6416
   173
        l.M_Product_ID,p.NAME AS RELATION_NAME, l.C_OrderLine_ID,l.Line, l.taxbaseamt
carlos@0
   174
        FROM C_UOM uom left join C_UOM_TRL uomt on uom.C_UOM_ID = uomt.C_UOM_ID
carlos@0
   175
                                               and  uomt.AD_Language = ?, 
carlos@0
   176
        C_INVOICE_CANDIDATE_V ic,   C_ORDER o, C_ORDERLINE l, M_PRODUCT p 
carlos@0
   177
        WHERE l.C_UOM_ID=uom.C_UOM_ID
carlos@0
   178
        AND o.C_ORDER_ID = ic.C_ORDER_ID 
carlos@0
   179
        AND o.C_ORDER_ID=l.C_ORDER_ID 
carlos@0
   180
        AND ic.AD_Client_ID IN ('1') 
carlos@0
   181
        AND ic.AD_Org_ID IN ('1') 
carlos@0
   182
        AND l.M_Product_ID=p.M_Product_ID
juanpablo@1605
   183
        AND ic.C_Order_ID = ? 
carlos@0
   184
        GROUP BY o.C_ORDER_ID, o.DocumentNo, o.DateOrdered, o.GrandTotal, l.QtyOrdered,
eduardo@6416
   185
        l.C_UOM_ID,uom.UOMSymbol, uomt.UOMSymbol,l.M_Product_ID,p.NAME,l.Line,l.C_OrderLine_ID, l.QTYINVOICED,
eduardo@6416
   186
        l.taxbaseamt 
carlos@0
   187
        ORDER BY NAME, l.Line
carlos@0
   188
      ]]>
carlos@0
   189
    </Sql>
carlos@0
   190
    <Field name="rownum" value="count"/>
carlos@0
   191
    <Parameter name="adLanguage"/>
carlos@0
   192
    <Parameter name="adUserClient" type="replace" optional="true" after="ic.AD_Client_ID IN (" text="'1'"/>
carlos@0
   193
    <Parameter name="adOrgClient" type="replace" optional="true" after="ic.AD_Org_ID IN (" text="'1'"/>
carlos@0
   194
    <Parameter name="adLanguage"/>
carlos@0
   195
    <Parameter name="poId"/>
carlos@0
   196
  </SqlMethod>
carlos@0
   197
  
carlos@0
   198
  <SqlMethod name="selectFromPOUpdate" type="preparedStatement" connection="true" return="multiple">
carlos@0
   199
    <SqlMethodComment></SqlMethodComment>
carlos@0
   200
    <Sql>
carlos@0
   201
      <![CDATA[
carlos@0
   202
      SELECT (CASE WHEN B.M_InOutLine_ID IS NULL THEN A.QTY ELSE B.QTY END) AS ID, 
rafael@5919
   203
      A.C_UOM_ID,uom.UOMSymbol, A.M_Product_ID,p.NAME, A.C_OrderLine_ID,A.Line, uom.stdprecision AS stdprecision, cur.priceprecision, 
carlos@0
   204
      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
   205
      (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,
eduardo@6416
   206
      A.taxbaseamt
harikrishnan@4494
   207
      FROM c_currency cur,C_UOM uom, M_PRODUCT p, 
harikrishnan@4494
   208
      (SELECT 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))) AS QTY,
eduardo@6416
   209
      l.quantityOrder*C_DIVIDE((l.QtyOrdered-SUM(COALESCE(mp.Qty,0))),l.QtyOrdered) as quantityOrder, l.M_Product_UOM_ID, l.M_ATTRIBUTESETINSTANCE_ID,
eduardo@6416
   210
      l.taxbaseamt
carlos@0
   211
        FROM C_ORDERLINE l left join M_MATCHPO mp on l.C_OrderLine_ID=mp.C_OrderLine_ID
carlos@0
   212
                                                  and mp.C_InvoiceLine_ID IS NOT NULL
harikrishnan@4494
   213
        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,
eduardo@6416
   214
        l.M_ATTRIBUTESETINSTANCE_ID, l.taxbaseamt) A left join 
carlos@0
   215
      (SELECT il.M_InOutLine_ID, il.C_OrderLine_ID, (il.MovementQty-SUM(COALESCE(mi.Qty,0))) AS QTY,
gorkaion@1787
   216
      il.quantityOrder*C_DIVIDE((il.MovementQty-SUM(COALESCE(mi.Qty,0))),(il.MovementQty)) as quantityOrder, il.M_Product_UOM_ID
gorkaion@158
   217
        FROM M_INOUTLINE il left join M_MATCHINV mi on il.M_InOutLine_ID=mi.M_InOutLine_ID,
gorkaion@158
   218
             M_INOUT io
carlos@0
   219
        WHERE il.C_OrderLine_ID IS NOT NULL
gorkaion@158
   220
        AND io.M_InOut_ID = il.M_InOut_ID
gorkaion@158
   221
        AND io.Processed = 'Y'
carlos@0
   222
        GROUP BY il.M_InOutLine_ID, il.C_OrderLine_ID, il.MovementQty, il.quantityOrder, il.M_Product_UOM_ID
carlos@0
   223
        HAVING (il.MovementQty-SUM(COALESCE(mi.Qty,0))) <> 0) B on A.C_OrderLine_ID=B.C_OrderLine_ID
carlos@0
   224
      WHERE A.C_UOM_ID=uom.C_UOM_ID
harikrishnan@4494
   225
      AND cur.C_CURRENCY_ID=A.C_CURRENCY_ID
carlos@0
   226
      AND A.M_Product_ID=p.M_Product_ID
carlos@0
   227
      ORDER BY A.Line
carlos@0
   228
      ]]>
carlos@0
   229
    </Sql>
carlos@0
   230
    <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
   231
  </SqlMethod>
carlos@0
   232
  <SqlMethod name="selectFromPOUpdateSOTrx" type="preparedStatement" connection="true" return="multiple">
carlos@0
   233
    <SqlMethodComment></SqlMethodComment>
carlos@0
   234
    <Sql>
carlos@0
   235
      <![CDATA[
carlos@0
   236
        SELECT (CASE WHEN il.M_INOUTLINE_ID IS NULL THEN (l.QtyOrdered-COALESCE(l.QTYINVOICED ,0)) ELSE il.MOVEMENTQTY END) AS ID, 
carlos@0
   237
        l.C_UOM_ID,uom.UOMSymbol, l.M_Product_ID,p.NAME, 
rafael@5919
   238
        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
   239
        (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, 
eduardo@6416
   240
        (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, l.M_ATTRIBUTESETINSTANCE_ID, l.ad_org_id,
eduardo@6416
   241
        l.taxbaseamt
gorkaion@158
   242
        FROM C_ORDERLINE l left join (SELECT M_InOutLine.* FROM M_InOutLine, M_InOut
gorkaion@158
   243
                                      WHERE M_InOut.M_InOut_ID = M_InOutLine.M_InOut_ID
gorkaion@158
   244
                                      AND M_InOutLine.IsInvoiced = 'N'
gorkaion@158
   245
                                      AND M_InOut.Processed='Y') il  on l.C_OrderLine_ID = il.C_OrderLine_ID,
harikrishnan@4494
   246
        C_UOM uom,  M_PRODUCT p  ,c_currency cur
harikrishnan@4494
   247
        WHERE cur.C_CURRENCY_ID=l.C_CURRENCY_ID AND l.C_UOM_ID=uom.C_UOM_ID
carlos@0
   248
        AND l.M_Product_ID=p.M_Product_ID 
carlos@0
   249
        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
   250
        l.QTYINVOICED, il.M_InOutLine_ID, il.MovementQty, l.Description, l.quantityOrder, il.quantityOrder, 
eduardo@6416
   251
        l.M_Product_UOM_ID, il.M_Product_UOM_ID, l.M_ATTRIBUTESETINSTANCE_ID,l.ad_org_id, cur.priceprecision,
eduardo@6416
   252
        l.taxbaseamt
carlos@0
   253
        HAVING (l.QtyOrdered-COALESCE(l.QTYINVOICED ,0)) <> 0 
carlos@0
   254
        ORDER BY l.Line
carlos@0
   255
      ]]>
carlos@0
   256
    </Sql>
carlos@0
   257
    <Field name="rownum" value="count"/>
carlos@0
   258
    <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
   259
  </SqlMethod>
carlos@0
   260
  <SqlMethod name="selectFromShipment" type="preparedStatement" return="multiple">
carlos@0
   261
    <SqlMethodComment></SqlMethodComment>
carlos@0
   262
    <Sql>
carlos@0
   263
      <![CDATA[
carlos@0
   264
        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
   265
        s.DocumentNo AS NAMESHIPMENT, 
ainhoa@3030
   266
        (l.MovementQty-SUM(COALESCE(mi.Qty,0))) AS QTY, l.C_UOM_ID,uom.UOMSymbol, l.M_Product_ID,p.NAME||' '||(CASE WHEN ma.serno IS NOT NULL THEN TO_CHAR('('||' '||ma.serno||' '||')') ELSE TO_CHAR('')  END) AS RELATION_NAME, 
carlos@0
   267
        l.M_InOutLine_ID,l.Line, l.M_InOutLine_ID as C_OrderLine_ID 
carlos@0
   268
        FROM M_INOUTLINE l left join M_ATTRIBUTESETINSTANCE ma on l.M_ATTRIBUTESETINSTANCE_ID = ma.M_ATTRIBUTESETINSTANCE_ID
carlos@0
   269
                           left join M_MATCHINV mi             on l.M_InOutLine_ID=mi.M_InOutLine_ID,
carlos@0
   270
        M_INOUT s, C_UOM uom,  M_PRODUCT p
carlos@0
   271
        WHERE s.M_INOUT_ID = l.M_INOUT_ID 
carlos@0
   272
        AND s.IsSOTrx='N' 
carlos@0
   273
        AND s.DocStatus IN ('CL','CO') 
carlos@0
   274
        AND s.AD_Client_ID IN ('1') 
carlos@0
   275
        AND s.AD_Org_ID IN ('1') 
carlos@0
   276
        AND l.C_UOM_ID=uom.C_UOM_ID 
carlos@0
   277
        AND l.M_Product_ID=p.M_Product_ID 
juanpablo@1605
   278
        AND l.M_InOut_ID=? 
carlos@0
   279
        GROUP BY s.M_InOut_ID, s.DocumentNo, s.MovementDate, l.MovementQty,l.C_UOM_ID,uom.UOMSymbol,
carlos@0
   280
        l.M_Product_ID,ma.serno,p.NAME, l.M_InOutLine_ID,l.Line,l.C_OrderLine_ID 
carlos@0
   281
        HAVING  (l.MovementQty-SUM(COALESCE(mi.Qty,0))) <> 0
carlos@0
   282
        ORDER BY NAME, l.Line
carlos@0
   283
      ]]>
carlos@0
   284
    </Sql>
carlos@0
   285
    <Field name="rownum" value="count"/>
carlos@0
   286
    <Parameter name="adLanguage"/>
carlos@0
   287
    <Parameter name="adUserClient" type="replace" optional="true" after="s.AD_Client_ID IN (" text="'1'"/>
carlos@0
   288
    <Parameter name="adOrgClient" type="replace" optional="true" after="s.AD_Org_ID IN (" text="'1'"/>
carlos@0
   289
    <Parameter name="mInoutId"/>
carlos@0
   290
  </SqlMethod>
carlos@0
   291
  <SqlMethod name="selectFromShipmentCombo" type="preparedStatement" return="multiple">
carlos@0
   292
    <SqlMethodComment></SqlMethodComment>
carlos@0
   293
    <Sql>
carlos@0
   294
      <![CDATA[
carlos@0
   295
        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
   296
        FROM M_INOUT s 
carlos@0
   297
        WHERE s.IsSOTrx='N' 
carlos@0
   298
        AND s.DocStatus IN ('CL','CO') 
david@1686
   299
        AND s.ISLOGISTIC <> 'Y'
carlos@0
   300
        AND s.AD_Client_ID IN ('1') 
carlos@0
   301
        AND s.AD_Org_ID IN ('1') 
juanpablo@1605
   302
        AND s.C_BPartner_ID = ? 
carlos@0
   303
        AND EXISTS (SELECT 1 
carlos@0
   304
                     FROM M_INOUTLINE l left join M_MATCHINV mi on l.M_InOutLine_ID=mi.M_InOutLine_ID
carlos@0
   305
                      WHERE l.M_INOUT_ID = s.M_INOUT_ID 
carlos@0
   306
                      GROUP BY l.MovementQty, l.M_InOutLine_ID 
carlos@0
   307
                      HAVING  (l.MovementQty-SUM(COALESCE(mi.Qty,0))) <> 0)
carlos@0
   308
        ORDER BY NAME
carlos@0
   309
      ]]>
carlos@0
   310
    </Sql>
carlos@0
   311
    <Field name="rownum" value="count"/>
carlos@0
   312
    <Parameter name="adLanguage"/>
carlos@0
   313
    <Parameter name="adUserClient" type="replace" optional="true" after="s.AD_Client_ID IN (" text="'1'"/>
carlos@0
   314
    <Parameter name="adOrgClient" type="replace" optional="true" after="s.AD_Org_ID IN (" text="'1'"/>
carlos@0
   315
    <Parameter name="cBpartnerId"/>
carlos@0
   316
  </SqlMethod>
carlos@0
   317
  <SqlMethod name="selectFromShipmentSOTrx" type="preparedStatement" return="multiple">
carlos@0
   318
    <SqlMethodComment></SqlMethodComment>
carlos@0
   319
    <Sql>
carlos@0
   320
      <![CDATA[
carlos@0
   321
        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
   322
        s.DocumentNo AS NAMESHIPMENT,
ainhoa@3030
   323
        l.MovementQty AS QTY, l.C_UOM_ID,uom.UOMSymbol, l.M_Product_ID,p.NAME||' '||(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,
carlos@0
   324
        l.M_InOutLine_ID as C_OrderLine_ID 
carlos@0
   325
        FROM M_INOUTLINE l left join M_ATTRIBUTESETINSTANCE ma on l.M_ATTRIBUTESETINSTANCE_ID = ma.M_ATTRIBUTESETINSTANCE_ID,
carlos@0
   326
        M_INOUT s, C_UOM uom, M_PRODUCT p 
carlos@0
   327
        WHERE s.M_INOUT_ID = l.M_INOUT_ID
carlos@0
   328
        AND s.IsSOTrx='Y' 
carlos@0
   329
        AND s.DocStatus IN ('CL','CO') 
carlos@0
   330
        AND s.AD_Client_ID IN ('1') 
carlos@0
   331
        AND s.AD_Org_ID IN ('1') 
carlos@0
   332
        AND l.C_UOM_ID=uom.C_UOM_ID 
carlos@0
   333
        AND l.M_Product_ID=p.M_Product_ID 
carlos@0
   334
        AND l.isinvoiced = 'N' 
juanpablo@1605
   335
        AND l.M_InOut_ID=?
carlos@0
   336
        GROUP BY s.M_InOut_ID, s.DocumentNo, s.MovementDate, l.MovementQty,l.C_UOM_ID,uom.UOMSymbol,
carlos@0
   337
        l.M_Product_ID,p.NAME, ma.serno, l.M_InOutLine_ID,l.Line,l.C_OrderLine_ID
carlos@0
   338
        ORDER BY NAME, l.Line
carlos@0
   339
      ]]>
carlos@0
   340
    </Sql>
carlos@0
   341
    <Field name="rownum" value="count"/>
carlos@0
   342
    <Parameter name="adLanguage"/>
carlos@0
   343
    <Parameter name="adUserClient" type="replace" optional="true" after="s.AD_Client_ID IN (" text="'1'"/>
carlos@0
   344
    <Parameter name="adOrgClient" type="replace" optional="true" after="s.AD_Org_ID IN (" text="'1'"/>
carlos@0
   345
    <Parameter name="mInoutId"/>
carlos@0
   346
  </SqlMethod>
carlos@0
   347
  <SqlMethod name="selectFromShipmentSOTrxCombo" type="preparedStatement" return="multiple">
carlos@0
   348
    <SqlMethodComment></SqlMethodComment>
carlos@0
   349
    <Sql>
carlos@0
   350
      <![CDATA[
carlos@0
   351
        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
   352
        FROM M_INOUT s 
carlos@0
   353
        WHERE s.IsSOTrx='Y' 
david@1686
   354
        AND s.ISLOGISTIC <> 'Y'
carlos@0
   355
        AND s.DocStatus IN ('CL','CO') 
carlos@0
   356
        AND s.AD_Client_ID IN ('1') 
carlos@0
   357
        AND s.AD_Org_ID IN ('1')
juanpablo@1605
   358
        AND s.C_BPartner_ID = ?  
carlos@0
   359
        AND EXISTS (SELECT 1 FROM M_INOUTLINE l 
carlos@0
   360
        WHERE l.M_INOUT_ID = s.M_INOUT_ID 
harikrishnan@8300
   361
        AND l.isinvoiced = 'N')
harikrishnan@8300
   362
        AND NOT EXISTS (SELECT 1 FROM C_INVOICE_CANDIDATE_V ic, C_ORDER o 
harikrishnan@8300
   363
        WHERE o.C_ORDER_ID = s.C_ORDER_ID
harikrishnan@8300
   364
        AND o.C_ORDER_ID = ic.C_ORDER_ID
javier@15163
   365
        AND ic.term = 'N')
javier@15683
   366
        AND NOT EXISTS (SELECT 1
javier@15163
   367
        FROM C_ORDER o , C_ORDERLINE ol
javier@15163
   368
        WHERE o.C_ORDER_ID = s.C_ORDER_ID
javier@15163
   369
        AND o.C_ORDER_ID = ol.C_ORDER_ID
javier@15164
   370
        GROUP BY o.INVOICERULE
javier@15683
   371
        HAVING (o.INVOICERULE = 'O' AND SUM(ol.qtyordered) <> SUM(ol.qtydelivered)))
carlos@0
   372
        ORDER BY NAME
carlos@0
   373
      ]]>
carlos@0
   374
    </Sql>
carlos@0
   375
    <Parameter name="adLanguage"/>
carlos@0
   376
    <Parameter name="adUserClient" type="replace" optional="true" after="s.AD_Client_ID IN (" text="'1'"/>
carlos@0
   377
    <Parameter name="adOrgClient" type="replace" optional="true" after="s.AD_Org_ID IN (" text="'1'"/>
carlos@0
   378
    <Parameter name="cBpartnerId"/>
carlos@0
   379
  </SqlMethod>
carlos@0
   380
  <SqlMethod name="selectFromShipmentTrl" type="preparedStatement" return="multiple">
carlos@0
   381
    <SqlMethodComment></SqlMethodComment>
carlos@0
   382
    <Sql>
carlos@0
   383
      <![CDATA[
carlos@0
   384
      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
   385
      s.DocumentNo AS NAMESHIPMENT,
carlos@0
   386
        (l.MovementQty-SUM(COALESCE(mi.Qty,0))) AS QTY, l.C_UOM_ID, COALESCE(uomt.UOMSymbol, uom.UOMSymbol) AS UOMSymbol, 
ainhoa@3030
   387
        l.M_Product_ID,p.NAME||' '||(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 
carlos@0
   388
        FROM M_INOUTLINE l left join M_ATTRIBUTESETINSTANCE ma on l.M_ATTRIBUTESETINSTANCE_ID = ma.M_ATTRIBUTESETINSTANCE_ID
carlos@0
   389
                           left join M_MATCHINV mi             on l.M_InOutLine_ID=mi.M_InOutLine_ID,
carlos@0
   390
             C_UOM uom     left join C_UOM_TRL uomt            on uom.C_UOM_ID = uomt.C_UOM_ID
carlos@0
   391
                                                              and uomt.AD_Language = ?,
carlos@0
   392
        M_INOUT s,   M_PRODUCT p
carlos@0
   393
        WHERE s.M_INOUT_ID = l.M_INOUT_ID 
carlos@0
   394
        AND s.IsSOTrx='N' 
carlos@0
   395
        AND s.DocStatus IN ('CL','CO') 
carlos@0
   396
        AND s.AD_Client_ID IN ('1') 
carlos@0
   397
        AND s.AD_Org_ID IN ('1') 
carlos@0
   398
        AND l.C_UOM_ID=uom.C_UOM_ID 
carlos@0
   399
        AND l.M_Product_ID=p.M_Product_ID 
juanpablo@1605
   400
        AND l.M_InOut_ID=? 
carlos@0
   401
        GROUP BY s.M_InOut_ID, s.DocumentNo, s.MovementDate, l.MovementQty,l.C_UOM_ID,uom.UOMSymbol, uomt.UOMSymbol,
carlos@0
   402
        l.M_Product_ID,p.NAME, ma.serno, l.M_InOutLine_ID,l.Line,l.C_OrderLine_ID 
carlos@0
   403
        HAVING  (l.MovementQty-SUM(COALESCE(mi.Qty,0))) <> 0
carlos@0
   404
        ORDER BY NAME, l.Line
carlos@0
   405
      ]]>
carlos@0
   406
    </Sql>
carlos@0
   407
    <Field name="rownum" value="count"/>
carlos@0
   408
    <Parameter name="adLanguage"/>
carlos@0
   409
    <Parameter name="adUserClient" type="replace" optional="true" after="s.AD_Client_ID IN (" text="'1'"/>
carlos@0
   410
    <Parameter name="adOrgClient" type="replace" optional="true" after="s.AD_Org_ID IN (" text="'1'"/>
carlos@0
   411
    <Parameter name="adLanguage"/>
carlos@0
   412
    <Parameter name="mInoutId"/>
carlos@0
   413
  </SqlMethod>
carlos@0
   414
  <SqlMethod name="selectFromShipmentTrlSOTrx" type="preparedStatement" return="multiple">
carlos@0
   415
    <SqlMethodComment></SqlMethodComment>
carlos@0
   416
    <Sql>
carlos@0
   417
      <![CDATA[
carlos@0
   418
        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
   419
        s.DocumentNo AS NAMESHIPMENT, 
carlos@0
   420
        l.MovementQty AS QTY, l.C_UOM_ID,COALESCE(uomt.UOMSymbol, uom.UOMSymbol) AS UOMSymbol, 
ainhoa@3030
   421
        l.M_Product_ID,p.NAME||' '|| (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 
carlos@0
   422
        FROM M_INOUTLINE l left join M_ATTRIBUTESETINSTANCE ma on l.M_ATTRIBUTESETINSTANCE_ID = ma.M_ATTRIBUTESETINSTANCE_ID,
carlos@0
   423
             C_UOM uom     left join C_UOM_TRL uomt            on uom.C_UOM_ID = uomt.C_UOM_ID
carlos@0
   424
                                                              and uomt.AD_Language = ? ,
carlos@0
   425
        M_INOUT s,    M_PRODUCT p 
carlos@0
   426
        WHERE s.M_INOUT_ID = l.M_INOUT_ID
carlos@0
   427
        AND s.IsSOTrx='Y' 
carlos@0
   428
        AND s.DocStatus IN ('CL','CO') 
carlos@0
   429
        AND s.AD_Client_ID IN ('1') 
carlos@0
   430
        AND s.AD_Org_ID IN ('1') 
carlos@0
   431
        AND l.C_UOM_ID=uom.C_UOM_ID 
carlos@0
   432
        AND l.M_Product_ID=p.M_Product_ID 
carlos@0
   433
        AND l.isinvoiced = 'N' 
juanpablo@1605
   434
        AND l.M_InOut_ID=? 
carlos@0
   435
        GROUP BY s.M_InOut_ID, s.DocumentNo, s.MovementDate, l.MovementQty,l.C_UOM_ID,uom.UOMSymbol, uomt.UOMSymbol,
carlos@0
   436
        l.M_Product_ID,p.NAME,ma.serno, l.M_InOutLine_ID,l.Line,l.C_OrderLine_ID
carlos@0
   437
        ORDER BY NAME, l.Line
carlos@0
   438
      ]]>
carlos@0
   439
    </Sql>
carlos@0
   440
    <Field name="rownum" value="count"/>
carlos@0
   441
    <Parameter name="adLanguage"/>
carlos@0
   442
    <Parameter name="adUserClient" type="replace" optional="true" after="s.AD_Client_ID IN (" text="'1'"/>
carlos@0
   443
    <Parameter name="adOrgClient" type="replace" optional="true" after="s.AD_Org_ID IN (" text="'1'"/>
carlos@0
   444
    <Parameter name="adLanguage"/>
carlos@0
   445
    <Parameter name="mInoutId"/>
carlos@0
   446
  </SqlMethod>
carlos@0
   447
  <SqlMethod name="selectFromShipmentUpdate" type="preparedStatement" connection="true" return="multiple">
carlos@0
   448
    <SqlMethodComment></SqlMethodComment>
carlos@0
   449
    <Sql>
carlos@0
   450
      <![CDATA[
carlos@0
   451
      SELECT (l.MovementQty-SUM(COALESCE(mi.Qty,0))) AS ID, l.C_UOM_ID,uom.UOMSymbol, l.M_Product_ID,p.NAME, l.M_InOutLine_ID,l.Line,
gorkaion@1787
   452
      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, 
carlos@0
   453
      l.M_Product_UOM_ID, l.M_ATTRIBUTESETINSTANCE_ID, l.ad_org_id
carlos@0
   454
      FROM M_INOUTLINE l left join M_MATCHINV mi on l.M_InOutLine_ID=mi.M_InOutLine_ID,
carlos@0
   455
      C_UOM uom,  M_PRODUCT p
carlos@0
   456
      WHERE l.C_UOM_ID=uom.C_UOM_ID
carlos@0
   457
      AND l.M_Product_ID=p.M_Product_ID
carlos@0
   458
      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
carlos@0
   459
      ORDER BY l.Line
carlos@0
   460
      ]]>
carlos@0
   461
    </Sql>
david@7243
   462
    <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
   463
  </SqlMethod>
carlos@0
   464
  <SqlMethod name="selectFromShipmentUpdateSOTrx" type="preparedStatement" connection="true" return="multiple">
carlos@0
   465
    <SqlMethodComment></SqlMethodComment>
carlos@0
   466
    <Sql>
carlos@0
   467
      <![CDATA[
carlos@0
   468
        SELECT l.MovementQty AS ID, l.C_UOM_ID,uom.UOMSymbol, l.M_Product_ID,p.NAME, l.M_InOutLine_ID,l.Line,
carlos@0
   469
        l.C_OrderLine_ID, max(uom.STDPRECISION) as stdprecision, l.Description,l.quantityOrder, l.M_Product_UOM_ID,
carlos@0
   470
        l.M_ATTRIBUTESETINSTANCE_ID, l.ad_org_id
carlos@0
   471
        FROM C_UOM uom, M_INOUTLINE l, M_PRODUCT p 
carlos@0
   472
        WHERE l.C_UOM_ID=uom.C_UOM_ID
carlos@0
   473
        AND l.M_Product_ID=p.M_Product_ID 
carlos@0
   474
        AND l.isinvoiced = 'N' 
carlos@0
   475
        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
carlos@0
   476
        ORDER BY l.Line
carlos@0
   477
      ]]>
carlos@0
   478
    </Sql>
carlos@0
   479
    <Field name="rownum" value="count"/>
carlos@0
   480
    <Parameter name="cOrderlineId" optional="true" type="argument" after="AND l.isinvoiced = 'N' " text=" AND l.M_InOutLine_ID IN "/>
carlos@0
   481
  </SqlMethod>
carlos@0
   482
  <SqlMethod name="set" type="constant" return="multiple">
carlos@0
   483
      <SqlMethodComment></SqlMethodComment>
carlos@0
   484
      <Sql></Sql>
carlos@0
   485
  </SqlMethod>
carlos@0
   486
  <SqlMethod name="selectPriceList" type="preparedStatement" connection="true" return="multiple">
carlos@0
   487
    <SqlMethodComment></SqlMethodComment>
carlos@0
   488
    <Sql>
carlos@0
   489
      <![CDATA[
rafael@5919
   490
      SELECT plv.M_PriceList_Version_ID AS ID, cur.Priceprecision
rafael@5919
   491
      FROM M_PRICELIST pl, M_PRICELIST_VERSION plv, C_CURRENCY cur
rafael@5919
   492
      WHERE pl.M_PriceList_ID = plv.M_PriceList_ID 
rafael@5919
   493
      AND pl.C_Currency_ID = cur.C_Currency_ID
rafael@5919
   494
      AND plv.IsActive='Y'
juanpablo@1605
   495
      AND plv.ValidFrom <= TO_DATE(?) AND pl.M_PriceList_ID=? 
carlos@0
   496
      ORDER BY plv.ValidFrom DESC
carlos@0
   497
      ]]>
carlos@0
   498
    </Sql>
carlos@0
   499
    <Parameter name="validfrom"/>
carlos@0
   500
    <Parameter name="mPricelistId"/>
carlos@0
   501
  </SqlMethod>
carlos@0
   502
carlos@0
   503
  <SqlMethod name="selectPrices" type="preparedStatement" connection="true" return="multiple">
carlos@0
   504
    <SqlMethodComment></SqlMethodComment>
carlos@0
   505
    <Sql>
carlos@0
   506
      <![CDATA[
harikrishnan@7575
   507
      SELECT PriceActual,PriceList,PriceLimit,Description, '' AS C_ORDERLINE_ID, PriceStd, CancelPriceAD
carlos@0
   508
      FROM C_OrderLine 
juanpablo@1605
   509
      WHERE C_OrderLine_ID=?
carlos@0
   510
      ]]>
carlos@0
   511
    </Sql>
carlos@0
   512
    <Parameter name="cOrderlineId"/>
carlos@0
   513
  </SqlMethod>
carlos@0
   514
  <SqlMethod name="selectBOM" type="preparedStatement" connection="true" return="multiple">
carlos@0
   515
    <SqlMethodComment></SqlMethodComment>
carlos@0
   516
    <Sql>
carlos@0
   517
      <![CDATA[
gorkaion@1787
   518
      SELECT M_BOM_PriceStd(pp.M_Product_ID,pp.M_PriceList_Version_ID) AS PriceStd,
gorkaion@1787
   519
      M_BOM_PriceList(pp.M_Product_ID,pp.M_PriceList_Version_ID) AS PriceList,
gorkaion@1787
   520
      M_BOM_PriceLimit(pp.M_Product_ID,pp.M_PriceList_Version_ID) AS PriceLimit, 
gorkaion@1787
   521
      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
   522
      M_GET_OFFER(to_date(?), ?, pp.M_Product_ID) AS M_Offer_ID
carlos@0
   523
      FROM M_PRODUCTPRICE pp 
juanpablo@1605
   524
      WHERE pp.M_Product_ID=? 
juanpablo@1605
   525
      AND pp.M_PriceList_Version_ID=? 
carlos@0
   526
      ]]>
carlos@0
   527
    </Sql>
carlos@0
   528
    <Parameter name="dateinvoiced"/>
carlos@0
   529
    <Parameter name="cBpartnerId"/>
carlos@0
   530
    <Parameter name="dateinvoiced"/>
carlos@0
   531
    <Parameter name="cBpartnerId"/>
carlos@0
   532
    <Parameter name="mProductId"/>
carlos@0
   533
    <Parameter name="mPricelistVersionId"/>
carlos@0
   534
  </SqlMethod>
carlos@0
   535
  <SqlMethod name="insert" type="preparedStatement" connection="true" return="rowCount">
carlos@0
   536
    <SqlMethodComment></SqlMethodComment>
carlos@0
   537
    <Sql>
juanpablo@1605
   538
      INSERT INTO C_INVOICELINE 
juanpablo@1605
   539
        (C_InvoiceLine_ID,C_Invoice_ID, AD_Client_ID,AD_Org_ID,IsActive,
juanpablo@1605
   540
        Created,CreatedBy,Updated,UpdatedBy, 
juanpablo@1605
   541
        C_OrderLine_ID,M_InOutLine_ID, Line,Description, M_Product_ID,
juanpablo@1605
   542
        C_UOM_ID,QtyInvoiced, PriceList,
juanpablo@1605
   543
        PriceActual,PriceLimit, LineNetAmt,
juanpablo@1605
   544
        ChargeAmt,C_Tax_ID, QuantityOrder, M_Product_UOM_ID,
eduardo@6416
   545
        M_Attributesetinstance_ID, PriceStd, taxbaseamt)
juanpablo@1605
   546
      VALUES 
juanpablo@1605
   547
        (?, ?, ?, ?, 'Y', 
juanpablo@1605
   548
         now(), ?, now(), ?, 
juanpablo@1605
   549
         ?, ?, (SELECT (COALESCE(Max(Line),0))+10 FROM C_InvoiceLine WHERE C_Invoice_ID=?), ?, ?, 
juanpablo@1605
   550
         ?, TO_NUMBER(?), TO_NUMBER(?), 
juanpablo@1605
   551
         TO_NUMBER(?), TO_NUMBER(?), TO_NUMBER(?), 
juanpablo@1605
   552
         0, ?, TO_NUMBER(?), ?, 
eduardo@6416
   553
         ?, TO_NUMBER(?), TO_NUMBER(?))
carlos@0
   554
    </Sql>
carlos@0
   555
    <Parameter name="cInvoicelineId"/>
carlos@0
   556
    <Parameter name="cInvoiceId"/>
carlos@0
   557
    <Parameter name="adClientId"/>
carlos@0
   558
    <Parameter name="adOrgId"/>
carlos@0
   559
    <Parameter name="adUserId"/>
carlos@0
   560
    <Parameter name="adUserId"/>
carlos@0
   561
    <Parameter name="cOrderlineId"/>
carlos@0
   562
    <Parameter name="mInoutlineId"/>
carlos@0
   563
    <Parameter name="cInvoiceId"/>
carlos@0
   564
    <Parameter name="description"/>
carlos@0
   565
    <Parameter name="mProductId"/>
carlos@0
   566
    <Parameter name="cUomId"/>
carlos@0
   567
    <Parameter name="qtyinvoiced"/>
carlos@0
   568
    <Parameter name="pricelist"/>
carlos@0
   569
    <Parameter name="priceactual"/>
carlos@0
   570
    <Parameter name="pricelimit"/>
carlos@0
   571
    <Parameter name="linenetamt"/>
carlos@0
   572
    <Parameter name="cTaxId"/>
carlos@0
   573
    <Parameter name="quantityOrder"/>
carlos@0
   574
    <Parameter name="mProductUomId"/>
carlos@0
   575
    <Parameter name="mAttributesetinstanceId"/>
carlos@0
   576
    <Parameter name="pricestd"/>
eduardo@6416
   577
    <Parameter name="taxBaseAmt"/>
carlos@0
   578
  </SqlMethod>
carlos@0
   579
  <SqlMethod name="updateC_Order_ID" type="preparedStatement" connection="true" return="rowCount">
carlos@0
   580
    <SqlMethodComment></SqlMethodComment>
carlos@0
   581
    <Sql><![CDATA[
juanpablo@1605
   582
      UPDATE C_Invoice SET C_Order_id = ?
juanpablo@1605
   583
      WHERE C_Invoice_ID = ?
carlos@0
   584
    ]]></Sql>
carlos@0
   585
    <Parameter name="cOrderId"/>
carlos@0
   586
    <Parameter name="cInvoiceId"/>
carlos@0
   587
  </SqlMethod>
carlos@0
   588
  <SqlMethod name="deleteC_Order_ID" type="preparedStatement" connection="true" return="rowCount">
carlos@0
   589
    <SqlMethodComment></SqlMethodComment>
carlos@0
   590
    <Sql> <![CDATA[
carlos@0
   591
      UPDATE C_INVOICE SET C_Order_ID=NULL
juanpablo@1605
   592
      WHERE C_Invoice_ID=?
juanpablo@1605
   593
      AND C_Order_ID IS NOT NULL AND C_Order_ID <> ?
carlos@0
   594
    ]]></Sql>
carlos@0
   595
    <Parameter name="cInvoiceId"/>
carlos@0
   596
    <Parameter name="cOrderId"/>
carlos@0
   597
  </SqlMethod>
carlos@0
   598
  <SqlMethod name="selectProject" type="preparedStatement" return="String" default="">
carlos@0
   599
    <SqlMethodComment></SqlMethodComment>
carlos@0
   600
    <Sql><![CDATA[
carlos@0
   601
      SELECT C_Project_ID
carlos@0
   602
      FROM C_INVOICE
juanpablo@1605
   603
      WHERE C_INVOICE_ID = ? 
carlos@0
   604
    ]]></Sql>
carlos@0
   605
    <Parameter name="cInvoiceId"/>
carlos@0
   606
  </SqlMethod>
carlos@0
   607
  <SqlMethod name="getTax" type="preparedStatement" return="String" default="">
carlos@0
   608
    <SqlMethodComment></SqlMethodComment>
carlos@0
   609
    <Sql><![CDATA[
carlos@0
   610
      SELECT C_Tax_ID
carlos@0
   611
      FROM C_ORDERLINE
juanpablo@1605
   612
      WHERE C_ORDERLINE_ID = ? 
carlos@0
   613
    ]]></Sql>
carlos@0
   614
    <Parameter name="cOrderLineId"/>
carlos@0
   615
  </SqlMethod>
harikrishnan@7218
   616
  <SqlMethod name="getOffersPriceInvoice" type="preparedStatement" return="String" default="0">
harikrishnan@7218
   617
    <SqlMethodComment></SqlMethodComment>
harikrishnan@7218
   618
    <Sql>
harikrishnan@7218
   619
      SELECT ROUND(M_GET_OFFERS_PRICE(TO_DATE(?),?,?,TO_NUMBER(?), TO_NUMBER(?), ?),
harikrishnan@7218
   620
                    (SELECT PRICEPRECISION 
harikrishnan@7218
   621
                       FROM C_CURRENCY C,
harikrishnan@7218
   622
                            C_INVOICE  I 
harikrishnan@7218
   623
                      WHERE C.C_CURRENCY_ID = I.C_CURRENCY_ID
harikrishnan@7218
   624
                        AND I.C_INVOICE_ID  = ?)) AS TOTAL FROM DUAL
harikrishnan@7218
   625
    </Sql>
harikrishnan@7218
   626
    <Parameter name="dateordered"/>
harikrishnan@7218
   627
    <Parameter name="cBpartnerId"/>
harikrishnan@7218
   628
    <Parameter name="mProductId"/>
harikrishnan@7218
   629
    <Parameter name="pricestd"/>
harikrishnan@7218
   630
    <Parameter name="qty"/>
harikrishnan@7218
   631
    <Parameter name="pricelist"/>
harikrishnan@7218
   632
    <Parameter name="invoiceid"/>
harikrishnan@7218
   633
  </SqlMethod>
carlos@0
   634
</SqlClass>