<?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.cashflow_id,
       t.transaction_number transaction_num,
       t.transaction_type,
       t.times,
       t.session_id,
       t.contract_id,
       t.cf_item_n,
       t.extra_name extra_nam,
       t.contract_number,
       t.machine_number,
       t.bp_code_tenant bp_id_tenant,
       t.bp_name,
       t.id_card_no,
       t.contract_amount total_amount,
       t.due_date,
         t.due_amount -
       (nvl((SELECT SUM(v.write_off_amount)
              FROM business_write_detail v
             WHERE v.cashflow_id = t.cashflow_id
               and v.session_id=${/session/@session_id}
               AND v.business_detail_id<t.business_detail_id),
            0)) due_amount,
       t.transaction_date,
       t.write_off_date,
       t.received_amount,
      (t.due_amount- (nvl((SELECT SUM(v.write_off_amount)
              FROM business_write_detail v
             WHERE v.cashflow_id = t.cashflow_id
               and v.session_id=${/session/@session_id}
               AND v.business_detail_id<t.business_detail_id),
            0))-t.write_off_amount)unreceived_amount,
       t.write_off_amount received_amount
  FROM business_write_detail t
  #WHERE_CLAUSE#
  order by extra_name, bp_code_tenant, contract_id,times,transaction_date,write_off_date
            ]]></bm:query-sql>
        </bm:operation>
        <bm:operation name="execute">
            <bm:update-sql><![CDATA[
                begin
                  contract_item_query_pkg.calc_business_write_report(p_user_id   => ${/session/@user_id},
                                                                    p_session_id => ${/session/@session_id});

                end;]]></bm:update-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="contract_number" queryExpression="t.contract_number like &apos;%&apos; || ${@contract_number} || &apos;%&apos;"/>
        <bm:query-field name="bp_name" queryExpression="t.bp_name like &apos;%&apos; || ${@bp_name} || &apos;%&apos;"/>
        <bm:query-field name="transaction_date_from" queryExpression="trunc(t.transaction_date) &gt;=  trunc(to_date(${@transaction_date_from},'yyyy-mm-dd'))"/>
        <bm:query-field name="transaction_date_to" queryExpression="trunc(t.transaction_date) &lt;=  trunc(to_date(${@transaction_date_to},'yyyy-mm-dd'))"/>
        <bm:query-field name="journal_date_from" queryExpression="trunc(t.write_off_date) &gt;=  trunc(to_date(${@journal_date_from},'yyyy-mm-dd'))"/>
        <bm:query-field name="journal_date_to" queryExpression="trunc(t.write_off_date) &lt;=  trunc(to_date(${@journal_date_to},'yyyy-mm-dd'))"/>
        <bm:query-field name="transaction_num" queryExpression="t.transaction_number like &apos;%&apos; || ${@transaction_num} || &apos;%&apos;"/>
        <bm:query-field name="cf_item_n" queryExpression="t.cf_item_n like &apos;%&apos; || ${@cf_item_n} || &apos;%&apos;"/>
        <bm:query-field name="extra_nam" queryExpression="t.extra_name like &apos;%&apos; || ${@extra_nam} || &apos;%&apos;"/>
    </bm:query-fields>
    <bm:data-filters>
        <bm:data-filter enforceOperations="query" expression="t.session_id=${/session/@session_id}"/>
        <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-filter enforceOperations="query" expression="( (decode(${/session/@user_id}, 10600, 1, 2) = 1
                                                   and exists (select 1 from con_contract c, hls_bp_master hb where c.contract_id = t.contract_id
                                                   and hb.bp_id = c.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) )"/>
    </bm:data-filters>
</bm:model>
