<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: lpc 9874
    $Date: 2017/11/21 9:39:12
    $Revision: 1.0
    $Purpose:
-->
<bm:model xmlns:s="leaf.plugin.script" xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
             select    t2.equnm,
                       t2.brandname,
                       t2.equmdl,
                       t2.vinno,
                       t2.machineno,
                       t2.status
  from (select t1.*
          from (select li.LEASE_NAME equnm,
                       li.BRAND_VALUE brandname,
                       li.PATTERN equmdl,
                       nvl(li.machine_number, li.machine_number_70) vinno,
                       null machineno,
                       '1' status,
                       p.creation_date
                  from prj_project p, prj_project_lease_item li
                 where p.project_status not in ('CLOSED', 'CONTRACT_CREATED')
                   and p.project_id = li.project_id
                   and p.division != '95'
                   and (li.machine_number=${@machine_number} or li.machine_number_70=${@machine_number})
                union
                select li.LEASE_NAME equnm,
                       li.BRAND_VALUE brandname,
                       li.PATTERN equmdl,
                       nvl(li.machine_number, li.machine_number_70) vinno,
                       null machineno,
                       CASE
                         WHEN CC.CONTRACT_STATUS IN
                              ('REPUR', 'ET', 'TERMINATE') then
                          '3'
                         else
                          nvl((select '2'
                                from con_unearned_finance_income ci
                               where ci.contract_id = cc.contract_id
                                 and rownum = 1),
                              '1')
                       end status,
                       cc.inception_of_lease creation_date
                  from con_contract cc, prj_project_lease_item li
                 where cc.project_id = li.project_id
                   and cc.data_class = 'NORMAL'
                   and cc.contract_status not in
                       ('RESCIND', 'CANCEL', 'CLOSED')
                   and cc.division != '95'
                   and (li.machine_number=${@machine_number} or li.machine_number_70=${@machine_number})
                union
                select lil.ASSET_NAME equnm,
                       li.BRAND_VALUE brandname,
                       lil.SPECIFICATION equmdl,
                       lil.ASSET_NUM vinno,
                       null machineno,
                       '1' status,
                       p.creation_date
                  from prj_project                 p,
                       prj_project_lease_item      li,
                       prj_project_lease_item_list lil
                 where p.project_status not in ('CLOSED', 'CONTRACT_CREATED')
                   and p.project_id = li.project_id
                   and li.project_lease_item_id = lil.project_lease_item_id
                   and p.division != '95'
                   and lil.ASSET_NUM=${@machine_number}
                union
                select lil.ASSET_NAME equnm,
                       li.BRAND_VALUE brandname,
                       lil.SPECIFICATION equmdl,
                       lil.ASSET_NUM vinno,
                       null machineno,
                       CASE
                         WHEN CC.CONTRACT_STATUS IN
                              ('REPUR', 'ET', 'TERMINATE') then
                          '3'
                         else
                          nvl((select '2'
                                from con_unearned_finance_income ci
                               where ci.contract_id = cc.contract_id
                                 and rownum = 1),
                              '1')
                       end status,
                       cc.inception_of_lease creation_date
                  from con_contract                cc,
                       prj_project_lease_item      li,
                       prj_project_lease_item_list lil
                 where cc.project_id = li.project_id
                   and cc.data_class = 'NORMAL'
                   and cc.contract_status not in
                       ('RESCIND', 'CANCEL', 'CLOSED')
                   and li.project_lease_item_id = lil.project_lease_item_id
                   and cc.division != '95'
                   and lil.ASSET_NUM=${/@machine_number}
                   ) t1
         order by nvl(t1.creation_date, to_date('2000-01-01', 'yyyy-mm-dd')) desc) t2
 where rownum = 1


            ]]></bm:query-sql>
        </bm:operation>

    </bm:operations>
    <bm:fields>
        <bm:field name="equnm" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="brandname" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="equmdl" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="vinno" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="machineno" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="status" databaseType="VARCHAR2" datatype="java.lang.String"/>
    </bm:fields>
</bm:model>
