<?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 to_char(t.bp_birth, 'yyyy-mm-dd') bp_birth,
       t.BP_MARRIAGE,
       bp_marriage_n,
       t.BP_HEALTH,
       t.BP_HEALTH_n,
       t.BP_BORN,
       t.BP_BORN_n,
       t.PROPERTY_BELONG,
       t.PROPERTY_BELONG_n,
       t.OWN_PROPERTY_ADDRESS,
       t.HOUSE_AREA,
       t.HOUSE_VALUE,
       t.OWN_MONEY,
       t.BANK_CREDIT_APPROVAL,
       t.BANK_CREDIT_APPROVAL_n,
       t.OTHER_OWN_ASSETS,
       t.FAMILY_SOURCE_INCOME,
       t.FAMILY_AVG_INCOME,
       t.FAMILY_MONTH_AVG_OUTCOME,
       t.BANK_LOAN,
       t.OTHER_LOAN,
       t.FIXED_ASSETS,
       t.CAPITAL,
       t.CAPITAL_PLACE,
       t.CAR_TYPE,
       t.CAR_BRAND,
       t.CAR_NUM,
       t.CAR_BUY_DATE,
       t.CAR_BUY_MONEY,
       t.WORK_EXPERIENCE,
       t.WORK_EXPERIENCE_n,
       t.WORK_OPERATOR,
       t.WORK_OPERATOR_n,
       t.WORK_TYPE,
       t.WORK_TYPE_n,
       t.WORK_AVG_MONTH_INCOME,
       t.WORK_AVG_MONTH_OUTCOME,
       t.OTHER_DESCRIPTION,
       t.PAYMENT_SOURCE,
       t.E_PLACE,
       t.E_CONTRACT_AMOUNT,
       t.CONTRACT_COPY,
       t.CONTRACT_COPY_n,
       t.E_CON_AUTHENTICITY,
       t.E_CON_AUTHENTICITY_n,
       t.SURVEY_FIT,
       t.SURVEY_FIT_n,
       t.DAILY_CREDIT,
       t.DAILY_CREDIT_n,
       t.BORROW_SITUATION,
       t.BORROW_SITUATION_n,
       t.RENT_REPAYMENT,
       t.RENTAL_BALANCE,
       t.LIQUIDATED_DAMAGES,
       t.GUAR_MORTGAGE_STATUS,
       t.GUAR_MORTGAGE_STATUS_n,
       t.PAST_CONT_EXE_STATUS,
       t.PAST_CONT_EXE_STATUS_n,
       t.INDUSTRY_JUDGEMENT,
       t.INDUSTRY_JUDGEMENT_n,
       t.THIRD_PARTY_GUARANTEE,
       t.THIRD_PARTY_GUARANTEE_n,
       t.THIRD_PARTY_CREDIBILITY,
       t.THIRD_PARTY_CREDIBILITY_n,
       t.E_PLACE_n,
       t.BP_AGE,
       t.score_name,
       t.third_guar_score
  from (select a.*
          from (select t1.bp_birth,
                       t1.BP_MARRIAGE,
                       t1.bp_marriage_n,
                       t1.BP_HEALTH,
                       t1.BP_HEALTH_n,
                       t1.BP_BORN,
                       t1.BP_BORN_n,
                       t1.PROPERTY_BELONG,
                       t1.PROPERTY_BELONG_n,
                       t1.OWN_PROPERTY_ADDRESS,
                       t1.HOUSE_AREA,
                       t1.HOUSE_VALUE,
                       t1.OWN_MONEY,
                       t1.BANK_CREDIT_APPROVAL,
                       t1.BANK_CREDIT_APPROVAL_n,
                       t1.OTHER_OWN_ASSETS,
                       t1.FAMILY_SOURCE_INCOME,
                       t1.FAMILY_AVG_INCOME,
                       t1.FAMILY_MONTH_AVG_OUTCOME,
                       t1.BANK_LOAN,
                       t1.OTHER_LOAN,
                       t1.FIXED_ASSETS,
                       t1.CAPITAL,
                       t1.CAPITAL_PLACE,
                       t1.CAR_TYPE,
                       t1.CAR_BRAND,
                       t1.CAR_NUM,
                       t1.CAR_BUY_DATE,
                       t1.CAR_BUY_MONEY,
                       t1.WORK_EXPERIENCE,
                       t1.WORK_EXPERIENCE_n,
                       t1.WORK_OPERATOR,
                       t1.WORK_OPERATOR_n,
                       t1.WORK_TYPE,
                       t1.WORK_TYPE_n,
                       t1.WORK_AVG_MONTH_INCOME,
                       t1.WORK_AVG_MONTH_OUTCOME,
                       t1.OTHER_DESCRIPTION,
                       t1.PAYMENT_SOURCE,
                       t1.E_PLACE,
                       t1.E_CONTRACT_AMOUNT,
                       t1.CONTRACT_COPY,
                       t1.CONTRACT_COPY_n,
                       t1.E_CON_AUTHENTICITY,
                       t1.E_CON_AUTHENTICITY_n,
                       t1.SURVEY_FIT,
                       t1.SURVEY_FIT_n,
                       t1.DAILY_CREDIT,
                       t1.DAILY_CREDIT_n,
                       t1.BORROW_SITUATION,
                       t1.BORROW_SITUATION_n,
                       t1.RENT_REPAYMENT,
                       t1.RENTAL_BALANCE,
                       t1.LIQUIDATED_DAMAGES,
                       t1.GUAR_MORTGAGE_STATUS,
                       t1.GUAR_MORTGAGE_STATUS_n,
                       t1.PAST_CONT_EXE_STATUS,
                       t1.PAST_CONT_EXE_STATUS_n,
                       t1.INDUSTRY_JUDGEMENT,
                       t1.INDUSTRY_JUDGEMENT_n,
                       t1.THIRD_PARTY_GUARANTEE,
                       t1.THIRD_PARTY_GUARANTEE_n,
                       t1.THIRD_PARTY_CREDIBILITY,
                       t1.THIRD_PARTY_CREDIBILITY_n,
                       t1.E_PLACE_n,
                       t1.BP_AGE,
                       t1.score_name,
                       t1.third_guar_score,
                       t1.tj_score
                  from bp_tenant_rate_detail_v t1
                 where exists
                 (select 1
                          from prj_project p
                         where p.project_id = t1.PROJECT_ID
                           and p.bp_id_tenant = ${@bp_id}
                           and p.project_status not in ('CLOSED'))
                 order by t1.project_id desc) a
         where rownum = 1
        union
        SELECT h.date_of_birth bp_birth,
               h.marital_status bp_marriage,
               (SELECT code_value_name
                  FROM sys_code_values_v
                 WHERE code LIKE 'MARITAL_STATUS'
                   AND code_value_enabled_flag = 'Y'
                   AND code_value = h.marital_status) bp_marriage_n,
               null BP_HEALTH,
               null BP_HEALTH_n,

               null BP_BORN,
               null BP_BORN_n,
               null PROPERTY_BELONG,
               null PROPERTY_BELONG_n,
               null OWN_PROPERTY_ADDRESS,
               null HOUSE_AREA,
               null HOUSE_VALUE,
               null OWN_MONEY,
               null BANK_CREDIT_APPROVAL,
               null BANK_CREDIT_APPROVAL_n,
               null OTHER_OWN_ASSETS,
               null FAMILY_SOURCE_INCOME,
               null FAMILY_AVG_INCOME,
               null FAMILY_MONTH_AVG_OUTCOME,
               null BANK_LOAN,
               null OTHER_LOAN,
               null FIXED_ASSETS,
               null CAPITAL,
               null CAPITAL_PLACE,
               null CAR_TYPE,
               null CAR_BRAND,
               null CAR_NUM,
               null CAR_BUY_DATE,
               null CAR_BUY_MONEY,
               null WORK_EXPERIENCE,
               null WORK_EXPERIENCE_n,
               null WORK_OPERATOR,
               null WORK_OPERATOR_n,
               h.industry work_type,
               (SELECT code_value_name
                  FROM sys_code_values_v
                 WHERE code LIKE 'INDUSTRY_HL'
                   AND code_value_enabled_flag = 'Y'
                   AND code_value = h.industry) WORK_TYPE_n,
               null WORK_AVG_MONTH_INCOME,
               null WORK_AVG_MONTH_OUTCOME,
               null OTHER_DESCRIPTION,
               null PAYMENT_SOURCE,
               null E_PLACE,
               null E_CONTRACT_AMOUNT,
               null CONTRACT_COPY,
               null CONTRACT_COPY_n,
               null E_CON_AUTHENTICITY,
               null E_CON_AUTHENTICITY_n,
               null SURVEY_FIT,
               null SURVEY_FIT_n,
               null DAILY_CREDIT,
               null DAILY_CREDIT_n,
               null BORROW_SITUATION,
               null BORROW_SITUATION_n,
               null RENT_REPAYMENT,
               null RENTAL_BALANCE,
               null LIQUIDATED_DAMAGES,
               null GUAR_MORTGAGE_STATUS,
               null GUAR_MORTGAGE_STATUS_n,
               null PAST_CONT_EXE_STATUS,
               null PAST_CONT_EXE_STATUS_n,
               null INDUSTRY_JUDGEMENT,
               null INDUSTRY_JUDGEMENT_n,
               null THIRD_PARTY_GUARANTEE,
               null THIRD_PARTY_GUARANTEE_n,
               null THIRD_PARTY_CREDIBILITY,
               null THIRD_PARTY_CREDIBILITY_n,
               null E_PLACE_n,
               null BP_AGE,
               DECODE(h.bp_class, 'NP',h.bp_name, 'ORG', h.legal_person) score_name,
               null third_guar_score,
               null tj_score
          FROM hls_bp_master h
         where h.bp_id = ${@bp_id}) t
 where rownum = 1


]]></bm:query-sql>
        </bm:operation>
    </bm:operations>


</bm:model>
