src/org/openbravo/erpCommon/info/InvoiceLine_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 IL.C_INVOICELINE_ID, b.NAME AS BPARTNER_NAME, p.NAME AS PRODUCT_NAME, I.DATEINVOICED, I.DOCUMENTNO, 
    31       	SELECT * FROM ( SELECT '0' AS RN1, A.* FROM (
    32           I.ISSOTRX, IL.QTYINVOICED AS QTY, IL.PRICEACTUAL, IL.LINENETAMT, 
    32 	          SELECT IL.C_INVOICELINE_ID, b.NAME AS BPARTNER_NAME, p.NAME AS PRODUCT_NAME, I.DATEINVOICED, I.DOCUMENTNO, 
    33           (I.DOCUMENTNO || ' - ' || I.DATEINVOICED || ' - ' || I.GRANDTOTAL || ' - ' || IL.LINE || ' - ' || IL.LINENETAMT) AS LINE_TEXT
    33 	          I.ISSOTRX, IL.QTYINVOICED AS QTY, IL.PRICEACTUAL, IL.LINENETAMT, 
    34           FROM C_INVOICE I, C_INVOICELINE IL, M_PRODUCT p, C_BPARTNER b
    34 	          (I.DOCUMENTNO || ' - ' || I.DATEINVOICED || ' - ' || I.GRANDTOTAL || ' - ' || IL.LINE || ' - ' || IL.LINENETAMT) AS LINE_TEXT,
    35           WHERE I.C_INVOICE_ID = IL.C_INVOICE_ID 
    35 	          IL.C_INVOICELINE_ID || '#' || (I.DOCUMENTNO || ' - ' || I.DATEINVOICED || ' - ' || I.GRANDTOTAL || ' - ' || IL.LINE || ' - ' || IL.LINENETAMT) AS ROWKEY
    36           AND I.C_BPARTNER_ID = b.C_BPARTNER_ID 
    36 	          FROM C_INVOICE I, C_INVOICELINE IL, M_PRODUCT p, C_BPARTNER b
    37           AND IL.M_PRODUCT_ID = p.M_PRODUCT_ID
    37 	          WHERE I.C_INVOICE_ID = IL.C_INVOICE_ID 
    38           AND I.AD_CLIENT_ID IN ('1') 
    38 	          AND I.C_BPARTNER_ID = b.C_BPARTNER_ID 
    39           AND I.AD_ORG_ID IN ('1') 
    39 	          AND IL.M_PRODUCT_ID = p.M_PRODUCT_ID
    40           AND I.IsActive='Y' 
    40 	          AND I.AD_CLIENT_ID IN ('1') 
    41           AND I.ISSOTRX = 'Y'
    41 	          AND I.AD_ORG_ID IN ('1') 
    42           ORDER BY b.NAME, I.DATEINVOICED, I.DOCUMENTNO, IL.LINE
    42 	          AND I.IsActive='Y' 
       
    43 	          AND I.ISSOTRX = 'Y'
       
    44 	          ORDER BY b.NAME
       
    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="I.AD_CLIENT_ID IN (" text="'1'"/>
    51         <Parameter name="adUserClient" type="replace" optional="true" after="I.AD_CLIENT_ID IN (" text="'1'"/>
    47         <Parameter name="adUserOrg" type="replace" optional="true" after="I.AD_ORG_ID IN (" text="'1'"/>
    52         <Parameter name="adUserOrg" type="replace" optional="true" after="I.AD_ORG_ID IN (" text="'1'"/>
    48         <Parameter name="documentno" optional="true" after="AND I.IsActive='Y'"><![CDATA[ AND UPPER(I.DocumentNo) LIKE UPPER(?) ]]></Parameter>
    53         <Parameter name="documentno" optional="true" after="AND I.IsActive='Y'"><![CDATA[ AND UPPER(I.DocumentNo) LIKE UPPER(?) ]]></Parameter>
    49         <Parameter name="description" optional="true" after="AND I.IsActive='Y'"><![CDATA[ AND UPPER(I.Description) LIKE UPPER(?) ]]></Parameter>
    54         <Parameter name="description" optional="true" after="AND I.IsActive='Y'"><![CDATA[ AND UPPER(I.Description) LIKE UPPER(?) ]]></Parameter>
    50         <Parameter name="orderReference" optional="true" after="AND I.IsActive='Y'"><![CDATA[ AND UPPER(I.POReference) LIKE UPPER(?) ]]></Parameter>
    55         <Parameter name="orderReference" optional="true" after="AND I.IsActive='Y'"><![CDATA[ AND UPPER(I.POReference) LIKE UPPER(?) ]]></Parameter>
    51         <Parameter name="businesPartner" optional="true" after="AND I.IsActive='Y'"><![CDATA[ AND I.C_BPartner_ID = TO_NUMBER(?) ]]></Parameter>
    56         <Parameter name="businesPartner" optional="true" after="AND I.IsActive='Y'"><![CDATA[ AND I.C_BPartner_ID = TO_NUMBER(?) ]]></Parameter>
    52         <Parameter name="dateFrom" optional="true" after="AND I.IsActive='Y'"><![CDATA[ AND I.LINENETAMT >= to_date(?) ]]></Parameter>
    57         <Parameter name="dateFrom" optional="true" after="AND I.IsActive='Y'"><![CDATA[ AND I.DATEINVOICED >= TO_DATE(?) ]]></Parameter>
    53         <Parameter name="dateTo" optional="true" after="AND I.IsActive='Y'"><![CDATA[ AND I.LINENETAMT < to_date(?) ]]></Parameter>
    58         <Parameter name="dateTo" optional="true" after="AND I.IsActive='Y'"><![CDATA[ AND I.DATEINVOICED < TO_DATE(?) ]]></Parameter>
    54         <Parameter name="grandTotalFrom" optional="true" after="AND I.IsActive='Y'"><![CDATA[ AND I.GrandTotal >= ? ]]></Parameter>
    59         <Parameter name="grandTotalFrom" optional="true" after="AND I.IsActive='Y'"><![CDATA[ AND I.GrandTotal >= TO_NUMBER(?) ]]></Parameter>
    55         <Parameter name="grandTotalTo" optional="true" after="AND I.IsActive='Y'"><![CDATA[ AND I.GrandTotal <= ? ]]></Parameter>
    60         <Parameter name="grandTotalTo" optional="true" after="AND I.IsActive='Y'"><![CDATA[ AND I.GrandTotal <= TO_NUMBER(?) ]]></Parameter>
    56         <Parameter name="product" optional="true" after="AND I.IsActive='Y'"><![CDATA[ AND IL.M_Product_ID = TO_NUMBER(?) ]]></Parameter>
    61         <Parameter name="product" optional="true" after="AND I.IsActive='Y'"><![CDATA[ AND IL.M_Product_ID = TO_NUMBER(?) ]]></Parameter>
       
    62         <Parameter name="orderBy" type="replace" optional="true" after="ORDER BY " text="b.NAME" />
       
    63         <Parameter name="oraLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[AND RN1 BETWEEN ]]></Parameter>
       
    64         <Parameter name="pgLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[LIMIT ]]></Parameter>
    57       </SqlMethod>
    65       </SqlMethod>
    58    <SqlMethod name="set" type="constant" return="multiple">
    66    <SqlMethod name="set" type="constant" return="multiple">
    59       <SqlMethodComment></SqlMethodComment>
    67       <SqlMethodComment></SqlMethodComment>
    60       <Sql></Sql>
    68       <Sql></Sql>
    61    </SqlMethod>
    69    </SqlMethod>