<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: trd
    $Date: 2024-3-26 下午03:42:50
    $Revision: 1.0  
    $Purpose: 对账单查询
-->
<bm:model xmlns:s="leaf.plugin.script" xmlns:f="leaf.database.features"
          xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
          select * from(
                    select hb.batch_number,
					       hc.batch_id,
                           hc.policy_hd_id,
                           hc.bp_id,
                                      (select cc.contract_number
                                         from con_contract cc
                                        where cc.contract_id = hc.contract_id) contract_number,
                                      (select cc.contract_name
                                         from con_contract cc
                                        where cc.contract_id = hc.contract_id) contract_name,
                                      hc.bp_id bp_id_agent_level1,
                                      (select hbm.bp_name from hls_bp_master hbm where hbm.bp_id = hc.bp_id) bp_id_agent_level1_n,
                                       (select h.description
                                         from con_contract cc,hls_division h
                                        where cc.division= h.division and cc.contract_id = hc.contract_id) division_n,
                                       hc.finance_amount,
                                      (select to_char(cc.lease_start_date,'yyyy/mm/dd')
                                         from con_contract cc
                                        where cc.contract_id = hc.contract_id) lease_start_date,
                                      (select cc.business_type
                                       from con_contract cc
                                      where cc.contract_id = hc.contract_id) con_business_type,
                                       (select cc.business_type
                                             from con_contract cc,hls_division hd
                                            where cc.division= hd.division and cc.contract_id = hc.contract_id) con_business_type_n,
                                      hc.rental_re_times,
                                      nvl((select trunc(sysdate) - min(trunc(cc.due_date))
                                      from con_contract_cashflow cc
                                      where cc.contract_id = hc.contract_id
                                       and cc.cf_item = 1
                                       and cc.cf_direction = 'INFLOW'
                                       and cc.cf_status = 'RELEASE'
                                       and trunc(cc.due_date) < trunc(sysdate)
                                       and cc.write_off_flag != 'FULL'),
                                       0) overdue_max_days,
                                      round(hc.comsn_rate*100,2)||'%' comsn_rate,
                                      hc.comsn_amount,
                                      (select sum(i.unearned_finance_income)
                                                from con_unearned_finance_income i
                                               where i.contract_id = hc.contract_id) net_interest_amount,
                                      (select ccc.net_due_amount from con_contract_cashflow ccc where ccc.cashflow_id= hc.cashflow_id) net_comm_amount,
                                      hc.cashflow_id,
                                      hc.contract_id,
                                      (select hd.policy_name from hlcm_comsn_policy_hd hd where hd.policy_hd_id=hc.policy_hd_id) policy_name
                                         from hlcm_comsn_reconcilia_batch hb, hlcm_comsn_reconcilia hc
                                where hb.batch_id = hc.batch_id
                                and hb.wfl_status='APPROVED'
                                AND NOT EXISTS (
                                       SELECT 1
                                         FROM csh_payment_req_ln l,csh_payment_req_hd hd
                                         WHERE l.ref_doc_line_id=hc.cashflow_id
                                          AND l.ref_doc_category='CONTRACT'
                                          and l.payment_req_id=hd.payment_req_id
                                          and hd.approval_status in ('NEW','APPROVED','APPROVING','RETURN')
                                    )
                             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 = hc.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))))


                                ) t1
           #WHERE_CLAUSE#
		   order by t1.overdue_max_days desc


            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
  <bm:query-fields>
  <bm:query-field name="batch_number"
                  queryExpression="t1.batch_number like &apos;%&apos;||${@batch_number}||&apos;%&apos;"/>
  <bm:query-field name="contract_number"
                    queryExpression="t1.contract_number like &apos;%&apos;||${@contract_number}||&apos;%&apos;"/>
  <bm:query-field name="bp_id_agent_level1_n" queryExpression="t1.bp_id_agent_level1_n like &apos;%&apos;||${@bp_id_agent_level1_n}||&apos;%&apos;"/>
  <bm:query-field name="division_n" queryExpression="t1.division_n like &apos;%&apos;||${@division_n}||&apos;%&apos;"/>
  <bm:query-field name="policy_name" queryExpression="t1.policy_name=${@policy_name}"/>
  <bm:query-field name="overdue_max_days_f" queryExpression="t1.overdue_max_days &gt;= ${@overdue_max_days_f}"/>
  <bm:query-field name="overdue_max_days_t" queryExpression="t1.overdue_max_days &lt;= ${@overdue_max_days_t}"/>
  <bm:query-field name="lease_start_date_f" queryExpression="t1.lease_start_date &gt;= TO_CHAR(TO_DATE(${@lease_start_date_f}, 'YYYY-MM-DD'), 'YYYY/MM/DD')"/>
  <bm:query-field name="lease_start_date_t" queryExpression="t1.lease_start_date &lt;= TO_CHAR(TO_DATE(${@lease_start_date_t}, 'YYYY-MM-DD'), 'YYYY/MM/DD')"/>
  </bm:query-fields>
</bm:model>
