<?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
                    cc.contract_id,
                    hd.batch_id,
                    cc.contract_number,
                    cc.contract_number
                    || '-1' contract_number_1, --租赁附表编号
                    cc.contract_number
                    || '-3' contract_number_3,
                    cc.contract_number
                    || '-4' contract_number_4,
                    cc.contract_number
                    || '-5' contract_number_5, --实际租金支付表编号
                    cc.contract_number
                    || '-6' contract_number_6, --租金调整通知书编号
                    cc.contract_number
                    || '-7' contract_number_7,
                    cc.contract_name,
                    hbm.bp_name,
                    cc.business_type,
                    cc.pre_rental_business,
                    cc.inception_of_lease,
                    TO_CHAR(sysdate, 'YYYY') now_year,
                    TO_CHAR(sysdate, 'MM') now_month,
                    TO_CHAR(sysdate, 'DD') now_day,
                    TO_CHAR(cc.inception_of_lease, 'YYYY') inception_of_lease_year,
                    TO_CHAR(cc.inception_of_lease, 'MM') inception_of_lease_month,
                    TO_CHAR(cc.inception_of_lease, 'DD') inception_of_lease_day,
                    (SELECT
                        TO_CHAR(cct.due_amount, 'FM999,999,999,990.00')
                    FROM
                        con_contract_cashflow cct
                    WHERE
                        cct.contract_id = cc.contract_id AND
                        cct.cf_item     = 0 AND
                        cct.times       = 0
                    ) lease_item_amount,
                    REPLACE(change_number_to_rmb(
                    (SELECT
                        cct.due_amount
                    FROM
                        con_contract_cashflow cct
                    WHERE
                        cct.contract_id = cc.contract_id AND
                        cct.cf_item     = 0 AND
                        cct.times       = 0
                    )), '圆', '元') lease_item_amount_rmb,
                    ROUND(cc.int_rate * 100, 2) int_rate,
                    (SELECT
                        TO_CHAR(SUM(cct.due_amount), 'FM999,999,999,990.00')
                    FROM
                        con_contract_cashflow cct
                    WHERE
                        cct.contract_id = cc.contract_id AND
                        cct.cf_item     = 1
                    ) due_amount,
                    REPLACE(change_number_to_rmb(
                    (SELECT
                        SUM(cct.due_amount)
                    FROM
                        con_contract_cashflow cct
                    WHERE
                        cct.contract_id = cc.contract_id AND
                        cct.cf_item     = 1
                    )), '圆', '元') due_amount_rmb,
                    NVL(
                    (SELECT
                        TO_CHAR(SUM(cct.due_amount), 'FM999,999,999,990.00')
                    FROM
                        con_contract_cashflow cct
                    WHERE
                        cct.contract_id = cc.contract_id AND
                        cct.cf_item     = 51
                    ), 0) deposit,
                    REPLACE(change_number_to_rmb(NVL(
                    (SELECT
                        SUM(cct.due_amount)
                    FROM
                        con_contract_cashflow cct
                    WHERE
                        cct.contract_id = cc.contract_id AND
                        cct.cf_item     = 51
                    ), 0)), '圆', '元') deposit_rmb,
                    NVL(
                    (SELECT
                        TO_CHAR(SUM(cct.due_amount), 'FM999,999,999,990.00')
                    FROM
                        con_contract_cashflow cct
                    WHERE
                        cct.contract_id = cc.contract_id AND
                        cct.cf_item     = 3
                    ), 0) lease_charge,
                    REPLACE(change_number_to_rmb(NVL(
                    (SELECT
                        SUM(cct.due_amount)
                    FROM
                        con_contract_cashflow cct
                    WHERE
                        cct.contract_id = cc.contract_id AND
                        cct.cf_item     = 3
                    ), 0)), '圆', '元') lease_charge_rmb,
                    NVL(
                    (SELECT
                        TO_CHAR(SUM(cct.due_amount), 'FM999,999,999,990.00')
                    FROM
                        con_contract_cashflow cct
                    WHERE
                        cct.contract_id = cc.contract_id AND
                        cct.cf_item     = 8
                    ), 0) residual_value,
                    REPLACE(change_number_to_rmb(NVL(
                    (SELECT
                        SUM(cct.due_amount)
                    FROM
                        con_contract_cashflow cct
                    WHERE
                        cct.contract_id = cc.contract_id AND
                        cct.cf_item     = 8
                    ), 0)), '圆', '元') residual_value_rmb,
                    (
                        CASE
                            WHEN 12 * cc.lease_term <= 12
                            THEN '一年以内（含一年）'
                            WHEN 12 * cc.lease_term > 12 AND
                                12 * cc.lease_term <= 60
                            THEN '一至五年（含五年）'
                            ELSE '五年以上'
                        END) base_rate_type_n,
                    ccc.times,
                    TO_CHAR (
                    (SELECT
                        NVL(SUM(NVL(ccct.due_amount-NVL(ccct.received_amount,0), 0)), 0)
                    FROM
                        con_contract_cashflow ccct
                    WHERE
                        ccct.contract_id     = ccc.contract_id AND
                        ccct.times           = ccc.times AND
                        ccct.cf_status       = 'RELEASE' AND
                        ccct.write_off_flag <> 'FULL' AND
                        ccct.cf_item        IN (1,3,12, 15,8)
                    ) , 'FM999,999,999,990.00' ) pay_amount,
                    TO_CHAR (
                    (SELECT
                        NVL(SUM(NVL(ccct.principal, 0)),0)
                    FROM
                        con_contract_cashflow ccct
                    WHERE
                        ccct.contract_id     = ccc.contract_id AND
                        ccct.times           = ccc.times AND
                        ccct.cf_status       = 'RELEASE' AND
                        ccct.write_off_flag <> 'FULL' AND
                        ccct.cf_item        IN (1,3,12, 15)
                    ) , 'FM999,999,999,990.00' ) principal_all,  --保理项目的融资款  add by shz
                    TO_CHAR (
                    (SELECT
                        NVL(SUM(NVL(ccct.interest, 0)), 0)
                    FROM
                        con_contract_cashflow ccct
                    WHERE
                        ccct.contract_id     = ccc.contract_id AND
                        ccct.times           = ccc.times AND
                        ccct.cf_status       = 'RELEASE' AND
                        ccct.write_off_flag <> 'FULL' AND
                        ccct.cf_item        IN (1,3,12, 15)
                    ) , 'FM999,999,999,990.00' ) interest_all,--保理项目的融资利息  add by shz
                     TO_CHAR (
                    (SELECT
                        NVL(SUM(NVL(ccct.due_amount, 0)), 0)
                    FROM
                        con_contract_cashflow ccct
                    WHERE
                        ccct.contract_id     = ccc.contract_id AND
                        ccct.times           = ccc.times AND
                        ccct.cf_status       = 'RELEASE' AND
                        ccct.write_off_flag <> 'FULL' AND
                        ccct.cf_item        IN (1,3,12, 15)
                    ) , 'FM999,999,999,990.00' ) due_amount_all, --保理项目的合计款  add by shz
                    
                    TO_CHAR(ccc.due_date,'yyyy-mm-dd') pay_date,
                    TO_CHAR(
                    (SELECT
                        SUM(cct.principal)
                    FROM
                        con_contract_cashflow cct
                    WHERE
                        cct.contract_id = cc.contract_id AND
                        cct.cf_item     = 1
                    ), 'FM999,999,999,990.00') sum_principal,
                    TO_CHAR(
                    (SELECT
                        SUM(cct.interest)
                    FROM
                        con_contract_cashflow cct
                    WHERE
                        cct.contract_id = cc.contract_id AND
                        cct.cf_item     = 1
                    ), 'FM999,999,999,990.00') sum_interest,
                    NVL(
                    (SELECT
                        TO_CHAR(SUM(cct.due_amount), 'FM999,999,999,990.00')
                    FROM
                        con_contract_cashflow cct
                    WHERE
                        cct.contract_id = ccc.contract_id AND
                        cct.cf_item     = 3
                    ), 0) sum_lease_charge,
                    (SELECT
                        SUM(cct.principal)
                    FROM
                        con_contract_cashflow cct
                    WHERE
                        cct.contract_id = cc.contract_id AND
                        cct.cf_item     = 1
                    ) +
                    (SELECT
                        SUM(cct.interest)
                    FROM
                        con_contract_cashflow cct
                    WHERE
                        cct.contract_id = cc.contract_id AND
                        cct.cf_item     = 1
                    ) + NVL(
                    (SELECT
                        SUM(cct.due_amount)
                    FROM
                        con_contract_cashflow cct
                    WHERE
                        cct.contract_id = ccc.contract_id AND
                        cct.cf_item     = 3
                    ), 0) sum_pay_amount,
                    (SELECT
                        cba.bank_account_name
                    FROM
                        csh_bank_account cba,
                        hn_con_bank_account hcba
                    WHERE
                        cba.bank_account_id = hcba.bank_account_id AND
                        ccc.due_date        >= hcba.valid_from AND
                        ccc.due_date        <= hcba.valid_to AND
                        ccc.contract_id     = hcba.contract_id
                    ) bank_account_name,
                    (SELECT
                        cbb.bank_branch_name
                    FROM
                        csh_bank_account cba,
                        hn_con_bank_account hcba,
                        csh_bank_branch cbb
                    WHERE
                        cba.bank_account_id = hcba.bank_account_id AND
                        ccc.due_date        >= hcba.valid_from AND
                        ccc.due_date        <= hcba.valid_to AND
                        ccc.contract_id     = hcba.contract_id AND
                        cba.bank_branch_id  = cbb.bank_branch_id AND
                        cbb.enabled_flag    = 'Y'
                    ) bank_branch_name, --开户行
                    (SELECT
                        cba.bank_account_num
                    FROM
                        csh_bank_account cba,
                        hn_con_bank_account hcba
                    WHERE
                        cba.bank_account_id = hcba.bank_account_id AND
                        ccc.due_date        >= hcba.valid_from AND
                        ccc.due_date        <= hcba.valid_to AND
                        ccc.contract_id     = hcba.contract_id
                    ) bank_account_num, --银行账号
                    (SELECT ee.name FROM exp_employees ee WHERE ee.employee_id = cc.employee_id
                    ) manager, --(支付表)业务经理
                    (SELECT ee.mobil FROM exp_employees ee WHERE ee.employee_id = cc.employee_id
                    ) mobil --(支付表)业务经理联系方式
                FROM
                    con_contract cc,
                    con_contract_cashflow ccc,
                    hn_payment_notice_hd hd,
                    hn_payment_notice_ln ln,
                    hls_bp_master hbm
                WHERE
                    cc.contract_id  = ccc.contract_id AND
                    ccc.cashflow_id = ln.cashflow_id AND
                    cc.bp_id_tenant = hbm.bp_id AND
                    hd.batch_id     = ln.batch_id AND
                    hd.enabled_flag = 'Y' AND
                    cc.contract_id  = ${@contract_id} AND
                    ccc.cashflow_id = ${@cashflow_id}
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
