<?xml version="1.0" encoding="UTF-8"?>

<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                SELECT t1.*
                FROM (select pur_p.dcs_no,
                pur_p.BRAND_VALUE,
                pur_p.SPECIFICATION,
                pur_p.PATTERN,
                pur_p.UOM,
                pur_p.MODELCD,
                pur_p.EQUIPMENT_NUMBER,
                cc.search_term_1,
                to_char(pp.REF_D09, 'yyyy-mm-dd') ref_d09,
                to_char(pur_p.ACTUAL_PRICE, '9999999999999990.99') ACTUAL_PRICE,
                to_char(pur_p.ACTUAL_PRICE / (1 + cc.vat_rate),
                '9999999999999990.99') net_ACTUAL_PRICE,
                pur_p.ACCUMULATED_DEPRECIATION depreciation_month,
                to_char((select Q.BALLOON_RATIO * pur_p.ACTUAL_PRICE
                from prj_quotation q
                where q.document_id = ppi.source_project_id
                and q.document_category = 'PROJECT'),
                '9999999999999990.99') BALLOON,
                to_char(((SELECT book_value_end
                FROM (select hd.book_value_end, hd.asset_number
                from asset_depreciate_hd hd
                ORDER BY hd.depreciate_hd_id desc) a
                WHERE a.asset_number = pur_p.dcs_no
                and rownum = 1) * (1 + cc.vat_rate)),
                '9999999999999990.99') vat_book_value,
                to_char((SELECT book_value_end
                FROM (select hd.book_value_end, hd.asset_number
                from asset_depreciate_hd hd
                ORDER BY hd.depreciate_hd_id desc) a
                WHERE a.asset_number = pur_p.dcs_no
                and rownum = 1),
                '9999999999999990.99') book_value,
                to_char(((SELECT depreciate_sum_end
                FROM (select hd.depreciate_sum_end, hd.asset_number
                from asset_depreciate_hd hd
                ORDER BY hd.depreciate_hd_id desc) a
                WHERE a.asset_number = pur_p.dcs_no
                and rownum = 1) * (1 + cc.vat_rate)),
                '9999999999999990.99') vat_sim_depreciate_sum,
                to_char(((SELECT depreciate_sum_end
                FROM (select hd.depreciate_sum_end, hd.asset_number
                from asset_depreciate_hd hd
                ORDER BY hd.depreciate_hd_id desc) a
                WHERE a.asset_number = pur_p.dcs_no
                and rownum = 1)),
                '9999999999999990.99') simple_depreciate_sum,
                (select bp.bp_name
                from hls_bp_master bp
                where bp.bp_id = cc.bp_id_tenant) tenant_name,
                to_char(cc.CONTRACT_SIGN_DATE, 'yyyy-mm-dd') CONTRACT_SIGN_DATE,
                cc.contract_number,
                to_char(cc.inception_of_lease, 'yyyy-mm-dd') inception_of_lease,
                to_char((select MAX(ccc.due_date)
                from con_contract_cashflow ccc
                where ccc.cf_item = 1
                and ccc.contract_id = cc.contract_id
                and ccc.cf_direction = 'INFLOW'
                AND CCC.CF_STATUS = 'RELEASE'),
                'yyyy-mm-dd') max_due_date,
                cc.Lease_Times,
                (select COUNT(1)
                from con_contract_cashflow ccc
                where ccc.cf_item = 1
                and ccc.contract_id = cc.contract_id
                and ccc.write_off_flag = 'FULL'
                and ccc.cf_direction = 'INFLOW'
                AND CCC.CF_STATUS = 'RELEASE') WRITE_OFF_TIMES,
                (select COUNT(1)
                from con_contract_cashflow ccc
                where ccc.cf_item = 1
                and ccc.contract_id = cc.contract_id
                and ccc.write_off_flag != 'FULL'
                and ccc.cf_direction = 'INFLOW'
                AND CCC.CF_STATUS = 'RELEASE') UN_WRITE_OFF_TIMES,
                (select CCC.TIMES
                from con_contract_cashflow ccc
                where ccc.contract_id = cc.contract_id
                and ccc.cf_item = 1
                and ccc.due_date <
                trunc(add_months(to_date('2023-11-01', 'yyyy-mm-dd'),
                1),
                'MM')
                and ccc.due_date >=
                trunc(to_date('2023-11-01', 'yyyy-mm-dd'), 'MM')) current_month_time,
                (select COUNT(1)
                from con_contract_cashflow ccc
                where ccc.contract_id = cc.contract_id
                and ccc.cf_item = 1
                and ccc.write_off_flag != 'FULL'
                and nvl(ccc.due_amount, 0) > 0
                and trunc(ccc.due_date, 'MM') <
                trunc(add_months(to_date('2023-11-01', 'yyyy-mm-dd'),
                1),
                'MM')) become_due_time,
                to_char((select max(cwo.write_off_date)
                from con_contract_cashflow ccc, csh_write_off cwo
                where ccc.contract_id = cc.contract_id
                and ccc.cf_item = 1
                and cwo.cashflow_id = ccc.cashflow_id
                and trunc(ccc.due_date, 'MM') <
                trunc(add_months(to_date('2023-11-01',
                'yyyy-mm-dd'),
                1),
                'MM')),
                'yyyy-mm-dd') max_write_off_date,
                to_char(hlcm_op_asset_depre_pkg.get_con_rental(cc.contract_id,
                pur_p.project_lease_item_id),
                '9999999999999990.99') month_rental,
                to_char(hlcm_op_asset_depre_pkg.get_vat_con_rental(cc.contract_id,
                pur_p.project_lease_item_id),
                '9999999999999990.99') month_vat_rental,
                to_char(hlcm_op_asset_depre_pkg.get_con_sum_rental(cc.contract_id,
                pur_p.project_lease_item_id,
                'Y'),
                '9999999999999990.99') sum_received_amount,
                to_char(hlcm_op_asset_depre_pkg.get_vat_con_sum_rental(cc.contract_id,
                pur_p.project_lease_item_id,
                'Y'),
                '9999999999999990.99') sum_vat_received_amount,
                to_char(hlcm_op_asset_depre_pkg.get_con_sum_rental(cc.contract_id,
                pur_p.project_lease_item_id,
                'N'),
                '9999999999999990.99') sum_un_received_amount,
                to_char(hlcm_op_asset_depre_pkg.get_vat_con_sum_rental(cc.contract_id,
                pur_p.project_lease_item_id,
                'N'),
                '9999999999999990.99') sum_vat_un_received_amount,
                to_char((nvl((select COUNT(1)
                from con_contract_cashflow ccc
                where ccc.contract_id = cc.contract_id
                and ccc.cf_item = 1
                and ccc.write_off_flag != 'FULL'
                and nvl(ccc.due_amount, 0) > 0
                and trunc(ccc.due_date, 'MM') <
                trunc(add_months(to_date('2023-11-01',
                'yyyy-mm-dd'),
                1),
                'MM')),
                0) *
                hlcm_op_asset_depre_pkg.get_con_rental(cc.contract_id,
                pur_p.project_lease_item_id)),
                '9999999999999990.99') become_due_amount,
                to_char((nvl((select COUNT(1)
                from con_contract_cashflow ccc
                where ccc.contract_id = cc.contract_id
                and ccc.cf_item = 1
                and ccc.write_off_flag != 'FULL'
                and nvl(ccc.due_amount, 0) > 0
                and trunc(ccc.due_date, 'MM') <
                trunc(add_months(to_date('2023-11-01',
                'yyyy-mm-dd'),
                1),
                'MM')),
                0) *
                hlcm_op_asset_depre_pkg.get_vat_con_rental(cc.contract_id,
                pur_p.project_lease_item_id)),
                '9999999999999990.99') become_vat_due_amount,
                ahd.BALANCE_SHEET_ACCOUNT_CDE,
                ahd.BALANCE_SHEET_ACCOUNT_DES,
                ahd.ASSET_TYPE,
                ahd.asset_number,
                ahd.asset_desc,
                to_char(ahd.ACQUISITION_COST_HIS, '9999999999999990.99') ACQUISITION_COST_HIS,
                to_char(ahd.CAPITALIZATION_DATE, 'yyyy-mm-dd') CAPITALIZATION_DATE,
                ahd.USEFUL_LIFE,
                ahd.REMAINING_USEFUL_LIFE,
                ahd.DEPRECIATION_TYPE,
                to_char(ahd.ACQUISITION_COST_START, '9999999999999990.99') ACQUISITION_COST_START,
                to_char(ahd.DEPRECIATE_SUM_START, '9999999999999990.99') DEPRECIATE_SUM_START,
                to_char(ahd.WRITE_UP_SUM_START, '9999999999999990.99') WRITE_UP_SUM_START,
                to_char(ahd.BOOK_VALUE_START, '9999999999999990.99') BOOK_VALUE_START,
                to_char(ahd.CAPITALIZATION, '9999999999999990.99') CAPITALIZATION,
                to_char(ahd.ACQUISITION_COST_SCRAP, '9999999999999990.99') ACQUISITION_COST_SCRAP,
                to_char(ahd.BOOK_VALUE_SCRAP, '9999999999999990.99') BOOK_VALUE_SCRAP,
                to_char(ahd.ACQUISITION_COST_TRANSFER, '9999999999999990.99') ACQUISITION_COST_TRANSFER,
                to_char(ahd.BOOK_VALUE_TRANSFER, '9999999999999990.99') BOOK_VALUE_TRANSFER,
                to_char(ahd.WRITE_UP_SUM, '9999999999999990.99') WRITE_UP_SUM,
                to_char(ahd.SIMPLE_DEPRECIATE_SUM, '9999999999999990.99') SIMPLE_DEPRECIATE,
                to_char(ahd.ACQUISITION_COST_END, '9999999999999990.99') ACQUISITION_COST_END,
                to_char(ahd.BOOK_VALUE_END, '9999999999999990.99') BOOK_VALUE_END,
                to_char(ahd.DEPRECIATE_SUM_TO_ENDDATE, '9999999999999990.99') DEPRECIATE_SUM_TO_ENDDATE,
                to_char(ahd.DEPRECIATE_SUM_END, '9999999999999990.99') DEPRECIATE_SUM_END,
                ahd.dts
                from con_contract           cc,
                prj_project_lease_item ppi,
                prj_project            pp,
                prj_project_lease_item pur_p,
                asset_depreciate_hd    ahd
                where cc.business_type = 'LEASEOPERAT'
                and cc.data_class = 'NORMAL'
                and cc.project_id = ppi.project_id
                and ppi.source_project_id = pur_p.project_id
                and pp.project_id = ppi.source_project_id
                and pur_p.DCS_NO = AHD.ASSET_NUMBER(+)
                AND (ahd.depreciate_hd_id IS NULL OR
                ahd.depreciate_hd_id =
                (SELECT MAX(ahd_sub.depreciate_hd_id)
                FROM asset_depreciate_hd ahd_sub
                WHERE ahd_sub.ASSET_NUMBER = pur_p.DCS_NO))) t1
                #WHERE_CLAUSE#
                ]]>
            </bm:query-sql>
        </bm:operation>
    </bm:operations>

    <bm:query-fields>
        <bm:query-field name="query_time" queryExpression="t1.inception_of_lease &lt;= ${@query_time} "/>
        <bm:query-field name="query_time" queryExpression="t1.max_due_date &gt;= ${@query_time} "/>
        <bm:query-field name="dcs_no" queryExpression="dcs_no like ${@dcs_no}"/>
        <bm:query-field name="brand_value" queryExpression="brand_value like ${@brand_value}"/>
        <bm:query-field name="specification" queryExpression="specification like ${@specification}"/>
        <bm:query-field name="equipment_number" queryExpression="equipment_number like ${@equipment_number}"/>
        <bm:query-field name="search_term_1" queryExpression="search_term_1 like ${@search_term_1}"/>
        <bm:query-field name="ref_d09_from" queryExpression="t1.ref_d09 &gt;= ${@ref_d09_from} "/>
        <bm:query-field name="ref_d09_to" queryExpression="t1.ref_d09 &lt;= ${@ref_d09_to} "/>
        <bm:query-field name="tenant_name" queryExpression="tenant_name like ${@tenant_name}"/>
        <bm:query-field name="contract_number" queryExpression="contract_number like ${@contract_number}"/>
        <bm:query-field name="inception_of_lease_from"
                        queryExpression="t1.inception_of_lease &gt;= ${@inception_of_lease_from} "/>
        <bm:query-field name="inception_of_lease_to"
                        queryExpression="t1.inception_of_lease &lt;= ${@inception_of_lease_to} "/>
        <bm:query-field name="header_id" queryExpression="t1.header_id=${@header_id}"/>
    </bm:query-fields>


</bm:model>
