<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                SELECT
                    t1.*
                FROM
                    (SELECT
                        cc.contract_id,
                        cc.contract_number,
                        cc.bp_id_tenant,
                        (SELECT bp_name FROM hls_bp_master WHERE bp_id = cc.bp_id_tenant
                        ) bp_id_tenant_n,
                        cc.business_type,
                        (SELECT
                            bt.description
                        FROM
                            hls_business_type bt
                        WHERE
                            bt.business_type = cc.business_type
                        ) AS business_type_n,
                        cc.contract_status,
                        (SELECT
                            sv.code_value_name
                        FROM
                            sys_code_values_v sv
                        WHERE
                            sv.code                    = 'CON500_CONTRACT_STATUS' AND
                            sv.code_value              = cc.contract_status AND
                            sv.code_value_enabled_flag = 'Y'
                        ) contract_status_n,
                        (SELECT
                            NVL(SUM(t.principal), 0)
                        FROM
                            con_contract_cashflow t
                        WHERE
                            t.contract_id  = cc.contract_id AND
                            t.cf_item      = 1 AND
                            t.cf_direction = 'INFLOW' AND
                            t.cf_status    = 'RELEASE'
                        ) delivery_total,
                        (SELECT
                            NVL(SUM(t.received_principal), 0)
                        FROM
                            con_contract_cashflow t
                        WHERE
                            t.contract_id  = cc.contract_id AND
                            t.cf_item      = 1 AND
                            t.cf_direction = 'INFLOW' AND
                            t.cf_status    = 'RELEASE'
                        ) total_rental,
                        (
                        (SELECT
                            NVL(SUM(t.principal), 0)
                        FROM
                            con_contract_cashflow t
                        WHERE
                            t.contract_id  = cc.contract_id AND
                            t.cf_item      = 1 AND
                            t.cf_direction = 'INFLOW' AND
                            t.cf_status    = 'RELEASE'
                        ) -
                        (SELECT
                            NVL(SUM(t.received_principal), 0)
                        FROM
                            con_contract_cashflow t
                        WHERE
                            t.contract_id  = cc.contract_id AND
                            t.cf_item      = 1 AND
                            t.cf_direction = 'INFLOW' AND
                            t.cf_status    = 'RELEASE'
                        )) use_amount,
                        cc.overdue_status,
                        (SELECT
                            sv.code_value_name
                        FROM
                            sys_code_values_v sv
                        WHERE
                            sv.code       = 'YES_NO' AND
                            sv.code_value = cc.overdue_status
                        ) overdue_status_n,
                        (SELECT
                            TO_CHAR(due_date, 'yyyy-mm-dd')
                        FROM
                            con_contract_cashflow
                        WHERE
                            cf_item      = 0 AND
                            cf_direction = 'NONCASH' AND
                            contract_id  = cc.contract_id
                        ) release_date
                    FROM
                        con_contract cc
                    WHERE
                        cc.data_class    = 'NORMAL' AND
                        cc.contract_status not in ('CLOSED') AND
                        cc.bp_id_tenant IN
                        (SELECT
                            l.bp_id
                        FROM
                            hls_bp_master_credit_assign l
                        WHERE
                            l.bp_credit_hd_id = ${@bp_credit_hd_id}
                        )
                    ) t1 #WHERE_CLAUSE#
            ]]></bm:query-sql>
        </bm:operation>
        <bm:operation name="update">
            <bm:update-sql><![CDATA[
               begin
               	hls_bp_master_credit_pkg.hls_credit_ln_save(p_bp_credit_line_id =>${@bp_credit_line_id},
						                               p_bp_credit_hd_id   => ${@bp_credit_hd_id},
						                               p_bp_id             => ${@bp_id},
						                               p_credit_number     => ${@credit_number},
						                               p_total_amount      =>${@credit_total_amount},
						                               p_open_amount       =>${@open_amount},
						                               p_credit_date_from  =>to_date(${@credit_date_from},'yyyy-mm-dd'),
						                               p_credit_date_to    =>to_date(${@credit_date_to},'yyyy-mm-dd'),
						                               p_enable_flag       =>${@enable_flag},
						                               p_user_id            =>${/session/@user_id});
               end;
            ]]></bm:update-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="contract_id"/>
        <bm:field name="contract_number"/>
        <bm:field name="bp_id_tenant"/>
        <bm:field name="bp_id_tenant_n"/>
        <bm:field name="contract_status"/>
        <bm:field name="contract_status_n"/>
        <bm:field name="business_type"/>
        <bm:field name="business_type_n"/>
        <bm:field name="delivery_total"/>
        <bm:field name="total_rental"/>
        <bm:field name="use_amount"/>
        <bm:field name="overdue_status"/>
        <bm:field name="overdue_status_n"/>
        <bm:field name="release_date"/>
    </bm:fields>
</bm:model>
