<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: weiwuzhen  
    $Date: 2016-5-8 下午3:05:58  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[SELECT  
           to_char(pbp.prj_bp_id) prj_bp_id,
            BP.bp_name, --承租人全称
BP.group_membership, --隶属集团公司 
BP.industry_big,
( select t.description value_name
   from hls_stat_class t
  where t.parent_class_id =
        (select TT.class_id
           from hls_stat_class tt
          where tt.class_code = 'INDUSTRY')
        AND T.class_id=BP.industry_big
)industry_big_n ,--所处行业
BP.business_license_num, --营业执照号码 
trim(to_char(BP.registered_capital,'999,999,999,990.99')) registered_capital, --注册资本
trim(to_char(BP.paid_up_capital,'999,999,999,990.99')) paid_up_capital, --实收资本
to_char(bp.setup_date,'yyyy')||'/'||to_char(bp.setup_date,'mm')||'/'||to_char(bp.setup_date,'dd') setup_date, --成立时间
bp.company_nature,
(select v.code_value_name as value_name 
from sys_code_values_v v where v.code = 'PRJ_NATURE_OF_BUSINESS' 
and v.code_enabled_flag = 'Y' and v.code_value_enabled_flag = 'Y'
and v.code_value=bp.company_nature) company_nature_n, --企业性质
bp.tax_registry_num , --税务登记号
bp.legal_person ,--法人代表
bp.actual_controller ,--实际控制人
BP.bussiness_scope ,-- 经营范围
bp.primary_business ,--主营业务
pbp.pro_tenant_main_product , --主导产品

pbp.special_industry, --特殊行业经营资质
(select he.pro_tenant_income from prj_bp_hist_evolution he where he.prj_bp_id=pbp.prj_bp_id
) pro_tenant_income, --收入（去年与预期）
(select he.pro_tenant_total_income from prj_bp_hist_evolution he where he.prj_bp_id=pbp.prj_bp_id
)*100||'%' pro_tenant_total_income, --毛利率
(select he.pro_tenant_net_income from prj_bp_hist_evolution he where he.prj_bp_id=pbp.prj_bp_id
)*100||'%' pro_tenant_net_income, --净利率
(select sc.code_value_name from prj_bp_hist_evolution he,sys_code_values_v sc
 where he.prj_bp_id=pbp.prj_bp_id
and he.PRO_TENANT_COMMERCIAL=sc.code_value
and sc.code='PRJ501N_PRO_TENANT_COMMERCIAL'
and sc.code_enabled_flag='Y'
) pro_tenant_commercial_n, --企业商业模式
(select sc.code_value_name from prj_bp_hist_evolution he,sys_code_values_v sc
 where he.prj_bp_id=pbp.prj_bp_id
and he.pro_tenant_event=sc.code_value
and sc.code='PRJ501N_PRO_TENANT_EVENT'
and sc.code_enabled_flag='Y'
) pro_tenant_event_n, --重大投资活动
nvl((select sum(nvl(bt.pro_tenant_bank_amount,0)) from prj_bp_transactor bt where bt.prj_bp_id=pbp.prj_bp_id
and bt.siv_flag='LOAN'),0)  pro_tenant_bank_amount, --银行借款
(select he.pro_tenant_credit from prj_bp_hist_evolution he where he.prj_bp_id=pbp.prj_bp_id
) pro_tenant_credit,--民间借贷、员工集资
pbp.pro_tenant_pro_introduction, --企业生产经营简要情况

pbp.pro_tenant_pro_plan ,--中长期发展规划
pbp.pro_redit_and_review ,--金融机构评定的信用等级
pbp.pro_redit_record,--近三年银行信用记录情况
pbp.policy_accord ,--公司治理与内部控制评价
pbp.pro_pro_industry,--行业基本情况
pbp.pro_main_business_degree, --主营业务上、下游及依赖程度
pbp.pro_main_business_status, --主导产品市场占有率及市场地位
pbp.pro_main_business_competitor, --主要竞争对手情况
pbp.pro_main_business_forecast ,--行业发展趋势预测
pbp.pro_main_products_sales ,--主导产品营销

pbp.pro_main_production ,--主导产品产销情况
pbp.corporate_tax, --企业税收
pbp.pro_related_transactions, --关联交易
pbp.remarks , --综述
pbp.pro_compensation_ability,--偿还租金能力综述
pbp.pro_sinking_analysis,--偿债能力
pbp.pro_assets_analysis,--资本结构
pbp.pro_debt_capacity,--借款情况分析
pbp.pro_quotation, --股价表现
pbp.pro_assets_analysis, --资产分析
pbp.pro_sinking_analysis, --负债分析
pbp.pro_profitability, --盈利能力
pbp.pro_cashflow , --现金流状况
to_char(pbc.query_date,'yyyy')||'/'||to_char(pbc.query_date,'mm')||'/'||to_char(pbc.query_date,'dd') query_date, --查询日期
pbc.foreign_guarantee_pen,--对外担保笔数
trim(to_char(pbc.foreign_guarantee_amount,'999,999,999,990.99')) foreign_guarantee_amount,--对外担保金额
pbc.outstanding_rmb_loan, --人民币贷款余额
pbc.outstanding_foreign_loan,--外币贷款余额
pbc.outstanding_trade_finance, --贸易融资
pbc.outstanding_factoring, --保理
pbc.outstanding_bill_discounting, --票据贴现
pbc.outstanding_bank_accpt_bill, --银行承兑汇票
pbc.outstanding_credit, --信用证
pbc.outstanding_guarantee, --保函
pbc.concerned_about_credit_info,--关注类信贷信息
pbc.bad_information_record, --是否有不良信用记录
pbc.performance_status, --为他人担保的履约情况
pbc.rating_agencies,  --评级机构
pbc.credit_rating, --信用等级
to_char(pbc.grade_time,'yyyy')||'/'||to_char(pbc.grade_time,'mm')||'/'||to_char(pbc.grade_time,'dd') grade_time, --评级时间
pbc.maximum_credit, --最高授信
pbp.organization_code,--组织机构代码
pbp.legal_person,--法人代表
pbp.actual_controller,--实际控制人
pbp.right_decision,--有权决策机构
pbc.capital_analysis,--还款来源分析
pbc.summary,--承租人偿还租金能力分析——综述
pbp.pro_tenant_review  --综述
 FROM PRJ_PROJECT_BP  PBP,HLS_BP_MASTER BP,prj_project_bp_credit pbc 
WHERE Pbp.bp_category='TENANT'
AND PBP.BP_TYPE='TENANT_SEC' 
AND PBP.BP_ID=BP.BP_ID
and pbp.prj_bp_id=pbc.prj_bp_id(+)
and pbp.project_id=${/parameter/@project_id}
]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
