<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" xmlns:f="leaf.database.features" alias="t">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
        SELECT
               rownum seq,
                    t.contract_lease_item_id,
                    t.contract_id,
                    (SELECT
                        cc.contract_number
                    FROM
                        con_contract cc
                    WHERE
                        cc.contract_id = t.contract_id
                    ) contract_number,
                    (SELECT
                        cc.contract_name
                    FROM
                        con_contract cc
                    WHERE
                        cc.contract_id = t.contract_id
                    ) contract_id_n,
                    t.lease_item_id,
                    t.enabled_flag,
                    (SELECT
                        h.description
                    FROM
                        hls_lease_item h
                    WHERE
                        h.lease_item_id = t.lease_item_id
                    ) lease_item_id_n,
                    (
                    SELECT
                        g.currency_name
                    FROM
                        gld_currency_vl g,
                        hls_lease_item t1
                    WHERE
                        t1.lease_item_id = t.lease_item_id AND
                        g.currency_code  = t1.currency
                    ) AS currency_n,
                    (
                    SELECT
                        cb.bp_id_n
                    FROM
                        con_contract_bp_lv cb
                    WHERE
                        cb.contract_id = t.contract_id AND
                        cb.BP_CATEGORY ='VENDER' AND
                        rownum         =1
                    ) vender_name,
            
                    (
                    SELECT
                        t1.price * NVL(t1.quantity, 1)
                    FROM
                        hls_lease_item t1
                    WHERE
                        t1.lease_item_id = t.lease_item_id
                    ) lease_item_amount,
                    t.serial_number,
                    t.pattern,
                    t.specification,
                    t.uom,
                    t.quantity,
                    t.currency,
                    t.price,
                    t.manufacturer_name,
                    t.manufacturing_date,
       
                    t.installation_site,
                    t.fixed_assets_site,
                    t.aircraft_category,
                    (SELECT
                        v.code_value_name
                    FROM
                        sys_code_values_v v
                    WHERE
                        v.code       = 'CAR_CATEGORY' AND
                        v.code_value =T.AIRCRAFT_CATEGORY
                    ) AS AIRCRAFT_CATEGORY_N,
                    t.engine,
                    t.ref_v05,
                    (SELECT
                        v.code_value_name
                    FROM
                        sys_code_values_v v
                    WHERE
                        v.code       = 'PRJ_LEASE_LICENCE_OWNER' AND
                        v.code_value =T.REF_V05
                    ) AS REF_V05_N,
                    t.brand,
                    t.series,
                    t.model,
                    t.configure,
                    t.model_id,
                    (SELECT cm.description FROM hls_car_model cm WHERE cm.model_id = t.model_id
                    ) model_id_n,
                    t.series_id,
                    (SELECT
                        hcs.description
                    FROM
                        hls_car_series hcs
                    WHERE
                        hcs.series_id = t.series_id
                    ) series_id_n,
                    t.brand_id,
                    (SELECT cb.description FROM hls_car_brand cb WHERE cb.brand_id = t.brand_id
                    ) brand_id_n,
                    t.LICENSE_PLATE_NUMBER,
                    t.full_name,
                    t.insure_type,
                    t.bp_id_insure,
                    (SELECT bp.bp_name FROM hls_bp_master bp WHERE bp.bp_id = t.bp_id_insure
                    ) bp_id_insure_n,
                    t.gps_flag,
                    t.lock_flag,
                    T.ESTIMATED_DELIVER_DATE,
                    t.BRAND_NAME,
                    t.LEASE_CATEGORY,
                    (SELECT
                        v.code_value_name
                    FROM
                        sys_code_values_v v
                    WHERE
                        v.code       = 'LEASE_CATEGORY_TYPE' AND
                        v.code_value =T.LEASE_CATEGORY
                    ) AS LEASE_CATEGORY_N,
                    t.GPS_STATUS,
                    (SELECT
                        v.code_value_name
                    FROM
                        sys_code_values_v v
                    WHERE
                        v.code       = 'CON543R_GPS_STATUS' AND
                        v.code_value =T.GPS_STATUS
                    ) AS GPS_STATUS_N,
                    t.trip,
                    t.lease_size,
                    t.mortgage_flag,
                    t.vender_id,
                    (SELECT hp.bp_name FROM hls_bp_master hp WHERE hp.bp_id = t.vender_id
                    ) bp_id_vender_n
                FROM
                    con_contract_lease_item t #WHERE_CLAUSE#
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="contract_id" queryExpression="contract_id = ${@contract_id}"/>
    </bm:query-fields>
</bm:model>
