vender_deposit_out.lwm 4.06 KB
Newer Older
Spencer Chang's avatar
Spencer Chang committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 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
<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: qianming  
    $Date: 2014-8-27 下午6:22:32  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" alias="t1">
    <bm:fields>
        <bm:field name="transaction_id"/>
        <bm:field name="contract_number"/>
        <bm:field name="bp_name"/>
        <bm:field name="write_off_type_dis"/>
        <bm:field name="write_off_due_amount"/>
        <bm:field name="write_off_date"/>
        <bm:field name="lease_times"/>
        <bm:field name="description"/>
    </bm:fields>
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
				  SELECT transaction_id,contract_number,bp_name,write_off_type_dis,write_off_due_amount,write_off_date,
				  lease_times,description
				  FROM (SELECT t.csh_transaction_id transaction_id,
                       (SELECT contract_number FROM con_contract WHERE contract_id = t.contract_id) contract_number,
                       (SELECT a.bp_name
                          FROM hls_bp_master   a,
                               con_contract_bp b
                         WHERE a.bp_id = b.bp_id
                               AND b.bp_category = 'TENANT'
                               AND b.contract_id = t.contract_id) bp_name,
                       (SELECT b.bp_id
                          FROM con_contract_bp b
                         WHERE b.bp_category = 'TENANT'
                               AND b.contract_id = t.contract_id) bp_id,
                       (SELECT code_value_name
                          FROM sys_code_values_v
                         WHERE code = 'CSH513_WRITE_OFF_TYPE'
                               AND code_value = t.write_off_type) write_off_type_dis,
                       decode(nvl(t.csh_write_off_amount,0),0,'0.00',to_char(t.csh_write_off_amount,'FM999,999,999.00'))write_off_due_amount,
                       to_char(t.write_off_date,'yyyy-mm-dd')write_off_date,
                       t.times lease_times,
                       t.description
                  FROM csh_write_off t
                 WHERE t.write_off_type in ('DEPOSIT_CREDIT','CSH_RETURN')
                 and t.reversed_flag = 'N'
                 and t.csh_transaction_id in (select ct.transaction_id from csh_transaction ct where ct.bp_id=${@bp_id})
                 union
                 SELECT t.csh_transaction_id transaction_id,
                       (SELECT contract_number FROM con_contract WHERE contract_id = t.contract_id) contract_number,
                       (SELECT a.bp_name
                          FROM hls_bp_master   a,
                               con_contract_bp b
                         WHERE a.bp_id = b.bp_id
                               AND b.bp_category = 'TENANT'
                               AND b.contract_id = t.contract_id) bp_name,
                       (SELECT b.bp_id
                          FROM con_contract_bp b
                         WHERE b.bp_category = 'TENANT'
                               AND b.contract_id = t.contract_id) bp_id,
                       (SELECT code_value_name
                          FROM sys_code_values_v
                         WHERE code = 'CSH513_WRITE_OFF_TYPE'
                               AND code_value = t.write_off_type) write_off_type_dis,
                       decode(nvl(t.csh_write_off_amount,0),0,'0.00',to_char(t.csh_write_off_amount,'FM999,999,999.00'))write_off_due_amount,
                       to_char(t.write_off_date,'yyyy-mm-dd')write_off_date,
                       t.times lease_times,
                       t.description
                  FROM csh_write_off t
                 WHERE t.write_off_type = 'DEPOSIT'
                 and t.reversed_flag = 'N'
                 and t.csh_transaction_id in (select ct.transaction_id from csh_transaction ct where ct.bp_id=${@bp_id} and ct.transaction_type = 'DEDUCTION_DEPOSIT')
                 order by write_off_date desc
				         ) t1 #WHERE_CLAUSE#
    		]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields><![CDATA[
    ]]></bm:query-fields>
</bm:model>