<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:fields>
        <bm:field name="change_req_number"/>
        <bm:field name="bp_agent_name"/>
        <bm:field name="extra_nam"/>
        <bm:field name="bp_name"/>
        <bm:field name="contract_number"/>
        <bm:field name="lease_channel"/>
        <bm:field name="lease_channel_n"/>
        <bm:field name="business_type"/>
        <bm:field name="business_type_n"/>
        <bm:field name="lease_execution_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="repayment_date_day" databaseType="NUMBER" datatype="java.lang.Long"/>
        <bm:field name="lease_times" databaseType="NUMBER" datatype="java.lang.Long"/>
        <bm:field name="delay_type_n"/>
        <bm:field name="start_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="end_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="req_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="delay_deal_time" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="delay_month_rent" databaseType="NUMBER" datatype="java.lang.Long"/>
        <bm:field name="req_status"/>
        <bm:field name="req_status_n"/>
        <bm:field name="due_amount" databaseType="NUMBER" datatype="java.lang.Long"/>
        <bm:field name="received_amount" databaseType="NUMBER" datatype="java.lang.Long"/>
        <bm:field name="created_by"/>
        <bm:field name="created_by_n"/>
        <bm:field name="contract_status"/>
        <bm:field name="contract_status_n"/>
    </bm:fields>
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
        select *
  from (select cr.change_req_number,
       (SELECT ma.bp_name
          FROM hls_bp_master ma
         WHERE ma.bp_id = cc.bp_id_agent_level1) bp_agent_name,
       (SELECT ma.extra_nam
          FROM hls_bp_master ma
         WHERE ma.bp_id = cc.bp_id_agent_level1) extra_nam,
       (SELECT ma.bp_name
          FROM hls_bp_master ma
         WHERE ma.bp_id = cc.bp_id_tenant) bp_name,
       cc.contract_number,
       (SELECT c.lease_channel
          FROM con_contract c
         WHERE c.contract_id = cc.contract_id) lease_channel,
       (Select ch.description
          from hls_lease_channel ch
         Where ch.lease_channel =
               (SELECT c.lease_channel
                  FROM con_contract c
                 WHERE c.contract_id = cc.contract_id)) lease_channel_n,
       (SELECT c.business_type
          FROM con_contract c
         WHERE c.contract_id = cc.contract_id) business_type,
       (select bt.description
          from hls_business_type bt
         where bt.business_type =
               (SELECT cc.business_type
                  FROM con_contract c
                 WHERE c.contract_id = cc.contract_id)) AS business_type_n,
       cc.lease_execution_date,
       cc.repayment_date_day,
       cc.lease_times,
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code_enabled_flag = 'Y'
           AND v.code_value_enabled_flag = 'Y'
           AND v.code = 'WINTER_DELAY_PRICE'
           AND v.code_value = cc.hd_user_col_v04) delay_type_n,
       (SELECT ccc.due_date
          FROM con_contract_cashflow ccc
         WHERE ccc.times = cc.ccr_start_times
           AND ccc.cf_item = 1
           AND ccc.contract_id = cc.contract_id
           AND ccc.cf_direction = 'INFLOW'
           AND ccc.cf_status = 'RELEASE') start_date,
       (SELECT add_months(ccc.due_date, cc.interim_times - 1)
          FROM con_contract_cashflow ccc
         WHERE ccc.times = cc.ccr_start_times
           AND ccc.cf_item = 1
           AND ccc.contract_id = cc.contract_id
           AND ccc.cf_direction = 'INFLOW'
           AND ccc.cf_status = 'RELEASE') end_date,
       cr.req_date,
       (select to_date(to_char(cccr.last_update_date,'yyyy-mm-dd'),'yyyy-mm-dd')
          from con_contract_case_record cccr
         where cccr.record_id = ccd.record_id
           and cccr.status = 'APPROVED') delay_deal_time,
       (SELECT ccc.due_amount
          FROM con_contract_cashflow ccc
         WHERE ccc.times = cc.ccr_start_times
           AND ccc.cf_item = 1
           AND ccc.contract_id = cc.contract_id
           AND ccc.cf_direction = 'INFLOW'
           AND ccc.cf_status = 'RELEASE') delay_month_rent,
       cr.req_status,
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code = 'CON_CHANGE_REQ_STATUS'
           AND v.code_value = cr.req_status) AS req_status_n,
       (SELECT nvl(sum(nvl(ccc.due_amount, 0)), 0)
          FROM con_contract_cashflow ccc
         WHERE ccc.cf_item = 304
           AND ccc.contract_id = cr.contract_id
           AND ccc.cf_direction = 'INFLOW'
           AND ccc.cf_status = 'RELEASE') due_amount,
       (SELECT nvl(sum(nvl(ccc.received_amount, 0)), 0)
          FROM con_contract_cashflow ccc
         WHERE ccc.cf_item = 304
           AND ccc.contract_id = cr.contract_id
           AND ccc.cf_direction = 'INFLOW'
           AND ccc.cf_status = 'RELEASE') received_amount,
       cc.created_by,
       (SELECT su.description
          FROM sys_user su
         WHERE su.user_id = cr.created_by) created_by_n,
         cc.contract_status,
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code = 'CON500_CONTRACT_STATUS'
           AND v.code_value = cc.contract_status) AS contract_status_n
  FROM con_contract             cc,
       con_contract_change_req  cr,
       con_contract_case_record ccd
 WHERE ccd.document_id = cr.change_req_id
   and cc.contract_id = cr.change_req_id
   and cc.data_class = 'CHANGE_REQ'
   and cr.document_type = 'CONEXTHAG'
   and ccd.document_table = 'CON_CONTRACT_CHANGE_REQ'
   and cc.contract_status not in ('CANCEL','CLOSED')
   and cr.req_status in ('APPROVING','APPROVED')
   UNION ALL
   select cr.change_req_number,
       (SELECT ma.bp_name
          FROM hls_bp_master ma
         WHERE ma.bp_id = cc.bp_id_agent_level1) bp_agent_name,
       (SELECT ma.extra_nam
          FROM hls_bp_master ma
         WHERE ma.bp_id = cc.bp_id_agent_level1) extra_nam,
       (SELECT ma.bp_name
          FROM hls_bp_master ma
         WHERE ma.bp_id = cc.bp_id_tenant) bp_name,
       cc.contract_number,
       (SELECT c.lease_channel
          FROM con_contract c
         WHERE c.contract_id = cc.contract_id) lease_channel,
       (Select ch.description
          from hls_lease_channel ch
         Where ch.lease_channel =
               (SELECT c.lease_channel
                  FROM con_contract c
                 WHERE c.contract_id = cc.contract_id)) lease_channel_n,
       (SELECT c.business_type
          FROM con_contract c
         WHERE c.contract_id = cc.contract_id) business_type,
       (select bt.description
          from hls_business_type bt
         where bt.business_type =
               (SELECT cc.business_type
                  FROM con_contract c
                 WHERE c.contract_id = cc.contract_id)) AS business_type_n,
       cc.lease_execution_date,
       cc.repayment_date_day,
       cc.lease_times,
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code_enabled_flag = 'Y'
           AND v.code_value_enabled_flag = 'Y'
           AND v.code = 'WINTER_DELAY_PRICE'
           AND v.code_value = cc.hd_user_col_v04) delay_type_n,
       (SELECT ccc.due_date
          FROM con_contract_cashflow ccc
         WHERE ccc.times = cc.ccr_start_times
           AND ccc.cf_item = 1
           AND ccc.contract_id = cc.contract_id
           AND ccc.cf_direction = 'INFLOW'
           AND ccc.cf_status = 'RELEASE') start_date,
       (SELECT add_months(ccc.due_date, cc.interim_times - 1)
          FROM con_contract_cashflow ccc
         WHERE ccc.times = cc.ccr_start_times
           AND ccc.cf_item = 1
           AND ccc.contract_id = cc.contract_id
           AND ccc.cf_direction = 'INFLOW'
           AND ccc.cf_status = 'RELEASE') end_date,
       cr.req_date,
       null delay_deal_time,
       (SELECT ccc.due_amount
          FROM con_contract_cashflow ccc
         WHERE ccc.times = cc.ccr_start_times
           AND ccc.cf_item = 1
           AND ccc.contract_id = cc.contract_id
           AND ccc.cf_direction = 'INFLOW'
           AND ccc.cf_status = 'RELEASE') delay_month_rent,
       cr.req_status,
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code = 'CON_CHANGE_REQ_STATUS'
           AND v.code_value = cr.req_status) AS req_status_n,
       (SELECT nvl(sum(nvl(ccc.due_amount, 0)), 0)
          FROM con_contract_cashflow ccc
         WHERE ccc.cf_item = 304
           AND ccc.contract_id = cr.contract_id
           AND ccc.cf_direction = 'INFLOW'
           AND ccc.cf_status = 'RELEASE') due_amount,
       (SELECT nvl(sum(nvl(ccc.received_amount, 0)), 0)
          FROM con_contract_cashflow ccc
         WHERE ccc.cf_item = 304
           AND ccc.contract_id = cr.contract_id
           AND ccc.cf_direction = 'INFLOW'
           AND ccc.cf_status = 'RELEASE') received_amount,
       cc.created_by,
       (SELECT su.description
          FROM sys_user su
         WHERE su.user_id = cr.created_by) created_by_n,
       cc.contract_status,
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code = 'CON500_CONTRACT_STATUS'
           AND v.code_value = cc.contract_status) AS contract_status_n
   FROM con_contract cc, con_contract_change_req cr
         WHERE cc.contract_id = cr.contract_id
           and cr.history_flag = 'Y'
           and cr.document_type = 'CONEXTHAG'
           and cc.contract_status not in ('CANCEL','CLOSED')
           and cr.req_status in ('APPROVING','APPROVED')) t1


                #WHERE_CLAUSE#
                #ORDER_BY_CLAUSE#
                ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field field="req_status" queryOperator="="/>
        <bm:query-field field="repayment_date_day" queryOperator="="/>
        <bm:query-field field="contract_number" queryOperator="like"/>
        <bm:query-field field="bp_agent_name" queryOperator="like"/>
        <bm:query-field field="extra_nam" queryOperator="like"/>
        <bm:query-field name="req_date_from" queryExpression="t1.req_date &gt;= to_date(${@req_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="req_date_to" queryExpression="t1.req_date &lt;= to_date(${@req_date_to},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="delay_deal_time_from" queryExpression="t1.delay_deal_time &gt;= to_date(${@delay_deal_time_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="delay_deal_time_to" queryExpression="t1.delay_deal_time &lt;= to_date(${@delay_deal_time_to},&apos;yyyy-mm-dd&apos;)"/>
    </bm:query-fields>
</bm:model>
