<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                SELECT
                    t.contract_id,
                    t.contract_number,
                    (SELECT h.extra_nam
                     FROM hls_bp_master h
                     WHERE h.bp_id = t.bp_id_agent_level1)                 extra_nam,
                    (SELECT h.bp_name
                     FROM hls_bp_master h
                     WHERE h.bp_id = t.bp_id_tenant)                       tenant_name,
                    --租赁开始日
                    t.lease_start_date,
                    --机型
                    ccli.modelcd,
                    --机号
                    ccli.machine_number,
                    --回数
                    decode(ccc.cf_item, 8, '留购价格', ccc.times)              times,
                    --支付预定日
                    ccc.due_date,
                    --应收款金额
					--modify by 18938 I-7310
				     (case
					 when t.contract_number = 'SXJ21040016' and ccc.times = 36 and
						  ccc.cf_item = 200 then
					  1170220.13
					 else					
                    (ccc.due_amount -
                     nvl((SELECT SUM(cwo.write_off_due_amount)
                          FROM csh_transaction t, csh_write_off cwo
                          WHERE cwo.cashflow_id = ccc.cashflow_id
                                AND t.transaction_id = cwo.csh_transaction_id
                                AND nvl(cwo.reversed_flag, 'N') = 'N'
                                AND trunc(t.transaction_date) <
                                    trunc(to_date(${@start_calc_date}, 'yyyy-mm-dd'))),
                         0)) end)                                              net_amount,
                    --当月已收款金额
				    --modify by 18938 I-7310
				    (case
					 when t.contract_number = 'SXJ21040016' and ccc.times = 36 and
						  ccc.cf_item = 200 then
					  1170220.13
					 else					
                    nvl((SELECT SUM(cwo.write_off_due_amount)
                         FROM csh_transaction t, csh_write_off cwo
                         WHERE cwo.cashflow_id = ccc.cashflow_id
                               AND t.transaction_id = cwo.csh_transaction_id
                               AND nvl(cwo.reversed_flag, 'N') = 'N'
                               AND to_char(t.transaction_date, 'yyyy-mm') = ${@period_name}),
                        0) end )                                                 write_off_amount,
                    --剩余金额
				   --modify by 18938 I-7310
				   (case
					 when t.contract_number = 'SXJ21040016' and ccc.times = 36 and
						  ccc.cf_item = 200 then
					  0
					 else					
                    (ccc.due_amount -
                     nvl((SELECT SUM(cwo.write_off_due_amount)
                          FROM csh_transaction t, csh_write_off cwo
                          WHERE cwo.cashflow_id = ccc.cashflow_id
                                AND t.transaction_id = cwo.csh_transaction_id
                                AND nvl(cwo.reversed_flag, 'N') = 'N'
                                AND trunc(t.transaction_date) <=
                                    trunc(to_date(${@last_month_date}, 'yyyy-mm-dd'))),
                         0))  end)                                                unrecived_amount,
                    --逾期天数
                           --modify by 18938 I-7310
				   decode(t.contract_number , 'SXJ21040016',
						  0,					
                    (CASE
                     WHEN (ccc.due_amount -
                           nvl((SELECT SUM(cwo.write_off_due_amount)
                                FROM csh_transaction t, csh_write_off cwo
                                WHERE cwo.cashflow_id = ccc.cashflow_id
                                      AND t.transaction_id = cwo.csh_transaction_id
                                      AND nvl(cwo.reversed_flag, 'N') = 'N'
                                      AND trunc(t.transaction_date) <=
                                          trunc(to_date(${@last_month_date}, 'yyyy-mm-dd'))),
                               0)) > 0
                         THEN
                             floor(to_date(${@last_month_date}, 'yyyy-mm-dd') - ccc.due_date)
                     ELSE
                         0
                     END))                                                  overdue_days,
                    NVL((select sum((nvl(tt.transaction_amount, 0) - nvl(tt.write_off_amount, 0) -
                                     nvl(tt.returned_amount, 0)))
                         from csh_transaction tt
                         where tt.transaction_type = 'DEPOSIT'
                               AND tt.deposit_trans_type = 'rent_deposit'
                               AND NVL(tt.reversed_flag, 'N') = 'N'
                               and tt.ref_contract_id = T.contract_id), 0) deposit_amount,
                    t.is_buyout,
                    (Select v1.code_value_name
                     From sys_code_values_v v1
                     Where v1.code = 'YES_OR_NO'
                           And v1.code_enabled_flag = 'Y'
                           And v1.code_value_enabled_flag = 'Y'
                           and v1.CODE_VALUE = t.is_buyout)                is_buyout_n,
                          -- 应收违约金
                           CON_OVERDUE_PENALTY_PKG.CALC_PENALTY_FOR_WRITE_OFF(p_cashflow_id  => ccc.cashflow_id,
                                                                         p_calc_date    => decode(${@period_name},to_char(sysdate,'yyyy-mm'),sysdate,to_date(${@last_month_date}, 'yyyy-mm-dd')),
                                                                         p_user_id      => -1,
                                                                         p_penalty_flag => 'N') as net_penalty_amount,
                               --已收违约金
                               case ccc.cf_item
                                 when 1 then
                                  NVL((select sum(csh.write_off_due_amount)
                                        from csh_write_off csh
                                       where csh.cf_item = 9
                                         and csh.cf_type = 9
                                         AND csh.reversed_flag = 'N'
                                         and csh.contract_id = t.contract_id
                                         and csh.times = ccc.times
                                         and csh.period_name = ${@period_name}
                                         ),
                                      0)
                                 else
                                  0
                               end as write_of_penalty_amount,
                  t.large_balance,
                  (select v.code_value_name
                    from sys_code_values_v v
                   where v.code = 'SECONDARY_LEASE'
                     and v.code_value = t.large_balance) large_balance_n,
                     t.branch_code,
                   (Select v.code_value_name
                  From sys_code_values_v v
                 Where v.code = 'BRANCH_CODE_SZMP'
                   And v.code_enabled_flag = 'Y'
                   And v.code_value_enabled_flag = 'Y'
                   And v.code_value = t.branch_code) branch_code_n
                FROM con_contract_cashflow ccc,
                    con_contract t,
                    con_contract_lease_item ccli
                WHERE ccc.contract_id = t.contract_id
                      AND t.contract_id = ccli.contract_id
                      AND ccc.cf_item IN (1, 8, 200, 250)
                      AND ccc.cf_status = 'RELEASE'
                      AND t.data_class = 'NORMAL'
                      AND trunc(ccc.due_date) <=
                          trunc(to_date(${@last_month_date}, 'yyyy-mm-dd'))
                      AND ((trunc(t.lease_start_date) <=
                            trunc(to_date(${@last_month_date}, 'yyyy-mm-dd')) AND
                            t.contract_status in ('INCEPT','PENDING','ETING','REPURING','TERMINATING')) OR
                           (to_char(t.et_date, 'yyyy-mm') >= ${@period_name} AND
                            t.contract_status in ('TERMINATE','ET','REPUR')))
                      AND ccc.due_amount >
                          nvl((SELECT SUM(cwo.write_off_due_amount)
                               FROM csh_transaction t, csh_write_off cwo
                               WHERE cwo.cashflow_id = ccc.cashflow_id
                                     AND t.transaction_id = cwo.csh_transaction_id
                                     AND nvl(cwo.reversed_flag, 'N') = 'N'
                                     AND trunc(t.transaction_date) <
                                         trunc(to_date(${@start_calc_date}, 'yyyy-mm-dd'))),
                              0)

                      AND exists (select 1 from aut_trx_user_authorize a1, aut_owner_user_authorize
                      a2 WHERE a1.trx_category = 'CONTRACT'
                      and a1.trx_id = t.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 not exists (select 1
                        from exp_emp_assign_e_v ee, hls_bp_master h
                        where ee.user_id = ${/session/@user_id}
                        and ee.unit_id = h.unit_id
                        and h.bp_category = 'AGENT'
                        AND h.bp_code  in('D00000028', 'D00000035', 'D00000016',
                                         'D00000021', 'D00000039', 'D00000025',
                                         'D00000034', 'D00000017', 'D00000018',
                                         'D00000024', 'D00000032', 'D00000009',
                                         'D00000012', 'D00000014', 'D00000015', 'D00000023'))
            ]]>
            </bm:query-sql>
        </bm:operation>

    </bm:operations>
    <bm:query-fields>
        <!--        <bm:query-field name="period_name" queryExpression="t1.period_day=${@period_name}"/>-->
        <!--        <bm:query-field name="journal_date_to" queryExpression="to_char(t.journal_date,&apos;yyyy-mm-dd&apos;) &lt;= ${@journal_date_to}"/>-->
        <!--        <bm:query-field name="bp_id_agent_level1" queryExpression="t.bp_id_agent_level1=${@bp_id_agent_level1}"/>-->
    </bm:query-fields>
<!--    <bm:data-filters>-->
<!--        <bm:data-filter enforceOperations="query" expression="exists (select 1 from aut_trx_user_authorize a1, aut_owner_user_authorize a2 WHERE a1.trx_category = 'CONTRACT' and a1.trx_id = t.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)))"/>-->
<!--    </bm:data-filters>-->
</bm:model>
