src/org/openbravo/erpCommon/info/SalesOrderLine_data.xsql
changeset 712 aa0c40278c5a
parent 423 ecf368072c48
child 1605 8a0fe0193bef
equal deleted inserted replaced
711:4e8209a9d749 712:aa0c40278c5a
    26    <SqlClassComment></SqlClassComment>
    26    <SqlClassComment></SqlClassComment>
    27    <SqlMethod name="select" type="preparedStatement" return="multiple">
    27    <SqlMethod name="select" type="preparedStatement" return="multiple">
    28       <SqlMethodComment></SqlMethodComment>
    28       <SqlMethodComment></SqlMethodComment>
    29       <Sql>
    29       <Sql>
    30       <![CDATA[
    30       <![CDATA[
       
    31       SELECT * FROM ( SELECT '0' AS RN1, A.* FROM (  
    31           SELECT ol.C_ORDERLINE_ID, b.NAME AS BPARTNER_NAME, p.NAME AS PRODUCT_NAME, o.DATEORDERED, o.DOCUMENTNO, 
    32           SELECT ol.C_ORDERLINE_ID, b.NAME AS BPARTNER_NAME, p.NAME AS PRODUCT_NAME, o.DATEORDERED, o.DOCUMENTNO, 
    32           o.ISSOTRX, ol.QTYORDERED AS QTY, ol.PRICEACTUAL, ol.LINENETAMT, 
    33           o.ISSOTRX, ol.QTYORDERED AS QTY, ol.PRICEACTUAL, ol.LINENETAMT, 
    33           (o.DOCUMENTNO || ' - ' || o.DATEORDERED || ' - ' || o.GRANDTOTAL || ' - ' || ol.LINE || ' - ' || ol.LINENETAMT) AS LINE_TEXT
    34           (o.DOCUMENTNO || ' - ' || o.DATEORDERED || ' - ' || o.GRANDTOTAL || ' - ' || ol.LINE || ' - ' || ol.LINENETAMT) AS LINE_TEXT,
       
    35 			 ol.C_ORDERLINE_ID || '#' || o.DOCUMENTNO || ' - ' || o.DATEORDERED || ' - ' || o.GRANDTOTAL || ' - ' || ol.LINE || ' - ' || ol.LINENETAMT as rowkey
    34           FROM C_ORDER o, C_ORDERLINE ol, M_PRODUCT p, C_BPARTNER b
    36           FROM C_ORDER o, C_ORDERLINE ol, M_PRODUCT p, C_BPARTNER b
    35           WHERE o.C_ORDER_ID = ol.C_ORDER_ID 
    37           WHERE o.C_ORDER_ID = ol.C_ORDER_ID 
    36           AND o.C_BPARTNER_ID = b.C_BPARTNER_ID 
    38           AND o.C_BPARTNER_ID = b.C_BPARTNER_ID 
    37           AND ol.M_PRODUCT_ID = p.M_PRODUCT_ID
    39           AND ol.M_PRODUCT_ID = p.M_PRODUCT_ID
    38           AND o.AD_CLIENT_ID IN ('1') 
    40           AND o.AD_CLIENT_ID IN ('1') 
    39           AND o.AD_ORG_ID IN ('1') 
    41           AND o.AD_ORG_ID IN ('1') 
    40           AND o.IsActive='Y' 
    42           AND o.IsActive='Y' 
    41           AND o.ISSOTRX = 'Y'
    43           AND o.ISSOTRX = 'Y'
    42           ORDER BY b.NAME, o.DATEORDERED, o.DOCUMENTNO, ol.LINE
    44           ORDER BY ol.C_ORDERLINE_ID
       
    45 		) A ) B
       
    46 		WHERE 1=1
    43         ]]>
    47         ]]>
    44         </Sql>
    48         </Sql>
    45         <Field name="position" value="count"/>
    49         <Field name="position" value="count"/>
       
    50 			<Parameter name="rownum" type="replace" optional="true" after="FROM ( SELECT " text="'0'" />
    46         <Parameter name="adUserClient" type="replace" optional="true" after="o.AD_CLIENT_ID IN (" text="'1'"/>
    51         <Parameter name="adUserClient" type="replace" optional="true" after="o.AD_CLIENT_ID IN (" text="'1'"/>
    47         <Parameter name="adUserOrg" type="replace" optional="true" after="o.AD_ORG_ID IN (" text="'1'"/>
    52         <Parameter name="adUserOrg" type="replace" optional="true" after="o.AD_ORG_ID IN (" text="'1'"/>
    48         <Parameter name="documentno" optional="true" after="AND o.IsActive='Y'"><![CDATA[ AND UPPER(o.DocumentNo) LIKE UPPER(?) ]]></Parameter>
    53         <Parameter name="documentno" optional="true" after="AND o.IsActive='Y'"><![CDATA[ AND UPPER(o.DocumentNo) LIKE UPPER(?) ]]></Parameter>
    49         <Parameter name="description" optional="true" after="AND o.IsActive='Y'"><![CDATA[ AND UPPER(o.Description) LIKE UPPER(?) ]]></Parameter>
    54         <Parameter name="description" optional="true" after="AND o.IsActive='Y'"><![CDATA[ AND UPPER(o.Description) LIKE UPPER(?) ]]></Parameter>
    50         <Parameter name="orderReference" optional="true" after="AND o.IsActive='Y'"><![CDATA[ AND UPPER(o.POReference) LIKE UPPER(?) ]]></Parameter>
    55         <Parameter name="orderReference" optional="true" after="AND o.IsActive='Y'"><![CDATA[ AND UPPER(o.POReference) LIKE UPPER(?) ]]></Parameter>
    54         <Parameter name="grandTotalFrom" optional="true" after="AND o.IsActive='Y'"><![CDATA[ AND o.GrandTotal >= ? ]]></Parameter>
    59         <Parameter name="grandTotalFrom" optional="true" after="AND o.IsActive='Y'"><![CDATA[ AND o.GrandTotal >= ? ]]></Parameter>
    55         <Parameter name="grandTotalTo" optional="true" after="AND o.IsActive='Y'"><![CDATA[ AND o.GrandTotal <= ? ]]></Parameter>
    60         <Parameter name="grandTotalTo" optional="true" after="AND o.IsActive='Y'"><![CDATA[ AND o.GrandTotal <= ? ]]></Parameter>
    56         <Parameter name="product" optional="true" after="AND o.IsActive='Y'"><![CDATA[ AND ol.M_Product_ID = TO_NUMBER(?) ]]></Parameter>
    61         <Parameter name="product" optional="true" after="AND o.IsActive='Y'"><![CDATA[ AND ol.M_Product_ID = TO_NUMBER(?) ]]></Parameter>
    57         <Parameter name="isdelivered" optional="true" type="none" after="AND o.IsActive='Y'"><![CDATA[ AND (ol.QTYORDERED - COALESCE(ol.QTYDELIVERED, 0)) <> 0 ]]></Parameter>
    62         <Parameter name="isdelivered" optional="true" type="none" after="AND o.IsActive='Y'"><![CDATA[ AND (ol.QTYORDERED - COALESCE(ol.QTYDELIVERED, 0)) <> 0 ]]></Parameter>
    58         <Parameter name="isinvoiced" optional="true" type="none" after="AND o.IsActive='Y'"><![CDATA[ AND (ol.QTYORDERED - COALESCE(ol.QTYINVOICED, 0)) <> 0 ]]></Parameter>
    63         <Parameter name="isinvoiced" optional="true" type="none" after="AND o.IsActive='Y'"><![CDATA[ AND (ol.QTYORDERED - COALESCE(ol.QTYINVOICED, 0)) <> 0 ]]></Parameter>
       
    64 			<Parameter name="orderBy" type="replace" optional="true" after="ORDER BY " text="ol.C_ORDERLINE_ID" />
       
    65         <Parameter name="oraLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[AND RN1 BETWEEN ]]></Parameter>
       
    66         <Parameter name="pgLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[LIMIT ]]></Parameter>
    59    </SqlMethod>
    67    </SqlMethod>
    60    <SqlMethod name="set" type="constant" return="multiple">
    68    <SqlMethod name="set" type="constant" return="multiple">
    61       <SqlMethodComment></SqlMethodComment>
    69       <SqlMethodComment></SqlMethodComment>
    62       <Sql></Sql>
    70       <Sql></Sql>
    63    </SqlMethod>
    71    </SqlMethod>
    64    <SqlMethod name="selectSOTrx" type="preparedStatement" return="multiple">
    72    <SqlMethod name="selectSOTrx" type="preparedStatement" return="multiple">
    65       <SqlMethodComment></SqlMethodComment>
    73       <SqlMethodComment></SqlMethodComment>
    66       <Sql>
    74       <Sql>
    67       <![CDATA[
    75       <![CDATA[
       
    76       SELECT * FROM ( SELECT '0' AS RN1, A.* FROM (  
    68           SELECT ol.C_ORDERLINE_ID, b.NAME AS BPARTNER_NAME, p.NAME AS PRODUCT_NAME, o.DATEORDERED, o.DOCUMENTNO, 
    77           SELECT ol.C_ORDERLINE_ID, b.NAME AS BPARTNER_NAME, p.NAME AS PRODUCT_NAME, o.DATEORDERED, o.DOCUMENTNO, 
    69           o.ISSOTRX, ol.QTYORDERED AS QTY, ol.PRICEACTUAL, ol.LINENETAMT, 
    78           o.ISSOTRX, ol.QTYORDERED AS QTY, ol.PRICEACTUAL, ol.LINENETAMT, 
    70           (o.DOCUMENTNO || ' - ' || o.DATEORDERED || ' - ' || o.GRANDTOTAL || ' - ' || ol.LINE || ' - ' || ol.LINENETAMT) AS LINE_TEXT
    79           (o.DOCUMENTNO || ' - ' || o.DATEORDERED || ' - ' || o.GRANDTOTAL || ' - ' || ol.LINE || ' - ' || ol.LINENETAMT) AS LINE_TEXT,
       
    80 			  ol.C_ORDERLINE_ID || '#' || o.DOCUMENTNO || ' - ' || o.DATEORDERED || ' - ' || o.GRANDTOTAL || ' - ' || ol.LINE || ' - ' || ol.LINENETAMT as rowkey
    71           FROM C_ORDERLINE ol left join M_MATCHPO mp on ol.C_ORDERLINE_ID = mp.C_ORDERLINE_ID 
    81           FROM C_ORDERLINE ol left join M_MATCHPO mp on ol.C_ORDERLINE_ID = mp.C_ORDERLINE_ID 
    72                                                     AND mp.C_INVOICELINE_ID IS NOT NULL
    82                                                     AND mp.C_INVOICELINE_ID IS NOT NULL
    73                               left join M_MATCHPO mp2 on ol.C_ORDERLINE_ID = mp2.C_ORDERLINE_ID
    83                               left join M_MATCHPO mp2 on ol.C_ORDERLINE_ID = mp2.C_ORDERLINE_ID
    74                                                     AND mp2.M_INOUTLINE_ID IS NOT NULL,
    84                                                     AND mp2.M_INOUTLINE_ID IS NOT NULL,
    75                 C_ORDER o,  M_PRODUCT p, C_BPARTNER b
    85                 C_ORDER o,  M_PRODUCT p, C_BPARTNER b
    81           AND o.IsActive='Y' 
    91           AND o.IsActive='Y' 
    82           AND o.ISSOTRX = 'N'
    92           AND o.ISSOTRX = 'N'
    83           GROUP BY ol.C_ORDERLINE_ID, b.NAME, p.NAME, o.DATEORDERED, o.DOCUMENTNO, 
    93           GROUP BY ol.C_ORDERLINE_ID, b.NAME, p.NAME, o.DATEORDERED, o.DOCUMENTNO, 
    84           o.ISSOTRX, ol.QTYORDERED, ol.PRICEACTUAL, ol.LINENETAMT, o.GRANDTOTAL, ol.LINE, ol.LINENETAMT
    94           o.ISSOTRX, ol.QTYORDERED, ol.PRICEACTUAL, ol.LINENETAMT, o.GRANDTOTAL, ol.LINE, ol.LINENETAMT
    85           HAVING 1=1 
    95           HAVING 1=1 
    86           ORDER BY b.NAME, o.DATEORDERED, o.DOCUMENTNO, ol.LINE
    96           ORDER BY ol.C_ORDERLINE_ID
       
    97 		) A ) B
       
    98 		WHERE 1=1
    87         ]]>
    99         ]]>
    88         </Sql>
   100         </Sql>
    89         <Field name="position" value="count"/>
   101         <Field name="position" value="count"/>
       
   102 			<Parameter name="rownum" type="replace" optional="true" after="FROM ( SELECT " text="'0'" />
    90         <Parameter name="adUserClient" type="replace" optional="true" after="o.AD_CLIENT_ID IN (" text="'1'"/>
   103         <Parameter name="adUserClient" type="replace" optional="true" after="o.AD_CLIENT_ID IN (" text="'1'"/>
    91         <Parameter name="adUserOrg" type="replace" optional="true" after="o.AD_ORG_ID IN (" text="'1'"/>
   104         <Parameter name="adUserOrg" type="replace" optional="true" after="o.AD_ORG_ID IN (" text="'1'"/>
    92         <Parameter name="documentno" optional="true" after="AND o.IsActive='Y'"><![CDATA[ AND UPPER(o.DocumentNo) LIKE UPPER(?) ]]></Parameter>
   105         <Parameter name="documentno" optional="true" after="AND o.IsActive='Y'"><![CDATA[ AND UPPER(o.DocumentNo) LIKE UPPER(?) ]]></Parameter>
    93         <Parameter name="description" optional="true" after="AND o.IsActive='Y'"><![CDATA[ AND UPPER(o.Description) LIKE UPPER(?) ]]></Parameter>
   106         <Parameter name="description" optional="true" after="AND o.IsActive='Y'"><![CDATA[ AND UPPER(o.Description) LIKE UPPER(?) ]]></Parameter>
    94         <Parameter name="orderReference" optional="true" after="AND o.IsActive='Y'"><![CDATA[ AND UPPER(o.POReference) LIKE UPPER(?) ]]></Parameter>
   107         <Parameter name="orderReference" optional="true" after="AND o.IsActive='Y'"><![CDATA[ AND UPPER(o.POReference) LIKE UPPER(?) ]]></Parameter>
    98         <Parameter name="grandTotalFrom" optional="true" after="AND o.IsActive='Y'"><![CDATA[ AND o.GrandTotal >= ? ]]></Parameter>
   111         <Parameter name="grandTotalFrom" optional="true" after="AND o.IsActive='Y'"><![CDATA[ AND o.GrandTotal >= ? ]]></Parameter>
    99         <Parameter name="grandTotalTo" optional="true" after="AND o.IsActive='Y'"><![CDATA[ AND o.GrandTotal <= ? ]]></Parameter>
   112         <Parameter name="grandTotalTo" optional="true" after="AND o.IsActive='Y'"><![CDATA[ AND o.GrandTotal <= ? ]]></Parameter>
   100         <Parameter name="product" optional="true" after="AND o.IsActive='Y'"><![CDATA[ AND ol.M_Product_ID = TO_NUMBER(?) ]]></Parameter>
   113         <Parameter name="product" optional="true" after="AND o.IsActive='Y'"><![CDATA[ AND ol.M_Product_ID = TO_NUMBER(?) ]]></Parameter>
   101         <Parameter name="isdelivered" optional="true" type="none" after="HAVING 1=1 "><![CDATA[ AND (ol.QTYORDERED - COALESCE(SUM(mp2.QTY), 0)) <> 0 ]]></Parameter>
   114         <Parameter name="isdelivered" optional="true" type="none" after="HAVING 1=1 "><![CDATA[ AND (ol.QTYORDERED - COALESCE(SUM(mp2.QTY), 0)) <> 0 ]]></Parameter>
   102         <Parameter name="isinvoiced" optional="true" type="none" after="HAVING 1=1 "><![CDATA[ AND (ol.QTYORDERED - COALESCE(SUM(mp.QTY),0)) <> 0 ]]></Parameter>
   115         <Parameter name="isinvoiced" optional="true" type="none" after="HAVING 1=1 "><![CDATA[ AND (ol.QTYORDERED - COALESCE(SUM(mp.QTY),0)) <> 0 ]]></Parameter>
       
   116 			<Parameter name="orderBy" type="replace" optional="true" after="ORDER BY " text="ol.C_ORDERLINE_ID" />
       
   117         <Parameter name="oraLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[AND RN1 BETWEEN ]]></Parameter>
       
   118         <Parameter name="pgLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[LIMIT ]]></Parameter>
   103    </SqlMethod>
   119    </SqlMethod>
   104    <SqlMethod name="selectKey" type="preparedStatement" return="multiple">
   120    <SqlMethod name="selectKey" type="preparedStatement" return="multiple">
   105       <SqlMethodComment></SqlMethodComment>
   121       <SqlMethodComment></SqlMethodComment>
   106       <Sql> 
   122       <Sql> 
   107       <![CDATA[
   123       <![CDATA[
   120         AND (ol.QTYORDERED - COALESCE(ol.QTYDELIVERED, 0)) > 0 
   136         AND (ol.QTYORDERED - COALESCE(ol.QTYDELIVERED, 0)) > 0 
   121         AND o.ISSOTRX = 'Y'
   137         AND o.ISSOTRX = 'Y'
   122         ORDER BY b.NAME, o.DATEORDERED, o.DOCUMENTNO, ol.LINE
   138         ORDER BY b.NAME, o.DATEORDERED, o.DOCUMENTNO, ol.LINE
   123       ]]>
   139       ]]>
   124       </Sql>
   140       </Sql>
   125         <Parameter name="adUserClient" type="replace" optional="true" after="o.AD_Client_ID IN (" text="'1'"/>
   141         <Parameter name="adUserClient" type="replace" optional="true" after="AND o.AD_CLIENT_ID IN (" text="'1'"/>
   126         <Parameter name="adUserOrg" type="replace" optional="true" after="o.AD_Org_ID IN (" text="'1'"/>
   142         <Parameter name="adUserOrg" type="replace" optional="true" after="AND o.AD_ORG_ID IN (" text="'1'"/>
   127         <Parameter name="key"/>
   143         <Parameter name="key"/>
   128    </SqlMethod>
   144    </SqlMethod>
   129    <SqlMethod name="selectKeySOTrx" type="preparedStatement" return="multiple">
   145    <SqlMethod name="selectKeySOTrx" type="preparedStatement" return="multiple">
   130       <SqlMethodComment></SqlMethodComment>
   146       <SqlMethodComment></SqlMethodComment>
   131       <Sql> 
   147       <Sql> 
   151         HAVING (ol.QTYORDERED - COALESCE(SUM(mp.QTY),0)) > 0 
   167         HAVING (ol.QTYORDERED - COALESCE(SUM(mp.QTY),0)) > 0 
   152         AND (ol.QTYORDERED - COALESCE(SUM(mp2.QTY), 0)) > 0
   168         AND (ol.QTYORDERED - COALESCE(SUM(mp2.QTY), 0)) > 0
   153         ORDER BY b.NAME, o.DATEORDERED, o.DOCUMENTNO, ol.LINE
   169         ORDER BY b.NAME, o.DATEORDERED, o.DOCUMENTNO, ol.LINE
   154       ]]>
   170       ]]>
   155       </Sql>
   171       </Sql>
   156         <Parameter name="adUserClient" type="replace" optional="true" after="o.AD_Client_ID IN (" text="'1'"/>
   172         <Parameter name="adUserClient" type="replace" optional="true" after="AND o.AD_CLIENT_ID IN (" text="'1'"/>
   157         <Parameter name="adUserOrg" type="replace" optional="true" after="o.AD_Org_ID IN (" text="'1'"/>
   173         <Parameter name="adUserOrg" type="replace" optional="true" after="AND o.AD_ORG_ID IN (" text="'1'"/>
   158         <Parameter name="key"/>
   174         <Parameter name="key"/>
   159    </SqlMethod>
   175    </SqlMethod>
   160    <SqlMethod name="selectBPartner" type="preparedStatement" return="String" default="">
   176    <SqlMethod name="selectBPartner" type="preparedStatement" return="String" default="">
   161       <SqlMethodComment></SqlMethodComment>
   177       <SqlMethodComment></SqlMethodComment>
   162       <Sql> 
   178       <Sql>