<?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 (select v.code_value_name
				          from sys_code_values_v v
				         where v.code = 'HN_INDUSTRY_CLASSIFICATION'
				           and v.code_value = 
				       (select v.code
				          from sys_code_values_v v
				         where v.code IN ('WITHIN_GROUP', 'MARKETIZATION')
				           and v.code_value = tt.binary_classification )) code_name,
				       tt.binary_classification,
				       (select v.code_value_name 
				          from sys_code_values_v v
				         where v.code in ('WITHIN_GROUP','MARKETIZATION')
				           and v.code_value = tt.binary_classification) code_value_name,
				       null bp_id,
				       '' bp_name,
				       nvl(sum(nvl(tt.project_num,0)),0) project_num,
				       round(nvl(sum(nvl(tt.sur_principal,0)),0),2) prj_remain_principal,
				       to_char(round(nvl(sum(nvl(tt.sur_principal_p,0)),0)*100,2),'FM990.00')||'%' prj_remain_principal_percent,
				       round(nvl(sum(nvl(tt.sur_rent,0)),0),2) prj_remain_due_amount,
				       to_char(round(nvl(sum(nvl(tt.sur_rent_p,0)),0)*100,2),'FM990.00')||'%' prj_remain_due_amount_percent
				  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,
			       hn_report_pkg.get_project_left_principal(p_project_id => pp.project_id,
			                                                p_calc_date => to_date(${@calc_date},'YYYY-MM-DD')) sur_principal,
			       hn_report_pkg.get_project_left_principal_p(p_project_id => pp.project_id,
			                                                  p_calc_date => to_date(${@calc_date},'YYYY-MM-DD')) sur_principal_p,
			       hn_report_pkg.get_project_left_rent(p_project_id => pp.project_id,
			                                           p_calc_date => to_date(${@calc_date},'YYYY-MM-DD')) sur_rent,
			       hn_report_pkg.get_project_left_rent_p(p_project_id => pp.project_id,
			                                             p_calc_date => to_date(${@calc_date},'YYYY-MM-DD')) sur_rent_p,
			       pp.bp_id_tenant,
			       (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')))))) tt 
				 where tt.dimension_type = 'BINARY_CLASSIFICATION'
				   and ${@dimension} ='BINARY_CLASSIFICATION'
				 group by tt.binary_classification
	       union
	            select '' code_name,
	                   '' binary_classification,
	                   '' code_value_name,
	                   tt.bp_id_tenant bp_id,
				       (select bp_name from hls_bp_master hbm where hbm.bp_id = tt.bp_id_tenant) bp_name,
				       nvl(sum(nvl(tt.project_num,0)),0) project_num,
				       round(nvl(sum(nvl(tt.sur_principal,0)),0),2) prj_remain_principal,
				       to_char(round(nvl(sum(nvl(tt.sur_principal_p,0)),0)*100,2),'FM990.00')||'%' prj_remain_principal_percent,
				       ROUND(nvl(sum(nvl(tt.sur_rent,0)),0),2) prj_remain_due_amount,
				       to_char(round(nvl(sum(nvl(tt.sur_rent_p,0)),0)*100,2),'FM990.00')||'%' prj_remain_due_amount_percent
				  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,
				       hn_report_pkg.get_project_left_principal(p_project_id => pp.project_id,p_calc_date => to_date(${@calc_date},'YYYY-MM-DD')) sur_principal,
				       hn_report_pkg.get_project_left_principal_p(p_project_id => pp.project_id,p_calc_date => to_date(${@calc_date},'YYYY-MM-DD')) sur_principal_p,
				       hn_report_pkg.get_project_left_rent(p_project_id => pp.project_id,p_calc_date => to_date(${@calc_date},'YYYY-MM-DD')) sur_rent,
				       hn_report_pkg.get_project_left_rent_p(p_project_id => pp.project_id,p_calc_date => to_date(${@calc_date},'YYYY-MM-DD')) sur_rent_p,
				       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,
				       pp.bp_id_tenant,
				       (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'))))) tt 
				 where tt.dimension_type = 'BP_TENANT'
				   and ${@dimension}='BP_TENANT'
				  group by tt.bp_id_tenant 
				        
			            ) t1 #WHERE_CLAUSE#
			     ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="hn_industry_classification" queryExpression="t1.code=${@hn_industry_classification}"/>
        <bm:query-field name="binary_classification" queryExpression="t1.code_value=${@binary_classification}"/>
        <bm:query-field name="bp_name" queryExpression="t1.bp_name like &apos;%&apos;||${@bp_name}||&apos;%&apos;"/>
    </bm:query-fields>
</bm:model>
