<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: hp  
    $Date: 2015-3-20 下午3:22:57  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" xmlns:f="leaf.database.features">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                SELECT
                    *
                FROM
                    (SELECT
                        t1.contract_id,
                        t1.calc_session_id,
                        t1.contract_number,
                        t1.contract_name,
                        t1.business_type,
                        T1.INCEPTION_CATEGORY,
                        (SELECT
                            v.code_value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code       = 'PRJ501N_ARRANGE_TYPE' AND
                            v.code_value = t1.INCEPTION_CATEGORY
                        ) INCEPTION_CATEGORY_N,
                        t1.SALE_ASSISTANT_ID,
                        (SELECT
                            su.description
                        FROM
                            sys_user su
                        WHERE
                            su.user_id = t1.sale_assistant_id
                        ) sale_assistant_id_n,
                        (SELECT
                            hl.bp_class_n
                        FROM
                            hls_bp_master_lv hl
                        WHERE
                            hl.bp_id = t1.bp_id_tenant
                        ) bp_class_n,
                        (SELECT hl.bp_class FROM hls_bp_master_lv hl WHERE hl.bp_id = t1.bp_id_tenant
                        ) bp_class,
                        (SELECT
                            bt.description
                        FROM
                            hls_business_type bt
                        WHERE
                            bt.business_type = t1.business_type
                        ) AS business_type_n,
                        t1.document_type,
                        (SELECT
                            dt.description
                        FROM
                            hls_document_type dt
                        WHERE
                            dt.document_type = t1.document_type
                        ) AS document_type_n,
                        t1.document_category,
                        (SELECT
                            dc.description
                        FROM
                            hls_document_category dc
                        WHERE
                            dc.document_category = t1.document_category
                        ) AS document_category_n,
                        t1.project_id,
                        (SELECT
                            p.project_number
                        FROM
                            prj_project p
                        WHERE
                            t1.project_id = p.project_id
                        ) AS project_id_c,
                        (SELECT p.project_name FROM prj_project p WHERE t1.project_id = p.project_id
                        ) AS project_id_n,
                        t1.company_id,
                        (SELECT
                            c.company_full_name
                        FROM
                            fnd_companies_vl c
                        WHERE
                            c.COMPANY_ID = t1.company_id
                        ) AS company_id_n,
                        t1.spv_company_id,
                        (SELECT
                            c.COMPANY_SHORT_NAME
                        FROM
                            fnd_companies_vl c
                        WHERE
                            c.COMPANY_ID = t1.spv_company_id
                        ) AS spv_company_id_n,
                        t1.lease_organization,
                        (SELECT
                            o.description
                        FROM
                            hls_lease_organization o
                        WHERE
                            o.lease_organization = t1.lease_organization
                        ) AS lease_organization_n,
                        t1.lease_channel,
                        (SELECT
                            ch.description
                        FROM
                            hls_lease_channel ch
                        WHERE
                            ch.lease_channel = t1.lease_channel
                        ) AS lease_channel_n,
                        t1.division,
                        (SELECT d.description FROM hls_division d WHERE d.division = t1.division
                        ) AS division_n,
                        t1.bp_id_tenant,
                        (SELECT
                            ha.province_id_n
                        FROM
                            hls_bp_master_address_lv ha
                        WHERE
                            ha.bp_id        = t1.bp_id_tenant AND
                            ha.address_type = 'DOC_SENT_ADDRESS' AND
                            ha.enabled_flag = 'Y' AND
                            ROWNUM          = 1
                        ) province_id_tenant_n,
                        -- dbms_lob.substr(wmsys.wm_concat(cb.bp_id_n))
                        (
                        SELECT
                            dbms_lob.substr(wmsys.wm_concat(ci.specification))
                        FROM
                            con_contract_lease_item ci
                        WHERE
                            ci.contract_id    = t1.contract_id AND
                            ci.specification IS NOT NULL
                        ) specification,
                        (SELECT
                            dbms_lob.substr(wmsys.wm_concat(ci.serial_number))
                        FROM
                            con_contract_lease_item ci
                        WHERE
                            ci.contract_id    = t1.contract_id AND
                            ci.serial_number IS NOT NULL
                        ) serial_number,
                        /*(select h.bp_type
                        from con_contract_bp b,hls_bp_master h where 1=1
                        and b.bp_id = h.bp_id and b.contract_id = t1.contract_id and b.bp_category = 'TENANT') as bp_tenant_type,*/
                        (
                        SELECT
                            m.bp_name
                        FROM
                            hls_bp_master m
                        WHERE
                            m.bp_id = t1.bp_id_tenant
                        ) AS bp_id_tenant_n,
                        (SELECT
                            dbms_lob.substr(wmsys.wm_concat(h.bp_name ))
                        FROM
                            con_contract_bp b,
                            hls_bp_master h
                        WHERE
                            1             =1 AND
                            b.bp_id       = h.bp_id AND
                            b.contract_id = t1.contract_id AND
                            b.bp_category = 'TENANT'
                        ) bp_id_tenant_name,
                        t1.bp_id_agent_level1,
                        (SELECT m.bp_code FROM hls_bp_master m WHERE m.bp_id = t1.bp_id_agent_level1
                        ) AS bp_id_agent_level1_n,
                        t1.bp_id_agent_level2,
                        (SELECT m.bp_code FROM hls_bp_master m WHERE m.bp_id = t1.bp_id_agent_level2
                        ) AS bp_id_agent_level2_n,
                        t1.bp_id_agent_level3,
                        (SELECT m.bp_code FROM hls_bp_master m WHERE m.bp_id = t1.bp_id_agent_level3
                        ) AS bp_id_agent_level3_n,
                        t1.owner_user_id,
                        t1.employee_id,
                        (SELECT e.name FROM exp_employees e WHERE e.employee_id = t1.employee_id
                        ) AS employee_id_n,
                        (SELECT ee.phone FROM exp_employees ee WHERE ee.employee_id = t1.employee_id
                        ) AS employee_phone,
                        t1.unit_id,
                        (SELECT u.description FROM exp_org_unit_vl u WHERE u.unit_id = t1.unit_id
                        ) AS unit_id_n,
                        t1.employee_id_of_manager,
                        (SELECT
                            e.name
                        FROM
                            exp_employees e
                        WHERE
                            e.employee_id = t1.employee_id_of_manager
                        ) AS employee_id_of_manager_n,
                        T1.factoring_type,
                        t1.description,
                        t1.price_list,
                        (SELECT
                            l.description
                        FROM
                            hls_price_list l
                        WHERE
                            l.price_list = t1.price_list
                        ) AS price_list_n,
                        t1.calc_method,
                        cus_con_lease_item_pkg.get_ccr_type(t1.contract_id) change_type,
                         to_char(t1.inception_of_lease,'yyyy-mm-dd') inception_of_lease,
                        t1.lease_start_date,
                        t1.first_pay_date,
                        t1.last_pay_date,
                        t1.lease_end_date,
                        t1.lease_times,
                        t1.pay_times,
                        t1.annual_pay_times,
                        (SELECT
                            v.code_value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code       = 'HLS500_ANNUAL_PAY_TIMES' AND
                            v.code_value = t1.annual_pay_times
                        ) AS annual_pay_times_n,
                        t1.lease_term,
                        t1.pay_type,
                        (SELECT
                            v.code_value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code       = 'HLS500_PAY_TYPE' AND
                            v.code_value = t1.pay_type
                        ) AS pay_type_n,
                        t1.currency,
                        (SELECT
                            g.currency_name
                        FROM
                            gld_currency_vl g
                        WHERE
                            g.currency_code = t1.currency
                        ) AS currency_n,
                        t1.currency_precision,
                        t1.machinery_amount,
                        t1.parts_amount,
                        t1.lease_item_amount,
                        t1.lease_item_cost,
                        t1.down_payment,
                        t1.down_payment_ratio,
                        t1.finance_amount,
                        t1.net_finance_amount,
                        t1.total_interest,
                        t1.total_rental,
                        t1.total_fee,
                        t1.contract_amount,
                        t1.tax_type_id,
                        (SELECT
                            t.description
                        FROM
                            fnd_tax_type_codes t
                        WHERE
                            t.tax_type_id = t1.tax_type_id
                        ) AS tax_type_id_n,
                        T1.IP_WITHDRAWER,
                        (SELECT Th.NAME FROM EXP_EMPLOYEES Th WHERE th.EMPLOYEE_ID=t1.IP_WITHDRAWER
                        )IP_WITHDRAWER_N,
                        T1.IP_WITHDRAW_DATE,
                        T1.TERMINATION_DOC_DATE,
                        T1.TERMINATION_TYPE,
                        (SELECT
                            v.code_value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code       = 'CON506_TERMINATION_TYPE' AND
                            v.code_value = t1.termination_type
                        ) AS TERMINATION_TYPE_N,
                        t1.DOC_NOTE,
                        t1.vat_flag,
                        t1.vat_rate,
                        t1.vat_input,
                        t1.vat_total_interest,
                        t1.vat_finance_amount,
                        t1.vat_total_rental,
                        t1.vat_total_fee,
                        t1.net_total_interest,
                        t1.net_total_rental,
                        t1.net_total_fee,
                        t1.net_lease_item_amount,
                        t1.lease_charge,
                        t1.lease_charge_ratio,
                        nvl(t1.lease_mgt_fee,0) lease_mgt_fee,
                        t1.lease_mgt_fee_ratio,
                        t1.lease_mgt_fee_rule,
                        (SELECT
                            V.code_value_name
                        FROM
                            SYS_CODE_VALUES_V V
                        WHERE
                            V.code       = 'HLS500_MGT_FEE_RULE' AND
                            V.code_value = t1.lease_mgt_fee_rule
                        ) AS lease_mgt_fee_rule_n,
                        t1.deposit,
                        t1.deposit_ratio,
                        t1.deposit_deduction,
                        (SELECT
                            V.code_value_name
                        FROM
                            SYS_CODE_VALUES_V V
                        WHERE
                            V.code       = 'HLS500_DEPOSIT_DEDUCTION' AND
                            V.code_value = t1.deposit_deduction
                        ) AS deposit_deduction_n,
                        t1.residual_value,
                        t1.residual_ratio,
                        t1.balloon,
                        t1.balloon_ratio,
                        t1.interim_rent_period,
                        t1.interim_times,
                        t1.interim_rental,
                        t1.insurance_fee,
                        t1.insurance_rate,
                        t1.commission_payable,
                        t1.commission_receivable,
                        t1.third_party_deposit,
                        t1.promise_to_pay,
                        t1.other_fee,
                        t1.other_payment,
                        t1.rounding_object,
                        t1.rounding_method,
                        (SELECT
                            v.code_value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code       = 'HLS500_CASHFLOW_ACCURATED' AND
                            v.code_value = t1.rounding_method
                        ) AS rounding_method_n,
                        t1.int_rate_fixing_way,
                        (SELECT
                            v.code_value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code       = 'TRE502_INT_RATE_FIXING_WAY' AND
                            v.code_value = t1.int_rate_fixing_way
                        ) AS int_rate_fixing_way_n,
                        t1.int_rate_fixing_range,
                        t1.int_rate_display,
                        t1.base_rate_type,
                        (SELECT
                            rt.description
                        FROM
                            fnd_base_rate_type rt
                        WHERE
                            rt.base_rate_type = t1.base_rate_type
                        ) AS base_rate_type_n,
                        t1.base_rate,
                        t1.int_rate,
                        t1.int_rate_implicit,
                        t1.int_rate_type,
                        (SELECT
                            V.code_value_name
                        FROM
                            SYS_CODE_VALUES_V V
                        WHERE
                            V.code       = 'CON500_INT_RATE_TYPE' AND
                            V.code_value = t1.int_rate_type
                        ) AS int_rate_type_n,
                        t1.flt_rate_profile,
                        (SELECT
                            description
                        FROM
                            con_flt_rate_profile p
                        WHERE
                            p.flt_rate_profile = t1.flt_rate_profile
                        ) AS flt_rate_profile_n,
                        t1.flt_rate_adj_method,
                        (SELECT
                            V.code_value_name
                        FROM
                            SYS_CODE_VALUES_V V
                        WHERE
                            V.code       = 'HLS500_FLT_RATE_ADJ_METHOD' AND
                            V.code_value = t1.flt_rate_adj_method
                        ) AS flt_rate_adj_method_n,
                        t1.flt_simulate_step,
                        t1.flt_simulate_range,
                        t1.flt_unit_adj_amt,
                        t1.flt_execute_times_rule,
                        (SELECT
                            V.code_value_name
                        FROM
                            SYS_CODE_VALUES_V V
                        WHERE
                            V.code       = 'HLS_FLT_EXECUTE_TIMES_RULE' AND
                            V.code_value = t1.flt_execute_times_rule
                        ) AS flt_execute_times_rule_n,
                        t1.flt_int_rate_adj_date,
                        t1.flt_delay_execute_period,
                        t1.flt_annual_adj_times ,
                        (SELECT
                            v.code_value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code       = 'HLS_FLT_ANNUAL_ADJ_TIMES' AND
                            v.code_value = t1.flt_annual_adj_times
                        ) AS flt_annual_adj_times_n,
                        flt_next_adj_date ,
                        t1.int_rate_precision,
                        t1.irr,
                        t1.irr_after_tax,
                        t1.int_rate_implicit_after_tax,
                        t1.irr_reserved1,
                        t1.irr_reserved2,
                        t1.irr_reserved3,
                        t1.pmt,
                        t1.pmt_first,
                        t1.npv_using_cof,
                        t1.annual_mean_rate,
                        t1.total_salestax,
                        t1.biz_day_convention,
                        (SELECT
                            V.code_value_name
                        FROM
                            SYS_CODE_VALUES_V V
                        WHERE
                            V.code       = 'HLS050_BIZ_DAY_CONVENTION' AND
                            V.code_value = t1.biz_day_convention
                        ) AS biz_day_convention_n,
                        t1.calc_with_residual_value,
                        t1.exchange_rate_type,
                        (SELECT
                            v.rate_type_desc
                        FROM
                            gld_exchange_rate_type_v v
                        WHERE
                            v.rate_type_code = t1.exchange_rate_type
                        ) AS exchange_rate_type_n,
                        t1.exchange_rate_quotation,
                        t1.exchange_rate,
                        t1.penalty_profile,
                        t1.grace_period,
                        t1.penalty_rate,
                        t1.penalty_calc_base,
                        t1.penalty_total_base_ratio,
                        t1.credit_write_off_order,--?
                        t1.fin_income_recognize_method,
                        t1.early_termination_profile,
                        (SELECT
                            p.description
                        FROM
                            con_contract_et_profile p
                        WHERE
                            p.et_profile = t1.early_termination_profile
                        ) AS early_termination_profile_n,
                        t1.payment_method_id,
                        (SELECT
                            cm.description
                        FROM
                            csh_payment_method cm
                        WHERE
                            cm.payment_method_id = t1.payment_method_id
                        ) payment_method_id_n,
                        t1.telex_transfer_bank_id,
                        t1.tt_bank_branch_name,
                        t1.tt_bank_account_num,
                        t1.tt_bank_account_name,
                        t1.tt_remark,
                        t1.direct_debit_bank_id,
                        (SELECT
                            cb.bank_full_name
                        FROM
                            csh_bank cb
                        WHERE
                            cb.bank_id = t1.direct_debit_bank_id
                        ) AS direct_debit_bank_id_n,
                        t1.dd_bank_branch_name,
                        t1.dd_bank_account_num,
                        t1.dd_bank_account_name,
                        t1.dd_agreement_no,
                        t1.dd_agreement_status,
                        t1.dd_remark,
                        t1.purchase_order_no,
                        t1.contract_status,
                        (SELECT
                            v.code_value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code       = 'CON500_CONTRACT_STATUS' AND
                            v.code_value = t1.contract_status
                        ) AS contract_status_n,
                        t1.user_status_1,
                        (SELECT
                            v.code_value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code       = 'CON543_USER_STATUS_1' AND
                            v.code_value = t1.USER_STATUS_1
                        ) AS USER_STATUS_1_N,
                        t1.user_status_2,
                        t1.user_status_3,
                        t1.print_status,
                        t1.print_times,
                        t1.first_print_date,
                        t1.first_print_by,
                        t1.delivery_status,
                        t1.delivery_date,
                        t1.billing_method,
                        (SELECT
                            bm.description
                        FROM
                            con_billing_method bm
                        WHERE
                            bm.billing_method = t1.billing_method
                        ) AS billing_method_n,
                        t1.billing_status,
                        to_char(t1.signing_date,'yyyy-mm-dd') signing_date,
                        t1.original_recall_date,
                        t1.lease_card_recall_date,
                        t1.early_termination_date,
                        t1.termination_date,
                        t1.assignment_agreement_no,
                        t1.assignment_date,
                        t1.btb_payment_date,
                        t1.btb_int_rate,
                        t1.btb_int_rate_implicit,
                        t1.btb_finance_amount,
                        t1.btb_net_finance_amount,
                        t1.btb_total_repayment,
                        t1.btb_total_interest,
                        t1.btb_total_fee_pv,
                        t1.btb_total_fee,
                        t1.btb_finance_ratio,
                        t1.btb_interest_margin,
                        t1.btb_vat_interest,
                        t1.btb_vat_fee,
                        t1.btb_interest_after_tax,
                        t1.btb_fee_after_tax,
                        t1.cdd_list_id,
                        (SELECT
                            t.code_value_name
                        FROM
                            sys_code_values_v t
                        WHERE
                            t.code      = 'HLS050_ZERO_OR_ONE' AND
                            t.code_value=TO_CHAR(t1.hd_user_col_n13)
                        ) AS hd_user_col_n13_n,
                        t1.hd_user_col_n14,
                        (SELECT
                            t.code_value_name
                        FROM
                            sys_code_values_v t
                        WHERE
                            t.code      = 'HLS050_ZERO_OR_ONE' AND
                            t.code_value=TO_CHAR(t1.hd_user_col_n14)
                        ) AS hd_user_col_n14_n,
                        t1.hd_user_col_n15,
                        (SELECT
                            t.code_value_name
                        FROM
                            sys_code_values_v t
                        WHERE
                            t.code      = 'HLS050_ZERO_OR_ONE' AND
                            t.code_value=TO_CHAR(t1.hd_user_col_n15)
                        ) AS hd_user_col_n15_n,
                        overdue_status,
                        overdue_max_days,
                        five_class_code,
                        (SELECT
                            r.description
                        FROM
                            rsc_five_class_code r
                        WHERE
                            t1.five_class_code = r.five_class_code
                        ) AS five_class_code_n,
                        main_business_income ,
                        main_business_cost,
                        financing_cost,
                        calc_prompt,
                        calc_prompt_msg,
                        lease_item_price_agent,
                        legal_fee,
                        version,
                        version_date,
                        version_display,
                        version_external,
                        version_reason,
                        version_note ,
                        t1.created_by,
                        t1.creation_date,
                        t1.last_updated_by,
                        t1.last_update_date,
                        t1.search_term_1,
                        t1.search_term_2,
                        t1.data_class,
                        t1.ccr_start_times,
                        t1.ccr_outstanding_times,
                        t1.ccr_outstanding_prin_tax_incld,
                        t1.ccr_overdue_rental,
                        t1.ccr_penalty,
                        t1.ccr_fee,
                        t1.ccr_finance_amount,
                        t1.ccr_pmt,
                        t1.ccr_outstanding_prin_ti_total,
                        t1.ccr_financing_overdue_rental,
                        (SELECT
                            t.code_value_name
                        FROM
                            sys_code_values_v t
                        WHERE
                            t.code      = 'HLS050_ZERO_OR_ONE' AND
                            t.code_value=TO_CHAR(t1.ccr_financing_overdue_rental)
                        ) AS ccr_financing_overdue_rental_n,
                        t1.ccr_period_int_rate_implicit,
                        t1.ccr_period_lease_start_date,
                        t1.ccr_added_principal,
                        t1.ccr_nominal_finance_amount,
                        t1.ccr_original_finance_amount,
                        t1.et_date,
                        t1.ccr_deposit_balance,
                        t1.ccr_debt_exemption_amount,
                        t1.et_interest,
                        t1.lessor_id,
                        t1.lessor_name,
                        (SELECT COUNT(1) FROM leg_legal_fee lg WHERE lg.contract_id = t1.contract_id
                        ) legal_fee_count,
                        (SELECT
                            dbms_lob.substr(wmsys.wm_concat(h.bp_name ))
                        FROM
                            con_contract_bp b,
                            hls_bp_master h
                        WHERE
                            1             =1 AND
                            b.bp_id       = h.bp_id AND
                            b.contract_id = t1.contract_id AND
                            b.bp_category = 'VENDER'
                        ) bp_id_vender_n,
                        NVL(
                        (SELECT
                            NVL(SUM(due_amount),0) - NVL(SUM(received_amount),0)
                        FROM
                            con_contract_cashflow
                        WHERE
                            contract_id  = t1.contract_id AND
                            due_date    <= sysdate AND
                            cf_item      = 1 AND
                            cf_direction = 'INFLOW'
                        ), 0) overdue_amount,
                        NVL(
                        (SELECT
                            SUM((NVL(f.due_amount,0) - NVL(f.received_amount,0))/NVL(f.due_amount,0))
                        FROM
                            con_contract_cashflow f
                        WHERE
                            contract_id          = t1.contract_id AND
                            cf_item              = 1 AND
                            TRUNC(f.due_date)    < TRUNC(sysdate) AND
                            NVL(f.due_amount,0) <> 0 AND
                            cf_direction         = 'INFLOW'
                        ), 0) overdue_times,
                        (SELECT
                            t.code_value_name
                        FROM
                            sys_code_values_v t
                        WHERE
                            t.code      = 'CON543_FILE_CONFIRM_STATUS' AND
                            t.code_value=t1.ALL_FILE_CONFIRM_STATUS
                        ) AS ALL_FILE_CONFIRM_STATUS_N,
                        (SELECT
                            t.code_value_name
                        FROM
                            sys_code_values_v t
                        WHERE
                            t.code      = 'CON543_FILE_CONFIRM_STATUS' AND
                            t.code_value=t1.P_FILE_CONFIRM_STATUS
                        ) AS P_FILE_CONFIRM_STATUS_N,
                        t1.BR_FILE_CONFIRM_DATE,
                        t1.P_FILE_CONFIRM_STATUS,
                        t1.FILE_CONFIRM_date,
                        t1.FILE_STATUS,
                        t1.signing_location,
                        T1.DD_BANK_ACCOUNT_TYPE,
                        (SELECT
                            v.code_value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code       = 'CON519D_DD_BANK_ACCOUNT_TYPE' AND
                            v.code_value = T1.DD_BANK_ACCOUNT_TYPE
                        ) DD_BANK_ACCOUNT_TYPE_N,
                        (SELECT
                            v.code_value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code       = 'CON543_FILE_STATUS' AND
                            v.code_value = T1.FILE_STATUS
                        ) FILE_STATUS_N,
                        t1.legal_status,
                        (SELECT
                            t.code_value_name
                        FROM
                            sys_code_values_v t
                        WHERE
                            t.code      = 'LEG501_LEGAL_STATUS' AND
                            t.code_value=t1.legal_status
                        ) AS legal_status_n,
                        t1.collector_user_id,
                        (SELECT
                            vv.employee_name
                        FROM
                            sys_user_v vv
                        WHERE
                            vv.user_id = t1.collector_user_id
                        ) collector_user_id_n,
                        (NVL(t1.ccr_overdue_rental,0)+NVL(t1.ccr_penalty,0)+NVL(t1.ccr_outstanding_prin_tax_incld,0)+NVL(t1.ccr_fee,0)+NVL(t1.et_interest,0)+NVL(t1.legal_fee,0)) ccr_total_amt,
                        (NVL(t1.ccr_overdue_rental,0)+NVL(t1.ccr_penalty,0)+NVL(t1.ccr_outstanding_prin_tax_incld,0)+NVL(t1.ccr_fee,0)+NVL(t1.et_interest,0)+NVL(t1.legal_fee,0))-NVL(t1.ccr_debt_exemption_amount,0)-NVL(t1.ccr_debt_exemption_amt_legal,0)-NVL(t1.ccr_deposit_balance,0) ccr_total_amt_acl,
                        T1.CCR_DEBT_EXEMPTION_AMT_LEGAL,
                        T1.FILE_USER_ID,
                        (SELECT su.description FROM sys_user su WHERE su.user_id = t1.FILE_USER_ID
                        )FILE_USER_ID_N,
                        t1.NOTARIZATION_FLAG,
                        (SELECT document_type FROM prj_project WHERE project_id = t1.project_id
                        ) AS document_type_p,
                        (SELECT bp_tenant_type FROM prj_project WHERE project_id = t1.project_id
                        ) AS bp_tenant_type_p,
                        (SELECT bp_vender_type FROM prj_project WHERE project_id = t1.project_id
                        ) AS bp_vender_type_p,
                        t1.lawsuit_status,
                        (SELECT
                            t.code_value_name
                        FROM
                            sys_code_values_v t
                        WHERE
                            t.code      = 'LEG501_LAWSUIT_STATUS' AND
                            t.code_value=t1.lawsuit_status
                        ) AS lawsuit_status_n,
                        t1.CCR_SIMPLIFY_FLAG,
                        (SELECT
                            ip.valid_from
                        FROM
                            ins_insurance_policy ip
                        WHERE
                            ip.contract_id = t1.contract_id AND
                            rownum         =1
                        ) valid_from,
                        (SELECT
                            ip.valid_to
                        FROM
                            ins_insurance_policy ip
                        WHERE
                            ip.contract_id = t1.contract_id AND
                            rownum         =1
                        ) valid_to,
                        (SELECT
                            ip.insured_flag
                        FROM
                            ins_insurance_policy ip
                        WHERE
                            ip.contract_id = t1.contract_id AND
                            rownum         =1
                        ) insured_flag,
                        t1.contract_seq,
                        (SELECT
                            cr.cashflow_change_flag
                        FROM
                            con_contract_change_req cr
                        WHERE
                            cr.change_req_id=t1.contract_id
                        ) cashflow_change_flag,
                        t1.delivery_type,
                        (SELECT
                            v.code_value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code                    = 'CON500_DELIVERY_TYPE' AND
                            v.code_value              =t1.delivery_type AND
                            v.code_enabled_flag       = 'Y' AND
                            v.code_value_enabled_flag = 'Y'
                        ) AS delivery_type_n,
                        --CSA
                        t1.customer_service_fee csa,
                        t1.customer_service_fee_financing,
                        --应付账款
                        (
                        SELECT
                            SUM(NVL(cf.due_amount,0))
                        FROM
                            con_contract_cashflow cf
                        WHERE
                            cf.contract_id  = t1.contract_id AND
                            cf.cf_direction = 'OUTFLOW' 
                            AND
                            cf.cf_status = 'RELEASE'
                        ) ar,
                        --应收未收余额
                        (
                        SELECT
                            SUM(NVL(cf.due_amount,0))-SUM(NVL(cf.received_amount,0))
                        FROM
                            con_contract_cashflow cf
                        WHERE
                            cf.contract_id  = t1.contract_id AND
                            cf.cf_direction = 'INFLOW' 
                            AND
                            cf.cf_status = 'RELEASE'
                        ) uncollected_ar ,
                        --未实现利息余额
                        (
                        SELECT
                            SUM(NVL(cf.interest,0))-SUM(NVL(cf.received_interest,0))
                        FROM
                            con_contract_cashflow cf
                        WHERE
                            cf.contract_id  = t1.contract_id AND
                            cf.cf_direction = 'INFLOW' AND
                            cf.cf_status    = 'RELEASE'
                        ) uncollected_interest ,
                        --本月确认利息
                        NVL(
                        (SELECT
                            SUM(NVL(cf.interest, 0))
                        FROM
                            con_contract_cashflow cf,
                            con_finance_income cfi
                        WHERE
                            1              = 1 AND
                            cf.cashflow_id = cfi.cashflow_id AND
                            cf.contract_id = cfi.contract_id AND
                            cf.contract_id = t1.contract_id AND
                            cf.due_date   >=TRUNC(to_date(nvl(${@transaction_date_from},'1999-01-01'),'yyyy-mm-dd')) AND
                            cf.due_date    < TRUNC(to_date(${@transaction_date_to},'yyyy-mm-dd')) + 0.01 AND
                            cf.cf_status   = 'RELEASE' AND
                            NOT
                            (
                                cf.times        = 0 AND
                                cf.cf_direction = 'NONCASH'
                            )
                        ),0) interest_current,
                        --未付租期
                        NVL(
                        (SELECT
                            SUM((NVL(f.due_amount,0) - NVL(f.received_amount,0))/NVL(f.due_amount,0))
                        FROM
                            con_contract_cashflow f
                        WHERE
                            contract_id          = t1.contract_id AND
                            cf_item              = 1 AND
                            NVL(f.due_amount,0) <> 0 AND
                            f.cf_status          = 'RELEASE' AND
                            cf_direction         = 'INFLOW'
                        ) , 0) uncollected_times ,
                        --已付租期
                        t1.lease_times - NVL(
                        (SELECT
                            SUM((NVL(f.due_amount,0) - NVL(f.received_amount,0))/NVL(f.due_amount,0))
                        FROM
                            con_contract_cashflow f
                        WHERE
                            contract_id          = t1.contract_id AND
                            cf_item              = 1 AND
                            NVL(f.due_amount,0) <> 0 AND
                            f.cf_status          = 'RELEASE' AND
                            cf_direction         = 'INFLOW'
                        ) , 0) collected_times ,
                        t1.lease_times TIMES,
                        --净投资额= 到期未还租金+未到期本金-预收本金
                        NVL(
                        (SELECT
                            SUM(NVL(f.due_amount,0)-NVL(f.received_amount,0))
                        FROM
                            con_contract_cashflow f
                        WHERE
                            contract_id          = t1.contract_id AND
                            cf_item              = 1 AND
                            TRUNC(f.due_date)    < TRUNC(to_date(${@transaction_date_to},'yyyy-mm-dd')) AND
                            NVL(f.due_amount,0) <> 0 AND
                            f.cf_status          = 'RELEASE' AND
                            cf_direction         = 'INFLOW'
                        ),0) + NVL(
                        (SELECT
                            SUM(NVL(f.principal,0)-NVL(f.received_amount,0))
                        FROM
                            con_contract_cashflow f
                        WHERE
                            contract_id          = t1.contract_id AND
                            cf_item              = 1 AND
                            TRUNC(f.due_date)   >= TRUNC(to_date(${@transaction_date_to},'yyyy-mm-dd')) AND
                            NVL(f.due_amount,0) <> 0 AND
                            f.cf_status          = 'RELEASE' AND
                            cf_direction         = 'INFLOW'
                        ),0) NET_INVEST_AMOUNT,
                        --月租金总和
                        NVL(
                        (SELECT
                            SUM(NVL(f.due_amount,0))
                        FROM
                            con_contract_cashflow f
                        WHERE
                            contract_id          = t1.contract_id AND
                            cf_item              = 1 AND 
                            f.due_date    < TRUNC(to_date(${@transaction_date_to},'yyyy-mm-dd')) + 0.01 AND 
                            NVL(f.due_amount,0) <> 0 AND
                            f.cf_status          = 'RELEASE' AND
                            cf_direction         = 'INFLOW'
                        ),0) due_amount ,
                        --当月收到租金
                        NVL(
                        (SELECT
                            SUM(NVL(f.received_amount,0))
                        FROM
                            con_contract_cashflow f
                        WHERE
                            contract_id          = t1.contract_id AND
                            cf_item              = 1 AND
                            f.due_date  > trunc(last_day(add_months(to_date(${@transaction_date_to},'yyyy-mm-dd'),-1))+1)-0.01 AND
                            f.due_date    < TRUNC(to_date(${@transaction_date_to},'yyyy-mm-dd')) + 0.01 AND 
                            NVL(f.due_amount,0) <> 0 AND
                            f.cf_status          = 'RELEASE' AND
                            cf_direction         = 'INFLOW'
                        ),0) transaction_amount_rental ,
                        
                        --确认总收入
						 nvl((select sum(nvl(cfi.finance_income,0)) from con_finance_income cfi
						 where cfi.contract_id = t1.contract_id ),0)  finance_income_sum,
                        --延迟履行金
                        NVL(
                        (SELECT
                            SUM(NVL(f.due_amount,0))
                        FROM
                            con_contract_cashflow f
                        WHERE
                            contract_id  = t1.contract_id AND
                            cf_item      = 9 AND
                            f.due_date   > trunc(last_day(add_months(to_date(${@transaction_date_to},'yyyy-mm-dd'),-1))+1)-0.01 AND
                            f.due_date    < TRUNC(to_date(${@transaction_date_to},'yyyy-mm-dd')) + 0.01 AND 
                            f.cf_status  = 'RELEASE' AND
                            cf_direction = 'INFLOW'
                        ) ,0) penalty,
                        --次12个月未到期本金
                        NVL(
                        (SELECT
                            SUM(NVL(f.principal,0)- NVL(f.received_principal,0))
                        FROM
                            con_contract_cashflow f
                        WHERE
                            contract_id  = t1.contract_id AND
                            cf_item      = 1 AND
                            f.cf_status  = 'RELEASE' AND
                            f.due_date  > TRUNC(to_date(${@transaction_date_to},'yyyy-mm-dd')) + 0.01 AND
                            f.due_date  <= TRUNC(add_months(to_date(${@transaction_date_to},'yyyy-mm-dd'),12)) AND
                            cf_direction = 'INFLOW'
                        ) ,0) principal_12,
                        --次12个月未到期利息
                        NVL(
                        (SELECT
                            SUM(NVL(f.interest,0)- NVL(f.received_interest,0))
                        FROM
                            con_contract_cashflow f
                        WHERE
                            contract_id  = t1.contract_id AND
                            cf_item      = 1 AND
                            f.cf_status  = 'RELEASE' AND
                            f.due_date  > TRUNC(to_date(${@transaction_date_to},'yyyy-mm-dd')) + 0.01 AND
                            f.due_date  <= TRUNC(add_months(to_date(${@transaction_date_to},'yyyy-mm-dd'),12)) AND
                            cf_direction = 'INFLOW'
                        ) ,0) interest_12,
                         --当月开始往后12月本金
                        NVL(
                        (SELECT
                            SUM(NVL(f.principal,0)- NVL(f.received_principal,0))
                        FROM
                            con_contract_cashflow f
                        WHERE
                            contract_id = t1.contract_id AND
                            cf_item     = 1 AND
                            f.cf_status = 'RELEASE'
                         
                            AND
                            f.due_date  >= TRUNC(add_months(to_date(${@transaction_date_to},'yyyy-mm-dd'),1*12)) AND
                            f.due_date  >= TRUNC(to_date(${@transaction_date_to},'yyyy-mm-dd')) AND
                            cf_direction = 'INFLOW'
                        ) ,0) principal_c_12_remain,
                        --剩余5年内到期本金
                        NVL(
                        (SELECT
                            SUM(NVL(f.principal,0)- NVL(f.received_principal,0))
                        FROM
                            con_contract_cashflow f
                        WHERE
                            contract_id = t1.contract_id AND
                            cf_item     = 1 AND
                            f.cf_status = 'RELEASE'
                         
                            AND
                            f.due_date  <= TRUNC(add_months(to_date(${@transaction_date_to},'yyyy-mm-dd'),5*12)) AND
                            cf_direction = 'INFLOW'
                        ) ,0) principal_5,
                        --超过12个月到期本金
                        NVL(
                        (SELECT
                            SUM(NVL(f.principal,0)- NVL(f.received_principal,0))
                        FROM
                            con_contract_cashflow f
                        WHERE
                            contract_id = t1.contract_id AND
                            cf_item     = 1 AND
                            f.cf_status = 'RELEASE'
                         
                            AND
                            f.due_date  >= TRUNC(add_months(to_date(${@transaction_date_to},'yyyy-mm-dd'),1*12)) AND 
                            cf_direction = 'INFLOW'
                        ) ,0) principal_12_remain,
                        --超过12个月到期利息
                        NVL(
                        (SELECT
                            SUM(NVL(f.interest,0)- NVL(f.received_interest,0))
                        FROM
                            con_contract_cashflow f
                        WHERE
                            contract_id = t1.contract_id AND
                            cf_item     = 1 AND
                            f.cf_status = 'RELEASE'
                         
                            AND
                            f.due_date  >= TRUNC(add_months(to_date(${@transaction_date_to},'yyyy-mm-dd'),5*12)) AND 
                            cf_direction = 'INFLOW'
                        ) ,0) interest_12_remain,
                        --剩余5年内到期利息
                        NVL(
                        (SELECT
                            SUM(NVL(f.interest,0) - NVL(f.received_interest,0))
                        FROM
                            con_contract_cashflow f
                        WHERE
                            contract_id = t1.contract_id AND
                            cf_item     = 1 AND
                            f.cf_status = 'RELEASE'
                         
                            AND
                            f.due_date  <= TRUNC(add_months(to_date(${@transaction_date_to},'yyyy-mm-dd'),5*12)) AND
                            cf_direction = 'INFLOW'
                        ) ,0) interest_5 ,
                        --超过5年内到期本金
                        NVL(
                        (SELECT
                            SUM(NVL(f.principal,0))
                        FROM
                            con_contract_cashflow f
                        WHERE
                            contract_id = t1.contract_id AND
                            cf_item     = 1 AND
                            f.cf_status = 'RELEASE'
                            
                            AND
                            f.due_date   > TRUNC(add_months(to_date(${@transaction_date_to},'yyyy-mm-dd'),5*12)) AND
                            cf_direction = 'INFLOW'
                        ) ,0) principal_5_over,
                        --超过5年内到期利息
                        NVL(
                        (SELECT
                            SUM(NVL(f.interest,0))
                        FROM
                            con_contract_cashflow f
                        WHERE
                            contract_id = t1.contract_id AND
                            cf_item     = 1 AND
                            f.cf_status = 'RELEASE' 
                            AND
                            f.due_date   > TRUNC(add_months(to_date(${@transaction_date_to},'yyyy-mm-dd'),5*12)) AND
                            cf_direction = 'INFLOW'
                        ) ,0) interest_5_over ,
                        --累计已收租金
                        NVL(
                        (SELECT
                            SUM(NVL(f.received_amount,0))
                        FROM
                            con_contract_cashflow f
                        WHERE
                            contract_id  = t1.contract_id AND
                            cf_item      = 1 AND
                            f.cf_status  = 'RELEASE' AND
                            cf_direction = 'INFLOW'
                        ) ,0) collected_rental ,
                        --直线法每期利息
                        NVL(t1.total_interest,0)/t1.lease_times interest_straight,
                        --未到期直线法利息
                        ROUND(NVL(
                        (SELECT
                            SUM(NVL(f.interest,0))
                        FROM
                            con_contract_cashflow f
                        WHERE
                            contract_id        = t1.contract_id AND
                            cf_item            = 1 AND
                            f.cf_status        = 'RELEASE' AND
                            TRUNC(f.due_date) >= TRUNC(to_date(${@transaction_date_to},'yyyy-mm-dd')) AND
                            cf_direction       = 'INFLOW'
                        ) ,0),2) uncollected_interest_straight ,
                        --税务调整利息
                        ROUND(NVL(
                        (SELECT
                            SUM(NVL(t1.total_interest,0)/t1.lease_times - NVL(f.interest,0))
                        FROM
                            con_contract_cashflow f
                        WHERE
                            contract_id  = t1.contract_id AND
                            cf_item      = 1 AND
                            f.cf_status  = 'RELEASE' AND
                            cf_direction = 'INFLOW'
                        ) ,0),2) interest_tax_adjust,
                        --本月申报已收利息,
                        NVL(
                        (SELECT
                            SUM(NVL(f.interest,0))
                        FROM
                            con_contract_cashflow f
                        WHERE
                            contract_id                                  = t1.contract_id AND
                            cf_item                                      = 1 AND
                            SUBSTR(TO_CHAR(f.due_date,'yyyy-mm-dd'),0,7) = SUBSTR(${@transaction_date_to},0,7)  AND
                            f.write_off_flag                             = 'FULL' AND
                            f.cf_status                                  = 'RELEASE' AND
                            cf_direction                                 = 'INFLOW'
                        ) ,0) interest_declared,
                        --截止上月累计已申报
                        NVL(
                        (SELECT
                            SUM(NVL(f.interest,0))
                        FROM
                            con_contract_cashflow f
                        WHERE
                            contract_id      = t1.contract_id AND
                            cf_item          = 1 AND
                            f.due_date       < TRUNC(add_months(last_day(to_date(${@transaction_date_to},'yyyy-mm-dd')),-1)+1) AND
                            f.write_off_flag = 'FULL' AND
                            f.cf_status      = 'RELEASE' AND
                            cf_direction     = 'INFLOW'
                        ) ,0) interest_declared_last,
                        --截止本月累计已申报
                        NVL(
                        (SELECT
                            SUM(NVL(f.interest,0))
                        FROM
                            con_contract_cashflow f
                        WHERE
                            contract_id      = t1.contract_id AND
                            cf_item          = 1 AND
                            f.due_date       < TRUNC(to_date(${@transaction_date_to},'yyyy-mm-dd')) AND
                            f.write_off_flag = 'FULL' AND
                            f.cf_status      = 'RELEASE' AND
                            cf_direction     = 'INFLOW'
                        ) ,0) interest_declared_current,
                        --首付款 = 首付款+留购金+管理费+保证金
                        NVL(t1.down_payment,0)+NVL(t1.residual_value,0)+NVL(t1.lease_charge,0)+NVL(t1.deposit,0) down_payment_1,
                        --收款日期
                        (
                        SELECT
                            to_char(MAX(f.last_received_date),'yyyy-mm-dd')
                        FROM
                            con_contract_cashflow f
                        WHERE
                            contract_id  = t1.contract_id AND
                            f.cf_status  = 'RELEASE' AND
                            cf_direction = 'INFLOW'
                        ) transaction_date ,
                        cus_con_lease_item_pkg.get_overdue_amount(t1.contract_id,to_date(${@transaction_date_to},'yyyy-mm-dd')) overdue_amount_new,
                        --会计期间
                        SUBSTR(TO_CHAR(t1.inception_of_lease,'yyyy-mm-dd'),0,7) account_period ,
                        --(select et.all_file_confirm_status_n from con_export_temp_v et where et.contract_id = t1.contract_id) all_file_confirm_status_n,
                        --变更
                        --变更日期
                        (
                        SELECT
                            to_char(MIN(ccr.approved_date),'yyyy-mm-dd')
                        FROM
                            con_contract_change_req ccr
                        WHERE
                            ccr.contract_id = t1.contract_id AND
                            ccr.req_status <> 'CANCEL'
                        ) CHANGE_DATE ,
                        t1.lease_times TENURE,
                        t1.actual_finance_amount
                    FROM
                        con_contract t1
                    WHERE
                        1                   =1 AND
                        t1.contract_status IN ('INCEPT','PENDING') AND
                        t1.data_class       = 'NORMAL' AND
                        t1.bp_id_tenant = nvl(${@bp_id_tenant},t1.bp_id_tenant) AND
                        t1.search_term_1 like nvl(${@search_term_1},t1.search_term_1) AND
                        t1.contract_number like nvl(${@contract_number},t1.contract_number)
                    ORDER BY
                        t1.contract_number DESC
                    ) tt #WHERE_CLAUSE#
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="search_term_1"/>
        <bm:field name="contract_number"/>
        <bm:field name="bp_id_tenant_n"/>
        <bm:field name="account_period"/>
        <bm:field name="inception_of_lease"/>
        <bm:field name="signing_date"/>
        <bm:field name="transaction_date"/>
        <bm:field name="division_n"/>
        <bm:field name="specification"/>
        <bm:field name="serial_number"/>
        <bm:field name="bp_id_vender_n"/>
        <bm:field name="province_id_tenant_n"/>
        <bm:field name="lease_item_amount"/>
        <bm:field name="insurance_fee"/>
        <bm:field name="csa"/>
        <bm:field name="ar"/>
        <bm:field name="residual_value"/>
        <bm:field name="deposit"/>
        <bm:field name="down_payment"/>
        <bm:field name="down_payment_1"/>
        <bm:field name="change_type"/>
        <bm:field name="change_date"/>
        <bm:field name="ccr_fee"/>
        <bm:field name="actual_finance_amount"/>
        <bm:field name="total_interest"/>
        <bm:field name="due_amount"/>
        <bm:field name="total_rental"/>
        <bm:field name="int_rate"/>
        <bm:field name="collected_times"/>
        <bm:field name="uncollected_times"/>
        <bm:field name="overdue_times"/>
        <bm:field name="times"/>
        <bm:field name="transaction_amount_rental"/>
        <bm:field name="penalty"/>
        <bm:field name="uncollected_ar"/>
        <bm:field name="net_invest_amount"/>
        <bm:field name="uncollected_interest"/>
        <bm:field name="interest_current"/>
        <bm:field name="principal_12"/>
        <bm:field name="interest_12"/>
        <bm:field name="principal_5"/>
        <bm:field name="interest_5"/>
        <bm:field name="principal_5_over"/>
        <bm:field name="interest_5_over"/>
        <bm:field name="interest_straight"/>
        <bm:field name="uncollected_interest_straight"/>
        <bm:field name="interest_tax_adjust"/>
        <bm:field name="interest_declared"/>
        <bm:field name="interest_declared_last"/>
        <bm:field name="interest_declared_current"/>
        <bm:field name="collected_rental"/>
        <bm:field name="contract_status_n"/>
        <bm:field name="all_file_confirm_status_n"/>
        <bm:field name="company_id_n"/>
        <bm:field name="finance_income_sum"/>
        <bm:field name="lease_mgt_fee"/>
        <bm:field name="principal_c_12_remain"/>
        <bm:field name="overdue_amount"/>
        <bm:field name="principal_12_remain"/>
        <bm:field name="interest_12_remain"/>
        <bm:field name="overdue_amount_new"/>
    </bm:fields>
    <bm:query-fields>
        <bm:query-field field="bp_id_tenant_n" queryOperator="like"/>
        <bm:query-field field="search_term_1" queryOperator="like"/>
        <bm:query-field field="contract_number" queryOperator="like"/>
        <bm:query-field field="account_period" queryOperator="like"/>
    </bm:query-fields>
</bm:model>
