<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: a $
    $Date: 2011/4/28 05:48:38 $
    $Revision: 1.1 $
    $Purpose: 对应 系统级供应商 弹出公司分配 的bm
-->
<ns1:model xmlns:ns2="leaf.database.local.oracle" xmlns:ns1="http://www.leaf-framework.org/schema/bm">
    <ns1:fields>
        <!-- ns1:field name="company_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName=""  /--><![CDATA[
        
        
        
        
    ]]></ns1:fields>
    <ns1:operations>
        <ns1:operation name="query">
            <ns1:query-sql><![CDATA[
    			SELECT C.COMPANY_ID,
  C.VENDER_ID,
  S.VENDER_CODE,
  S.DESCRIPTION vender_de,
  S.BANK_BRANCH_CODE,
  S.BANK_ACCOUNT_CODE,
  S.address,
  s.artificial_person,
  s.tax_id_number,
  C.PAYMENT_TERM_ID,
  (select pvt.vender_type_code
          from pur_vender_types_vl pvt
         where pvt.vender_type_id =
               (select psv.VENDER_TYPE_ID
                  from pur_system_venders_vl psv
                 where psv.VENDER_ID = c.VENDER_ID)) vender_type_code,
       (select pvt.description
          from pur_vender_types_vl pvt
         where pvt.vender_type_id =
               (select psv.VENDER_TYPE_ID
                  from pur_system_venders_vl psv
                 where psv.VENDER_ID = c.VENDER_ID)) vender_type_name,
  PT.DESCRIPTION payment_term_de,
  C.PAYMENT_METHOD,
  (select fd.description_text
          from FND_DESCRIPTIONS FD, CSH_PAYMENT_METHODS PM
         where fd.description_id = pm.description_id
           and pm.payment_method_code = c.payment_method and fd.language=${/session/@lang}) payment_method_display,
  C.CURRENCY_CODE,
  (select fd.description_text
          from FND_DESCRIPTIONS FD, GLD_CURRENCY GC
         where gc.currency_name_id=fd.description_id
           and gc.currency_code = c.currency_code and fd.language=${/session/@lang}) currency_code_display,
  C.TAX_TYPE_ID,
  TTC.TAX_TYPE_CODE,
  (select fd.description_text
          from FND_DESCRIPTIONS FD 
          where ttc.description_id=fd.description_id 
          and ttc.tax_type_id=c.tax_type_id and fd.language=${/session/@lang})tax_type_code_display,
  NVL(C.APPROVED_VENDER_FLAG,NVL(S.APPROVED_VENDER_FLAG,'N')) APPROVED_VENDER_FLAG,
  C.ENABLED_FLAG,
  C.ACCOUNT_ID,
  V.ACCOUNT_CODE,
  V.ACCOUNT_DESC
FROM PUR_COMPANY_VENDERS C,
  PUR_SYSTEM_VENDERS_VL S,
  CSH_PAYMENT_TERMS_VL PT,
  FND_TAX_TYPE_CODES TTC,
  (SELECT V.ACCOUNT_ID,
    V.ACCOUNT_CODE,
    V.DESCRIPTION account_desc
  FROM GLD_ACCOUNTS_VL V,
    GLD_COMPANY_ACCOUNTS V1
  WHERE V1.ACCOUNT_ID = V.ACCOUNT_ID
  AND V1.COMPANY_ID   = ${/session/@company_id}
  AND V1.ENABLED_FLAG = 'Y'
  AND V.SUMMARY_FLAG  = 'N'
  AND V.ENABLED_FLAG  = 'Y'
  ) V
  
  #WHERE_CLAUSE#
order by S.VENDER_CODE
    		]]></ns1:query-sql>
        </ns1:operation>
        <ns1:operation name="update">
            <ns1:update-sql><![CDATA[
        	
        	begin
		   pur_system_venders_pkg.update_pur_company_venders
		   (
		      ${@company_id},
		      ${@vender_id},
			  ${@payment_term_id},
			  ${@payment_method},
			  ${@currency_code},
			  ${@tax_type_id},
			  ${@approved_vender_flag},
			  ${@enabled_flag},
              ${/session/@user_id}
		   );
		end;
        	]]></ns1:update-sql>
        </ns1:operation>
        <ns1:operation name="insert">
            <ns1:update-sql><![CDATA[
            declare v_vender_id number;
		begin
         pur_system_venders_pkg.insert_pur_system_venders(p_vender_code => ${@vender_code},
                                                   p_description => ${@vender_de},
                                                   p_address => ${@address},
                                                   p_artificial_person => ${@artificial_person},
                                                   p_tax_id_number =>${@tax_id_number},
                                                   p_bank_branch_code => ${@bank_branch_code},
                                                   p_bank_account_code => ${@bank_account_code},
                                                   p_enabled_flag =>${@enabled_flag},
                                                   p_created_by => ${/session/@user_id},
                                                   p_company_id => ${/session/@company_id},
                                                   p_vender_type_id => ${@vender_type_id},
                                                   p_payment_term_id => ${@payment_term_id},
                                                   p_payment_method => ${@payment_method_code},
                                                   p_vender_id => v_vender_id,
                                                   p_currency_code => ${@currency_code},
                                                   p_tax_type_id => ${@tax_type_id},
                                                   p_approved_vender_flag => ${@approved_vender_flag});
          end;
		]]></ns1:update-sql>
        </ns1:operation>
    </ns1:operations>
    <ns1:data-filters>
        <ns1:data-filter enforceOperations="query" expression="c.vender_id     = s.vender_id"/>
        <ns1:data-filter enforceOperations="query" expression="c.payment_term_id = pt.payment_term_id(+)"/>
        <ns1:data-filter enforceOperations="query" expression="c.tax_type_id     = ttc.tax_type_id(+)"/>
        <ns1:data-filter enforceOperations="query" expression="V.ACCOUNT_ID(+) = c.account_id"/>
        <ns1:data-filter enforceOperations="query" expression="c.company_id = nvl(${/session/@company_id},1)"/>
        <!-- $(/session/@company_id}) -->
    </ns1:data-filters>
    <ns1:query-fields>
        <ns1:query-field name="vender_code" queryExpression="s.vender_code like ${@vender_code}"/>
        <ns1:query-field name="vender_de" queryExpression="s.description like ${@vender_de}"/>
        <!--<ns1:query-field field="vender_code" queryOperator="="/>-->
        <!-- <ns1:query-field field="VENDER_DE" queryOperator="like"/>-->
        <!-- queryExpression="exists (select 1 from fnd_descriptions fd where pu.description_id=fd.description_id and fd.description_text like ${@description} and fd.language = ${/session/@lang})"-->
    </ns1:query-fields>
</ns1:model>
