<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: Feng  
    $Date: 2013-9-23 下午8:16:18  
    $Revision: 1.0  
    $Purpose: 
-->
<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.*,
                (Select Min(cc1.TIMES)
                From con_contract_cashflow cc1
                Where cc1.contract_id = t1.contract_id
                And cc1.due_date <= Sysdate
                And cc1.cf_item In (1, 8, 9)
                and cc1.cf_status='RELEASE'
                And cc1.write_off_flag <> 'FULL'
                And cc1.due_date = t1.min_overdue_date) min_overdue_times
                FROM (SELECT
                cc.contract_id,
                cc.document_type,

                cc.document_category,
                --cc.contract_name,
                (SELECT pp.project_number
                FROM prj_project pp
                WHERE pp.project_id = cc.project_id)                                    project_number,
                cc.bp_id_tenant,
                to_char(cc.inception_of_lease, 'yyyy-mm-dd')                             inception_of_lease,
                (SELECT hb.bp_name
                FROM hls_bp_master hb, con_contract_bp ccb
                WHERE hb.bp_category = 'AGENT'
                AND ccb.bp_category = 'AGENT'
                AND hb.bp_id = ccb.bp_id
                AND ccb.contract_id = cc.contract_id)                             agent_id_n,
                (SELECT bp_name
                FROM hls_bp_master
                WHERE bp_id = cc.bp_id_tenant
                AND enabled_flag = 'Y')                                           bp_id_tenant_desc,
                cc.contract_number,
                cc.bp_id_agent_level1,
                (SELECT extra_nam
                FROM hls_bp_master
                WHERE bp_id = cc.bp_id_agent_level1
                AND enabled_flag = 'Y')                                           bp_id_agent_desc,
                cc.owner_user_id,
                (SELECT description
                FROM sys_user
                WHERE user_id = cc.owner_user_id)                                       owner_user_id_desc,
                cc.lease_organization,
                (SELECT description
                FROM hls_lease_organization
                WHERE lease_organization = cc.lease_organization
                AND enabled_flag =
                'Y')                                                          lease_organization_desc,
                (SELECT a.description value_name
                FROM hls_business_type_v a
                WHERE a.enabled_flag = 'Y'
                AND a.business_type = cc.business_type)                           business_type_n,
                cc.data_class,
                -- add by lihh  20210317 租赁物
                (SELECT  v.code_value_name
                FROM sys_code_values_v v
                where v.code = 'DIVISION'
                and v.code_value = cc.division)                                    division_n,
                cc.division,
                contract_item_query_pkg.calc_ac_overdue_times(p_contract_id => cc.contract_id,
                p_calc_date   => SYSDATE,
                p_user_id     => -1)       account_due_times,
                contract_item_query_pkg.calc_ac_overdue_days(p_contract_id => cc.contract_id,
                p_calc_date   => SYSDATE,
                p_user_id     => -1)        account_due_days,


                (SELECT SUM(ccc.due_amount)
                FROM con_contract_cashflow ccc
                WHERE ccc.contract_id = cc.contract_id
                AND ccc.cf_status = 'RELEASE'
                AND ccc.cf_item IN (1, 8, 250)
                AND trunc(ccc.due_date) <= trunc(SYSDATE)) -
                nvl((SELECT SUM(nvl(gwo.write_off_amount, 0))
                FROM gld_write_off gwo, con_contract_cashflow ccw
                WHERE gwo.reversed_flag = 'N'
                AND gwo.contract_id = cc.contract_id
                AND gwo.cf_item IN (1, 8, 250)
                and gwo.cf_item = ccw.cf_item
                and gwo.times = ccw.times
                and ccw.cf_status='RELEASE'
                and gwo.contract_id = ccw.contract_id
                AND trunc(ccw.due_date) <= trunc(SYSDATE)), 0)                account_due_amount,

                contract_item_query_pkg.calc_business_overdue_times(p_contract_id => cc.contract_id,
                p_calc_date   => SYSDATE,
                p_user_id     => -1) business_due_times,
                contract_item_query_pkg.calc_bussiness_overdue_days(p_contract_id => cc.contract_id,
                p_calc_date   => SYSDATE,
                p_user_id     => -1) business_due_days,

                (SELECT SUM(ccc.due_amount)
                FROM con_contract_cashflow ccc
                WHERE ccc.contract_id = cc.contract_id
                AND ccc.cf_status = 'RELEASE'
                AND ccc.cf_item IN (1, 8, 250)
                AND trunc(ccc.due_date) <= trunc(SYSDATE)) -
                nvl((SELECT SUM(nvl(cwo.write_off_due_amount, 0))
                FROM csh_write_off cwo
                WHERE cwo.reversed_flag = 'N'
                AND cwo.contract_id = cc.contract_id
                AND cwo.cf_item IN (1, 8, 250)
                And Exists
                (Select 1
                From con_contract_cashflow cn
                Where cn.cashflow_id = cwo.cashflow_id
                And cn.cf_status = 'RELEASE'
                And trunc(cn.due_date) <= trunc(Sysdate))),
                0)                                                                   business_due_amount,
                --营业逾期总金额

                (SELECT SUM(ccc.due_amount - nvl(ccc.received_amount, 0))
                FROM con_contract_cashflow ccc
                WHERE ccc.contract_id = cc.contract_id
                AND ccc.cf_item = 9
                AND ccc.write_off_flag <> 'FULL'
                and ccc.cf_status = 'RELEASE')                                    over_due_amount,
                --营业最早逾期的预定支付日

                (SELECT MIN(ccc.due_date)
                FROM con_contract_cashflow ccc
                WHERE ccc.contract_id = cc.contract_id
                AND ccc.due_amount > 0
                and ccc.cf_status = 'RELEASE'
                AND ccc.due_date <= sysdate
                AND ccc.cf_item in (1, 8, 9)
                AND ccc.write_off_flag <> 'FULL')                                 min_overdue_date,

                --营业已收租金
                (SELECT SUM(nvl(ccc.received_amount, 0))
                FROM con_contract_cashflow ccc
                WHERE ccc.contract_id = cc.contract_id
                and ccc.cf_status = 'RELEASE'
                AND ccc.cf_item IN
                (1, 8, 250))                                                  business_received_amount,
                --营业未收租金
                (SELECT SUM(ccc.due_amount - nvl(ccc.received_amount, 0))
                FROM con_contract_cashflow ccc
                WHERE ccc.contract_id = cc.contract_id
                and ccc.cf_status = 'RELEASE'
                AND ccc.cf_item IN (1, 8, 250)
                )                                                                        business_unreceived_amount,
                --会计已收租金
                nvl((SELECT SUM(nvl(gwo.write_off_amount, 0))
                FROM gld_write_off gwo
                WHERE gwo.reversed_flag = 'N'
                AND gwo.contract_id = cc.contract_id
                AND gwo.cf_item IN (1, 8, 250)),
                0)                                                                   ac_received_amount,
                --会计未收租金
                (SELECT SUM(ccc.due_amount)
                FROM con_contract_cashflow ccc
                WHERE ccc.contract_id = cc.contract_id
                AND ccc.cf_status = 'RELEASE'
                AND ccc.cf_item IN (1, 8, 250)
                ) -
                nvl((SELECT SUM(nvl(gwo.write_off_amount, 0))
                FROM gld_write_off gwo
                WHERE gwo.reversed_flag = 'N'
                AND gwo.contract_id = cc.contract_id
                AND gwo.cf_item IN (1, 8, 250)),
                0)                                                                   ac_unreceived_amount,
                --机型
                (SELECT ccli.modelcd
                FROM con_contract_lease_item ccli
                WHERE ccli.contract_id = cc.contract_id)                                modelcd,
                --机号
                (SELECT ccli.machine_number
                FROM con_contract_lease_item ccli
                WHERE ccli.contract_id = cc.contract_id)                                machine_number,
                to_char(cc.lease_start_date, 'yyyy-mm-dd')                               lease_start_date,
                cc.lease_times,
                --租金合计
                (SELECT SUM(ccc.due_amount)
                FROM con_contract_cashflow ccc
                WHERE ccc.contract_id = cc.contract_id
                AND ccc.cf_status = 'RELEASE'
                AND ccc.cf_item IN (1, 8, 250)
                )                                                                        total_due_amount,
                cc.contract_status,
                cc.BUSINESS_TYPE/*,
                (Select Count(1)
                From con_contract_cashflow ccc
                Where ccc.contract_id = cc.contract_id
                And ccc.cf_item = 1
                And ccc.cf_status = 'RELEASE'
                And ccc.cf_direction <> 'NONCASH'
                And nvl(ccc.overdue_status, 'N') = 'Y'
                And ccc.due_amount > nvl(ccc.received_amount, 0)) As business_due_times, -- 营业逾期期数
                (Select Max(ccc.overdue_max_days)
                From con_contract_cashflow ccc
                Where ccc.contract_id = cc.contract_id
                And ccc.cf_item = 1
                And ccc.cf_status = 'RELEASE'
                And ccc.cf_direction <> 'NONCASH'
                And nvl(ccc.overdue_status, 'N') = 'Y'
                And ccc.due_amount > nvl(ccc.received_amount, 0)) As business_due_days, -- 营业逾期天数

                (Select Count(1)
                From con_contract_cashflow ccc
                Where ccc.contract_id = cc.contract_id
                And ccc.cf_item = 1
                And ccc.cf_status = 'RELEASE'
                And ccc.cf_direction <> 'NONCASH'
                And nvl(ccc.overdue_status, 'N') = 'Y'
                And ccc.due_amount >
                nvl((Select Sum(nvl(gwo.write_off_amount, 0))
                From gld_write_off gwo
                Where gwo.reversed_flag = 'N'
                And gwo.contract_id = ccc.contract_id
                And gwo.times = ccc.times
                And gwo.cf_item = ccc.cf_item),
                0)) As account_due_times,  -- 会计逾期期数
                (Select floor(Sysdate - Min(ccc.due_date))
                From con_contract_cashflow ccc
                Where ccc.contract_id = cc.contract_id
                --modify by shen 不统计留购价
                And ccc.cf_item In (1)
                And ccc.cf_status = 'RELEASE'
                And trunc(ccc.due_date) <= trunc(Sysdate)
                And ccc.due_amount >
                nvl((Select Sum(nvl(gwo.write_off_amount, 0))
                From gld_write_off gwo
                Where gwo.reversed_flag = 'N'
                And gwo.contract_id = ccc.contract_id
                And gwo.times = ccc.times
                And gwo.cf_item = ccc.cf_item),
                0)) As account_due_days -- 会计逾期天数*/
                ,(select to_char(max(t.creation_date), 'yyyy-mm-dd')
                from cux_rent_dun_msg t
                where t.contract_id = cc.contract_id) dun_date,
                cc.send_message,
                (select v.code_value_name
                From sys_code_values_v v
                Where v.code = 'SEND_MESSAGE'
                And cc.send_message = v.code_value ) send_message_n,
                cc.cell_phone,
                cc.lease_item_amount,
                cc.contract_amount
                FROM con_contract cc
                WHERE
                EXISTS(SELECT 1
                FROM con_contract_cashflow ccw
                WHERE ccw.contract_id = cc.contract_id
                AND TRUNC(ccw.due_date) <= TRUNC(SYSDATE)
                AND ccw.due_amount > nvl(ccw.received_amount, 0)
                AND ccw.write_off_flag <> 'FULL'
                And ccw.overdue_status = 'Y'
                AND ccw.cf_status = 'RELEASE'
                AND ccw.cf_direction = 'INFLOW')
                AND ('Y' = 'Y' AND EXISTS
                (SELECT 1
                FROM aut_trx_user_authorize a1, aut_owner_user_authorize a2
                WHERE a1.trx_category = 'CONTRACT'
                AND a1.trx_id = cc.contract_id
                AND trunc(SYSDATE) BETWEEN a1.start_date AND
                nvl(a1.end_date, trunc(SYSDATE))
                AND a1.user_id = a2.owner_user_id
                AND a1.trx_category = a2.trx_category
                AND a2.authorized_user_id = ${/session/@user_id}
                AND trunc(SYSDATE) BETWEEN a2.start_date AND
                nvl(a2.end_date, trunc(SYSDATE))))
                And ((decode(${/session/@user_id}, 10600, 1, 2) = 1
                And exists(select 1
                from hls_bp_master hb
                where hb.bp_id = cc.bp_id_agent_level1
                And hb.bp_code in
                ('D00000028', 'D00000035', 'D00000016', 'D00000021', 'D00000039', 'D00000025', 'D00000034', 'D00000017', 'D00000018', 'D00000024', 'D00000032', 'D00000009', 'D00000012', 'D00000014', 'D00000015', 'D00000023')))
                or (decode(${/session/@user_id}, 10600, 1, 2) = 2)) and nvl(division,'-1') not IN ('70','92','95')
                union all
                SELECT
                cc.contract_id,
                cc.document_type,

                cc.document_category,
                --cc.contract_name,
                (SELECT pp.project_number
                FROM prj_project pp
                WHERE pp.project_id = cc.project_id)                                    project_number,
                cc.bp_id_tenant,
                to_char(cc.inception_of_lease, 'yyyy-mm-dd')                             inception_of_lease,
                (SELECT hb.bp_name
                FROM hls_bp_master hb, con_contract_bp ccb
                WHERE hb.bp_category = 'AGENT'
                AND ccb.bp_category = 'AGENT'
                AND hb.bp_id = ccb.bp_id
                AND ccb.contract_id = cc.contract_id)                             agent_id_n,
                (SELECT bp_name
                FROM hls_bp_master
                WHERE bp_id = cc.bp_id_tenant
                AND enabled_flag = 'Y')                                           bp_id_tenant_desc,
                cc.contract_number,
                cc.bp_id_agent_level1,
                (SELECT extra_nam
                FROM hls_bp_master
                WHERE bp_id = cc.bp_id_agent_level1
                AND enabled_flag = 'Y')                                           bp_id_agent_desc,
                cc.owner_user_id,
                (SELECT description
                FROM sys_user
                WHERE user_id = cc.owner_user_id)                                       owner_user_id_desc,
                cc.lease_organization,
                (SELECT description
                FROM hls_lease_organization
                WHERE lease_organization = cc.lease_organization
                AND enabled_flag =
                'Y')                                                          lease_organization_desc,
                (SELECT a.description value_name
                FROM hls_business_type_v a
                WHERE a.enabled_flag = 'Y'
                AND a.business_type = cc.business_type)                           business_type_n,
                cc.data_class,
                -- add by lihh  20210317 租赁物
                (SELECT  v.code_value_name
                FROM sys_code_values_v v
                where v.code = 'DIVISION'
                and v.code_value = cc.division)                                    division_n,
                cc.division,
                contract_item_query_pkg.calc_ac_overdue_times(p_contract_id => cc.contract_id,
                p_calc_date   => SYSDATE,
                p_user_id     => -1)       account_due_times,
                contract_item_query_pkg.calc_ac_overdue_days(p_contract_id => cc.contract_id,
                p_calc_date   => SYSDATE,
                p_user_id     => -1)        account_due_days,


                (SELECT SUM(ccc.due_amount)
                FROM con_contract_cashflow ccc
                WHERE ccc.contract_id = cc.contract_id
                AND ccc.cf_status = 'RELEASE'
                AND ccc.cf_item IN (1, 8, 250)
                AND trunc(ccc.due_date) <= trunc(SYSDATE)) -
                nvl((SELECT SUM(nvl(gwo.write_off_amount, 0))
                FROM gld_write_off gwo, con_contract_cashflow ccw
                WHERE gwo.reversed_flag = 'N'
                AND gwo.contract_id = cc.contract_id
                AND gwo.cf_item IN (1, 8, 250)
                and gwo.cf_item = ccw.cf_item
                and gwo.times = ccw.times
                and ccw.cf_status='RELEASE'
                and gwo.contract_id = ccw.contract_id
                AND trunc(ccw.due_date) <= trunc(SYSDATE)), 0)                account_due_amount,

                contract_item_query_pkg.calc_business_overdue_times(p_contract_id => cc.contract_id,
                p_calc_date   => SYSDATE,
                p_user_id     => -1) business_due_times,
                contract_item_query_pkg.calc_bussiness_overdue_days(p_contract_id => cc.contract_id,
                p_calc_date   => SYSDATE,
                p_user_id     => -1) business_due_days,

                (SELECT SUM(ccc.due_amount)
                FROM con_contract_cashflow ccc
                WHERE ccc.contract_id = cc.contract_id
                AND ccc.cf_status = 'RELEASE'
                AND ccc.cf_item IN (1, 8, 250)
                AND trunc(ccc.due_date) <= trunc(SYSDATE)) -
                nvl((SELECT SUM(nvl(cwo.write_off_due_amount, 0))
                FROM csh_write_off cwo
                WHERE cwo.reversed_flag = 'N'
                AND cwo.contract_id = cc.contract_id
                AND cwo.cf_item IN (1, 8, 250)
                And Exists
                (Select 1
                From con_contract_cashflow cn
                Where cn.cashflow_id = cwo.cashflow_id
                And cn.cf_status = 'RELEASE'
                And trunc(cn.due_date) <= trunc(Sysdate))),
                0)                                                                   business_due_amount,
                --营业逾期总金额

                (SELECT SUM(ccc.due_amount - nvl(ccc.received_amount, 0))
                FROM con_contract_cashflow ccc
                WHERE ccc.contract_id = cc.contract_id
                AND ccc.cf_item = 9
                AND ccc.write_off_flag <> 'FULL'
                and ccc.cf_status = 'RELEASE')                                    over_due_amount,
                --营业最早逾期的预定支付日

                (SELECT MIN(ccc.due_date)
                FROM con_contract_cashflow ccc
                WHERE ccc.contract_id = cc.contract_id
                AND ccc.due_amount > 0
                and ccc.cf_status = 'RELEASE'
                AND ccc.due_date <= sysdate
                AND ccc.cf_item in (1, 8, 9)
                AND ccc.write_off_flag <> 'FULL')                                 min_overdue_date,

                --营业已收租金
                (SELECT SUM(nvl(ccc.received_amount, 0))
                FROM con_contract_cashflow ccc
                WHERE ccc.contract_id = cc.contract_id
                and ccc.cf_status = 'RELEASE'
                AND ccc.cf_item IN
                (1, 8, 250))                                                  business_received_amount,
                --营业未收租金
                (SELECT SUM(ccc.due_amount - nvl(ccc.received_amount, 0))
                FROM con_contract_cashflow ccc
                WHERE ccc.contract_id = cc.contract_id
                and ccc.cf_status = 'RELEASE'
                AND ccc.cf_item IN (1, 8, 250)
                )                                                                        business_unreceived_amount,
                --会计已收租金
                nvl((SELECT SUM(nvl(gwo.write_off_amount, 0))
                FROM gld_write_off gwo
                WHERE gwo.reversed_flag = 'N'
                AND gwo.contract_id = cc.contract_id
                AND gwo.cf_item IN (1, 8, 250)),
                0)                                                                   ac_received_amount,
                --会计未收租金
                (SELECT SUM(ccc.due_amount)
                FROM con_contract_cashflow ccc
                WHERE ccc.contract_id = cc.contract_id
                AND ccc.cf_status = 'RELEASE'
                AND ccc.cf_item IN (1, 8, 250)
                ) -
                nvl((SELECT SUM(nvl(gwo.write_off_amount, 0))
                FROM gld_write_off gwo
                WHERE gwo.reversed_flag = 'N'
                AND gwo.contract_id = cc.contract_id
                AND gwo.cf_item IN (1, 8, 250)),
                0)                                                                   ac_unreceived_amount,
                --机型
                (SELECT ccli.modelcd
                FROM con_contract_lease_item ccli
                WHERE ccli.contract_id = cc.contract_id)                                modelcd,
                --机号
                (SELECT ccli.machine_number_70
                FROM con_contract_lease_item ccli
                WHERE ccli.contract_id = cc.contract_id)                              machine_number,
                to_char(cc.lease_start_date, 'yyyy-mm-dd')                               lease_start_date,
                cc.lease_times,
                --租金合计
                (SELECT SUM(ccc.due_amount)
                FROM con_contract_cashflow ccc
                WHERE ccc.contract_id = cc.contract_id
                AND ccc.cf_status = 'RELEASE'
                AND ccc.cf_item IN (1, 8, 250)
                )                                                                        total_due_amount,
                cc.contract_status,
                cc.BUSINESS_TYPE/*,
                (Select Count(1)
                From con_contract_cashflow ccc
                Where ccc.contract_id = cc.contract_id
                And ccc.cf_item = 1
                And ccc.cf_status = 'RELEASE'
                And ccc.cf_direction <> 'NONCASH'
                And nvl(ccc.overdue_status, 'N') = 'Y'
                And ccc.due_amount > nvl(ccc.received_amount, 0)) As business_due_times, -- 营业逾期期数
                (Select Max(ccc.overdue_max_days)
                From con_contract_cashflow ccc
                Where ccc.contract_id = cc.contract_id
                And ccc.cf_item = 1
                And ccc.cf_status = 'RELEASE'
                And ccc.cf_direction <> 'NONCASH'
                And nvl(ccc.overdue_status, 'N') = 'Y'
                And ccc.due_amount > nvl(ccc.received_amount, 0)) As business_due_days, -- 营业逾期天数

                (Select Count(1)
                From con_contract_cashflow ccc
                Where ccc.contract_id = cc.contract_id
                And ccc.cf_item = 1
                And ccc.cf_status = 'RELEASE'
                And ccc.cf_direction <> 'NONCASH'
                And nvl(ccc.overdue_status, 'N') = 'Y'
                And ccc.due_amount >
                nvl((Select Sum(nvl(gwo.write_off_amount, 0))
                From gld_write_off gwo
                Where gwo.reversed_flag = 'N'
                And gwo.contract_id = ccc.contract_id
                And gwo.times = ccc.times
                And gwo.cf_item = ccc.cf_item),
                0)) As account_due_times,  -- 会计逾期期数
                (Select floor(Sysdate - Min(ccc.due_date))
                From con_contract_cashflow ccc
                Where ccc.contract_id = cc.contract_id
                --modify by shen 不统计留购价
                And ccc.cf_item In (1)
                And ccc.cf_status = 'RELEASE'
                And trunc(ccc.due_date) <= trunc(Sysdate)
                And ccc.due_amount >
                nvl((Select Sum(nvl(gwo.write_off_amount, 0))
                From gld_write_off gwo
                Where gwo.reversed_flag = 'N'
                And gwo.contract_id = ccc.contract_id
                And gwo.times = ccc.times
                And gwo.cf_item = ccc.cf_item),
                0)) As account_due_days -- 会计逾期天数*/
                ,(select to_char(max(t.creation_date), 'yyyy-mm-dd')
                from cux_rent_dun_msg t
                where t.contract_id = cc.contract_id) dun_date,
                cc.send_message,
                (select v.code_value_name
                From sys_code_values_v v
                Where v.code = 'SEND_MESSAGE'
                And cc.send_message = v.code_value ) send_message_n,
                cc.cell_phone,
                cc.lease_item_amount,
                cc.contract_amount
                FROM con_contract cc
                WHERE
                EXISTS(SELECT 1
                FROM con_contract_cashflow ccw
                WHERE ccw.contract_id = cc.contract_id
                AND TRUNC(ccw.due_date) <= TRUNC(SYSDATE)
                AND ccw.due_amount > nvl(ccw.received_amount, 0)
                AND ccw.write_off_flag <> 'FULL'
                And ccw.overdue_status = 'Y'
                AND ccw.cf_status = 'RELEASE'
                AND ccw.cf_direction = 'INFLOW')
                AND ('Y' = 'Y' AND EXISTS
                (SELECT 1
                FROM aut_trx_user_authorize a1, aut_owner_user_authorize a2
                WHERE a1.trx_category = 'CONTRACT'
                AND a1.trx_id = cc.contract_id
                AND trunc(SYSDATE) BETWEEN a1.start_date AND
                nvl(a1.end_date, trunc(SYSDATE))
                AND a1.user_id = a2.owner_user_id
                AND a1.trx_category = a2.trx_category
                AND a2.authorized_user_id = ${/session/@user_id}
                AND trunc(SYSDATE) BETWEEN a2.start_date AND
                nvl(a2.end_date, trunc(SYSDATE))))
                And ((decode(${/session/@user_id}, 10600, 1, 2) = 1
                And exists(select 1
                from hls_bp_master hb
                where hb.bp_id = cc.bp_id_agent_level1
                And hb.bp_code in
                ('D00000028', 'D00000035', 'D00000016', 'D00000021', 'D00000039', 'D00000025', 'D00000034', 'D00000017', 'D00000018', 'D00000024', 'D00000032', 'D00000009', 'D00000012', 'D00000014', 'D00000015', 'D00000023')))
                or (decode(${/session/@user_id}, 10600, 1, 2) = 2))
                and nvl(division,'-1')  IN ('70','92')
          --经租设备专用查询
               union all
                SELECT
                cc.contract_id,
                cc.document_type,
                cc.document_category,
                --cc.contract_name,
                (SELECT pp.project_number
                FROM prj_project pp
                WHERE pp.project_id = cc.project_id)                                    project_number,
                cc.bp_id_tenant,
                to_char(cc.inception_of_lease, 'yyyy-mm-dd')                             inception_of_lease,
                (SELECT hb.bp_name
                FROM hls_bp_master hb, con_contract_bp ccb
                WHERE hb.bp_category = 'AGENT'
                AND ccb.bp_category = 'AGENT'
                AND hb.bp_id = ccb.bp_id
                AND ccb.contract_id = cc.contract_id)                             agent_id_n,
                (SELECT bp_name
                FROM hls_bp_master
                WHERE bp_id = cc.bp_id_tenant
                AND enabled_flag = 'Y')                                           bp_id_tenant_desc,
                cc.contract_number,
                cc.bp_id_agent_level1,
                (SELECT extra_nam
                FROM hls_bp_master
                WHERE bp_id = cc.bp_id_agent_level1
                AND enabled_flag = 'Y')                                           bp_id_agent_desc,
                cc.owner_user_id,
                (SELECT description
                FROM sys_user
                WHERE user_id = cc.owner_user_id)                                       owner_user_id_desc,
                cc.lease_organization,
                (SELECT description
                FROM hls_lease_organization
                WHERE lease_organization = cc.lease_organization
                AND enabled_flag =
                'Y')                                                          lease_organization_desc,
                (SELECT a.description value_name
                FROM hls_business_type_v a
                WHERE a.enabled_flag = 'Y'
                AND a.business_type = cc.business_type)                           business_type_n,
                cc.data_class,
                -- add by lihh  20210317 租赁物
                (SELECT  v.code_value_name
                FROM sys_code_values_v v
                where v.code = 'DIVISION'
                and v.code_value = cc.division)                                    division_n,
                cc.division,
                null as account_due_times,
                null as account_due_days,
                null as account_due_amount,
                contract_item_query_pkg.calc_business_overdue_times(p_contract_id => cc.contract_id,
                p_calc_date   => SYSDATE,
                p_user_id     => -1) business_due_times,
                contract_item_query_pkg.calc_bussiness_overdue_days(p_contract_id => cc.contract_id,
                p_calc_date   => SYSDATE,
                p_user_id     => -1) business_due_days,

                (SELECT SUM(ccc.due_amount)
                FROM con_contract_cashflow ccc
                WHERE ccc.contract_id = cc.contract_id
                AND ccc.cf_status = 'RELEASE'
                AND ccc.cf_item IN (1, 8, 250)
                AND trunc(ccc.due_date) <= trunc(SYSDATE)) -
                nvl((SELECT SUM(nvl(cwo.write_off_due_amount, 0))
                FROM csh_write_off cwo
                WHERE cwo.reversed_flag = 'N'
                AND cwo.contract_id = cc.contract_id
                AND cwo.cf_item IN (1, 8, 250)
                And Exists
                (Select 1
                From con_contract_cashflow cn
                Where cn.cashflow_id = cwo.cashflow_id
                And cn.cf_status = 'RELEASE'
                And trunc(cn.due_date) <= trunc(Sysdate))),
                0)                                                                   business_due_amount,
                --营业逾期总金额

                (SELECT SUM(ccc.due_amount - nvl(ccc.received_amount, 0))
                FROM con_contract_cashflow ccc
                WHERE ccc.contract_id = cc.contract_id
                AND ccc.cf_item = 9
                AND ccc.write_off_flag <> 'FULL'
                and ccc.cf_status = 'RELEASE')                                    over_due_amount,
                --营业最早逾期的预定支付日

                (SELECT MIN(ccc.due_date)
                FROM con_contract_cashflow ccc
                WHERE ccc.contract_id = cc.contract_id
                AND ccc.due_amount > 0
                and ccc.cf_status = 'RELEASE'
                AND ccc.due_date <= sysdate
                AND ccc.cf_item in (1, 8, 9)
                AND ccc.write_off_flag <> 'FULL')                                 min_overdue_date,

                --营业已收租金
                (SELECT SUM(nvl(ccc.received_amount, 0))
                FROM con_contract_cashflow ccc
                WHERE ccc.contract_id = cc.contract_id
                and ccc.cf_status = 'RELEASE'
                AND ccc.cf_item IN
                (1, 8, 250))                                                  business_received_amount,
                --营业未收租金
                (SELECT SUM(ccc.due_amount - nvl(ccc.received_amount, 0))
                FROM con_contract_cashflow ccc
                WHERE ccc.contract_id = cc.contract_id
                and ccc.cf_status = 'RELEASE'
                AND ccc.cf_item IN (1, 8, 250)
                )                                                                        business_unreceived_amount,
                --会计已收租金
                null as ac_received_amount,
                --会计未收租金
                 null as ac_unreceived_amount,
                --机型
                (SELECT ccli.modelcd
                FROM con_contract_lease_item ccli
                WHERE ccli.contract_id = cc.contract_id)                                modelcd,
                --机号
                (SELECT ccli.machine_number_70
                FROM con_contract_lease_item ccli
                WHERE ccli.contract_id = cc.contract_id)                              machine_number,
                to_char(cc.lease_start_date, 'yyyy-mm-dd')                               lease_start_date,
                cc.lease_times,
                --租金合计
                (SELECT SUM(ccc.due_amount)
                FROM con_contract_cashflow ccc
                WHERE ccc.contract_id = cc.contract_id
                AND ccc.cf_status = 'RELEASE'
                AND ccc.cf_item IN (1, 8, 250)
                )                                                                        total_due_amount,
                cc.contract_status,
                cc.BUSINESS_TYPE,
                (select to_char(max(t.creation_date), 'yyyy-mm-dd')
                from cux_rent_dun_msg t
                where t.contract_id = cc.contract_id) dun_date,
                cc.send_message,
                (select v.code_value_name
                From sys_code_values_v v
                Where v.code = 'SEND_MESSAGE'
                And cc.send_message = v.code_value ) send_message_n,
                cc.cell_phone,
                cc.lease_item_amount,
                cc.contract_amount
                FROM con_contract cc
                WHERE
                EXISTS(SELECT 1
                FROM con_contract_cashflow ccw
                WHERE ccw.contract_id = cc.contract_id
                AND TRUNC(ccw.due_date) <= TRUNC(SYSDATE)
                AND ccw.due_amount > nvl(ccw.received_amount, 0)
                AND ccw.write_off_flag <> 'FULL'
                And ccw.overdue_status = 'Y'
                AND ccw.cf_status = 'RELEASE'
                AND ccw.cf_direction = 'INFLOW')
                AND ('Y' = 'Y' AND EXISTS
                (SELECT 1
                FROM aut_trx_user_authorize a1, aut_owner_user_authorize a2
                WHERE a1.trx_category = 'CONTRACT'
                AND a1.trx_id = cc.contract_id
                AND trunc(SYSDATE) BETWEEN a1.start_date AND
                nvl(a1.end_date, trunc(SYSDATE))
                AND a1.user_id = a2.owner_user_id
                AND a1.trx_category = a2.trx_category
                AND a2.authorized_user_id = 1
                AND trunc(SYSDATE) BETWEEN a2.start_date AND
                nvl(a2.end_date, trunc(SYSDATE))))
                And ((decode(1, 10600, 1, 2) = 1
                And exists(select 1
                from hls_bp_master hb
                where hb.bp_id = cc.bp_id_agent_level1
                And hb.bp_code in
                ('D00000028', 'D00000035', 'D00000016', 'D00000021', 'D00000039', 'D00000025', 'D00000034', 'D00000017', 'D00000018', 'D00000024', 'D00000032', 'D00000009', 'D00000012', 'D00000014', 'D00000015', 'D00000023')))
                or (decode(${/session/@user_id}, 10600, 1, 2) = 2))
                and nvl(division,'-1')  IN ('95')) t1
                #WHERE_CLAUSE#
                ]]>
            </bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="contract_number"
                        queryExpression="contract_number like &apos;%&apos;|| ${@contract_number} ||&apos;%&apos;"/>
        <!--<bm:query-field name="bp_id_tenant" queryExpression="t1.bp_id_tenant = ${@bp_id_tenant}"/>-->
        <bm:query-field name="bp_id_tenant_desc" queryExpression="t1.bp_id_tenant_desc like &apos;%&apos;|| ${@bp_id_tenant_desc} ||&apos;%&apos;"/>
        <!--        <bm:query-field name="contract_name" queryExpression="t1.contract_name like ${@contract_name} "/>-->
        <bm:query-field name="project_number" queryExpression="t1.project_number like ${@project_number}"/>
        <bm:query-field name="bp_id_agent_desc" queryExpression="t1.bp_id_agent_desc like &apos;%&apos;|| ${@bp_id_agent_desc}||&apos;%&apos;"/>
        <!-- <bm:query-field name="lease_start_date_from" queryExpression="t1.inception_of_lease &gt;=to_date(${@lease_start_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="lease_start_date_to" queryExpression="t1.inception_of_lease &lt;=to_date(${@lease_start_date_to},&apos;yyyy-mm-dd&apos;)"/>
        -->
        <bm:query-field name="division" queryExpression="t1.division = ${@division}"/>
        <bm:query-field name="lease_start_date_from"
                        queryExpression="t1.inception_of_lease &gt;=${@lease_start_date_from}"/>
        <bm:query-field name="lease_start_date_to"
                        queryExpression="t1.inception_of_lease &lt;=${@lease_start_date_to}"/>
        <bm:query-field name="lease_times_from" queryExpression="t1.lease_times &gt;=${@lease_times_from}"/>
        <bm:query-field name="lease_times_to" queryExpression="t1.lease_times &lt;=${@lease_times_to}"/>
        <bm:query-field name="received_times_from" queryExpression="t1.received_times &gt;=${@received_times_from}"/>
        <bm:query-field name="received_times_to" queryExpression="t1.received_times &lt;=${@received_times_to}"/>
        <bm:query-field name="agent_id_n" queryExpression="t1.agent_id_n = ${@agent_id_n}"/>
        <bm:query-field name="promised_return_date_from"
                        queryExpression="t1.promised_return_date &gt;= to_date(${@promised_return_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="promised_return_date_to"
                        queryExpression="t1.promised_return_date &lt;= to_date(${@promised_return_date_to},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="account_due_times_from"
                        queryExpression="t1.account_due_times &gt;= ${@account_due_times_from}"/>
        <bm:query-field name="account_due_times_to"
                        queryExpression="t1.account_due_times &lt;= ${@account_due_times_to}"/>
        <bm:query-field name="account_due_days_from"
                        queryExpression="t1.account_due_days &gt;= ${@account_due_days_from}"/>
        <bm:query-field name="account_due_days_to" queryExpression="t1.account_due_days &lt;= ${@account_due_days_to}"/>
        <bm:query-field name="account_due_amount_from"
                        queryExpression="t1.account_due_amount &gt;= ${@account_due_amount_from}"/>
        <bm:query-field name="account_due_amount_to"
                        queryExpression="t1.account_due_amount &lt;= ${@account_due_amount_to}"/>
        <bm:query-field name="business_due_times_from"
                        queryExpression="t1.business_due_times &gt;= ${@business_due_times_from}"/>
        <bm:query-field name="business_due_times_to"
                        queryExpression="t1.business_due_times &lt;= ${@business_due_times_to}"/>
        <bm:query-field name="business_due_days_from"
                        queryExpression="t1.business_due_days &gt;= ${@business_due_days_from}"/>
        <bm:query-field name="business_due_days_to"
                        queryExpression="t1.business_due_days &lt;= ${@business_due_days_to}"/>
        <bm:query-field name="business_due_amount_from"
                        queryExpression="t1.business_due_amount &gt;= ${@business_due_amount_from}"/>
        <bm:query-field name="business_due_amount_to"
                        queryExpression="t1.business_due_amount &lt;= ${@business_due_amount_to}"/>
        <bm:query-field name="owner_user_id" queryExpression="t1.owner_user_id = ${@owner_user_id}"/>
        <bm:query-field name="lease_organization" queryExpression="t1.lease_organization = ${@lease_organization}"/>
        <bm:query-field name="business_type_n" queryExpression="t1.business_type = ${@business_type}"/>
        <bm:query-field name="send_message" queryExpression="t1.send_message = ${@send_message}"/>
    </bm:query-fields>
    <bm:data-filters>
        <bm:data-filter name="contract_status" enforceOperations="query"
                        expression="t1.contract_status in ( &apos;INCEPT&apos;, &apos;PENDING&apos;)"/>
        <!-- <bm:data-filter name="overdue_days" enforceOperations="query" expression="t1.overdue_days &gt; 0"/> -->
        <!-- <bm:data-filter enforceOperations="query" expression="exists (select 1 from con_contract_cashflow where contract_id = t1.contract_id and cf_type = 9 and due_amount != nvl(received_amount,0) and generated_source not in (&apos;CREATE&apos;))"/> -->
        <bm:data-filter enforceOperations="query" expression="t1.data_class = &apos;NORMAL&apos;"/>
        <!--<bm:data-filter enforceOperations="query" expression="exists (select 1 from con_contract where contract_id = t1.contract_id and created_by = ${/session/@user_id})"/>-->
    </bm:data-filters>
</bm:model>
