<?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
                    cc.bp_id_tenant_n name,
                    t.item_frame_number standno,
                    DECODE(hbm.bp_class, 'ORG', hbm.phone_extra, hbm.phone) usertel,
                    DECODE(hbm.bp_class, 'ORG', hbm.organization_code, hbm.id_card_no) cardid,
                    DECODE(hbm.bp_class, 'ORG',
                    (SELECT
                        (SELECT
                            fp.description
                        FROM
                            fnd_province fp
                        WHERE
                            fp.province_id = cbp.work_province
                        )
                        ||
                        (SELECT fc.description FROM fnd_city fc WHERE fc.city_id = cbp.work_city
                        )
                        || cbp.work_unit_address
                    FROM
                        con_contract_bp cbp
                    WHERE
                        cbp.contract_id  = cc.contract_id AND
                        cbp.bp_id        = cc.bp_id_tenant AND
                        cbp.bp_category  = 'TENANT' AND
                        cbp.enabled_flag = 'Y' AND
                        rownum           = 1
                    ),
                    (SELECT
                        (SELECT
                            fp.description
                        FROM
                            fnd_province fp
                        WHERE
                            fp.province_id = cbp.liv_province
                        )
                        ||
                        (SELECT fc.description FROM fnd_city fc WHERE fc.city_id = cbp.liv_city
                        )
                        || cbp.living_address
                    FROM
                        con_contract_bp cbp
                    WHERE
                        cbp.contract_id  = cc.contract_id AND
                        cbp.bp_id        = cc.bp_id_tenant AND
                        cbp.bp_category  = 'TENANT' AND
                        cbp.enabled_flag = 'Y' AND
                        rownum           = 1
                    )) address,
                    '10380' companyid,
                    t.brand_id_n brandtype,
                    NVL(t.license_number,'NO_LICENSE_NUMBER') idnum,
                    t.item_engine_number engine,
                    DECODE(cc.division, '00', '0', '01', '1', '2') type,
                    DECODE(t.objective_buy_car, '10', '1', '20', '2', '30', '0', '40', '1', '3') usetype,
                    t.color_of_apprearance carcolour,
                    TO_CHAR(sysdate,'yyyy-mm-dd') startdate,
                    TO_CHAR(add_months(sysdate, 12 * cc.lease_term), 'yyyy-mm-dd') enddate,
                    TO_CHAR(acg.install_date, 'yyyy-mm-dd') fixtime,
                    TO_CHAR(t.car_old) carage,
                    acg.sn sn,
                    TO_CHAR(sysdate, 'yyyymmddhh24miss') TIMESTAMP,
                    sys_login_pkg.md5(sys_login_pkg.md5(${@token_key})
                    || TO_CHAR(sysdate, 'yyyymmddhh24miss')) SIGN,
                    acg.axb_url
                FROM
                    con_contract_lv cc,
                    con_contract_lease_item_lv t,
                    hls_bp_master hbm,
                    ast_car_gps acg
                WHERE
                    cc.contract_id  = t.contract_id AND
                    cc.bp_id_tenant = hbm.bp_id AND
                    cc.contract_id  = acg.contract_id AND
                    cc.contract_id  = ${@contract_id}
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
