src/org/openbravo/erpCommon/ad_actionButton/CopyFromOrder_data.xsql
changeset 1605 8a0fe0193bef
parent 1173 3b528c8e84a7
child 1674 e6434eaacdad
equal deleted inserted replaced
1604:a1c518e6ed93 1605:8a0fe0193bef
    29       <![CDATA[
    29       <![CDATA[
    30         SELECT M_PRODUCT_ID, PRODUCTNAME, M_ATTRIBUTESETINSTANCE_ID, DESCRIPTION, CLASS_OFFER,
    30         SELECT M_PRODUCT_ID, PRODUCTNAME, M_ATTRIBUTESETINSTANCE_ID, DESCRIPTION, CLASS_OFFER,
    31         OFFER_NAME, PRICESTD, LASTPRICESO, C_TAX_ID, NAMETAX, C_UOM_ID, UOMSYMBOL, '0' AS QTY, '' AS PRICELIMIT, '' AS PRICELIST FROM (
    31         OFFER_NAME, PRICESTD, LASTPRICESO, C_TAX_ID, NAMETAX, C_UOM_ID, UOMSYMBOL, '0' AS QTY, '' AS PRICELIMIT, '' AS PRICELIST FROM (
    32         SELECT CL.M_PRODUCT_ID AS M_PRODUCT_ID, M.NAME AS PRODUCTNAME,
    32         SELECT CL.M_PRODUCT_ID AS M_PRODUCT_ID, M.NAME AS PRODUCTNAME,
    33         MA.M_ATTRIBUTESETINSTANCE_ID AS M_ATTRIBUTESETINSTANCE_ID, MA.DESCRIPTION AS DESCRIPTION,
    33         MA.M_ATTRIBUTESETINSTANCE_ID AS M_ATTRIBUTESETINSTANCE_ID, MA.DESCRIPTION AS DESCRIPTION,
    34         (CASE coalesce(M_GET_OFFER(TO_DATE(NOW()), TO_NUMBER(?), CL.M_PRODUCT_ID),-1) WHEN -1 THEN '' ELSE '' END) AS CLASS_OFFER,
    34         (CASE coalesce(M_GET_OFFER(TO_DATE(NOW()), ?, CL.M_PRODUCT_ID),'-1') WHEN '-1' THEN '' ELSE '' END) AS CLASS_OFFER,
    35         (CASE coalesce(M_GET_OFFER(TO_DATE(NOW()), TO_NUMBER(?), CL.M_PRODUCT_ID),-1) WHEN -1 THEN '' ELSE TO_CHAR(
    35         (CASE coalesce(M_GET_OFFER(TO_DATE(NOW()), ?, CL.M_PRODUCT_ID),'-1') WHEN '-1' THEN '' ELSE TO_CHAR(
    36         M_GET_OFFERS_NAME(TO_DATE(NOW()), TO_NUMBER(?), CL.M_PRODUCT_ID)
    36         M_GET_OFFERS_NAME(TO_DATE(NOW()), ?, CL.M_PRODUCT_ID)
    37         ) END) AS OFFER_NAME,
    37         ) END) AS OFFER_NAME,
    38         (CASE coalesce(M_GET_OFFER(TO_DATE(NOW()), TO_NUMBER(?), CL.M_PRODUCT_ID),-1) WHEN -1 THEN '' ELSE TO_CHAR(M_GET_OFFER(TO_DATE(NOW()), TO_NUMBER(?), CL.M_PRODUCT_ID)) END) AS M_OFFER_ID,
    38         (CASE coalesce(M_GET_OFFER(TO_DATE(NOW()), ?, CL.M_PRODUCT_ID),'-1') WHEN '-1' THEN '' ELSE M_GET_OFFER(TO_DATE(NOW()), ?, CL.M_PRODUCT_ID) END) AS M_OFFER_ID,
    39         BOM_PRICESTD(CL.M_PRODUCT_ID, GET_PRICELIST_VERSION(TO_NUMBER(?), TO_DATE(?))) AS PRICESTD,
    39         BOM_PRICESTD(CL.M_PRODUCT_ID, GET_PRICELIST_VERSION(?, TO_DATE(?))) AS PRICESTD,
    40         (CASE coalesce(M_GET_OFFER(TO_DATE(NOW()), TO_NUMBER(?), CL.M_PRODUCT_ID),-1) WHEN -1 
    40         (CASE coalesce(M_GET_OFFER(TO_DATE(NOW()), ?, CL.M_PRODUCT_ID),'-1') WHEN '-1' 
    41                                                             THEN (SELECT MAX(PRICEACTUAL) 
    41                                                             THEN (SELECT MAX(PRICEACTUAL) 
    42                                                                     FROM C_ORDER, C_ORDERLINE
    42                                                                     FROM C_ORDER, C_ORDERLINE
    43                                                                     WHERE C_ORDER.C_ORDER_ID = C_ORDERLINE.C_ORDER_ID
    43                                                                     WHERE C_ORDER.C_ORDER_ID = C_ORDERLINE.C_ORDER_ID
    44                                                                     AND C_ORDER.DATEORDERED = (SELECT MAX(C_ORDER.DATEORDERED) FROM C_ORDER, C_ORDERLINE
    44                                                                     AND C_ORDER.DATEORDERED = (SELECT MAX(C_ORDER.DATEORDERED) FROM C_ORDER, C_ORDERLINE
    45                                                                     WHERE C_ORDER.C_ORDER_ID = C_ORDERLINE.C_ORDER_ID
    45                                                                     WHERE C_ORDER.C_ORDER_ID = C_ORDERLINE.C_ORDER_ID
    46                                                                     AND C_ORDER.C_BPARTNER_ID = to_number(?)
    46                                                                     AND C_ORDER.C_BPARTNER_ID = ?
    47                                                                     AND C_ORDERLINE.M_PRODUCT_ID = M.M_PRODUCT_ID
    47                                                                     AND C_ORDERLINE.M_PRODUCT_ID = M.M_PRODUCT_ID
    48                                                                     AND C_ORDER.DOCSTATUS = 'CO')
    48                                                                     AND C_ORDER.DOCSTATUS = 'CO')
    49                                                                     AND C_ORDER.C_BPARTNER_ID = to_number(?)
    49                                                                     AND C_ORDER.C_BPARTNER_ID = ?
    50                                                                     AND C_ORDERLINE.M_PRODUCT_ID = M.M_PRODUCT_ID
    50                                                                     AND C_ORDERLINE.M_PRODUCT_ID = M.M_PRODUCT_ID
    51                                                                     AND C_ORDER.DOCSTATUS = 'CO') 
    51                                                                     AND C_ORDER.DOCSTATUS = 'CO') 
    52                                                                ELSE M_GET_OFFERS_PRICE(TO_DATE(NOW()), TO_NUMBER(?), CL.M_PRODUCT_ID,BOM_PRICESTD(CL.M_PRODUCT_ID, GET_PRICELIST_VERSION(TO_NUMBER(?), TO_DATE(?))), 1 /*CL.QTYORDERED what should be here*/, C.M_PRICELIST_ID) 
    52                                                                ELSE M_GET_OFFERS_PRICE(TO_DATE(NOW()), ?, CL.M_PRODUCT_ID,BOM_PRICESTD(CL.M_PRODUCT_ID, GET_PRICELIST_VERSION(?, TO_DATE(?))), 1 /*CL.QTYORDERED what should be here*/, C.M_PRICELIST_ID) 
    53                                                                 END) AS LASTPRICESO,
    53                                                                 END) AS LASTPRICESO,
    54         C_TAX.C_TAX_ID, C_TAX.NAME AS NAMETAX, C_UOM.C_UOM_ID, C_UOM.UOMSYMBOL
    54         C_TAX.C_TAX_ID, C_TAX.NAME AS NAMETAX, C_UOM.C_UOM_ID, C_UOM.UOMSYMBOL
    55         FROM C_ORDER C, C_ORDERLINE CL LEFT JOIN (SELECT M_ATTRIBUTESETINSTANCE_ID , DESCRIPTION FROM M_ATTRIBUTESETINSTANCE) MA ON (CL.M_ATTRIBUTESETINSTANCE_ID = MA.M_ATTRIBUTESETINSTANCE_ID), M_PRODUCT M, C_UOM, C_TAX
    55         FROM C_ORDER C, C_ORDERLINE CL LEFT JOIN (SELECT M_ATTRIBUTESETINSTANCE_ID , DESCRIPTION FROM M_ATTRIBUTESETINSTANCE) MA ON (CL.M_ATTRIBUTESETINSTANCE_ID = MA.M_ATTRIBUTESETINSTANCE_ID), M_PRODUCT M, C_UOM, C_TAX
    56         WHERE C.C_ORDER_ID = CL.C_ORDER_ID
    56         WHERE C.C_ORDER_ID = CL.C_ORDER_ID
    57         AND CL.M_PRODUCT_ID = M.M_PRODUCT_ID
    57         AND CL.M_PRODUCT_ID = M.M_PRODUCT_ID
    58         AND CL.C_TAX_ID = C_TAX.C_TAX_ID
    58         AND CL.C_TAX_ID = C_TAX.C_TAX_ID
    59         AND M.C_UOM_ID = C_UOM.C_UOM_ID
    59         AND M.C_UOM_ID = C_UOM.C_UOM_ID
    60         AND C.C_BPARTNER_ID = to_number(?)
    60         AND C.C_BPARTNER_ID = ?
    61         AND C.DOCSTATUS = 'CO'
    61         AND C.DOCSTATUS = 'CO'
    62         AND M.ISACTIVE = 'Y'
    62         AND M.ISACTIVE = 'Y'
    63         AND C.ISSOTRX = ?
    63         AND C.ISSOTRX = ?
    64         AND C.DATEORDERED >= NOW()-TO_NUMBER(?)
    64         AND C.DATEORDERED >= NOW()-TO_NUMBER(?)
    65         AND C.DATEORDERED <NOW()+1
    65         AND C.DATEORDERED <NOW()+1
   101         WHERE P.M_PRODUCT_ID=PP.M_PRODUCT_ID
   101         WHERE P.M_PRODUCT_ID=PP.M_PRODUCT_ID
   102         AND PP.M_PRICELIST_VERSION_ID=PV.M_PRICELIST_VERSION_ID
   102         AND PP.M_PRICELIST_VERSION_ID=PV.M_PRICELIST_VERSION_ID
   103         AND PV.M_PRICELIST_ID=PL.M_PRICELIST_ID
   103         AND PV.M_PRICELIST_ID=PL.M_PRICELIST_ID
   104         AND PV.ISACTIVE='Y'
   104         AND PV.ISACTIVE='Y'
   105         AND PV.VALIDFROM <= TO_DATE(?)
   105         AND PV.VALIDFROM <= TO_DATE(?)
   106         AND P.M_PRODUCT_ID = TO_NUMBER(?)
   106         AND P.M_PRODUCT_ID = ?
   107         AND PL.M_PRICELIST_ID = TO_NUMBER(?)
   107         AND PL.M_PRICELIST_ID = ?
   108         ORDER BY PV.VALIDFROM DESC
   108         ORDER BY PV.VALIDFROM DESC
   109       ]]>
   109       ]]>
   110     </Sql>
   110     </Sql>
   111     <Parameter name="dateordered"/>
   111     <Parameter name="dateordered"/>
   112     <Parameter name="MProductID"/>
   112     <Parameter name="MProductID"/>
   117     <Sql>
   117     <Sql>
   118       <![CDATA[
   118       <![CDATA[
   119         INSERT INTO C_ORDERLINE (C_ORDERLINE_ID, AD_CLIENT_ID, AD_ORG_ID, CREATED, CREATEDBY, UPDATED, UPDATEDBY,
   119         INSERT INTO C_ORDERLINE (C_ORDERLINE_ID, AD_CLIENT_ID, AD_ORG_ID, CREATED, CREATEDBY, UPDATED, UPDATEDBY,
   120         C_ORDER_ID, LINE, C_BPARTNER_ID, C_BPARTNER_LOCATION_ID, DATEORDERED, DATEPROMISED, M_PRODUCT_ID, M_WAREHOUSE_ID, C_UOM_ID,
   120         C_ORDER_ID, LINE, C_BPARTNER_ID, C_BPARTNER_LOCATION_ID, DATEORDERED, DATEPROMISED, M_PRODUCT_ID, M_WAREHOUSE_ID, C_UOM_ID,
   121         QTYORDERED, C_CURRENCY_ID, PRICELIST, PRICEACTUAL, PRICELIMIT, C_TAX_ID, M_ATTRIBUTESETINSTANCE_ID) 
   121         QTYORDERED, C_CURRENCY_ID, PRICELIST, PRICEACTUAL, PRICELIMIT, C_TAX_ID, M_ATTRIBUTESETINSTANCE_ID) 
   122         VALUES (TO_NUMBER(?),TO_NUMBER(?),TO_NUMBER(?),NOW(),TO_NUMBER(?),NOW(),TO_NUMBER(?),
   122         VALUES (?,?,?,NOW(),?,NOW(),?,
   123                 TO_NUMBER(?),(SELECT (coalesce(Max(Line),0))+10 FROM C_OrderLine WHERE C_Order_ID=TO_NUMBER(?)),TO_NUMBER(?),TO_NUMBER(?),TO_DATE(?),TO_DATE(?),TO_NUMBER(?),TO_NUMBER(?),TO_NUMBER(?),
   123                 ?,(SELECT (coalesce(Max(Line),0))+10 FROM C_OrderLine WHERE C_Order_id = ?),?,?,TO_DATE(?),TO_DATE(?),?,?,?,
   124                 TO_NUMBER(?),TO_NUMBER(?),TO_NUMBER(?),TO_NUMBER(?),TO_NUMBER(?),TO_NUMBER(?), TO_NUMBER(?))
   124                 ?,?,?,?,?,?, ?)
   125       ]]>
   125       ]]>
   126     </Sql>
   126     </Sql>
   127     <Parameter name="cOrderlineId"/>
   127     <Parameter name="cOrderlineId"/>
   128     <Parameter name="adClientId"/>
   128     <Parameter name="adClientId"/>
   129     <Parameter name="adOrgId"/>
   129     <Parameter name="adOrgId"/>