src/org/openbravo/erpCommon/info/InvoiceLine_data.xsql
changeset 712 aa0c40278c5a
parent 423 ecf368072c48
child 1605 8a0fe0193bef
--- a/src/org/openbravo/erpCommon/info/InvoiceLine_data.xsql	Mon Apr 21 19:03:40 2008 +0000
+++ b/src/org/openbravo/erpCommon/info/InvoiceLine_data.xsql	Mon Apr 21 19:06:47 2008 +0000
@@ -28,32 +28,40 @@
       <SqlMethodComment></SqlMethodComment>
       <Sql>
       <![CDATA[
-          SELECT IL.C_INVOICELINE_ID, b.NAME AS BPARTNER_NAME, p.NAME AS PRODUCT_NAME, I.DATEINVOICED, I.DOCUMENTNO, 
-          I.ISSOTRX, IL.QTYINVOICED AS QTY, IL.PRICEACTUAL, IL.LINENETAMT, 
-          (I.DOCUMENTNO || ' - ' || I.DATEINVOICED || ' - ' || I.GRANDTOTAL || ' - ' || IL.LINE || ' - ' || IL.LINENETAMT) AS LINE_TEXT
-          FROM C_INVOICE I, C_INVOICELINE IL, M_PRODUCT p, C_BPARTNER b
-          WHERE I.C_INVOICE_ID = IL.C_INVOICE_ID 
-          AND I.C_BPARTNER_ID = b.C_BPARTNER_ID 
-          AND IL.M_PRODUCT_ID = p.M_PRODUCT_ID
-          AND I.AD_CLIENT_ID IN ('1') 
-          AND I.AD_ORG_ID IN ('1') 
-          AND I.IsActive='Y' 
-          AND I.ISSOTRX = 'Y'
-          ORDER BY b.NAME, I.DATEINVOICED, I.DOCUMENTNO, IL.LINE
+      	SELECT * FROM ( SELECT '0' AS RN1, A.* FROM (
+	          SELECT IL.C_INVOICELINE_ID, b.NAME AS BPARTNER_NAME, p.NAME AS PRODUCT_NAME, I.DATEINVOICED, I.DOCUMENTNO, 
+	          I.ISSOTRX, IL.QTYINVOICED AS QTY, IL.PRICEACTUAL, IL.LINENETAMT, 
+	          (I.DOCUMENTNO || ' - ' || I.DATEINVOICED || ' - ' || I.GRANDTOTAL || ' - ' || IL.LINE || ' - ' || IL.LINENETAMT) AS LINE_TEXT,
+	          IL.C_INVOICELINE_ID || '#' || (I.DOCUMENTNO || ' - ' || I.DATEINVOICED || ' - ' || I.GRANDTOTAL || ' - ' || IL.LINE || ' - ' || IL.LINENETAMT) AS ROWKEY
+	          FROM C_INVOICE I, C_INVOICELINE IL, M_PRODUCT p, C_BPARTNER b
+	          WHERE I.C_INVOICE_ID = IL.C_INVOICE_ID 
+	          AND I.C_BPARTNER_ID = b.C_BPARTNER_ID 
+	          AND IL.M_PRODUCT_ID = p.M_PRODUCT_ID
+	          AND I.AD_CLIENT_ID IN ('1') 
+	          AND I.AD_ORG_ID IN ('1') 
+	          AND I.IsActive='Y' 
+	          AND I.ISSOTRX = 'Y'
+	          ORDER BY b.NAME
+          	) A ) B
+        WHERE 1=1
         ]]>
         </Sql>
         <Field name="position" value="count"/>
+        <Parameter name="rownum" type="replace" optional="true" after="FROM ( SELECT " text="'0'" />
         <Parameter name="adUserClient" type="replace" optional="true" after="I.AD_CLIENT_ID IN (" text="'1'"/>
         <Parameter name="adUserOrg" type="replace" optional="true" after="I.AD_ORG_ID IN (" text="'1'"/>
         <Parameter name="documentno" optional="true" after="AND I.IsActive='Y'"><![CDATA[ AND UPPER(I.DocumentNo) LIKE UPPER(?) ]]></Parameter>
         <Parameter name="description" optional="true" after="AND I.IsActive='Y'"><![CDATA[ AND UPPER(I.Description) LIKE UPPER(?) ]]></Parameter>
         <Parameter name="orderReference" optional="true" after="AND I.IsActive='Y'"><![CDATA[ AND UPPER(I.POReference) LIKE UPPER(?) ]]></Parameter>
         <Parameter name="businesPartner" optional="true" after="AND I.IsActive='Y'"><![CDATA[ AND I.C_BPartner_ID = TO_NUMBER(?) ]]></Parameter>
-        <Parameter name="dateFrom" optional="true" after="AND I.IsActive='Y'"><![CDATA[ AND I.LINENETAMT >= to_date(?) ]]></Parameter>
-        <Parameter name="dateTo" optional="true" after="AND I.IsActive='Y'"><![CDATA[ AND I.LINENETAMT < to_date(?) ]]></Parameter>
-        <Parameter name="grandTotalFrom" optional="true" after="AND I.IsActive='Y'"><![CDATA[ AND I.GrandTotal >= ? ]]></Parameter>
-        <Parameter name="grandTotalTo" optional="true" after="AND I.IsActive='Y'"><![CDATA[ AND I.GrandTotal <= ? ]]></Parameter>
+        <Parameter name="dateFrom" optional="true" after="AND I.IsActive='Y'"><![CDATA[ AND I.DATEINVOICED >= TO_DATE(?) ]]></Parameter>
+        <Parameter name="dateTo" optional="true" after="AND I.IsActive='Y'"><![CDATA[ AND I.DATEINVOICED < TO_DATE(?) ]]></Parameter>
+        <Parameter name="grandTotalFrom" optional="true" after="AND I.IsActive='Y'"><![CDATA[ AND I.GrandTotal >= TO_NUMBER(?) ]]></Parameter>
+        <Parameter name="grandTotalTo" optional="true" after="AND I.IsActive='Y'"><![CDATA[ AND I.GrandTotal <= TO_NUMBER(?) ]]></Parameter>
         <Parameter name="product" optional="true" after="AND I.IsActive='Y'"><![CDATA[ AND IL.M_Product_ID = TO_NUMBER(?) ]]></Parameter>
+        <Parameter name="orderBy" type="replace" optional="true" after="ORDER BY " text="b.NAME" />
+        <Parameter name="oraLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[AND RN1 BETWEEN ]]></Parameter>
+        <Parameter name="pgLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[LIMIT ]]></Parameter>
       </SqlMethod>
    <SqlMethod name="set" type="constant" return="multiple">
       <SqlMethodComment></SqlMethodComment>