<?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
                    pp.lease_organization,
                    (SELECT
                        description
                    FROM
                        hls_lease_organization
                    WHERE
                        lease_organization = pp.lease_organization AND
                        enabled_flag       = 'Y'
                    ) lease_organization_n,
                    pp.province,
                    (SELECT p.description FROM fnd_province p WHERE p.province_id = pp.province
                    ) AS province_id_n,
                    pp.project_number,
                    cc.contract_number,
                    cc.bp_id_tenant,
                    (SELECT bp_name FROM hls_bp_master WHERE bp_id = cc.bp_id_tenant
                    ) bp_id_tenant_n,
                    pp.lease_channel,
                    (SELECT
                        a.description
                    FROM
                        hls_lease_channel a
                    WHERE
                        a.lease_channel = pp.lease_channel
                    ) hls_lease_channel,
                    1 AS total_num,
                    cci.item_frame_number,
                    ck.apply_user_id,
                    (SELECT su.description FROM sys_user su WHERE su.user_id = ck.apply_user_id
                    ) apply_user_id_n,
                    ck.apply_date,
                    ck.assign_bp_id,
                    (SELECT bp.bp_name FROM hls_bp_master bp WHERE bp.bp_id = ck.assign_bp_id
                    ) assign_bp_id_n,
                    h.inbound_date,
                    1 AS inbound_num,
                    h.car_delivery_province,
                    h.car_delivery_city,
                    (SELECT
                        p.description
                    FROM
                        fnd_province p
                    WHERE
                        p.province_id = h.car_delivery_province
                    ) car_delivery_province_n,
                    (SELECT c.description FROM fnd_city c WHERE c.city_id = h.car_delivery_city
                    ) car_delivery_city_n,
                    h.custodian,
                    h.docking_user_id,
                    (SELECT su.description FROM sys_user su WHERE su.user_id = h.docking_user_id
                    ) docking_user_id_n,
                    h.deal_price,
                    (SELECT
                        approved_date
                    FROM
                        con_col_management_hd
                    WHERE
                        wfl_status    = 'APPROVED' AND
                        management_id = h.management_id
                    ) approved_date,
                    h.car_commission,
                    h.parking_fee_o,
                    (SELECT
                        prosecution_time
                    FROM
                        con_accounting_manage
                    WHERE
                        contract_id =
                        (SELECT
                            contract_id
                        FROM
                            con_contract_lease_item
                        WHERE
                            contract_lease_item_id = cci.contract_lease_item_id
                        )
                    ) prosecution_time,
                    (SELECT
                        legal_person
                    FROM
                        con_accounting_manage
                    WHERE
                        contract_id =
                        (SELECT
                            contract_id
                        FROM
                            con_contract_lease_item
                        WHERE
                            contract_lease_item_id = cci.contract_lease_item_id
                        )
                    ) legal_person,
                    (SELECT
                        code_value_name
                    FROM
                        sys_code_values_v
                    WHERE
                        code       = 'LEGAL_DOCUMENT_STATUS' AND
                        code_value =
                        (SELECT
                            document_status
                        FROM
                            con_accounting_manage
                        WHERE
                            contract_id =
                            (SELECT
                                contract_id
                            FROM
                                con_contract_lease_item
                            WHERE
                                contract_lease_item_id = cci.contract_lease_item_id
                            )
                        )
                    ) document_status_n,
                    (SELECT
                        litigation_fund
                    FROM
                        con_accounting_manage
                    WHERE
                        contract_id =
                        (SELECT
                            contract_id
                        FROM
                            con_contract_lease_item
                        WHERE
                            contract_lease_item_id = cci.contract_lease_item_id
                        )
                    ) litigation_fund,
                    (SELECT
                        litigation_fee
                    FROM
                        con_accounting_manage
                    WHERE
                        contract_id =
                        (SELECT
                            contract_id
                        FROM
                            con_contract_lease_item
                        WHERE
                            contract_lease_item_id = cci.contract_lease_item_id
                        )
                    ) litigation_fee,
                    pp.employee_id,
                    (SELECT Name FROM exp_employees WHERE employee_id = pp.employee_id
                    ) employee_id_n
                FROM
                    con_contract cc,
                    prj_project pp,
                    con_contract_lease_item cci,
                    con_col_management_hd h,
                    con_collection_callback ck
                WHERE
                    cc.project_id            = pp.project_id AND
                    cc.contract_id           = cci.contract_id AND
                    h.contract_lease_item_id = cci.contract_lease_item_id AND
                    h.callback_id            = ck.callback_id AND
                    h.real_callback_flag     = 'Y' AND
                    h.wfl_status            IS NOT NULL
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
