con_contract_query.lwm

<?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 *
  from (select t1.contract_id,
               t1.contract_number,
               t1.contract_status,
               t1.bp_id_tenant,
               (select hm.bp_name
                  from hls_bp_master hm
                 where hm.bp_id = t1.bp_id_tenant) bp_id_tenant_n,
               t1.ec_sign_type,
               decode(t1.ec_sign_type,
                      'INITIATE',
                      '待签约',
                      'SIGNING',
                      '待签约') AS ec_sign_type_n,
               (select hm.id_card_no
                  from hls_bp_master hm
                 where hm.bp_id = t1.bp_id_tenant) id_card_no_tenant,
               t1.bp_id_agent_level1,
               (select hm.bp_name
                  from hls_bp_master hm
                 where hm.bp_id = t1.bp_id_agent_level1) bp_id_agent_level1_n,
               (select hm.id_card_no_leg
                  from hls_bp_master hm
                 where hm.bp_id = t1.bp_id_agent_level1) id_card_no_agent,
               decode(t1.data_class,
                      'NORMAL',
                      '合同签约',
                      'CHANGE_REQ',
                      '提前结清') contract_status_n,
               (select ci.brand_id
                  from con_contract_lease_item ci
                 where ci.contract_id = t1.contract_id
                   and ci.equipment_type = 'MAIN') brand_id,
               decode(t1.division,70,(select a.brand_value          --update by xsh35973 20220323
                   from CON_CONTRACT_LEASE_ITEM a
                   where a.contract_id = t1.contract_id),NVL((SELECT a.description
                     FROM hls_car_brands_vl a
                    WHERE a.brand_id =
                          (select ci.brand_id
                             from con_contract_lease_item ci
                            where ci.contract_id = t1.contract_id
                              and ci.equipment_type = 'MAIN')),
                   '日立')) brand_id_n,
               (select ci.modelcd
                  from con_contract_lease_item ci
                 where ci.contract_id = t1.contract_id
                   and ci.equipment_type = 'MAIN') modelcd, --机型
               decode(t1.division,70,(SELECT i.machine_number_70        --update by xsh35973 20220323
                  FROM con_contract_lease_item i
                 WHERE i.contract_id = t1.contract_id
                   AND i.equipment_type = 'MAIN'),(SELECT i.machine_number
                  FROM con_contract_lease_item i
                 WHERE i.contract_id = t1.contract_id
                   AND i.equipment_type = 'MAIN')) machine_number, --机号
               t1.lease_times, --期数
               t1.lease_start_date, --起租日
               (NVL(t1.down_payment, 0) + NVL(t1.residual_value, 0) +
               NVL(t1.total_rental, 0)) total_sign,
               (SELECT cs.sign_url
                  from con_contract_ele_signer cs
                 where cs.contract_id = t1.contract_id
                   and ((${@account_type} = 'AG' and
                       (cs.id_no =
                       (select hm.social_code
                             from hls_bp_master hm, con_contract_bp cb
                            where hm.bp_id = cb.bp_id
                              and cb.contract_id = t1.contract_id
                              and cb.id_card_no_leg = ${@id_no}) or
                       cs.id_no =
                       (select hm.social_code
                             from hls_bp_master hm, hls_bp_master_signer hs
                            where hm.bp_id = hs.bp_id
                              and hm.bp_id = t1.bp_id_tenant
                              and hs.id_card = ${@id_no}))) or
                       (${@account_type} != 'AG' and cs.id_no = ${@id_no}))
                   and rownum = 1) sign_url,
               (SELECT nvl(cs.ec_sign_status, 'N')
                  from con_contract_ele_signer cs
                 where cs.contract_id = t1.contract_id
                   and cs.id_no = ${@id_no}
                   and cs.id_type = ${@id_type}
                   and rownum = 1) userSignStatus
          from con_contract t1
         where t1.data_class in ('NORMAL','CHANGE_REQ')
           and t1.ec_sign_type in ('INITIATE', 'SIGNING')
           and t1.sign_type = 'ELE_SIGN'
           and exists (select 1 from con_contract_ele_flow cf where cf.contract_id = t1.contract_id)
           and exists (select 1 from con_contract_ele_signer csr where csr.contract_id = t1.contract_id and csr.sign_url is not null and csr.id_no = ${@id_no})
           and ((${@account_type} = 'PE' and exists
                (select 1
                    from con_contract_bp ccb, con_contract_ele_signer cs
                   where ccb.bp_id = cs.bp_id
                     and ccb.contract_id = t1.contract_id
                     and cs.esign_type = decode(t1.data_class,'NORMAL','CE','CHANGE_REQ','ET')
                     and ((ccb.bp_category in ('TENANT', 'GUARANTOR') and t1.data_class = 'NORMAL')
                         or ccb.bp_category = 'TENANT' and t1.data_class = 'CHANGE_REQ')
                     and (ccb.id_card_no = ${@id_no} or
                         (ccb.id_no_sp = ${@id_no} and ccb.sp_sign_flag = 'Y')))) or
               (${@account_type} = 'AG' and exists
                (select 1
                    from hls_bp_master_signer hs
                   where hs.hls_bp_signer_id = t1.signer
                     and hs.id_card = ${@id_no})
             and exists (select 1 from hls_bp_master hbm
             where hbm.bp_id = t1.bp_id_tenant
             and hbm.social_code = ${@organization_id})
                 and exists (select 1 from con_contract_ele_signer cs
                  where cs.id_no = ${@id_no}
                  and cs.contract_id = t1.contract_id)) or
               (${@account_type} = 'AG' and exists
                (select 1
                    from hls_bp_master_signer hs,con_contract_change_req cr
                   where hs.hls_bp_signer_id = cr.signer
                     and cr.change_req_id = t1.contract_id
                     and hs.id_card = ${@id_no})
                 and exists (select 1 from hls_bp_master hbm
                 where hbm.bp_id = t1.bp_id_tenant
                 and hbm.social_code = ${@organization_id})
                 and exists (select 1 from con_contract_ele_signer cs
                  where cs.id_no = ${@id_no}
                  and cs.contract_id = t1.contract_id)
             ) or
       (${@account_type} = 'AG' and exists
        (select 1
            from con_contract_bp cb,hls_bp_master hbm
           where cb.contract_id = t1.contract_id
             and cb.bp_category = 'GUARANTOR'
             and cb.bp_class = 'ORG'
             and cb.bp_id = hbm.bp_id
             and hbm.social_code = ${@organization_id}
                     and cb.id_card_no_leg = ${@id_no})) or
               (${@account_type} = 'LP' and exists
                (select 1
                    from con_contract_bp cb, hls_bp_master hb
                   where cb.bp_id = hb.bp_id
                     and cb.contract_id = t1.contract_id
                     and hb.social_code = ${@id_no})))) v
                #WHERE_CLAUSE#
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="contract_id"/>
        <bm:field name="contract_number"/>
        <bm:field name="bp_id_tenant_n"/>
        <bm:field name="bp_id_agent_level1_n"/>
        <bm:field name="contract_status"/>
        <bm:field name="contract_status_n"/>
        <bm:field name="ec_sign_type"/>
        <bm:field name="ec_sign_type_n"/>
        <bm:field name="brand_id_n"/>
        <bm:field name="modelcd"/>
        <bm:field name="machine_number"/>
        <bm:field name="lease_times"/>
        <bm:field name="lease_start_date"/>
        <bm:field name="total_sign"/>
        <bm:field name="sign_url"/>
        <bm:field name="userSignStatus"/>
    </bm:fields>
    <bm:query-fields>
        <bm:query-field name="contract_number" queryExpression="v.contract_number like &apos;%&apos;||${@contract_number}||&apos;%&apos;"/>
    </bm:query-fields>
</bm:model>