<?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 pp.project_number,
			       pp.project_name,
			       pp.business_type,
			       pp.finance_amount,
			       pp.hn_industry_classification,
			       (select v.code_value_name
			          from sys_code_values_v v
			         where v.code = 'HN_INDUSTRY_CLASSIFICATION'
			           and v.code_value = pp.hn_industry_classification) hn_industry_classification_n,
			       pp.binary_classification,
			       (select v.code_value_name from sys_code_values_v v
			         where v.code in ('WITHIN_GROUP','MARKETIZATION')
			           and v.code_value = pp.binary_classification) binary_classification_n,
			       round(hn_report_pkg.get_project_left_principal(p_project_id => pp.project_id,
			                                                      p_calc_date => to_date(${@calc_date},'YYYY-MM-DD')),2) sur_principal,
			       to_char(round(hn_report_pkg.get_project_left_principal_p(p_project_id => pp.project_id,
			                                                                p_calc_date => to_date(${@calc_date},'YYYY-MM-DD'))*100,2)
			                                                        ,'FM990.00')||'%' sur_principal_p,
			       ROUND(hn_report_pkg.get_project_left_rent(p_project_id => pp.project_id,
			                                                 p_calc_date => to_date(${@calc_date},'YYYY-MM-DD')),2) sur_rent,
			       to_char(round(hn_report_pkg.get_project_left_rent_p(p_project_id => pp.project_id,
			                                             p_calc_date => to_date(${@calc_date},'YYYY-MM-DD'))*100,2)
			                                                        ,'FM990.00')||'%' sur_rent_p,
			       null bp_id,
			       (select bp_name from hls_bp_master hbm where hbm.bp_id = pp.bp_id_tenant) bp_name,
			       1 project_num,
                   'BINARY_CLASSIFICATION' dimension_type,
			       pp.project_id
			  from prj_project pp
			 where pp.data_class = 'NORMAL'
			   and pp.binary_classification in
			       (select tt.code_value
			          from sys_code_values_v tt
			         where tt.code in ('WITHIN_GROUP', 'MARKETIZATION')
			           and tt.code_enabled_flag = 'Y'
			           and tt.code_value_enabled_flag = 'Y')
			   and pp.project_status = 'APPROVED'
			   and exists
			 (select 1
			          from prj_project ppt
			         where ppt.data_class = 'VIRTUAL_CON'
			           and ppt.ref_project_id = pp.project_id
			           and ppt.content_wfl_status = 'APPROVED'
			           and exists
			         (select 1
			                  from con_contract cc
			                 where cc.data_class = 'NORMAL'
			                   and cc.project_id = ppt.project_id
			                   and exists
			                 (select 1
			                          from csh_write_off cwo
			                         where cwo.write_off_type = 'PAYMENT_DEBT'
			                           and nvl(cwo.reversed_flag, 'N') = 'N'
			                           and cwo.contract_id = cc.contract_id
			                           and cwo.times = 0
			                           and cwo.cf_type = 0
			                           and cwo.cf_item = 0
			                           and cwo.write_off_date <= trunc(to_date(${@calc_date},'YYYY-MM-DD')))
			                   and not exists
			                 (select 1
			                          from csh_write_off cwo
			                         where cwo.write_off_type = 'RECEIPT_CREDIT'
			                           and nvl(cwo.reversed_flag, 'N') = 'N'
			                           and cwo.contract_id = cc.contract_id
			                           and cwo.times = cc.lease_times
			                           and cwo.cf_type = 1
			                           and cwo.cf_item = 1
			                           and cwo.write_off_date <= trunc(to_date(${@calc_date},'YYYY-MM-DD')))))
			   union
			    select pp.project_number,
				       pp.project_name,
				       pp.business_type,
				       pp.finance_amount,
				       pp.hn_industry_classification,
				       pp.binary_classification,
				       (select v.code_value_name from sys_code_values_v v
				         where v.code in ('WITHIN_GROUP','MARKETIZATION')
				           and v.code_value = pp.binary_classification) binary_classification_n,
				       (select v.code_value_name
				          from sys_code_values_v v
				         where v.code = 'HN_INDUSTRY_CLASSIFICATION'
				           and v.code_value = pp.hn_industry_classification) hn_industry_classification_n,
				       round(hn_report_pkg.get_project_left_principal(p_project_id => pp.project_id,
				                                                p_calc_date => to_date(${@calc_date},'YYYY-MM-DD')),2) sur_principal,
				       to_char(round(hn_report_pkg.get_project_left_principal_p(p_project_id => pp.project_id,
				                                                        p_calc_date => to_date(${@calc_date},'YYYY-MM-DD'))*100,2)
				                                                        ,'FM990.00')||'%' sur_principal_p,
				       round(hn_report_pkg.get_project_left_rent(p_project_id => pp.project_id,
				                                                 p_calc_date => to_date(${@calc_date},'YYYY-MM-DD')),2) sur_rent,
				       to_char(round(hn_report_pkg.get_project_left_rent_p(p_project_id => pp.project_id,
				                                                           p_calc_date => to_date(${@calc_date},'YYYY-MM-DD'))*100,2)
				                                                        ,'FM990.00')||'%' sur_rent_p,
				       pp.bp_id_tenant bp_id,
				       (select bp_name from hls_bp_master hbm where hbm.bp_id = pp.bp_id_tenant) bp_name,
				       1 project_num,
				       'BP_TENANT' dimension_type, 
				       pp.project_id
				  from prj_project pp
				 where pp.data_class = 'NORMAL'
				   and pp.project_id in
				       (SELECT
				              ppt.ref_project_id
				          FROM
				              prj_project ppt,
				              con_contract cc
				          WHERE
				              ppt.project_id     = cc.project_id AND
				              ppt.ref_project_id = pp.project_id AND
				              cc.data_class      = 'NORMAL' AND
				              cc.contract_id    IN
				              (SELECT
				                  cwo.contract_id
				              FROM
				                  csh_write_off cwo
				              WHERE
				                  cwo.write_off_type          = 'PAYMENT_DEBT' AND
				                  NVL(cwo.reversed_flag, 'N') = 'N' AND
				                  cwo.times                   = 0 AND
				                  cwo.cf_type                 = 0 AND
				                  cwo.cf_item                 = 0)
				              and not exists (select 1
				                              from csh_write_off cwo
				                             where cwo.write_off_type = 'RECEIPT_CREDIT'
				                               and nvl(cwo.reversed_flag, 'N') = 'N'
				                               and cwo.contract_id = cc.contract_id
				                               and cwo.times = cc.lease_times
				                               and cwo.cf_type = 1
				                               and cwo.cf_item = 1
				                               and cwo.write_off_date <= trunc(to_date(${@calc_date},'YYYY-MM-DD'))))
			                           
			            ) t1 where t1.binary_classification = ${@binary_classification} or t1.bp_id = ${@bp_id}
			     ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <!-- <bm:query-fields>
        <bm:query-field name="binary_classification" queryExpression="t1.binary_classification=${@binary_classification}"/>
        <bm:query-field name="bp_name" queryExpression="t1.bp_name like &apos;%&apos;||${@bp_name}||&apos;%&apos;"/>
    </bm:query-fields> -->
</bm:model>
