src/org/openbravo/erpCommon/info/BusinessPartner_data.xsql
changeset 712 aa0c40278c5a
parent 484 7df5213b20c5
child 1496 94ffca763083
equal deleted inserted replaced
711:4e8209a9d749 712:aa0c40278c5a
    10  * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
    10  * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
    11  * License for the specific  language  governing  rights  and  limitations
    11  * License for the specific  language  governing  rights  and  limitations
    12  * under the License. 
    12  * under the License. 
    13  * The Original Code is Openbravo ERP. 
    13  * The Original Code is Openbravo ERP. 
    14  * The Initial Developer of the Original Code is Openbravo SL 
    14  * The Initial Developer of the Original Code is Openbravo SL 
    15  * All portions are Copyright (C) 2001-2006 Openbravo SL 
    15  * All portions are Copyright (C) 2001-2008 Openbravo SL 
    16  * All Rights Reserved. 
    16  * All Rights Reserved. 
    17  * Contributor(s):  ______________________________________.
    17  * Contributor(s):  ______________________________________.
    18  ************************************************************************
    18  ************************************************************************
    19 -->
    19 -->
    20 
    20 
    25 <SqlClass name="BusinessPartnerData" package="org.openbravo.erpCommon.info">
    25 <SqlClass name="BusinessPartnerData" 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 DISTINCT bp.C_BPartner_ID, bp.value, bp.name as name, 
    30       		SELECT * FROM ( SELECT '0' AS RN1, A.* FROM (      				   
    31         bp.name as name_hidden, 
    31 					SELECT DISTINCT bp.C_BPartner_ID, bp.value, bp.name as name, 
    32         (bp.SO_CreditLimit-bp.SO_CreditUsed) AS SO_CreditAvailable, bp.SO_CreditUsed, 
    32 					        bp.name as name_hidden, 
    33         COALESCE(c.name,u.name) AS Contact, COALESCE(c.ad_user_id,u.ad_user_id) as C_BPartner_Contact_ID, bpl.Phone, l.Postal, 
    33 					        (bp.SO_CreditLimit-bp.SO_CreditUsed) AS SO_CreditAvailable, bp.SO_CreditUsed, 
    34         bpl.c_bpartner_location_id as C_BPartner_Location_ID,
    34 					        COALESCE(c.name,u.name) AS Contact, COALESCE(c.ad_user_id,u.ad_user_id) as C_BPartner_Contact_ID, bpl.Phone, l.Postal AS PC, 
    35         l.City, bp.ActualLifetimeValue
    35 					        bpl.c_bpartner_location_id as C_BPartner_Location_ID,
    36         FROM C_BPARTNER bp left join C_BPARTNER_LOCATION bpl on bp.c_bpartner_id = bpl.c_bpartner_id
    36 					        l.City, bp.ActualLifetimeValue AS Income, 
    37                                                              and bpl.IsActive = 'Y'
    37 					        bp.C_BPartner_ID || '#' || COALESCE(TO_CHAR(c.ad_user_id), TO_CHAR(u.ad_user_id), '') || '#' || COALESCE(TO_CHAR(bpl.c_bpartner_location_id),'') || '#' || bp.name AS rowKey  
    38                            left join AD_USER c on bpl.c_bpartner_location_id = c.C_BPartner_Location_ID
    38 					        FROM C_BPARTNER bp left join C_BPARTNER_LOCATION bpl on bp.c_bpartner_id = bpl.c_bpartner_id
    39                                                              and c.IsActive ='Y'
    39 					                                                             and bpl.IsActive = 'Y'
    40                            left join C_LOCATION l on bpl.c_location_id=l.C_Location_ID 
    40 					                           left join AD_USER c on bpl.c_bpartner_location_id = c.C_BPartner_Location_ID
    41                            left join  C_REGION r on l.c_region_id = r.c_region_id 
    41 					                                                             and c.IsActive ='Y'
    42                            left join
    42 					                           left join C_LOCATION l on bpl.c_location_id=l.C_Location_ID 
    43                 (SELECT c_bpartner_id, ad_user_id, name from ad_user where c_bpartner_id is not null 
    43 					                           left join  C_REGION r on l.c_region_id = r.c_region_id 
    44                 and c_bpartner_location_id is null and isactive='Y') u on bp.c_bpartner_id = u.c_bpartner_id
    44 					                           left join
    45         WHERE bp.AD_Client_ID IN ('1') 
    45 					                (SELECT c_bpartner_id, ad_user_id, name from ad_user where c_bpartner_id is not null 
    46         AND bp.AD_Org_ID IN ('1') 
    46 					                and c_bpartner_location_id is null and isactive='Y') u on bp.c_bpartner_id = u.c_bpartner_id
    47         AND bp.IsSummary='N' 
    47 					        WHERE bp.AD_Client_ID IN ('1') 
    48         AND bp.IsActive='Y'
    48 					        AND bp.AD_Org_ID IN ('1') 
    49         ORDER BY bp.name, contact
    49 					        AND bp.IsSummary='N' 
       
    50 					        AND bp.IsActive='Y'
       
    51 					        ORDER BY bp.value
       
    52 					) A ) B
       
    53 			WHERE 1=1
    50       ]]></Sql>
    54       ]]></Sql>
    51         <Field name="position" value="count"/>
    55         <Field name="position" value="count"/>
       
    56         <Parameter name="rownum" type="replace" optional="true" after="FROM ( SELECT " text="'0'" />
    52         <Parameter name="adUserClient" type="replace" optional="true" after="bp.AD_Client_ID IN (" text="'1'"/>
    57         <Parameter name="adUserClient" type="replace" optional="true" after="bp.AD_Client_ID IN (" text="'1'"/>
    53         <Parameter name="adUserOrg" type="replace" optional="true" after="bp.AD_Org_ID IN (" text="'1'"/>
    58         <Parameter name="adUserOrg" type="replace" optional="true" after="bp.AD_Org_ID IN (" text="'1'"/>
    54         <Parameter name="key" optional="true" after="AND bp.IsActive='Y'"><![CDATA[AND UPPER(bp.Value) LIKE UPPER(?) ]]></Parameter>
    59         <Parameter name="key" optional="true" after="AND bp.IsActive='Y'"><![CDATA[AND UPPER(bp.Value) LIKE UPPER(?) ]]></Parameter>
    55         <Parameter name="name" optional="true" after="AND bp.IsActive='Y'"><![CDATA[AND UPPER(bp.Name) LIKE UPPER(?) ]]></Parameter>
    60         <Parameter name="name" optional="true" after="AND bp.IsActive='Y'"><![CDATA[AND UPPER(bp.Name) LIKE UPPER(?) ]]></Parameter>
    56         <Parameter name="contact" optional="true" after="AND bp.IsActive='Y'"><![CDATA[AND UPPER(c.Name) LIKE UPPER(?) ]]></Parameter>
    61         <Parameter name="contact" optional="true" after="AND bp.IsActive='Y'"><![CDATA[AND UPPER(COALESCE(c.name,u.name)) LIKE UPPER(?) ]]></Parameter>
    57         <Parameter name="codigoPostal" optional="true" after="AND bp.IsActive='Y'"><![CDATA[AND UPPER(l.Postal) LIKE UPPER(?) ]]></Parameter>
    62         <Parameter name="codigoPostal" optional="true" after="AND bp.IsActive='Y'"><![CDATA[AND UPPER(l.Postal) LIKE UPPER(?) ]]></Parameter>
    58         <Parameter name="provincia" optional="true" after="AND bp.IsActive='Y'"><![CDATA[AND UPPER(r.NAME) LIKE UPPER(?)]]></Parameter>
    63         <Parameter name="provincia" optional="true" after="AND bp.IsActive='Y'"><![CDATA[AND UPPER(r.NAME) LIKE UPPER(?)]]></Parameter>
    59         <Parameter name="clients" optional="true" type="none" after="AND bp.IsActive='Y'"><![CDATA[AND ISCUSTOMER = 'Y' ]]></Parameter>
    64         <Parameter name="clients" optional="true" type="none" after="AND bp.IsActive='Y'"><![CDATA[AND ISCUSTOMER = 'Y' ]]></Parameter>
    60         <Parameter name="vendors" optional="true" type="none" after="AND bp.IsActive='Y'"><![CDATA[AND ISVENDOR ='Y']]></Parameter>
    65         <Parameter name="vendors" optional="true" type="none" after="AND bp.IsActive='Y'"><![CDATA[AND ISVENDOR ='Y']]></Parameter>
    61         <Parameter name="ciudad" optional="true" after="AND bp.IsActive='Y'"><![CDATA[AND UPPER(l.City) LIKE UPPER(?) ]]></Parameter>
    66         <Parameter name="ciudad" optional="true" after="AND bp.IsActive='Y'"><![CDATA[AND UPPER(l.City) LIKE UPPER(?) ]]></Parameter>
       
    67         <Parameter name="orderBy" type="replace" optional="true" after="ORDER BY " text="bp.value" />
       
    68         <Parameter name="oraLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[AND RN1 BETWEEN ]]></Parameter>
       
    69         <Parameter name="pgLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[LIMIT ]]></Parameter>
    62    </SqlMethod>
    70    </SqlMethod>
    63    <SqlMethod name="set" type="constant" return="multiple">
    71    <SqlMethod name="set" type="constant" return="multiple">
    64       <SqlMethodComment></SqlMethodComment>
    72       <SqlMethodComment></SqlMethodComment>
    65       <Sql></Sql>
    73       <Sql></Sql>
    66    </SqlMethod>
    74    </SqlMethod>