<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: gaoyang  
    $Date: 2013-5-14 下午01:23:36  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" xmlns:f="leaf.database.features" alias="t1"
          baseTable="CSH_TRANSACTION">
    <bm:operations>
        <bm:operation name="update">
            <bm:update-sql><![CDATA[
                update CSH_TRANSACTION
                set ref_v05 = ${@ref_v05},
                LAST_UPDATE_DATE = sysdate,
                last_updated_by = ${/session/@user_id}
                where transaction_id = ${@transaction_id}
                ]]>
            </bm:update-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="transaction_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="TRANSACTION_ID"
                  prompt="CSH511.CSH_TRANSACTION.TRANSACTION_ID"/>
        <bm:field name="transaction_num" databaseType="VARCHAR2" datatype="java.lang.String"
                  physicalName="TRANSACTION_NUM" prompt="CSH511.CSH_TRANSACTION.TRANSACTION_NUM"/>
        <bm:field name="transaction_category" databaseType="VARCHAR2" datatype="java.lang.String"
                  physicalName="TRANSACTION_CATEGORY" prompt="CSH511.CSH_TRANSACTION.TRANSACTION_CATEGORY"/>
        <bm:field name="transaction_type" databaseType="VARCHAR2" datatype="java.lang.String"
                  lookupCode="CSH511_TRANSACTION_TYPE" lookupField="transaction_type_desc"
                  physicalName="TRANSACTION_TYPE" prompt="CSH511.CSH_TRANSACTION.TRANSACTION_TYPE"/>
        <bm:field name="receivables_type" databaseType="VARCHAR2" datatype="java.lang.String"
                  lookupCode="RECEIVABLES_TYPE" lookupField="receivables_type_desc" physicalName="RECEIVABLES_TYPE"
                  prompt="收款类型"/>
        <bm:field name="transaction_date" databaseType="DATE" datatype="java.util.Date" physicalName="TRANSACTION_DATE"
                  prompt="CSH511.CSH_TRANSACTION.TRANSACTION_DATE"/>
        <bm:field name="penalty_calc_date" databaseType="DATE" datatype="java.util.Date"
                  physicalName="PENALTY_CALC_DATE" prompt="CSH511.CSH_TRANSACTION.PENALTY_CALC_DATE"/>
        <bm:field name="bank_slip_num" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="BANK_SLIP_NUM"
                  prompt="CSH511.CSH_TRANSACTION.BANK_SLIP_NUM"/>
        <bm:field name="company_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="COMPANY_ID"
                  prompt="CSH511.CSH_TRANSACTION.COMPANY_ID"/>
        <bm:field name="internal_period_num" databaseType="NUMBER" datatype="java.lang.Long"
                  physicalName="INTERNAL_PERIOD_NUM" prompt="CSH511.CSH_TRANSACTION.INTERNAL_PERIOD_NUM"/>
        <bm:field name="period_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="PERIOD_NAME"
                  prompt="HLS.ACCOUNT_PERIOD_NAME"/>
        <bm:field name="payment_method_id" databaseType="NUMBER" datatype="java.lang.Long"
                  physicalName="PAYMENT_METHOD_ID" prompt="HLS.PAYMENT_METHOD"/>
        <bm:field name="distribution_set_id" databaseType="NUMBER" datatype="java.lang.Long"
                  physicalName="DISTRIBUTION_SET_ID" prompt="CSH511.CSH_TRANSACTION.DISTRIBUTION_SET_ID"/>
        <bm:field name="cashflow_amount" databaseType="NUMBER" datatype="java.lang.Double"
                  physicalName="CASHFLOW_AMOUNT" prompt="CSH511.CSH_TRANSACTION.CASHFLOW_AMOUNT"/>
        <bm:field name="currency_code" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CURRENCY_CODE"
                  prompt="HLS.CURRENCY"/>
        <bm:field name="transaction_amount" databaseType="NUMBER" datatype="java.lang.Double"
                  physicalName="TRANSACTION_AMOUNT" prompt="CSH511.CSH_TRANSACTION.TRANSACTION_AMOUNT"/>
        <bm:field name="exchange_rate_type" databaseType="VARCHAR2" datatype="java.lang.String"
                  physicalName="EXCHANGE_RATE_TYPE" prompt="HLS.EXCHANGE_RATE_TYPE_DESC"/>
        <bm:field name="exchange_rate_quotation" databaseType="VARCHAR2" datatype="java.lang.String"
                  physicalName="EXCHANGE_RATE_QUOTATION" prompt="CSH511.CSH_TRANSACTION.EXCHANGE_RATE_QUOTATION"/>
        <bm:field name="exchange_rate" databaseType="NUMBER" datatype="java.lang.Double" physicalName="EXCHANGE_RATE"
                  prompt="HLS.EXCHANGE_RATE"/>
        <bm:field name="bank_account_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="BANK_ACCOUNT_ID"
                  prompt="CSH511.CSH_TRANSACTION.BANK_ACCOUNT_ID"/>
        <bm:field name="bp_category" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="BP_CATEGORY"
                  prompt="CSH511.CSH_TRANSACTION.BP_CATEGORY"/>
        <bm:field name="bp_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="BP_ID"
                  prompt="CSH511.CSH_TRANSACTION.BP_ID"/>
        <bm:field name="bp_bank_account_id" databaseType="NUMBER" datatype="java.lang.Long"
                  physicalName="BP_BANK_ACCOUNT_ID" prompt="CSH511.CSH_TRANSACTION.BP_BANK_ACCOUNT_ID"/>
        <bm:field name="bp_bank_account_num" databaseType="VARCHAR2" datatype="java.lang.String"
                  physicalName="BP_BANK_ACCOUNT_NUM" prompt="CSH511.BANK_ACCOUNT_NUM"/>
        <bm:field name="description" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="DESCRIPTION"
                  prompt="HLS.NOTE"/>
        <bm:field name="handling_charge" databaseType="NUMBER" datatype="java.lang.Double"
                  physicalName="HANDLING_CHARGE" prompt="CSH511.CSH_TRANSACTION.HANDLING_CHARGE"/>
        <bm:field name="posted_flag" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="POSTED_FLAG"
                  prompt="CSH511.CSH_TRANSACTION.POSTED_FLAG"/>
        <bm:field name="reversed_flag" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="REVERSED_FLAG"
                  prompt="CSH511.CSH_TRANSACTION.REVERSED_FLAG"/>
        <bm:field name="reversed_date" databaseType="DATE" datatype="java.util.Date" physicalName="REVERSED_DATE"
                  prompt="CSH511.CSH_TRANSACTION.REVERSED_DATE"/>
        <bm:field name="returned_flag" databaseType="VARCHAR2" datatype="java.lang.String"
                  lookupCode="CSH510_RETURN_SELECT" lookupField="returned_flag_desc" physicalName="RETURNED_FLAG"
                  prompt="CSH511.CSH_TRANSACTION.RETURNED_FLAG"/>
        <bm:field name="returned_amount" databaseType="NUMBER" datatype="java.lang.Double"
                  physicalName="RETURNED_AMOUNT" prompt="CSH511.CSH_TRANSACTION.RETURNED_AMOUNT"/>
        <bm:field name="write_off_flag" lookupCode="CON_WRITE_OFF_FLAG" lookupField="write_off_flag_desc"
                  databaseType="VARCHAR2" datatype="java.lang.String" physicalName="WRITE_OFF_FLAG"
                  prompt="CSH511.CSH_TRANSACTION.WRITE_OFF_FLAG"/>
        <bm:field name="write_off_amount" databaseType="NUMBER" datatype="java.lang.Double"
                  physicalName="WRITE_OFF_AMOUNT" prompt="CSH511.CSH_TRANSACTION.WRITE_OFF_AMOUNT"/>
        <bm:field name="full_write_off_date" databaseType="DATE" datatype="java.util.Date"
                  physicalName="FULL_WRITE_OFF_DATE" prompt="CSH511.CSH_TRANSACTION.FULL_WRITE_OFF_DATE"/>
        <bm:field name="twin_csh_trx_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="TWIN_CSH_TRX_ID"
                  prompt="CSH511.CSH_TRANSACTION.TWIN_CSH_TRX_ID"/>
        <bm:field name="return_from_csh_trx_id" databaseType="NUMBER" datatype="java.lang.Long"
                  physicalName="RETURN_FROM_CSH_TRX_ID" prompt="CSH511.CSH_TRANSACTION.RETURN_FROM_CSH_TRX_ID"/>
        <bm:field name="reversed_csh_trx_id" databaseType="NUMBER" datatype="java.lang.Long"
                  physicalName="REVERSED_CSH_TRX_ID" prompt="CSH511.CSH_TRANSACTION.REVERSED_CSH_TRX_ID"/>
        <bm:field name="source_csh_trx_type" databaseType="VARCHAR2" datatype="java.lang.String"
                  physicalName="SOURCE_CSH_TRX_TYPE" prompt="CSH511.CSH_TRANSACTION.SOURCE_CSH_TRX_TYPE"/>
        <bm:field name="source_csh_trx_id" databaseType="NUMBER" datatype="java.lang.Long"
                  physicalName="SOURCE_CSH_TRX_ID" prompt="CSH511.CSH_TRANSACTION.SOURCE_CSH_TRX_ID"/>
        <bm:field name="source_doc_category" databaseType="VARCHAR2" datatype="java.lang.String"
                  physicalName="SOURCE_DOC_CATEGORY"/>
        <bm:field name="source_doc_type" databaseType="VARCHAR2" datatype="java.lang.String"
                  physicalName="SOURCE_DOC_TYPE" prompt="CSH511.CSH_TRANSACTION.SOURCE_DOC_TYPE"/>
        <bm:field name="source_doc_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="SOURCE_DOC_ID"
                  prompt="CSH511.CSH_TRANSACTION.SOURCE_DOC_ID"/>
        <bm:field name="source_doc_line_id" databaseType="NUMBER" datatype="java.lang.Long"
                  physicalName="SOURCE_DOC_LINE_ID" prompt="CSH511.CSH_TRANSACTION.SOURCE_DOC_LINE_ID"/>
        <bm:field name="create_je_mothed" databaseType="VARCHAR2" datatype="java.lang.String"
                  physicalName="CREATE_JE_MOTHED"/>
        <bm:field name="create_je_flag" databaseType="VARCHAR2" datatype="java.lang.String"
                  physicalName="CREATE_JE_FLAG"/>
        <bm:field name="gld_interface_flag" databaseType="VARCHAR2" datatype="java.lang.String"
                  physicalName="GLD_INTERFACE_FLAG" prompt="CSH511.CSH_TRANSACTION.GLD_INTERFACE_FLAG"/>
        <bm:field name="bp_code" expression="(select d.bp_code from hls_bp_master d where d.bp_id=t1.bp_id)"
                  forInsert="false" forUpdate="false" prompt="HLS.BP_CODE"/>
        <bm:field name="bp_name" expression="(select d.bp_name from hls_bp_master d where d.bp_id=t1.bp_id)"
                  forInsert="false" forUpdate="false" prompt="HLS.BP_NAME"/>
        <bm:field name="payment_method_display"
                  expression="(select cp.description from csh_payment_method cp where cp.payment_method_id=t1.payment_method_id)"
                  forInsert="false" forUpdate="false" prompt="HLS.PAYMENT_METHOD"/>
        <bm:field name="currency_name"
                  expression="(select gc.currency_name from gld_currency_vl gc where gc.currency_code=t1.currency_code)"
                  forInsert="false" forUpdate="false" prompt="HLS.CURRENCY"/>
        <bm:field name="bp_category_display"
                  expression="(select b.description from hls_bp_category b where b.bp_category=t1.bp_category)"
                  forInsert="false" forUpdate="false" prompt="CSH511.CSH_TRANSACTION.BP_CATEGORY"/>
        <bm:field name="exchange_rate_type_display"
                  expression="(select tv.type_name from gld_exchangerate_types_vl tv where tv.type_code=t1.exchange_rate_type)"
                  forInsert="false" forUpdate="false" prompt="HLS.EXCHANGE_RATE_TYPE_DESC"/>
        <bm:field name="transaction_functional_amount"
                  expression="gld_exchange_rate_pkg.get_currency_exchange(t1.transaction_amount,t1.exchange_rate,t1.exchange_rate_quotation)"
                  forInsert="false" forUpdate="false" prompt="HLS.FUNCTIONAL_AMOUNT"/>
        <bm:field name="bank_account_code"
                  expression="(select a.bank_account_code from csh_bank_account a where a.bank_account_id=t1.bank_account_id)"
                  forInsert="false" forUpdate="false" prompt="CSH511.BANK_ACCOUNT_CODE"/>
        <bm:field name="bank_account_name"
                  expression="(select a.bank_account_name from csh_bank_account a where a.bank_account_id=t1.bank_account_id)"
                  forInsert="false" forUpdate="false" prompt="CSH511.BANK_ACCOUNT_NAME"/>
        <bm:field name="bank_account_num"
                  expression="(select a.bank_account_num from csh_bank_account a where a.bank_account_id=t1.bank_account_id)"
                  forInsert="false" forUpdate="false" prompt="CSH511.BANK_ACCOUNT_NUM"/>
        <bm:field name="bp_bank_account_code"
                  expression="(select a.bank_account_code  from hls_bp_master_bank_account a  where a.bank_account_id = t1.bp_bank_account_id)"
                  forInsert="false" forUpdate="false" prompt="CSH511.BP_BANK_ACCOUNT_CODE"/>
        <bm:field name="bp_bank_account_name" forInsert="false" forUpdate="false" prompt="CSH511.BANK_ACCOUNT_NAME"/>
        <bm:field name="functional_currency_code"
                  expression="(select gb.functional_currency_code   from fnd_companies fc, gld_set_of_books gb  where fc.company_id = ${/session/@company_id}    and fc.set_of_books_id = gb.set_of_books_id)"
                  forInsert="false" forUpdate="false"/>
        <bm:field name="rate_method_code"
                  expression="(select t.rate_method_code from gld_exchangerate_types t where t.type_code=t1.exchange_rate_type)"
                  forInsert="false" forUpdate="false"/>
        <bm:field name="zero_amounts_allowed"
                  expression="(select d.zero_amounts_allowed from csh_bank_account_v d where d.bank_account_id=t1.bank_account_id)"
                  forInsert="false" forUpdate="false"/>
        <bm:field name="transaction_type_desc" forInsert="false" forUpdate="false" prompt="HLS.TRANSACTION_TYPE"/>
        <bm:field name="receivables_type_desc"
                  expression="decode(t1.receivables_type,null,&apos;其他&apos;,t1.receivables_type)" forInsert="false"
                  forUpdate="false" prompt="收款类型"/>
        <bm:field name="unwrite_off_amount" datatype="java.lang.Double"
                  expression="(nvl(t1.transaction_amount,0)-nvl(t1.write_off_amount,0)-nvl(t1.returned_amount,0)-(select nvl(sum(ctr.this_return_amount),0) from csh_transaction_return ctr where ctr.transaction_id = t1.transaction_id and ctr.status = 'APPROVING'))"
                  forInsert="false" forUpdate="false" prompt="HLS.UNWRITEOFF_AMOUNT"/>
        <bm:field name="returning_amount" databasetype="java.lang.Double"
                  expression="(select nvl(sum(ctr.this_return_amount),0) from csh_transaction_return ctr where ctr.transaction_id = t1.transaction_id and ctr.status = 'APPROVING')"
                  forInsert="false" forceUpdate="false"/>
        <bm:field name="precision"
                  expression="(select g.precision from gld_currency g where g.currency_code =t1.currency_code)"
                  forInsert="false" forUpdate="false"/>
        <bm:field name="reverse_amount" expression="(-1*t1.transaction_amount)" forInsert="false" forUpdate="false"
                  prompt="CSH511.REVERSE_AMOUNT"/>
        <bm:field name="user_desc" expression="(select su.description from sys_user su where su.user_id=t1.created_by)"
                  forInsert="false" forUpdate="false"/>
        <bm:field name="auto_filt_penalty" expression="&apos;N&apos;" forInsert="false" forUpdate="false"
                  prompt="CSH513.AUTO_FILT_PENALTY"/>
        <bm:field name="auto_filt_prinicipal_interest" expression="&apos;Y&apos;" forInsert="false" forUpdate="false"
                  prompt="CSH513.AUTO_FILT_PRINICIPAL_INTEREST"/>
        <bm:field name="returned_flag_desc" forInsert="false" forUpdate="false"
                  prompt="CSH511.CSH_TRANSACTION.RETURNED_FLAG"/>
        <bm:field name="unallocate_amount"
                  expression="(nvl(t1.transaction_amount,0)-nvl(t1.write_off_amount,0)-nvl(t1.returned_amount,0)-nvl((select sum(i.write_off_amount) from csh_write_off_interface i where i.trx_interface_id = t1.transaction_id and i.record_type is not null),0))"
                  forInsert="false" forUpdate="false"/>
        <bm:field name="status" expression="nvl(t1.status,&apos;NEW&apos;)" forInsert="false" forUpdate="false"/>
        <bm:field name="ref_contract_id"/>
        <bm:field name="csh_bp_name"/>
        <bm:field name="ref_v01"/>
        <bm:field name="ref_v05"/>
        <bm:field name="purpose"/>
        <bm:field name="opposite_band_na"/>
        <bm:field name="bank_branch_name"
                  expression="(select a.bank_branch_name from csh_bank_account_v a where a.bank_account_id=t1.bank_account_id)"
                  forInsert="false" forUpdate="false" prompt="CSH511.BANK_BRANCH_NAME"/>
        <bm:field name="paid_byother_flag_n"
                  expression="(select code_value_name from sys_code_values_v where code = 'PAID_BYOTHER_FLAG' and code_value = t1.paid_byother_flag)"/>
        <bm:field name="contract_number"
                  expression="(select cc.contract_number from con_contract cc where cc.contract_id = t1.ref_contract_id)"/>
        <!--add by chenlingfeng-->
        <bm:field name="write_off_flag_desc" forInsert="false" forUpdate="false"/>
        <bm:field name="review_status" lookupCode="DS_CSH_REVIEW_STATUS" lookupField="review_status_desc"
                  databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="review_status_desc" forInsert="false" forUpdate="false"/>
        <bm:field name="collection_classes"/>
        <bm:field name="collection_classes_desc"
                  expression="(select v.code_value_name from sys_code_values_v v where v.code = 'DS_COLLECTION_CLASSES' and v.code_value =t1.collection_classes)"
                  forInsert="false" forUpdate="false"/>
        <bm:field name="receipt_type"/>
        <bm:field name="paid_byother_flag"/>
        <bm:field name="exists_advance_flag"  forceUpdate="false" forInsert="false" expression="nvl((select 'Y' from dual where exists(SELECT 1 FROM csh_transaction t  WHERE t.source_csh_trx_id = t1.transaction_id  AND transaction_type =  'ADVANCE_RECEIPT'  AND confirmed_flag != 'CANCELLED')),'N')"/>
        <bm:field name="exists_deposit_flag"  forceUpdate="false" forInsert="false" expression="nvl((select 'Y' from dual where exists(SELECT 1 FROM csh_transaction t  WHERE t.source_csh_trx_id = t1.transaction_id  AND transaction_type =  'DEPOSIT'  AND confirmed_flag != 'CANCELLED')),'N')"/>
        <!--end-->

        <!--add by lisitong-->
        <bm:field name="approving_return_amount" datatype="java.lang.Double"
                  expression="(select nvl(sum(ctr.this_return_amount),0) from csh_transaction_return ctr where ctr.transaction_id = t1.transaction_id and ctr.status = 'APPROVING')"
                  forInsert="false" forUpdate="false" prompt="审批中退款金额"/>
        <!--end-->
    </bm:fields>
    <bm:features>
        <f:standard-who/>
    </bm:features>
    <bm:primary-key>
        <bm:pk-field name="transaction_id"/>
    </bm:primary-key>
    <bm:data-filters>
        <bm:data-filter name="query"
                        expression="(t1.transaction_id=${@transaction_id} and t1.company_id=${/session/@company_id})"/>
    </bm:data-filters>
</bm:model>
