<?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="lease_execution_month"/>
        <bm:field name="lease_channel"/>
        <bm:field name="lease_channel_n"/>
        <bm:field name="bp_agent_name"/>
        <bm:field name="creation_date" databaseType="DATE" datatype="java.util.Date"/>
        <bm:field name="lease_execution_date" databaseType="DATE" datatype="java.util.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="division"/>
        <bm:field name="division_n"/>
        <bm:field name="pattern"/>
        <bm:field name="machine_number"/>
        <bm:field name="lease_item_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="net_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="total_amount"  databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="due_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="lease_net" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="net_due" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="lease_due" databaseType="NUMBER" datatype="java.lang.Double"/>
    </bm:fields>
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
       select t1.*,
       (t1.lease_item_amount - t1.total_amount) lease_net,
       (t1.total_amount - t1.due_amount) net_due,
       (t1.lease_item_amount - t1.due_amount) lease_due
  from (select to_char(c.lease_execution_date, 'yyyy-mm') lease_execution_month,
               c.lease_channel,
               (Select ch.description
                  from hls_lease_channel ch
                 Where ch.lease_channel = c.lease_channel) lease_channel_n,
               (select hb.bp_name
                  from hls_bp_master hb
                 where hb.bp_id = c.bp_id_agent_level1) bp_agent_name,
               (select i.creation_date
                  from zj_wfl_workflow_instance i
                 where i.instance_id = c.incept_instance_id) creation_date,
               c.lease_execution_date,
               c.contract_number,
               c.contract_status,
               (select v.code_value_name
                  from sys_code_values_v v
                 where v.code = 'CON500_CONTRACT_STATUS'
                   and v.code_value = c.contract_status) AS contract_status_n,
               (select hb.bp_name
                  from hls_bp_master hb
                 where hb.bp_id = c.bp_id_tenant) bp_name,
               c.division,
               (SELECT hd.description
                  FROM hls_division hd
                 WHERE hd.division = c.division) division_n,
               (select i.pattern
                  from con_contract_lease_item i
                 WHERE i.contract_id = c.contract_id
                   AND i.equipment_type = 'MAIN') pattern,
               (select i.machine_number
                  from con_contract_lease_item i
                 WHERE i.contract_id = c.contract_id
                   AND i.equipment_type = 'MAIN') machine_number,
               c.lease_item_amount,
               (select nvl(sum(nvl(al.net_amount, 0)), 0)
                  From acp_invoice_ln al
                 Where al.contract_id = c.contract_id
                   and nvl(al.reversed_flag, 'N') = 'N'
                   and al.invoice_status in
                       ('VERIFIED', 'ARCHIVED', 'ARCHIVED')) net_amount,
               (select nvl(sum(nvl(al.total_amount, 0)), 0)
                  From acp_invoice_ln al
                 Where al.contract_id = c.contract_id
                   and nvl(al.reversed_flag, 'N') = 'N'
                   and al.invoice_status in
                       ('VERIFIED', 'ARCHIVED', 'ARCHIVED')) total_amount,

               decode(c.lease_channel,
                      '00',
                      decode(c.business_type,
                             'LEASE',
                             (select nvl(sum(nvl(hj.amount_dr, 0)), 0)
                                from hls_journal_detail  hj,
                                     hls_journal_header  h,
                                     gld_je_template_hds gj
                               where hj.journal_header_id = h.journal_header_id
                                 and gj.je_template_hd_id = h.je_template_hd_id
                                 and h.source_table = 'CON_CONTRACT'
                                 and h.source_id = c.contract_id
                                 and h.reversed_flag = 'N'
                                 and h.je_transaction_code = 'LEASE_INCEPT'
                                 and gj.je_template_code = 'LEASE_INCEPT_L'),
                             'LEASEBACK',
                             (select nvl(sum(nvl(hj.amount_dr, 0)), 0)
                                from hls_journal_detail_v hj,
                                     hls_journal_header   h,
                                     gld_je_template_hds  gj
                               where hj.journal_header_id = h.journal_header_id
                                 and gj.je_template_hd_id = h.je_template_hd_id
                                 and h.source_table = 'CON_CONTRACT'
                                 and h.source_id = c.contract_id
                                 and h.reversed_flag = 'N'
                                 and h.je_transaction_code = 'LEASE_INCEPT'
                                 and gj.je_template_code = 'LEASE_INCEPT_LB'
                                 and hj.account_code in ('13050103', '11050110')),0),
                      '01',
                      (select nvl(sum(nvl(hj.amount_dr, 0)), 0)
                         from hls_journal_detail_v hj,
                              hls_journal_header   h,
                              gld_je_template_hds  gj
                        where hj.journal_header_id = h.journal_header_id
                          and gj.je_template_hd_id = h.je_template_hd_id
                          and h.source_table = 'CON_CONTRACT'
                          and h.source_id = c.contract_id
                          and h.reversed_flag = 'N'
                          and h.je_transaction_code = 'LEASE_INCEPT'
                          and gj.je_template_code = 'LEASE_INCEPT_CL'
                          and hj.account_code in
                              ('13050104', '11050109', '21100028', '21100029')),0) due_amount
        /*due_amount如果合同是一般租赁  那么直租  匹配  凭证模板代码为LEASE_INCEPT_L  的头凭证记录， 通过该记录找到行记录，把所有的借方加总  即 ACCOUT_DR之和
        回租    匹配凭证模板头   LEASE_INCEPT_LB  的头   找到行   取    科目为13050103  和  11050110 的 ACCOUNT_DR 之和
        如果合同是联合租赁   那么都匹配 LEASE_INCEPT_CL
        找到13050104 ，11050109，21100028,21100029四个科目所在行的 ACCOUNT_DR 之和*/
          from con_contract c
         where c.data_class = 'NORMAL'
           and c.contract_status not in ('PENDING', 'CLOSED', 'CANCEL')) t1


                #WHERE_CLAUSE#
                #ORDER_BY_CLAUSE#
                ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field field="contract_number" queryOperator="like"/>
        <bm:query-field field="contract_status" queryOperator="="/>
        <bm:query-field field="lease_channel" queryOperator="="/>
        <bm:query-field name="lease_execution_date_from" queryExpression="t1.lease_execution_date &gt;= to_date(${@lease_execution_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="lease_execution_date_to" queryExpression="t1.lease_execution_date &lt;= to_date(${@lease_execution_date_to},&apos;yyyy-mm-dd&apos;)"/>
    </bm:query-fields>
</bm:model>
