<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: jack  
    $Date: 2014-6-3 上午11:41:02  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:parameters>
                <!-- <bm:parameter  name="fin_statement_templet_id" inputPath=""  input="true" dataType="java.lang.Long"   /> -->
                <!-- <bm:parameter  name="bp_id" input="true" inputPath=""  dataType="java.lang.Long"   /> --><![CDATA[
                
                
                
                
                
                
                
                
                
                
                
                
        
        
        ]]></bm:parameters>
            <bm:query-sql><![CDATA[select t1.*,rownum from (SELECT a.*
  FROM (SELECT DISTINCT b.fin_statement_templet_id,
                        b.bp_id,
                        a.bp_category,
                        A.BP_TYPE,
                        a.bp_class,
                        a.project_id,
                        a.founded_date,
                        a.registered_capital,
                        a.paid_up_capital,
                        (SELECT t.fiscal_year || '年' || t.fiscal_month || '月' fiscal_year
                           FROM rsc_fin_statement_prj_hds t
                          WHERE t.fin_statement_id = (SELECT rsc_fin_statement_prj_pkg.get_fin_statement_id(b.fin_statement_templet_id,
                                                                                                            ${/session/@company_id},
                                                                                                            b.bp_id,
                                                                                                            1)
                                                        FROM dual)) year_1,
                        (SELECT t.fiscal_year || '年' || t.fiscal_month || '月' fiscal_year
                           FROM rsc_fin_statement_prj_hds t
                          WHERE t.fin_statement_id = (SELECT rsc_fin_statement_prj_pkg.get_fin_statement_id(b.fin_statement_templet_id,
                                                                                                             ${/session/@company_id},
                                                                                                            b.bp_id,
                                                                                                            2)
                                                        FROM dual)) year_2,
                        (SELECT t.fiscal_year || '年' || t.fiscal_month || '月' fiscal_year
                           FROM rsc_fin_statement_prj_hds t
                          WHERE t.fin_statement_id = (SELECT rsc_fin_statement_prj_pkg.get_fin_statement_id(b.fin_statement_templet_id,
                                                                                                             ${/session/@company_id},
                                                                                                            b.bp_id,
                                                                                                            3)
                                                        FROM dual)) year_3,
                        (SELECT t.fiscal_year || '年' || t.fiscal_month || '月' fiscal_year
                           FROM rsc_fin_statement_prj_hds t
                          WHERE t.fin_statement_id = (SELECT rsc_fin_statement_prj_pkg.get_fin_statement_id(b.fin_statement_templet_id,
                                                                                                             ${/session/@company_id},
                                                                                                            b.bp_id,
                                                                                                            4)
                                                        FROM dual)) year_4,
                       
       c.legal_person,
       c.business_license_num,
       c.industry,
       c.organization_code,
       c.primary_business,
       c.loan_card_num,
       a.description tenant_description,
                        c.bp_name,
        (SELECT d.country_name || d.province_name || d.city_name || d.district_name || d.address
  FROM hls_bp_master_address_v d
 WHERE d.address_type = 'COMPANY_ADDRESS' and d.bp_id(+)=a.bp_id )       address,
                        e.code_value_name company_nature_name
          FROM prj_project_bp a,
               rsc_fin_statement_prj_hds b,
               hls_bp_master c,
               (SELECT d.code_value,
                       e.description_text code_value_name
                  FROM sys_codes        c,
                       sys_code_values  d,
                       fnd_descriptions e
                 WHERE c.enabled_flag = 'Y'
                       AND c.code = 'PRJ_NATURE_OF_BUSINESS'
                       AND c.code_id = d.code_id
                       AND e.description_id = d.code_value_name_id
                       AND e.language = 'ZHS') e
         WHERE a.bp_id = b.bp_id
               AND a.bp_id = c.bp_id
               AND c.company_nature = e.code_value(+)) a
 ORDER BY a.bp_id) t1 #WHERE_CLAUSE#

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