<?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), 92, (select a.brand_value --update by 34291 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 FROM con_contract_lease_item i WHERE i.contract_id = t1.contract_id AND i.equipment_type = 'MAIN'),92,(SELECT i.machine_number_70 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 in (${@id_no},${@organization_id})) 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 '%'||${@contract_number}||'%'"/> </bm:query-fields> </bm:model>