update_amount_times.lwm 1.7 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
<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: zhuxianfei
    $Date: 2018/6/25 13:13
    $Revision: 1.0
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:operations>
        <bm:operation name="update">
            <bm:update-sql><![CDATA[
            begin
               update con_contract  t1
               set t1.balance = (SELECT SUM(cc.principal - nvl(cc.received_principal, 0)) as balance
                  FROM con_contract c, con_contract_cashflow cc
                 WHERE c.contract_id = cc.contract_id
                   AND cc.due_date >= to_date(${@datum_date},'yyyy-mm-dd')
                   AND cc.cf_direction = 'INFLOW'
                   and cc.cf_item in (1,200)
                   and t1.contract_id =c.contract_id),
                 t1.lave_times =(SELECT (max(cc.times) - min(cc.times))as times
                      FROM con_contract c, con_contract_cashflow cc
                     WHERE c.contract_id = cc.contract_id
                       AND cc.due_date >= to_date(${@datum_date},'yyyy-mm-dd')
                       AND cc.cf_direction = 'INFLOW'
                       and cc.cf_item = 1
                       and t1.contract_id =c.contract_id),
                t1.datum_date= to_date(${@datum_date},'yyyy-mm-dd')
                where t1.data_class = 'NORMAL'
                   AND t1.contract_status = 'INCEPT'
                   AND NOT EXISTS (SELECT 1
                          FROM tre_assets_contract_ln tl
                         WHERE tl.contract_id = t1.contract_id);
             end;
    ]]></bm:update-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="balance"/>
    </bm:fields>
</bm:model>