jyc_equno_query.lwm

<?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,
                       cc.sign_contract_status sign_contract_status,
                       null machineno,
                       CASE
                         WHEN CC.CONTRACT_STATUS = 'NEW' and
                           cc.sign_contract_status in ('SIGN','SIGNING') then
                          '2'
                         else '2' end status,
                       cc.creation_date 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 ('TERMINATE','ET','REPUR','CANCEL','TERMINATING')
                   and cc.sign_contract_status not in ('NEW','SIGN_RETURN')
                   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,
                       cc.sign_contract_status sign_contract_status,
                       null machineno,
                       CASE
                         WHEN CC.CONTRACT_STATUS = 'NEW' and
                           cc.sign_contract_status in ('SIGN','SIGNING') then
                          '2'
                         else '2' end status,
                       cc.creation_date 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 ('TERMINATE','ET','REPUR','CANCEL','TERMINATING')
                   and cc.sign_contract_status not in ('NEW','SIGN_RETURN')
                   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>