<?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 pq.document_number,
                   		   hdh.document_id loan_contract_id,
					       to_char(pq.lease_item_amount,'FM999,999,999,999,990.90') lease_item_amount,-- 借款总额
					       pq.int_rate,            -- 年利率
					       pq.hd_user_col_n18,     -- 付息日
					       pq.lease_times,         -- 还款期数
					       pq.quotation_id,
					       hd.price_list, -- 报价方案
					       (SELECT hpl.description
					          FROM hls_price_list hpl
					         WHERE hpl.price_list = hd.price_list) price_list_n,
					       to_char((select tlcwp.widthdrawal_date from tre_loan_con_withdrawal_plan tlcwp 
		                             where tlcwp.quotation_id = pq.quotation_id
		                               and nvl(tlcwp.write_off_flag, 'N') = 'Y'),'yyyy-mm-dd') inception_of_lease, -- 起始日
					       (select fbr.description 
					          from hls_fin_calculator_hd hfc,fnd_base_rate_type fbr
					         where hfc.calc_session_id = pq.calc_session_id and fbr.enabled_flag = 'Y' 
					           and fbr.base_rate_type = hfc.base_rate_type) base_rate_type, -- 基准利率类别
					       (select hfc.lease_item_price_agent 
					          from hls_fin_calculator_hd hfc where hfc.calc_session_id = pq.calc_session_id) lease_item_price_agent,-- 保函费率
					       to_char(pq.lease_end_date,'yyyy-mm-dd') lease_end_date,            -- 借款到期日
					       pq.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 = pq.annual_pay_times) as annual_pay_times_n,
					       to_char((select hfc.base_rate 
					          from hls_fin_calculator_hd hfc where hfc.calc_session_id = pq.calc_session_id) * 100,'FM90.9990')||'%' base_rate,-- 基准利率
					       (select hfc.other_fee2 
					          from hls_fin_calculator_hd hfc where hfc.calc_session_id = pq.calc_session_id) other_fee2,-- 咨询顾问费
					       TO_CHAR((select hfc.hd_user_col_v01 
					          from hls_fin_calculator_hd hfc where hfc.calc_session_id = pq.calc_session_id),'FM9999999990.90') hd_user_col_v01,-- 保函费计算基数
					       to_char((select hfc.hd_user_col_n20 
					          from hls_fin_calculator_hd hfc where hfc.calc_session_id = pq.calc_session_id),'yyyy-mm-dd') hd_user_col_n20,   -- 本金还款日
					       (select hfc.interim_times 
					          from hls_fin_calculator_hd hfc where hfc.calc_session_id = pq.calc_session_id) interim_times, -- 开始变更期
					       (select v.code_value_name 
					          from hls_fin_calculator_hd hfc,sys_code_values_v v
					         where hfc.calc_session_id = pq.calc_session_id and v.code = 'TRE502_INT_RATE_FIXING_WAY'
					           and v.code_value = hfc.int_rate_fixing_way) int_rate_fixing_way,              -- 利率调整规则
					       (select hfc.lease_charge 
					          from hls_fin_calculator_hd hfc where hfc.calc_session_id = pq.calc_session_id) lease_charge,-- 手续费
					       (select v.code_value_name 
					          from hls_fin_calculator_hd hfc,sys_code_values_v v
					         where hfc.calc_session_id = pq.calc_session_id and v.code = 'LOAN_CON_TYPE'
					           and v.code_value = hfc.repur_profile) repur_profile,-- 境内境外
					       hd.lease_times lease_times_change,        -- 还款期数(变更过后)
					       to_char((select hfc.int_rate_fixing_range 
					          from hls_fin_calculator_hd hfc where hfc.calc_session_id = pq.calc_session_id) *100,'FM90.999990')||'%' int_rate_fixing_range,--浮动幅度 
					       (select hfc.commission_receivable 
					          from hls_fin_calculator_hd hfc where hfc.calc_session_id = pq.calc_session_id) commission_receivable,   --安排费
					       (select v.code_value_name 
					          from hls_fin_calculator_hd hfc,sys_code_values_v v
					         where hfc.calc_session_id = pq.calc_session_id and v.code = 'TAX_RATE_TYPE' 
					           and v.code_value_enabled_flag = 'Y' and v.code_value = hfc.hd_user_col_n17) hd_user_col_n17,         --银行标准付息日
					       (select v.code_value_name 
		                      from sys_code_values_v v
		                     where v.code = 'HN_TRE_ADJUST_RATE_TYPE' and v.code_enabled_flag = 'Y' 
		                       and v.code_value_enabled_flag = 'Y' and v.code_value = hd.hd_user_col_v03) hd_user_col_v03,         --调息规则
					       (select hfc.lease_term 
					          from hls_fin_calculator_hd hfc where hfc.calc_session_id = pq.calc_session_id) lease_term,-- 期限      
					       pq.currency,                  -- 币种
					       (select v.code_value_name from sys_code_values_v v 
					         where v.code = 'TRE_CURRENCY_OPTIONS' and v.code_enabled_flag = 'Y' 
					           and v.code_value_enabled_flag = 'Y' and v.code_value = pq.currency) currency_n,
					       DECODE(hdh.usage_code,
					              'INT_RATE_FLOATING',
					              (SELECT MAX(slcrr.start_date)
					                 FROM sdic_loan_con_rate_record slcrr
					                WHERE slcrr.quotation_id = pq.quotation_id
					                  AND slcrr.int_rate = hd.int_rate),
					              'HISTORY',
					              (SELECT tlcc.req_date
					                 FROM tre_loan_contract_change tlcc
					                WHERE tlcc.change_req_id =
					                      (SELECT MAX(tlct.loan_contract_id)
					                         FROM tre_loan_contract tlct
					                        WHERE tlct.loan_contract_number =
					                              (SELECT tlc.loan_contract_number
					                                 FROM tre_loan_contract tlc
					                                WHERE tlc.loan_contract_id = pq.document_id)
					                          AND tlct.loan_contract_id < pq.document_id))) adj_date,
					       TO_CHAR(hd.int_rate * 100, 'FM90.999990') || '%' int_rate_n, --利率
					       ('v' || hdh.version) version,
					       DECODE(hdh.usage_code,
					              'INT_RATE_FLOATING',
					              '调息',
					              'HISTORY',
					              '合同变更') version_reason,
					               (select e.bp_name
			        from hls_bp_master e,tre_loan_contract tlc
			        where e.bp_id=tlc.loan_con_bp
			        and tlc.loan_contract_id = hdh.document_id) loan_bp_name
					  FROM prj_quotation pq, hls_document_history hdh, hls_fin_calculator_hd hd
					 WHERE hdh.data_class = 'HISTORY'
					   AND hdh.document_history_id = pq.document_id
					   AND hdh.document_category = 'LOAN_CONTRACT'
					   AND pq.document_category = 'LOAN_CONTRACT'
					   AND pq.calc_session_id = hd.calc_session_id(+)
					   AND exists (select 1 from tre_loan_con_repayment_plan t1 
                                    where t1.quotation_id = pq.quotation_id)
					   AND hdh.document_id = ${@loan_contract_id}
					UNION
					SELECT pq.document_number,
						   tlc.loan_contract_id,
					       to_char(pq.lease_item_amount,'FM999,999,999,999,990.90') lease_item_amount,-- 借款总额
					       pq.int_rate,            -- 年利率
					       pq.hd_user_col_n18,     -- 付息日
					       pq.lease_times,         -- 还款期数
					       pq.quotation_id,
					       hd.price_list, -- 报价方案
					       (SELECT hpl.description
					          FROM hls_price_list hpl
					         WHERE hpl.price_list = hd.price_list) price_list_n,
					       to_char((select tlcwp.widthdrawal_date from tre_loan_con_withdrawal_plan tlcwp 
		                             where tlcwp.quotation_id = pq.quotation_id
		                               and nvl(tlcwp.write_off_flag, 'N') = 'Y'),'yyyy-mm-dd') inception_of_lease, -- 起始日
					       (select fbr.description 
					          from hls_fin_calculator_hd hfc,fnd_base_rate_type fbr
					         where hfc.calc_session_id = pq.calc_session_id and fbr.enabled_flag = 'Y' 
					           and fbr.base_rate_type = hfc.base_rate_type) base_rate_type, -- 基准利率类别
					       (select hfc.lease_item_price_agent 
					          from hls_fin_calculator_hd hfc where hfc.calc_session_id = pq.calc_session_id) lease_item_price_agent,-- 保函费率
					       to_char(pq.lease_end_date,'yyyy-mm-dd') lease_end_date,            -- 借款到期日
					       pq.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 = pq.annual_pay_times) as annual_pay_times_n,
					       to_char((select hfc.base_rate 
					          from hls_fin_calculator_hd hfc where hfc.calc_session_id = pq.calc_session_id) * 100,'FM90.9990')||'%' base_rate,-- 基准利率
					       (select hfc.other_fee2 
					          from hls_fin_calculator_hd hfc where hfc.calc_session_id = pq.calc_session_id) other_fee2,-- 咨询顾问费
					       TO_CHAR((select hfc.hd_user_col_v01 
					          from hls_fin_calculator_hd hfc where hfc.calc_session_id = pq.calc_session_id),'FM9999999990.90') hd_user_col_v01,-- 保函费计算基数
					       to_char((select hfc.hd_user_col_n20 
					          from hls_fin_calculator_hd hfc where hfc.calc_session_id = pq.calc_session_id),'yyyy-mm-dd') hd_user_col_n20,   -- 本金还款日
					       (select hfc.interim_times 
					          from hls_fin_calculator_hd hfc where hfc.calc_session_id = pq.calc_session_id) interim_times, -- 开始变更期
					       (select v.code_value_name 
					          from hls_fin_calculator_hd hfc,sys_code_values_v v
					         where hfc.calc_session_id = pq.calc_session_id and v.code = 'TRE502_INT_RATE_FIXING_WAY'
					           and v.code_value = hfc.int_rate_fixing_way) int_rate_fixing_way,              -- 利率调整规则
					       (select hfc.lease_charge 
					          from hls_fin_calculator_hd hfc where hfc.calc_session_id = pq.calc_session_id) lease_charge,-- 手续费
					       (select v.code_value_name 
					          from hls_fin_calculator_hd hfc,sys_code_values_v v
					         where hfc.calc_session_id = pq.calc_session_id and v.code = 'LOAN_CON_TYPE'
					           and v.code_value = hfc.repur_profile) repur_profile,-- 境内境外
					       hd.lease_times lease_times_change,        -- 还款期数(变更过后)
					       to_char((select hfc.int_rate_fixing_range 
					          from hls_fin_calculator_hd hfc where hfc.calc_session_id = pq.calc_session_id) *100,'FM90.999990')||'%' int_rate_fixing_range,--浮动幅度  
					       (select hfc.commission_receivable 
					          from hls_fin_calculator_hd hfc where hfc.calc_session_id = pq.calc_session_id) commission_receivable,   --安排费  
					       (select v.code_value_name 
					          from hls_fin_calculator_hd hfc,sys_code_values_v v
					         where hfc.calc_session_id = pq.calc_session_id and v.code = 'TAX_RATE_TYPE' and v.code_enabled_flag = 'Y' 
					           and v.code_value_enabled_flag = 'Y' and v.code_value = hfc.hd_user_col_n17) hd_user_col_n17,         --银行标准付息日
					       (select v.code_value_name 
		                      from sys_code_values_v v
		                     where v.code = 'HN_TRE_ADJUST_RATE_TYPE' and v.code_enabled_flag = 'Y' 
		                       and v.code_value_enabled_flag = 'Y' and v.code_value = hd.hd_user_col_v03) hd_user_col_v03,         --调息规则
					       (select hfc.lease_term 
					          from hls_fin_calculator_hd hfc where hfc.calc_session_id = pq.calc_session_id) lease_term,-- 期限 
					       pq.currency,                  -- 币种 
					       (select v.code_value_name from sys_code_values_v v 
					         where v.code = 'TRE_CURRENCY_OPTIONS' and v.code_enabled_flag = 'Y' 
					           and v.code_value_enabled_flag = 'Y' and v.code_value = pq.currency) currency_n,
					       (SELECT MAX(slcrr.start_date)
					          FROM sdic_loan_con_rate_record slcrr
					         WHERE slcrr.quotation_id = pq.quotation_id
					           AND slcrr.int_rate = hd.int_rate) adj_date,
					       TO_CHAR(pq.int_rate * 100, 'FM90.999990') || '%' int_rate_n, --利率
					       'v' || (SELECT COUNT(1) + 1
					                 FROM hls_document_history cct
					                WHERE cct.document_id = tlc.loan_contract_id
					                  AND cct.data_class IN ('NORMAL', 'HISTORY')) version,
					       '正常' version_reason,
					        (select e.bp_name
			        from hls_bp_master e
			        where e.bp_id=tlc.loan_con_bp) loan_bp_name
					  FROM prj_quotation pq, tre_loan_contract tlc, hls_fin_calculator_hd hd
					 WHERE tlc.data_class = 'NORMAL'
					   AND tlc.loan_con_wfl_status = 'WFL_APPROVED'
					   AND pq.document_category = 'LOAN_CONTRACT'
					   AND pq.main_contract_flag = 'N'
					   AND tlc.loan_contract_id = pq.document_id
					   AND pq.calc_session_id = hd.calc_session_id(+)
					   AND exists (select 1 from tre_loan_con_repayment_plan t1 
                                    where t1.quotation_id = pq.quotation_id)
					   AND pq.document_id = ${@loan_contract_id}
                    ) t1 #WHERE_CLAUSE#
                ORDER BY
                    t1.document_number,
                    t1.version
            ]]></bm:query-sql>
            <bm:parameters>
                <bm:parameter name="loan_contract_id"/>
            </bm:parameters>
        </bm:operation>
    </bm:operations>
</bm:model>
