src/org/openbravo/erpCommon/info/BusinessPartnerMultiple_data.xsql
changeset 712 aa0c40278c5a
parent 0 0247c26f10c6
child 3553 6a109c695442
equal deleted inserted replaced
711:4e8209a9d749 712:aa0c40278c5a
    25 <SqlClass name="BusinessPartnerMultipleData" package="org.openbravo.erpCommon.info">
    25 <SqlClass name="BusinessPartnerMultipleData" package="org.openbravo.erpCommon.info">
    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> <![CDATA[
    29       <Sql> <![CDATA[
    30         SELECT bp.C_BPartner_ID, bp.VALUE, bp.NAME as name, replace(bp.NAME, '''', CHR(92) || '''') as name_hidden, 
    30       	SELECT * FROM ( SELECT '0' AS RN1, A.* FROM (
    31         (bp.SO_CreditLimit-bp.SO_CreditUsed) AS SO_CreditAvailable, bp.SO_CreditUsed, 
    31 	        SELECT bp.C_BPartner_ID, bp.VALUE, bp.NAME as name, replace(bp.NAME, '''', CHR(92) || '''') as name_hidden, 
    32         bp.ActualLifetimeValue, bp.URL, MAX(s.EMAIL) AS EMAIL, MAX(s.NAME) as SALESREP
    32 	        (bp.SO_CreditLimit-bp.SO_CreditUsed) AS SO_CreditAvailable, bp.SO_CreditUsed, 
    33         FROM C_BPARTNER_LOCATION l left join AD_USER c on l.C_BPartner_Location_ID = c.C_BPartner_Location_ID
    33 	        bp.ActualLifetimeValue, bp.URL, MAX(s.EMAIL) AS EMAIL, MAX(s.NAME) as SALESREP, bp.C_BPartner_ID || '#' || bp.NAME AS RowKey
    34                                    left join C_LOCATION a on l.C_Location_ID=a.C_Location_ID
    34 	        FROM C_BPARTNER_LOCATION l left join AD_USER c on l.C_BPartner_Location_ID = c.C_BPartner_Location_ID
    35                                    left join C_REGION on a.C_REGION_ID = C_REGION.C_REGION_ID,
    35 	                                   left join C_LOCATION a on l.C_Location_ID=a.C_Location_ID
    36              C_BPARTNER bp left join  AD_USER s on bp.SALESREP_ID = s.AD_USER_ID
    36 	                                   left join C_REGION on a.C_REGION_ID = C_REGION.C_REGION_ID,
    37         WHERE bp.C_BPARTNER_ID = l.C_BPartner_ID
    37 	             C_BPARTNER bp left join  AD_USER s on bp.SALESREP_ID = s.AD_USER_ID
    38         AND COALESCE(c.IsActive, 'Y')='Y' 
    38 	        WHERE bp.C_BPARTNER_ID = l.C_BPartner_ID
    39         AND bp.IsSummary='N' 
    39 	        AND COALESCE(c.IsActive, 'Y')='Y' 
    40         AND bp.AD_Client_ID IN ('1') 
    40 	        AND bp.IsSummary='N' 
    41         AND bp.AD_Org_ID IN ('1') 
    41 	        AND bp.AD_Client_ID IN ('1') 
    42         AND COALESCE(l.IsActive, 'Y')='Y'
    42 	        AND bp.AD_Org_ID IN ('1') 
    43         GROUP BY bp.C_BPartner_ID, bp.VALUE, bp.NAME, bp.SO_CreditLimit, bp.SO_CreditUsed, bp.ActualLifetimeValue, 
    43 	        AND COALESCE(l.IsActive, 'Y')='Y'
    44         bp.URL
    44 	        GROUP BY bp.C_BPartner_ID, bp.VALUE, bp.NAME, bp.SO_CreditLimit, bp.SO_CreditUsed, bp.ActualLifetimeValue, bp.URL
    45         ORDER BY bp.NAME
    45 	        ORDER BY bp.NAME
       
    46 	    	) A ) B
       
    47 		WHERE 1=1
    46       ]]></Sql>
    48       ]]></Sql>
    47         <Field name="position" value="count"/>
    49         <Field name="position" value="count"/>
       
    50         <Parameter name="rownum" type="replace" optional="true" after="FROM ( SELECT " text="'0'" />
    48         <Parameter name="adUserClient" type="replace" optional="true" after="bp.AD_Client_ID IN (" text="'1'"/>
    51         <Parameter name="adUserClient" type="replace" optional="true" after="bp.AD_Client_ID IN (" text="'1'"/>
    49         <Parameter name="adUserOrg" type="replace" optional="true" after="bp.AD_Org_ID IN (" text="'1'"/>
    52         <Parameter name="adUserOrg" type="replace" optional="true" after="bp.AD_Org_ID IN (" text="'1'"/>
    50         <Parameter name="key" optional="true" after="AND COALESCE(l.IsActive, 'Y')='Y'"><![CDATA[AND UPPER(bp.Value) LIKE UPPER(?) ]]></Parameter>
    53         <Parameter name="key" optional="true" after="AND COALESCE(l.IsActive, 'Y')='Y'"><![CDATA[AND UPPER(bp.Value) LIKE UPPER(?) ]]></Parameter>
    51         <Parameter name="name" optional="true" after="AND COALESCE(l.IsActive, 'Y')='Y'"><![CDATA[AND UPPER(bp.Name) LIKE UPPER(?) ]]></Parameter>
    54         <Parameter name="name" optional="true" after="AND COALESCE(l.IsActive, 'Y')='Y'"><![CDATA[AND UPPER(bp.Name) LIKE UPPER(?) ]]></Parameter>
    52         <Parameter name="contact" optional="true" after="AND COALESCE(l.IsActive, 'Y')='Y'"><![CDATA[AND UPPER(c.Name) LIKE UPPER(?) ]]></Parameter>
    55         <Parameter name="contact" optional="true" after="AND COALESCE(l.IsActive, 'Y')='Y'"><![CDATA[AND UPPER(c.Name) LIKE UPPER(?) ]]></Parameter>
    53         <Parameter name="codigoPostal" optional="true" after="AND COALESCE(l.IsActive, 'Y')='Y'"><![CDATA[AND UPPER(a.Postal) LIKE UPPER(?) ]]></Parameter>
    56         <Parameter name="codigoPostal" optional="true" after="AND COALESCE(l.IsActive, 'Y')='Y'"><![CDATA[AND UPPER(a.Postal) LIKE UPPER(?) ]]></Parameter>
    54         <Parameter name="provincia" optional="true" after="AND COALESCE(l.IsActive, 'Y')='Y'"><![CDATA[AND UPPER(C_REGION.NAME) LIKE UPPER(?)]]></Parameter>
    57         <Parameter name="provincia" optional="true" after="AND COALESCE(l.IsActive, 'Y')='Y'"><![CDATA[AND UPPER(C_REGION.NAME) LIKE UPPER(?)]]></Parameter>
    55         <Parameter name="clients" optional="true" type="none" after="AND COALESCE(l.IsActive, 'Y')='Y'"><![CDATA[AND ISCUSTOMER = 'Y' ]]></Parameter>
    58         <Parameter name="clients" optional="true" type="none" after="AND COALESCE(l.IsActive, 'Y')='Y'"><![CDATA[AND ISCUSTOMER = 'Y' ]]></Parameter>
    56         <Parameter name="vendors" optional="true" type="none" after="AND COALESCE(l.IsActive, 'Y')='Y'"><![CDATA[AND ISVENDOR ='Y']]></Parameter>
    59         <Parameter name="vendors" optional="true" type="none" after="AND COALESCE(l.IsActive, 'Y')='Y'"><![CDATA[AND ISVENDOR ='Y']]></Parameter>
    57         <Parameter name="ciudad" optional="true" after="AND COALESCE(l.IsActive, 'Y')='Y'"><![CDATA[AND UPPER(a.City) LIKE UPPER(?) ]]></Parameter>
    60         <Parameter name="ciudad" optional="true" after="AND COALESCE(l.IsActive, 'Y')='Y'"><![CDATA[AND UPPER(a.City) LIKE UPPER(?) ]]></Parameter>
       
    61         <Parameter name="orderBy" type="replace" optional="true" after="ORDER BY " text="bp.NAME" />
       
    62         <Parameter name="oraLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[AND RN1 BETWEEN ]]></Parameter>
       
    63         <Parameter name="pgLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[LIMIT ]]></Parameter>
    58    </SqlMethod>
    64    </SqlMethod>
    59    <SqlMethod name="set" type="constant" return="multiple">
    65    <SqlMethod name="set" type="constant" return="multiple">
    60       <SqlMethodComment></SqlMethodComment>
    66       <SqlMethodComment></SqlMethodComment>
    61       <Sql></Sql>
    67       <Sql></Sql>
    62    </SqlMethod>
    68    </SqlMethod>