<?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 * FROM (SELECT
                    cc.contract_id,
                    (SELECT
                        pp.virtual_con_number
                    FROM
                        prj_project pp
                    WHERE
                        pp.project_id = cc.project_id AND
                        pp.data_class = 'VIRTUAL_CON'
                    ) project_number,
                    (SELECT
                        pp.virtual_con_number||'-5'
                    FROM
                        prj_project pp
                    WHERE
                        pp.project_id = cc.project_id AND
                        pp.data_class = 'VIRTUAL_CON'
                    ) virtual_con_number_5,
                    (SELECT
                        pp.virtual_con_number||'-1'
                    FROM
                        prj_project pp
                    WHERE
                        pp.project_id = cc.project_id AND
                        pp.data_class = 'VIRTUAL_CON'
                    ) virtual_con_number_1,
                    (SELECT hbm.bp_name FROM hls_bp_master hbm WHERE hbm.bp_id = cc.bp_id_tenant
                    ) bp_name,
                    substr(change_number_to_rmb(cc.lease_item_amount),
              0,
              length(change_number_to_rmb(cc.lease_item_amount)) - 2) lease_item_amount_rmb,
                    TO_CHAR(cc.lease_item_amount, 'FM999,999,999,990.00') lease_item_amount,
                    substr(change_number_to_rmb(cc.total_rental),
              0,
              length(change_number_to_rmb(cc.total_rental))) total_rental_rmb,
                    to_char(cc.total_rental,'FM999,999,999,990.00') total_rental,
                    TO_CHAR(cc.finance_amount, 'FM999,999,999,990.00') finance_amount,
                    ROUND((cc.int_rate * 100), 2) int_rate,
                    nvl(substr(change_number_to_rmb(cc.deposit),
              0,
              length(change_number_to_rmb(cc.deposit))-2),'零') deposit_rmb,
                    TO_CHAR(cc.deposit, 'FM999,999,999,990.00') deposit,
                    nvl(substr(change_number_to_rmb(cc.residual_value),
              0,
              length(change_number_to_rmb(cc.residual_value))-2),'零') residual_value_rmb,
                    TO_CHAR(cc.residual_value, 'FM999,999,999,990.00') residual_value,
                    (SELECT
                        pq.inception_of_lease
                    FROM
                        prj_project pp,
                        prj_quotation pq
                    WHERE
                        pp.project_id        = cc.project_id AND
                        pp.data_class        = 'VIRTUAL_CON' AND
                        pq.document_category = 'PROJECT' AND
                        pp.project_id        = pq.document_id AND
                        pq.quotation_type    = 'MAJOR'
                    ) inception_of_lease,
                    (SELECT
                        to_char(pq.inception_of_lease,'YYYY')
                    FROM
                        prj_project pp,
                        prj_quotation pq
                    WHERE
                        pp.project_id        = cc.project_id AND
                        pp.data_class        = 'VIRTUAL_CON' AND
                        pq.document_category = 'PROJECT' AND
                        pp.project_id        = pq.document_id AND
                        pq.quotation_type    = 'MAJOR'
                    ) inception_of_lease_year,
                    (SELECT
                        to_char(pq.inception_of_lease,'MM')
                    FROM
                        prj_project pp,
                        prj_quotation pq
                    WHERE
                        pp.project_id        = cc.project_id AND
                        pp.data_class        = 'VIRTUAL_CON' AND
                        pq.document_category = 'PROJECT' AND
                        pp.project_id        = pq.document_id AND
                        pq.quotation_type    = 'MAJOR'
                    ) inception_of_lease_month,
                    (SELECT
                        to_char(pq.inception_of_lease,'DD')
                    FROM
                        prj_project pp,
                        prj_quotation pq
                    WHERE
                        pp.project_id        = cc.project_id AND
                        pp.data_class        = 'VIRTUAL_CON' AND
                        pq.document_category = 'PROJECT' AND
                        pp.project_id        = pq.document_id AND
                        pq.quotation_type    = 'MAJOR'
                    ) inception_of_lease_day,
                    nvl(substr(change_number_to_rmb(cc.lease_charge),
              0,
              length(change_number_to_rmb(cc.lease_charge))-2),'零') lease_charge_rmb,
                    TO_CHAR(cc.lease_charge, 'FM999,999,999,990.00') net_lease_charge
                FROM
                    con_contract cc) t1
                    #WHERE_CLAUSE#
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
