<?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,
                        pnb.session_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,
                        sdic_report_pkg.get_con_cashflow_adjust_flag(p_cashflow_id => ccc.cashflow_id) adjust_flag,
                        TO_CHAR(sdic_report_pkg.get_con_cashflow_adjust_date(p_cashflow_id => ccc.cashflow_id), 'YYYY') adjust_date_year,
                        TO_CHAR(sdic_report_pkg.get_con_cashflow_adjust_date(p_cashflow_id => ccc.cashflow_id), 'MM') adjust_date_month,
                        TO_CHAR(sdic_report_pkg.get_con_cashflow_adjust_date(p_cashflow_id => ccc.cashflow_id), 'DD') adjust_date_day,
                        sdic_report_pkg.get_con_cashflow_adjust_rate(p_cashflow_id => ccc.cashflow_id) adjust_base_rate,
                        TO_CHAR(sdic_report_pkg.get_con_cashflow_adjust_date_r(p_cashflow_id => ccc.cashflow_id), 'YYYY') real_adjust_date_year,
                        TO_CHAR(sdic_report_pkg.get_con_cashflow_adjust_date_r(p_cashflow_id => ccc.cashflow_id), 'MM') real_adjust_date_month,
                        TO_CHAR(sdic_report_pkg.get_con_cashflow_adjust_date_r(p_cashflow_id => ccc.cashflow_id), 'DD') real_adjust_date_day,
                        sdic_report_pkg.get_con_cashflow_adjust_rate_r(p_cashflow_id => ccc.cashflow_id) adjust_int_rate,
                        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) + NVL(
                        (SELECT
                            SUM(cct.due_amount)
                        FROM
                            con_contract_cashflow cct
                        WHERE
                            cct.contract_id = ccc.contract_id AND
                            cct.cf_item     = 15
                        ), 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_batch pnb,
                        hls_bp_master hbm
                    WHERE
                        cc.contract_id  = ccc.contract_id AND
                        ccc.cashflow_id = pnb.cashflow_id AND
                        cc.bp_id_tenant = hbm.bp_id
                    ) t1 #WHERE_CLAUSE#
            ]]></bm:query-sql>
        </bm:operation>
        <bm:operation name="delete">
            <bm:update-sql><![CDATA[
            delete from hn_payment_notice_batch t where t.session_id=${/session/@session_id}
            ]]></bm:update-sql>
        </bm:operation>
        <bm:operation name="insert">
            <bm:update-sql><![CDATA[
                INSERT
                INTO
                    hn_payment_notice_batch
                    (
                        session_id,
                        contract_id,
                        cashflow_id,
                        user_id
                    )
                    VALUES
                    (
                        ${/session/@session_id},
                        ${@contract_id},
                        ${@cashflow_id},
                        ${/session/@user_id}
                    )
            ]]></bm:update-sql>
        </bm:operation>
    </bm:operations>
    <bm:data-filters>
        <bm:data-filter enforceOperations="query" expression="t1.session_id=${/session/@session_id}"/>
    </bm:data-filters>
</bm:model>
