<?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
                    DECODE(t.ln_count, 1, t.project_name, t.project_name
                    || '等') project_name,
                    DECODE(t.ln_count, 1, t.employee_name, t.employee_name
                    || '等') employee_name,
                    DECODE(t.ln_count, 1, t.lease_organization_n, t.lease_organization_n
                    || '等') lease_organization_n,
                    DECODE(t.ln_count, 1, t.paid_date, t.paid_date
                    || '等') paid_date,
                    DECODE(t.ln_count, 1, t.paid_probability_desc, t.paid_probability_desc
                    || '等') paid_probability_desc,
                    t.sum_reserve_amount
                FROM
                    (SELECT
                        p.project_name,
                        p.employee_name,
                        p.lease_organization_n,
                        TO_CHAR(
                        (SELECT
                            NVL(SUM(ctl.reserve_amount), 0)
                        FROM
                            tre_funds_reservation_lns ctl
                        WHERE
                            ctl.reservation_hds_id     = th.reservation_hds_id AND
                            NVL(ctl.closed_flag, 'N') <> 'Y'
                        ), 'fm99,999,999,900.00') sum_reserve_amount,
                        TO_CHAR(tl.paid_date,'yyyy-mm-dd') paid_date,
                        (SELECT
                            s.code_value_name
                        FROM
                            sys_code_values_v s
                        WHERE
                            s.code_value = tl.paid_probability AND
                            s.code       = 'TRE_RES_PAID_PROBABILITY_CODE'
                        ) paid_probability_desc,
                        (SELECT
                            COUNT(*)
                        FROM
                            tre_funds_reservation_lns ctl
                        WHERE
                            ctl.reservation_hds_id     = th.reservation_hds_id AND
                            NVL(ctl.closed_flag, 'N') <> 'Y'
                        ) ln_count
                    FROM
                        tre_funds_reservation_hds th,
                        tre_funds_reservation_lns tl,
                        prj_project_v p
                    WHERE
                        th.reservation_hds_id     = ${@document_id} AND
                        th.document_category      = ${@document_category} AND
                        th.reservation_hds_id     = tl.reservation_hds_id AND
                        tl.project_id             = p.project_id AND
                        NVL(tl.closed_flag, 'N') <> 'Y'
                    ORDER BY
                        tl.reservation_lns_id
                    ) t
                WHERE
                    rownum = 1
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
