src/org/openbravo/erpCommon/ad_actionButton/CreateFrom_Invoice_data.xsql
changeset 1605 8a0fe0193bef
parent 423 ecf368072c48
child 1686 e5a629fb644c
equal deleted inserted replaced
1604:a1c518e6ed93 1605:8a0fe0193bef
    41         AND o.DocStatus IN ('CL','CO') 
    41         AND o.DocStatus IN ('CL','CO') 
    42         AND o.AD_Client_ID IN ('1') 
    42         AND o.AD_Client_ID IN ('1') 
    43         AND o.AD_Org_ID IN ('1') 
    43         AND o.AD_Org_ID IN ('1') 
    44         AND l.C_UOM_ID=uom.C_UOM_ID
    44         AND l.C_UOM_ID=uom.C_UOM_ID
    45         AND l.M_Product_ID=p.M_Product_ID
    45         AND l.M_Product_ID=p.M_Product_ID
    46         AND l.C_Order_ID = TO_NUMBER(?) 
    46         AND l.C_Order_ID = ? 
    47         GROUP BY o.C_ORDER_ID, o.DocumentNo, o.DateOrdered, o.GrandTotal, l.QtyOrdered,l.C_UOM_ID,
    47         GROUP BY o.C_ORDER_ID, o.DocumentNo, o.DateOrdered, o.GrandTotal, l.QtyOrdered,l.C_UOM_ID,
    48         uom.UOMSymbol,l.M_Product_ID,p.NAME,l.Line,l.C_OrderLine_ID, l.ad_org_id
    48         uom.UOMSymbol,l.M_Product_ID,p.NAME,l.Line,l.C_OrderLine_ID, l.ad_org_id
    49         HAVING (l.QtyOrdered-SUM(COALESCE(m.Qty,0))) <> 0
    49         HAVING (l.QtyOrdered-SUM(COALESCE(m.Qty,0))) <> 0
    50         ORDER BY NAME, l.Line
    50         ORDER BY NAME, l.Line
    51       ]]>
    51       ]]>
    64         FROM C_ORDER o 
    64         FROM C_ORDER o 
    65         WHERE o.IsSOTrx='N' 
    65         WHERE o.IsSOTrx='N' 
    66         AND o.DocStatus IN ('CL','CO') 
    66         AND o.DocStatus IN ('CL','CO') 
    67         AND o.AD_Client_ID IN ('1') 
    67         AND o.AD_Client_ID IN ('1') 
    68         AND o.AD_Org_ID IN ('1')
    68         AND o.AD_Org_ID IN ('1')
    69         AND o.C_BPartner_ID = TO_NUMBER(?)
    69         AND o.C_BPartner_ID = ?
    70         AND EXISTS (SELECT 1 
    70         AND EXISTS (SELECT 1 
    71                      FROM C_ORDERLINE l left join M_MATCHPO m on  l.C_OrderLine_ID=m.C_OrderLine_ID
    71                      FROM C_ORDERLINE l left join M_MATCHPO m on  l.C_OrderLine_ID=m.C_OrderLine_ID
    72                                                               and m.C_InvoiceLine_ID IS NOT NULL  
    72                                                               and m.C_InvoiceLine_ID IS NOT NULL  
    73                     WHERE l.C_ORDER_ID = o.C_ORDER_ID  
    73                     WHERE l.C_ORDER_ID = o.C_ORDER_ID  
    74                     GROUP BY l.QtyOrdered,l.C_OrderLine_ID 
    74                     GROUP BY l.QtyOrdered,l.C_OrderLine_ID 
    93         AND o.C_ORDER_ID = ic.C_ORDER_ID 
    93         AND o.C_ORDER_ID = ic.C_ORDER_ID 
    94         AND o.C_ORDER_ID=l.C_ORDER_ID 
    94         AND o.C_ORDER_ID=l.C_ORDER_ID 
    95         AND ic.AD_Client_ID IN ('1') 
    95         AND ic.AD_Client_ID IN ('1') 
    96         AND ic.AD_Org_ID IN ('1') 
    96         AND ic.AD_Org_ID IN ('1') 
    97         AND l.M_Product_ID=p.M_Product_ID 
    97         AND l.M_Product_ID=p.M_Product_ID 
    98         AND ic.C_Order_ID = TO_NUMBER(?) 
    98         AND ic.C_Order_ID = ? 
    99         GROUP BY o.C_ORDER_ID, o.DocumentNo, o.DateOrdered, o.GrandTotal, l.QtyOrdered,
    99         GROUP BY o.C_ORDER_ID, o.DocumentNo, o.DateOrdered, o.GrandTotal, l.QtyOrdered,
   100         l.C_UOM_ID,uom.UOMSymbol,l.M_Product_ID,p.NAME,l.Line,l.C_OrderLine_ID, l.QTYINVOICED, l.ad_org_id
   100         l.C_UOM_ID,uom.UOMSymbol,l.M_Product_ID,p.NAME,l.Line,l.C_OrderLine_ID, l.QTYINVOICED, l.ad_org_id
   101         ORDER BY NAME, l.Line
   101         ORDER BY NAME, l.Line
   102       ]]>
   102       ]]>
   103     </Sql>
   103     </Sql>
   114         SELECT o.C_ORDER_ID as ID, Ad_Column_Identifier(to_char('C_Order'), to_char(o.c_order_id), to_char(?)) AS NAME 
   114         SELECT o.C_ORDER_ID as ID, Ad_Column_Identifier(to_char('C_Order'), to_char(o.c_order_id), to_char(?)) AS NAME 
   115         FROM C_INVOICE_CANDIDATE_V ic, C_ORDER o 
   115         FROM C_INVOICE_CANDIDATE_V ic, C_ORDER o 
   116         WHERE o.C_ORDER_ID = ic.C_ORDER_ID 
   116         WHERE o.C_ORDER_ID = ic.C_ORDER_ID 
   117         AND ic.AD_Client_ID IN ('1') 
   117         AND ic.AD_Client_ID IN ('1') 
   118         AND ic.AD_Org_ID IN ('1') 
   118         AND ic.AD_Org_ID IN ('1') 
   119         AND ic.C_BPartner_ID = TO_NUMBER(?) 
   119         AND ic.C_BPartner_ID = ? 
   120         GROUP BY o.C_ORDER_ID, o.DocumentNo, o.DateOrdered, o.GrandTotal 
   120         GROUP BY o.C_ORDER_ID, o.DocumentNo, o.DateOrdered, o.GrandTotal 
   121         ORDER BY NAME
   121         ORDER BY NAME
   122       ]]>
   122       ]]>
   123     </Sql>
   123     </Sql>
   124     <Parameter name="language"/>
   124     <Parameter name="language"/>
   143         AND o.DocStatus IN ('CL','CO') 
   143         AND o.DocStatus IN ('CL','CO') 
   144         AND o.AD_Client_ID IN ('1') 
   144         AND o.AD_Client_ID IN ('1') 
   145         AND o.AD_Org_ID IN ('1') 
   145         AND o.AD_Org_ID IN ('1') 
   146         AND l.C_UOM_ID=uom.C_UOM_ID 
   146         AND l.C_UOM_ID=uom.C_UOM_ID 
   147         AND l.M_Product_ID=p.M_Product_ID  
   147         AND l.M_Product_ID=p.M_Product_ID  
   148         AND l.C_Order_ID = TO_NUMBER(?) 
   148         AND l.C_Order_ID = ? 
   149         GROUP BY o.C_ORDER_ID, o.DocumentNo, o.DateOrdered, o.GrandTotal, l.QtyOrdered,l.C_UOM_ID,
   149         GROUP BY o.C_ORDER_ID, o.DocumentNo, o.DateOrdered, o.GrandTotal, l.QtyOrdered,l.C_UOM_ID,
   150         uom.UOMSymbol, uomt.UOMSYMBOL,l.M_Product_ID,p.NAME,l.Line,l.C_OrderLine_ID 
   150         uom.UOMSymbol, uomt.UOMSYMBOL,l.M_Product_ID,p.NAME,l.Line,l.C_OrderLine_ID 
   151         HAVING (l.QtyOrdered-SUM(COALESCE(m.Qty,0))) <> 0
   151         HAVING (l.QtyOrdered-SUM(COALESCE(m.Qty,0))) <> 0
   152         ORDER BY NAME, l.Line
   152         ORDER BY NAME, l.Line
   153       ]]>
   153       ]]>
   173         AND o.C_ORDER_ID = ic.C_ORDER_ID 
   173         AND o.C_ORDER_ID = ic.C_ORDER_ID 
   174         AND o.C_ORDER_ID=l.C_ORDER_ID 
   174         AND o.C_ORDER_ID=l.C_ORDER_ID 
   175         AND ic.AD_Client_ID IN ('1') 
   175         AND ic.AD_Client_ID IN ('1') 
   176         AND ic.AD_Org_ID IN ('1') 
   176         AND ic.AD_Org_ID IN ('1') 
   177         AND l.M_Product_ID=p.M_Product_ID
   177         AND l.M_Product_ID=p.M_Product_ID
   178         AND ic.C_Order_ID = TO_NUMBER(?) 
   178         AND ic.C_Order_ID = ? 
   179         GROUP BY o.C_ORDER_ID, o.DocumentNo, o.DateOrdered, o.GrandTotal, l.QtyOrdered,
   179         GROUP BY o.C_ORDER_ID, o.DocumentNo, o.DateOrdered, o.GrandTotal, l.QtyOrdered,
   180         l.C_UOM_ID,uom.UOMSymbol, uomt.UOMSymbol,l.M_Product_ID,p.NAME,l.Line,l.C_OrderLine_ID, l.QTYINVOICED 
   180         l.C_UOM_ID,uom.UOMSymbol, uomt.UOMSymbol,l.M_Product_ID,p.NAME,l.Line,l.C_OrderLine_ID, l.QTYINVOICED 
   181         ORDER BY NAME, l.Line
   181         ORDER BY NAME, l.Line
   182       ]]>
   182       ]]>
   183     </Sql>
   183     </Sql>
   262         AND s.DocStatus IN ('CL','CO') 
   262         AND s.DocStatus IN ('CL','CO') 
   263         AND s.AD_Client_ID IN ('1') 
   263         AND s.AD_Client_ID IN ('1') 
   264         AND s.AD_Org_ID IN ('1') 
   264         AND s.AD_Org_ID IN ('1') 
   265         AND l.C_UOM_ID=uom.C_UOM_ID 
   265         AND l.C_UOM_ID=uom.C_UOM_ID 
   266         AND l.M_Product_ID=p.M_Product_ID 
   266         AND l.M_Product_ID=p.M_Product_ID 
   267         AND l.M_InOut_ID = TO_NUMBER(?) 
   267         AND l.M_InOut_ID=? 
   268         GROUP BY s.M_InOut_ID, s.DocumentNo, s.MovementDate, l.MovementQty,l.C_UOM_ID,uom.UOMSymbol,
   268         GROUP BY s.M_InOut_ID, s.DocumentNo, s.MovementDate, l.MovementQty,l.C_UOM_ID,uom.UOMSymbol,
   269         l.M_Product_ID,ma.serno,p.NAME, l.M_InOutLine_ID,l.Line,l.C_OrderLine_ID 
   269         l.M_Product_ID,ma.serno,p.NAME, l.M_InOutLine_ID,l.Line,l.C_OrderLine_ID 
   270         HAVING  (l.MovementQty-SUM(COALESCE(mi.Qty,0))) <> 0
   270         HAVING  (l.MovementQty-SUM(COALESCE(mi.Qty,0))) <> 0
   271         ORDER BY NAME, l.Line
   271         ORDER BY NAME, l.Line
   272       ]]>
   272       ]]>
   285         FROM M_INOUT s 
   285         FROM M_INOUT s 
   286         WHERE s.IsSOTrx='N' 
   286         WHERE s.IsSOTrx='N' 
   287         AND s.DocStatus IN ('CL','CO') 
   287         AND s.DocStatus IN ('CL','CO') 
   288         AND s.AD_Client_ID IN ('1') 
   288         AND s.AD_Client_ID IN ('1') 
   289         AND s.AD_Org_ID IN ('1') 
   289         AND s.AD_Org_ID IN ('1') 
   290         AND s.C_BPartner_ID = TO_NUMBER(?) 
   290         AND s.C_BPartner_ID = ? 
   291         AND EXISTS (SELECT 1 
   291         AND EXISTS (SELECT 1 
   292                      FROM M_INOUTLINE l left join M_MATCHINV mi on l.M_InOutLine_ID=mi.M_InOutLine_ID
   292                      FROM M_INOUTLINE l left join M_MATCHINV mi on l.M_InOutLine_ID=mi.M_InOutLine_ID
   293                       WHERE l.M_INOUT_ID = s.M_INOUT_ID 
   293                       WHERE l.M_INOUT_ID = s.M_INOUT_ID 
   294                       GROUP BY l.MovementQty, l.M_InOutLine_ID 
   294                       GROUP BY l.MovementQty, l.M_InOutLine_ID 
   295                       HAVING  (l.MovementQty-SUM(COALESCE(mi.Qty,0))) <> 0)
   295                       HAVING  (l.MovementQty-SUM(COALESCE(mi.Qty,0))) <> 0)
   318         AND s.AD_Client_ID IN ('1') 
   318         AND s.AD_Client_ID IN ('1') 
   319         AND s.AD_Org_ID IN ('1') 
   319         AND s.AD_Org_ID IN ('1') 
   320         AND l.C_UOM_ID=uom.C_UOM_ID 
   320         AND l.C_UOM_ID=uom.C_UOM_ID 
   321         AND l.M_Product_ID=p.M_Product_ID 
   321         AND l.M_Product_ID=p.M_Product_ID 
   322         AND l.isinvoiced = 'N' 
   322         AND l.isinvoiced = 'N' 
   323         AND l.M_InOut_ID = TO_NUMBER(?)
   323         AND l.M_InOut_ID=?
   324         GROUP BY s.M_InOut_ID, s.DocumentNo, s.MovementDate, l.MovementQty,l.C_UOM_ID,uom.UOMSymbol,
   324         GROUP BY s.M_InOut_ID, s.DocumentNo, s.MovementDate, l.MovementQty,l.C_UOM_ID,uom.UOMSymbol,
   325         l.M_Product_ID,p.NAME, ma.serno, l.M_InOutLine_ID,l.Line,l.C_OrderLine_ID
   325         l.M_Product_ID,p.NAME, ma.serno, l.M_InOutLine_ID,l.Line,l.C_OrderLine_ID
   326         ORDER BY NAME, l.Line
   326         ORDER BY NAME, l.Line
   327       ]]>
   327       ]]>
   328     </Sql>
   328     </Sql>
   340         FROM M_INOUT s 
   340         FROM M_INOUT s 
   341         WHERE s.IsSOTrx='Y' 
   341         WHERE s.IsSOTrx='Y' 
   342         AND s.DocStatus IN ('CL','CO') 
   342         AND s.DocStatus IN ('CL','CO') 
   343         AND s.AD_Client_ID IN ('1') 
   343         AND s.AD_Client_ID IN ('1') 
   344         AND s.AD_Org_ID IN ('1')
   344         AND s.AD_Org_ID IN ('1')
   345         AND s.C_BPartner_ID = TO_NUMBER(?)  
   345         AND s.C_BPartner_ID = ?  
   346         AND EXISTS (SELECT 1 FROM M_INOUTLINE l 
   346         AND EXISTS (SELECT 1 FROM M_INOUTLINE l 
   347         WHERE l.M_INOUT_ID = s.M_INOUT_ID 
   347         WHERE l.M_INOUT_ID = s.M_INOUT_ID 
   348         AND l.isinvoiced = 'N') 
   348         AND l.isinvoiced = 'N') 
   349         ORDER BY NAME
   349         ORDER BY NAME
   350       ]]>
   350       ]]>
   372         AND s.DocStatus IN ('CL','CO') 
   372         AND s.DocStatus IN ('CL','CO') 
   373         AND s.AD_Client_ID IN ('1') 
   373         AND s.AD_Client_ID IN ('1') 
   374         AND s.AD_Org_ID IN ('1') 
   374         AND s.AD_Org_ID IN ('1') 
   375         AND l.C_UOM_ID=uom.C_UOM_ID 
   375         AND l.C_UOM_ID=uom.C_UOM_ID 
   376         AND l.M_Product_ID=p.M_Product_ID 
   376         AND l.M_Product_ID=p.M_Product_ID 
   377         AND l.M_InOut_ID = TO_NUMBER(?) 
   377         AND l.M_InOut_ID=? 
   378         GROUP BY s.M_InOut_ID, s.DocumentNo, s.MovementDate, l.MovementQty,l.C_UOM_ID,uom.UOMSymbol, uomt.UOMSymbol,
   378         GROUP BY s.M_InOut_ID, s.DocumentNo, s.MovementDate, l.MovementQty,l.C_UOM_ID,uom.UOMSymbol, uomt.UOMSymbol,
   379         l.M_Product_ID,p.NAME, ma.serno, l.M_InOutLine_ID,l.Line,l.C_OrderLine_ID 
   379         l.M_Product_ID,p.NAME, ma.serno, l.M_InOutLine_ID,l.Line,l.C_OrderLine_ID 
   380         HAVING  (l.MovementQty-SUM(COALESCE(mi.Qty,0))) <> 0
   380         HAVING  (l.MovementQty-SUM(COALESCE(mi.Qty,0))) <> 0
   381         ORDER BY NAME, l.Line
   381         ORDER BY NAME, l.Line
   382       ]]>
   382       ]]>
   406         AND s.AD_Client_ID IN ('1') 
   406         AND s.AD_Client_ID IN ('1') 
   407         AND s.AD_Org_ID IN ('1') 
   407         AND s.AD_Org_ID IN ('1') 
   408         AND l.C_UOM_ID=uom.C_UOM_ID 
   408         AND l.C_UOM_ID=uom.C_UOM_ID 
   409         AND l.M_Product_ID=p.M_Product_ID 
   409         AND l.M_Product_ID=p.M_Product_ID 
   410         AND l.isinvoiced = 'N' 
   410         AND l.isinvoiced = 'N' 
   411         AND l.M_InOut_ID = TO_NUMBER(?) 
   411         AND l.M_InOut_ID=? 
   412         GROUP BY s.M_InOut_ID, s.DocumentNo, s.MovementDate, l.MovementQty,l.C_UOM_ID,uom.UOMSymbol, uomt.UOMSymbol,
   412         GROUP BY s.M_InOut_ID, s.DocumentNo, s.MovementDate, l.MovementQty,l.C_UOM_ID,uom.UOMSymbol, uomt.UOMSymbol,
   413         l.M_Product_ID,p.NAME,ma.serno, l.M_InOutLine_ID,l.Line,l.C_OrderLine_ID
   413         l.M_Product_ID,p.NAME,ma.serno, l.M_InOutLine_ID,l.Line,l.C_OrderLine_ID
   414         ORDER BY NAME, l.Line
   414         ORDER BY NAME, l.Line
   415       ]]>
   415       ]]>
   416     </Sql>
   416     </Sql>
   465     <Sql>
   465     <Sql>
   466       <![CDATA[
   466       <![CDATA[
   467       SELECT plv.M_PriceList_Version_ID AS ID
   467       SELECT plv.M_PriceList_Version_ID AS ID
   468       FROM M_PRICELIST pl,M_PRICELIST_VERSION plv 
   468       FROM M_PRICELIST pl,M_PRICELIST_VERSION plv 
   469       WHERE pl.M_PriceList_ID=plv.M_PriceList_ID AND plv.IsActive='Y'
   469       WHERE pl.M_PriceList_ID=plv.M_PriceList_ID AND plv.IsActive='Y'
   470       AND plv.ValidFrom <= TO_DATE(?) AND pl.M_PriceList_ID = TO_NUMBER(?) 
   470       AND plv.ValidFrom <= TO_DATE(?) AND pl.M_PriceList_ID=? 
   471       ORDER BY plv.ValidFrom DESC
   471       ORDER BY plv.ValidFrom DESC
   472       ]]>
   472       ]]>
   473     </Sql>
   473     </Sql>
   474     <Parameter name="validfrom"/>
   474     <Parameter name="validfrom"/>
   475     <Parameter name="mPricelistId"/>
   475     <Parameter name="mPricelistId"/>
   479     <SqlMethodComment></SqlMethodComment>
   479     <SqlMethodComment></SqlMethodComment>
   480     <Sql>
   480     <Sql>
   481       <![CDATA[
   481       <![CDATA[
   482       SELECT PriceActual,PriceList,PriceLimit,Description, '' AS C_ORDERLINE_ID, PriceStd
   482       SELECT PriceActual,PriceList,PriceLimit,Description, '' AS C_ORDERLINE_ID, PriceStd
   483       FROM C_OrderLine 
   483       FROM C_OrderLine 
   484       WHERE C_OrderLine_ID = TO_NUMBER(?)
   484       WHERE C_OrderLine_ID=?
   485       ]]>
   485       ]]>
   486     </Sql>
   486     </Sql>
   487     <Parameter name="cOrderlineId"/>
   487     <Parameter name="cOrderlineId"/>
   488   </SqlMethod>
   488   </SqlMethod>
   489   <SqlMethod name="selectBOM" type="preparedStatement" connection="true" return="multiple">
   489   <SqlMethod name="selectBOM" type="preparedStatement" connection="true" return="multiple">
   491     <Sql>
   491     <Sql>
   492       <![CDATA[
   492       <![CDATA[
   493       SELECT BOM_PriceStd(pp.M_Product_ID,pp.M_PriceList_Version_ID) AS PriceStd,
   493       SELECT BOM_PriceStd(pp.M_Product_ID,pp.M_PriceList_Version_ID) AS PriceStd,
   494       BOM_PriceList(pp.M_Product_ID,pp.M_PriceList_Version_ID) AS PriceList,
   494       BOM_PriceList(pp.M_Product_ID,pp.M_PriceList_Version_ID) AS PriceList,
   495       BOM_PriceLimit(pp.M_Product_ID,pp.M_PriceList_Version_ID) AS PriceLimit, 
   495       BOM_PriceLimit(pp.M_Product_ID,pp.M_PriceList_Version_ID) AS PriceLimit, 
   496       M_GET_OFFER_PRICE(M_GET_OFFER(to_date(?), to_number(?), pp.M_Product_ID), BOM_PriceStd(pp.M_Product_ID,pp.M_PriceList_Version_ID)) AS PriceActual, 
   496       M_GET_OFFER_PRICE(M_GET_OFFER(to_date(?), ?, pp.M_Product_ID), BOM_PriceStd(pp.M_Product_ID,pp.M_PriceList_Version_ID)) AS PriceActual, 
   497       M_GET_OFFER(to_date(?), to_number(?), pp.M_Product_ID) AS M_Offer_ID
   497       M_GET_OFFER(to_date(?), ?, pp.M_Product_ID) AS M_Offer_ID
   498       FROM M_PRODUCTPRICE pp 
   498       FROM M_PRODUCTPRICE pp 
   499       WHERE pp.M_Product_ID = TO_NUMBER(?) 
   499       WHERE pp.M_Product_ID=? 
   500       AND pp.M_PriceList_Version_ID = TO_NUMBER(?) 
   500       AND pp.M_PriceList_Version_ID=? 
   501       ]]>
   501       ]]>
   502     </Sql>
   502     </Sql>
   503     <Parameter name="dateinvoiced"/>
   503     <Parameter name="dateinvoiced"/>
   504     <Parameter name="cBpartnerId"/>
   504     <Parameter name="cBpartnerId"/>
   505     <Parameter name="dateinvoiced"/>
   505     <Parameter name="dateinvoiced"/>
   508     <Parameter name="mPricelistVersionId"/>
   508     <Parameter name="mPricelistVersionId"/>
   509   </SqlMethod>
   509   </SqlMethod>
   510   <SqlMethod name="insert" type="preparedStatement" connection="true" return="rowCount">
   510   <SqlMethod name="insert" type="preparedStatement" connection="true" return="rowCount">
   511     <SqlMethodComment></SqlMethodComment>
   511     <SqlMethodComment></SqlMethodComment>
   512     <Sql>
   512     <Sql>
   513       INSERT INTO C_INVOICELINE (C_InvoiceLine_ID,C_Invoice_ID, AD_Client_ID,AD_Org_ID,IsActive,
   513       INSERT INTO C_INVOICELINE 
   514       Created,CreatedBy,Updated,UpdatedBy, C_OrderLine_ID,M_InOutLine_ID, Line,Description, M_Product_ID,
   514         (C_InvoiceLine_ID,C_Invoice_ID, AD_Client_ID,AD_Org_ID,IsActive,
   515       C_UOM_ID,QtyInvoiced, PriceList,PriceActual,PriceLimit, LineNetAmt,ChargeAmt,C_Tax_ID, QuantityOrder, M_Product_UOM_ID,
   515         Created,CreatedBy,Updated,UpdatedBy, 
   516       M_Attributesetinstance_ID, PriceStd)
   516         C_OrderLine_ID,M_InOutLine_ID, Line,Description, M_Product_ID,
   517       VALUES (TO_NUMBER(?), TO_NUMBER(?), TO_NUMBER(?), TO_NUMBER(?), 'Y', now(), TO_NUMBER(?), now(), TO_NUMBER(?), TO_NUMBER(?), TO_NUMBER(?), (SELECT (COALESCE(Max(Line),0))+10 FROM C_InvoiceLine WHERE C_Invoice_ID = TO_NUMBER(?)), ?, 
   517         C_UOM_ID,QtyInvoiced, PriceList,
   518       TO_NUMBER(?), TO_NUMBER(?), TO_NUMBER(?), TO_NUMBER(?), TO_NUMBER(?), TO_NUMBER(?), TO_NUMBER(?), 0, TO_NUMBER(?), TO_NUMBER(?), TO_NUMBER(?), TO_NUMBER(?), TO_NUMBER(?))
   518         PriceActual,PriceLimit, LineNetAmt,
       
   519         ChargeAmt,C_Tax_ID, QuantityOrder, M_Product_UOM_ID,
       
   520         M_Attributesetinstance_ID, PriceStd)
       
   521       VALUES 
       
   522         (?, ?, ?, ?, 'Y', 
       
   523          now(), ?, now(), ?, 
       
   524          ?, ?, (SELECT (COALESCE(Max(Line),0))+10 FROM C_InvoiceLine WHERE C_Invoice_ID=?), ?, ?, 
       
   525          ?, TO_NUMBER(?), TO_NUMBER(?), 
       
   526          TO_NUMBER(?), TO_NUMBER(?), TO_NUMBER(?), 
       
   527          0, ?, TO_NUMBER(?), ?, 
       
   528          ?, TO_NUMBER(?))
   519     </Sql>
   529     </Sql>
   520     <Parameter name="cInvoicelineId"/>
   530     <Parameter name="cInvoicelineId"/>
   521     <Parameter name="cInvoiceId"/>
   531     <Parameter name="cInvoiceId"/>
   522     <Parameter name="adClientId"/>
   532     <Parameter name="adClientId"/>
   523     <Parameter name="adOrgId"/>
   533     <Parameter name="adOrgId"/>
   541     <Parameter name="pricestd"/>
   551     <Parameter name="pricestd"/>
   542   </SqlMethod>
   552   </SqlMethod>
   543   <SqlMethod name="updateC_Order_ID" type="preparedStatement" connection="true" return="rowCount">
   553   <SqlMethod name="updateC_Order_ID" type="preparedStatement" connection="true" return="rowCount">
   544     <SqlMethodComment></SqlMethodComment>
   554     <SqlMethodComment></SqlMethodComment>
   545     <Sql><![CDATA[
   555     <Sql><![CDATA[
   546       UPDATE C_Invoice SET C_Order_ID=TO_NUMBER(?)
   556       UPDATE C_Invoice SET C_Order_id = ?
   547       WHERE C_Invoice_ID = TO_NUMBER(?)
   557       WHERE C_Invoice_ID = ?
   548     ]]></Sql>
   558     ]]></Sql>
   549     <Parameter name="cOrderId"/>
   559     <Parameter name="cOrderId"/>
   550     <Parameter name="cInvoiceId"/>
   560     <Parameter name="cInvoiceId"/>
   551   </SqlMethod>
   561   </SqlMethod>
   552   <SqlMethod name="deleteC_Order_ID" type="preparedStatement" connection="true" return="rowCount">
   562   <SqlMethod name="deleteC_Order_ID" type="preparedStatement" connection="true" return="rowCount">
   553     <SqlMethodComment></SqlMethodComment>
   563     <SqlMethodComment></SqlMethodComment>
   554     <Sql> <![CDATA[
   564     <Sql> <![CDATA[
   555       UPDATE C_INVOICE SET C_Order_ID=NULL
   565       UPDATE C_INVOICE SET C_Order_ID=NULL
   556       WHERE C_Invoice_ID = TO_NUMBER(?)
   566       WHERE C_Invoice_ID=?
   557       AND C_Order_ID IS NOT NULL AND C_Order_ID <> TO_NUMBER(?)
   567       AND C_Order_ID IS NOT NULL AND C_Order_ID <> ?
   558     ]]></Sql>
   568     ]]></Sql>
   559     <Parameter name="cInvoiceId"/>
   569     <Parameter name="cInvoiceId"/>
   560     <Parameter name="cOrderId"/>
   570     <Parameter name="cOrderId"/>
   561   </SqlMethod>
   571   </SqlMethod>
   562   <SqlMethod name="selectProject" type="preparedStatement" return="String" default="">
   572   <SqlMethod name="selectProject" type="preparedStatement" return="String" default="">
   563     <SqlMethodComment></SqlMethodComment>
   573     <SqlMethodComment></SqlMethodComment>
   564     <Sql><![CDATA[
   574     <Sql><![CDATA[
   565       SELECT C_Project_ID
   575       SELECT C_Project_ID
   566       FROM C_INVOICE
   576       FROM C_INVOICE
   567       WHERE C_INVOICE_ID = TO_NUMBER(?) 
   577       WHERE C_INVOICE_ID = ? 
   568     ]]></Sql>
   578     ]]></Sql>
   569     <Parameter name="cInvoiceId"/>
   579     <Parameter name="cInvoiceId"/>
   570   </SqlMethod>
   580   </SqlMethod>
   571   <SqlMethod name="getTax" type="preparedStatement" return="String" default="">
   581   <SqlMethod name="getTax" type="preparedStatement" return="String" default="">
   572     <SqlMethodComment></SqlMethodComment>
   582     <SqlMethodComment></SqlMethodComment>
   573     <Sql><![CDATA[
   583     <Sql><![CDATA[
   574       SELECT C_Tax_ID
   584       SELECT C_Tax_ID
   575       FROM C_ORDERLINE
   585       FROM C_ORDERLINE
   576       WHERE C_ORDERLINE_ID = TO_NUMBER(?) 
   586       WHERE C_ORDERLINE_ID = ? 
   577     ]]></Sql>
   587     ]]></Sql>
   578     <Parameter name="cOrderLineId"/>
   588     <Parameter name="cOrderLineId"/>
   579   </SqlMethod>
   589   </SqlMethod>
   580 </SqlClass>
   590 </SqlClass>