<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:s="leaf.plugin.script" xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
            select *
  from (select  t1.contract_id,
                to_char(t1.lease_execution_date, 'yyyy/mm') lease_execution_year,
               (Select ch.lease_channel
                  from hls_lease_channel ch
                 Where ch.lease_channel =
                       (select cc.lease_channel
                          from con_contract cc
                         where cc.contract_id = t1.contract_id)) As lease_channel,
               (Select ch.description
                  from hls_lease_channel ch
                 Where ch.lease_channel =
                       (select cc.lease_channel
                          from con_contract cc
                         where cc.contract_id = t1.contract_id)) As lease_channel_desc,
               t1.business_type,
               (select bt.description
                  from hls_business_type bt
                 where bt.business_type = t1.business_type) AS business_type_n,
               (select h.large_area
                  from hls_bp_master h
                 where h.bp_id = t1.bp_id_tenant) large_area,
               (select h.bp_name
                  from hls_bp_master h
                 where h.bp_id = t1.bp_id_agent_level1) bp_agent_name,
               (select h.extra_nam
                  from hls_bp_master h
                 where h.bp_id = t1.bp_id_agent_level1) extra_nam,
               (select to_char(p.submit_date,'yyyy/mm/dd')
                  from prj_project p
                 where p.project_id = t1.project_id) submit_date,
               to_char(t1.lease_execution_date,'yyyy/mm/dd') lease_execution_date,
               t1.contract_number,
               t1.contract_status,
               (select v.code_value_name
                  from sys_code_values_v v
                 where v.code = 'CON500_CONTRACT_STATUS'
                   and v.code_value = t1.contract_status) AS contract_status_n,
               (select h.bp_name
                  from hls_bp_master h
                 where h.bp_id = t1.bp_id_tenant) bp_name,
               to_number(t1.sys_level) sys_level,
               decode(t1.division, 10, 00, t1.division) division,
               (SELECT hd.description
                  FROM hls_division hd
                 WHERE hd.division = t1.division) AS division_n,
               (select i.pattern
                  from con_contract_lease_item i
                 WHERE i.contract_id = t1.contract_id
                   AND i.equipment_type = 'MAIN') pattern,
               (select i.pattern
                  from con_contract_lease_item i
                 WHERE i.contract_id = t1.contract_id
                   AND i.equipment_type = 'ACCESSORY'
                   and rownum = 1) accessory_pattern,
               (select i.quantity
                  from con_contract_lease_item i
                 WHERE i.contract_id = t1.contract_id
                   AND i.equipment_type = 'MAIN') quantity,
               to_char(t1.first_pay_date,'yyyy/mm/dd') first_pay_date,
               t1.lease_term_month,
               to_number(to_char(round(nvl(t1.int_rate, 0) * 100, 2), 'FM99990.00')) int_rate,
               t1.annual_pay_times,
               (select v.code_value_name
                  from sys_code_values_v v
                 where v.code = 'HLS500_ANNUAL_PAY_TIMES'
                   and v.code_value = t1.annual_pay_times) annual_pay_times_n,
               t1.deduct_bank,
               (SELECT decode(scv.code_value_name,
                              'N',
                              '无',
                              scv.code_value_name)
                  FROM sys_code_values_v scv
                 WHERE scv.code = 'DEDUCT_BANK'
                   AND scv.code_value = t1.deduct_bank) deduct_bank_n,
               (select i.machine_number
                  from con_contract_lease_item i
                 WHERE i.contract_id = t1.contract_id
                   AND i.equipment_type = 'MAIN') machine_number,
               (select i.machine_number
                  from con_contract_lease_item i
                 WHERE i.contract_id = t1.contract_id
                   AND i.equipment_type = 'ACCESSORY'
                   and rownum = 1) accessory_machine_number,
               t1.sales_method,
               (Select v.code_value_name As value_name
                  From sys_code_values_v v
                 Where v.code = 'DS_SALES_METHOD'
                   And t1.sales_method = v.code_value) sales_method_n,
               t1.as_item_source,
               t1.special_policy,
               t1.product_name,
               t1.sub_price_list_name,
               t1.lease_item_amount,
               t1.lease_item_amount lease_item_amount2,
               to_number(to_char(round(nvl(t1.down_payment_ratio, 0) * 100, 2),
                       'FM99990.00'))  down_payment_ratio,
               t1.down_payment,
               t1.finance_amount,
               to_number(t1.score_level)score_level,
               (select nvl(sum(nvl(ccc.due_amount, 0)), 0)
                  from con_contract_cashflow ccc
                 where ccc.contract_id = t1.contract_id
                   and ccc.cf_item = 3
                   and ccc.cf_status = 'RELEASE'
                   and ccc.cf_direction = 'INFLOW') tenant_service_charge,
               decode((Select bp.direct_sales_agent_flag
                        From hls_bp_master bp
                       Where bp.bp_id = t1.bp_id_agent_level1),
                      'N',
                      t1.lease_mgt_fee,
                      NULL,
                      t1.lease_mgt_fee,
                      0) agent_fee,
               (select nvl(sum(nvl(ccc.due_amount, 0)), 0)
                  from con_contract_cashflow ccc
                 where ccc.contract_id = t1.contract_id
                   and ccc.cf_item = 508
                   and ccc.cf_status = 'RELEASE'
                   and ccc.cf_direction = 'INFLOW') agent_bond,
               (select nvl(sum(nvl(ccc.due_amount, 0)), 0)
                  from con_contract_cashflow ccc
                 where ccc.contract_id = t1.contract_id
                   and ccc.cf_item = 51
                   and ccc.cf_status = 'RELEASE'
                   and ccc.cf_direction = 'INFLOW') tenant_bond,
               to_char(t1.contract_inception_date,'yyyy/mm/dd') contract_inception_date,
               t1.area_distinguish,
               t1.hd_user_col_v05,
               (SELECT a.code_value_name
                  FROM Sys_Code_Values_v a
                 where a.code = 'YES_NO'
                   and a.code_value = t1.hd_user_col_v05) hd_user_col_v05_n,
               (select to_char(i.factory_date,'yyyy/mm/dd')
                  from con_contract_lease_item i
                 WHERE i.contract_id = t1.contract_id
                   AND i.equipment_type = 'MAIN') wholesale_date,
               (select to_char(i.sale_date,'yyyy/mm/dd')
                  from con_contract_lease_item i
                 WHERE i.contract_id = t1.contract_id
                   AND i.equipment_type = 'MAIN'
                   and rownum = 1) retail_date,
               t1.insurance_fee,
               decode((Select bp.direct_sales_agent_flag
                        From hls_bp_master bp
                       Where bp.bp_id = t1.bp_id_agent_level1),
                      'Y',
                      t1.lease_mgt_fee,
                      'N',
                      0,
                      0) direct_agent_fee,
               (select i.factory_price
                  from con_contract_lease_item i
                 WHERE i.contract_id = t1.contract_id
                   AND i.equipment_type = 'MAIN') main_factory_price,
               (select nvl(sum(nvl(ccc.due_amount, 0)), 0)
                  from con_contract_cashflow ccc
                 where ccc.contract_id = t1.contract_id
                   and ccc.cf_item in (992, 997)
                   and ccc.cf_status = 'RELEASE'
                   and ccc.cf_direction = 'OUTFLOW') main_profit,
               (select nvl(i.factory_price,0)
                  from con_contract_lease_item i
                 WHERE i.contract_id = t1.contract_id
                   AND i.equipment_type = 'ACCESSORY'
                   and rownum = 1) accessory_factory_price,
               (select nvl(sum(nvl(ccc.due_amount, 0)), 0)
                  from con_contract_cashflow ccc
                 where ccc.contract_id = t1.contract_id
                   and ccc.cf_item in (994, 995)
                   and ccc.cf_status = 'RELEASE'
                   and ccc.cf_direction = 'OUTFLOW') accessory_profit,
               (SELECT to_char(to_date(min(hde.sap_zaldt),'yyyy-mm-dd'),'yyyy/mm/dd')
                  FROM hls_journal_detail hde
                 where hde.source_table = 'CSH_WRITE_OFF'
                   and hde.source_id in
                       (SELECT cwo.write_off_id
                          FROM csh_write_off cwo, con_contract_cashflow ccc
                         where cwo.cashflow_id = ccc.cashflow_id
                           and ccc.cf_item in (5, 6)
                           and ccc.contract_id = t1.contract_id)) main_factory_sap_zaldt,
               (SELECT to_char(to_date(min(hde.sap_zaldt),'yyyy-mm-dd'),'yyyy/mm/dd')
                  FROM hls_journal_detail hde
                 where hde.source_table = 'CSH_WRITE_OFF'
                   and hde.source_id in
                       (SELECT cwo.write_off_id
                          FROM csh_write_off cwo, con_contract_cashflow ccc
                         where cwo.cashflow_id = ccc.cashflow_id
                           and ccc.cf_item in (992, 997)
                           and ccc.contract_id = t1.contract_id)) main_profit_sap_zaldt,
               (SELECT to_char(to_date(min(hde.sap_zaldt),'yyyy-mm-dd'),'yyyy/mm/dd')
                  FROM hls_journal_detail hde
                 where hde.source_table = 'CSH_WRITE_OFF'
                   and hde.source_id in
                       (SELECT cwo.write_off_id
                          FROM csh_write_off cwo, con_contract_cashflow ccc
                         where cwo.cashflow_id = ccc.cashflow_id
                           and ccc.cf_item in (993, 998)
                           and ccc.contract_id = t1.contract_id)) accessory_factory_sap_zaldt,
               (SELECT to_char(to_date(min(hde.sap_zaldt),'yyyy-mm-dd'),'yyyy/mm/dd')
                  FROM hls_journal_detail hde
                 where hde.source_table = 'CSH_WRITE_OFF'
                   and hde.source_id in
                       (SELECT cwo.write_off_id
                          FROM csh_write_off cwo, con_contract_cashflow ccc
                         where cwo.cashflow_id = ccc.cashflow_id
                           and ccc.cf_item in (994, 995)
                           and ccc.contract_id = t1.contract_id)) accessory_profit_sap_zaldt,
               (select to_char(min(h.req_date),'yyyy/mm/dd')
                  from con_contract_cashflow ccc,
                       csh_payment_req_hd    h,
                       csh_payment_req_ln    cl
                 where ccc.contract_id = t1.contract_id
                   and ccc.cf_item in (5, 6, 993, 998)
                   and ccc.cf_status = 'RELEASE'
                   and h.payment_req_id = cl.payment_req_id
                   and cl.ref_doc_line_id = ccc.cashflow_id) cost_req_date,
               (select to_char(min(h.req_date),'yyyy/mm/dd')
                  from con_contract_cashflow ccc,
                       csh_payment_req_hd    h,
                       csh_payment_req_ln    cl
                 where ccc.contract_id = t1.contract_id
                   and ccc.cf_item in (992, 997, 994, 995)
                   and ccc.cf_status = 'RELEASE'
                   and h.payment_req_id = cl.payment_req_id
                   and cl.ref_doc_line_id = ccc.cashflow_id) profit_req_date,
                   fnd_sc_score_pkg.get_overdue_period(t1.contract_id) max_cashflow_count,
                   t1.inception_of_lease as inception_of_lease,
                     (select i.EQUIPMENT_NUMBER
                  from con_contract_lease_item i
                 WHERE i.contract_id = t1.contract_id
                   AND i.vehicle_class = 'HEAD'
                   AND i.equipment_type = 'MAIN') equipment_number
          from con_contract t1
         where t1.contract_status in
               ('CHANGE',
                'INCEPT',
                'TERMINATE',
                'ETING',
                'ET',
                'PENDING',
                'AGENT_TERMINATE',
                'LOSSSHARE_TERMINATE',
                'VIRTUAL_TERMINATE')
           and t1.data_class = 'NORMAL') t
                 #WHERE_CLAUSE#
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="contract_id" />
        <bm:field name="lease_execution_year" />
        <bm:field name="lease_channel"/>
        <bm:field name="lease_channel_desc"/>
        <bm:field name="business_type" />
        <bm:field name="business_type_n"/>
        <bm:field name="large_area"/>
        <bm:field name="bp_agent_name"/>
        <bm:field name="extra_nam"/>
        <bm:field name="submit_date"/>
        <bm:field name="lease_execution_date"/>
        <bm:field name="contract_number"/>
        <bm:field name="contract_status"/>
        <bm:field name="contract_status_n"/>
        <bm:field name="bp_name" />
        <bm:field name="sys_level" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="score_level" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="division" />
        <bm:field name="division_n"/>
        <bm:field name="pattern"/>
        <bm:field name="accessory_pattern"/>
        <bm:field name="quantity" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="first_pay_date"/>
        <bm:field name="lease_term_month" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="int_rate" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="annual_pay_times"/>
        <bm:field name="annual_pay_times_n"/>
        <bm:field name="deduct_bank"/>
        <bm:field name="deduct_bank_n"/>
        <bm:field name="machine_number"/>
        <bm:field name="accessory_machine_number"/>
        <bm:field name="sales_method"/>
        <bm:field name="sales_method_n"/>
        <bm:field name="as_item_source"/>
        <bm:field name="special_policy"/>
        <bm:field name="product_name"/>
        <bm:field name="sub_price_list_name"/>
        <bm:field name="lease_item_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="lease_item_amount2" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="down_payment_ratio" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="down_payment" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="finance_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="tenant_service_charge" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="agent_fee" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="agent_bond" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="tenant_bond" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="contract_inception_date"/>
        <bm:field name="area_distinguish"/>
        <bm:field name="hd_user_col_v05"/>
        <bm:field name="hd_user_col_v05_n"/>
        <bm:field name="wholesale_date"/>
        <bm:field name="retail_date"/>
        <bm:field name="insurance_fee" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="direct_agent_fee" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="main_factory_price" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="main_profit" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="accessory_factory_price" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="accessory_profit" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="cost_req_date"/>
        <bm:field name="profit_req_date"/>
        <bm:field name="main_factory_sap_zaldt"/>
        <bm:field name="main_profit_sap_zaldt"/>
        <bm:field name="accessory_factory_sap_zaldt"/>
        <bm:field name="accessory_profit_sap_zaldt"/>
        <bm:field name="max_cashflow_count" databaseType="NUMBER" datatype="java.lang.Long"/>
        <bm:field name="inception_of_lease"/>
        <bm:field name="equipment_number"/>
    </bm:fields>
    <bm:query-fields>
        <bm:query-field field="contract_status_n" queryOperator="="/>
        <bm:query-field field="contract_number" queryOperator="="/>
        <bm:query-field field="bp_agent_name" queryOperator="="/>
        <bm:query-field field="extra_nam" queryOperator="like"/>
        <bm:query-field name="period_name_from" queryExpression="to_date(t.lease_execution_year,'yyyy-mm') &gt;= to_date(${@period_name_from},'yyyy-mm')"/>
        <bm:query-field name="period_name_to" queryExpression="to_date(t.lease_execution_year,'yyyy-mm') &lt;= to_date(${@period_name_to},'yyyy-mm')"/>
        <bm:query-field field="lease_channel_desc" queryOperator="="/>
        <bm:query-field field="business_type_n" queryOperator="="/>
        <bm:query-field field="bp_name" queryOperator="like"/>
        <bm:query-field field="pattern" queryOperator="like"/>
        <bm:query-field field="machine_number" queryOperator="like"/>
        <bm:query-field name="first_pay_date_from" queryExpression="to_date(t.first_pay_date,'yyyy/mm/dd') &gt;= to_date(${@first_pay_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="first_pay_date_to" queryExpression="to_date(t.first_pay_date,'yyyy/mm/dd') &lt;= to_date(${@first_pay_date_to},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="division_n" queryExpression="t.division =${@division}"/>
        <bm:query-field name="retail_date_from" queryExpression="to_date(t.retail_date,'yyyy/mm/dd') &gt;= to_date(${@retail_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="retail_date_to" queryExpression="to_date(t.retail_date,'yyyy/mm/dd') &lt;= to_date(${@retail_date_to},&apos;yyyy-mm-dd&apos;)"/>
    </bm:query-fields>
    <s:bm-script><![CDATA[
            var cx = Packages.leaf.javascript.Context.getCurrentContext();
            Packages.leaf.plugin.script.engine.ScriptImportor.defineExternScript(cx, this, $ctx.getData(), "aut_authority_bm_validate.js");
        ]]></s:bm-script>
</bm:model>
