csh_transaction.lwm 18.4 KB
Newer Older
Spencer Chang's avatar
Spencer Chang committed
1 2 3 4 5 6 7
<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: gaoyang  
    $Date: 2013-5-14 下午01:23:36  
    $Revision: 1.0  
    $Purpose: 
-->
8 9
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" xmlns:f="leaf.database.features" alias="t1"
          baseTable="CSH_TRANSACTION">
Spencer Chang's avatar
Spencer Chang committed
10 11 12
    <bm:operations>
        <bm:operation name="update">
            <bm:update-sql><![CDATA[
13 14 15 16 17 18 19
                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>
Spencer Chang's avatar
Spencer Chang committed
20 21 22
        </bm:operation>
    </bm:operations>
    <bm:fields>
23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149
        <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"/>
Spencer Chang's avatar
Spencer Chang committed
150
        <bm:field name="bp_bank_account_name" forInsert="false" forUpdate="false" prompt="CSH511.BANK_ACCOUNT_NAME"/>
151 152 153 154 155 156 157 158 159
        <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"/>
Spencer Chang's avatar
Spencer Chang committed
160
        <bm:field name="transaction_type_desc" forInsert="false" forUpdate="false" prompt="HLS.TRANSACTION_TYPE"/>
161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184
        <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))"
Spencer Chang's avatar
Spencer Chang committed
185 186 187 188 189 190 191
                  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"/>
192 193 194 195 196 197 198 199
        <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)"/>
Spencer Chang's avatar
Spencer Chang committed
200 201
        <!--add by chenlingfeng-->
        <bm:field name="write_off_flag_desc" forInsert="false" forUpdate="false"/>
202 203
        <bm:field name="review_status" lookupCode="DS_CSH_REVIEW_STATUS" lookupField="review_status_desc"
                  databaseType="VARCHAR2" datatype="java.lang.String"/>
Spencer Chang's avatar
Spencer Chang committed
204 205
        <bm:field name="review_status_desc" forInsert="false" forUpdate="false"/>
        <bm:field name="collection_classes"/>
206 207 208
        <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"/>
209 210 211 212
        <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')"/>
Spencer Chang's avatar
Spencer Chang committed
213 214 215
        <!--end-->

        <!--add by lisitong-->
216 217 218
        <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="审批中退款金额"/>
Spencer Chang's avatar
Spencer Chang committed
219 220 221 222 223 224 225 226 227
        <!--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>
228 229
        <bm:data-filter name="query"
                        expression="(t1.transaction_id=${@transaction_id} and t1.company_id=${/session/@company_id})"/>
Spencer Chang's avatar
Spencer Chang committed
230 231
    </bm:data-filters>
</bm:model>