<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: jack  
    $Date: 2014-7-14 下午1:19:52  
    $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 t1.* from (SELECT b.mortgage_id,
             a.contract_id,
(SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code = 'HLS209_MORTGAGE_TYPE'
               AND v.code_value = b.mortgage_type
               AND v.code_enabled_flag = 'Y') mortgage_type_name,
       (SELECT v.code_value_name AS value_name
          FROM sys_code_values_v v
         WHERE v.code = 'HLS209_ASSET_MORTGAGE'
               AND v.code_enabled_flag = 'Y'
               AND v.code_value_enabled_flag = 'Y'
               AND v.code_value = b.mortgage_ast_classfication
        UNION ALL
        SELECT v.code_value_name AS value_name
          FROM sys_code_values_v v
         WHERE v.code = 'HLS209_ASSET_PLEDGE'
               AND v.code_enabled_flag = 'Y'
               AND v.code_value_enabled_flag = 'Y'
               AND v.code_value = b.mortgage_ast_classfication) mortgage_ast_classfication,
       (SELECT v.code_value_name AS value_name
          FROM sys_code_values_v v
         WHERE v.code = 'HLS209_ASSET_DETAIL_IMMOVABLE_PROPERTY_M'
               AND v.code_enabled_flag = 'Y'
               AND v.code_value_enabled_flag = 'Y'
               AND v.code_value = b.mortgage_asset_detail
        UNION ALL
        SELECT v.code_value_name AS value_name
          FROM sys_code_values_v v
         WHERE v.code = 'HLS209_ASSET_DETAIL_MOVABLE_PROPERTY_M'
               AND v.code_enabled_flag = 'Y'
               AND v.code_value_enabled_flag = 'Y'
               AND v.code_value = b.mortgage_asset_detail
        
        UNION ALL
        SELECT v.code_value_name AS value_name
          FROM sys_code_values_v v
         WHERE v.code = 'HLS209_ASSET_DETAIL_RIGHT_P'
               AND v.code_enabled_flag = 'Y'
               AND v.code_value_enabled_flag = 'Y'
               AND v.code_value = b.mortgage_asset_detail) mortgage_asset_detail,
       b.mortgage_name,
       (SELECT c.project_name FROM prj_project c WHERE c.project_id = b.project_id) project_name,
       (SELECT c.bp_name FROM hls_bp_master c WHERE c.bp_id = b.bp_id_mortgagor) mortgagor_name,
       (SELECT c.bp_name FROM hls_bp_master c WHERE c.bp_id = b.bp_id_evaluator) evaluator_name,
       b.location,
       to_char(b.value,'FM999,999,999,990.00') value_c,
        to_char(b.total_value,'FM999,999,999,990.00') total_value,
       b.start_date,
       b.end_date,
       b.ownership,
       b.occupied,
       b.estate_license_code,
       decode(b.housing_area,'','',b.housing_area||(SELECT 
       u.description_text AS value_name
  FROM fnd_uom_codes_vl u where u.uom_code=b.uom)) housing_area,
       b.use,
       b.construction_date,
       b.building_structure,
       b.appraise_date,
       (select c.description_mortgage from prj_project c where c.project_id=b.project_id) description_mortgage
  FROM con_contract         a,
       prj_project_mortgage b
 WHERE a.project_id = b.project_id ) t1 
 #WHERE_CLAUSE#
]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
