<?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
                        ppb.bp_name, --承租人名称
                        ppb.bp_type,
                        (SELECT hbt.description FROM hls_bp_type hbt WHERE hbt.bp_type = ppb.bp_type
                        ) bp_role, --角色
                        ppb.bp_id,
                        (SELECT
                            hbmci.contact_person
                        FROM
                            hls_bp_master_contact_info hbmci
                        WHERE
                            hbmci.bp_id    = ppb.bp_id AND
                            hbmci.position = 'CP' AND
                            rownum      =1
                        ) contact_person_name, --联系人
                        (SELECT
                            hbmci.cell_phone
                        FROM
                            hls_bp_master_contact_info hbmci
                        WHERE
                            hbmci.bp_id    = ppb.bp_id AND
                            hbmci.position = 'CP' AND
                            rownum      =1
                        ) cell_phone, --联系人电话
                        (SELECT
                            hbmci.email
                        FROM
                            hls_bp_master_contact_info hbmci
                        WHERE
                            hbmci.bp_id    = ppb.bp_id AND
                            hbmci.position = 'CP' AND
                            rownum      =1
                        ) e_mail, --邮箱
                        (SELECT
                            hbmci.contact_person
                        FROM
                            hls_bp_master_contact_info hbmci
                        WHERE
                            hbmci.bp_id    = ppb.bp_id AND
                            hbmci.position = 'FM' AND
                            rownum      =1
                        ) financial_manager, --财务经理
                        (SELECT
                            hbmci.cell_phone
                        FROM
                            hls_bp_master_contact_info hbmci
                        WHERE
                            hbmci.bp_id    = ppb.bp_id AND
                            hbmci.position = 'FM' AND
                            rownum      =1
                        ) financial_manager_cell_phone, --财务经理联系电话
                        (SELECT
                            fp.description
                            || fc.description
                            || fd.description
                            || hbma.address
                        FROM
                            hls_bp_master_address hbma,
                            fnd_province fp,
                            fnd_city fc,
                            fnd_district fd
                        WHERE
                            hbma.bp_id        = ppb.bp_id AND
                            hbma.address_type = 'DOC_SENT_ADDRESS' AND
                            fp.province_id  = hbma.province_id AND
                            hbma.enabled_flag ='Y' AND
                            fc.city_id      = hbma.city_id AND
                            fd.district_id  = hbma.district_id
                        ) mail_address, --邮寄地址
                        (SELECT
                            hbma.zipcode
                        FROM
                            hls_bp_master_address hbma
                        WHERE
                            hbma.bp_id        = ppb.bp_id AND
                            hbma.address_type = 'DOC_SENT_ADDRESS' AND
                            hbma.enabled_flag ='Y'
                        ) zipcode --邮编
                    FROM
                        prj_project_bp ppb
                    WHERE
                        ppb.bp_category = 'GUARANTOR' AND
                        ppb.project_id  = ${@project_id}
                    ) t1 #WHERE_CLAUSE#
            ]]></bm:query-sql>
            <bm:parameters>
                <bm:parameter name="project_id"/>
            </bm:parameters>
        </bm:operation>
    </bm:operations>
</bm:model>
