<?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 *
  FROM (select cc.contract_id,
               --合同编号
               cc.contract_number,
               --承租人名称
               (select hbm.bp_name
                  from hls_bp_master hbm
                 where hbm.bp_id = cc.bp_id_tenant) bp_name,
               --承租人编号
               (select hbm.bp_code
                  from hls_bp_master hbm
                 where hbm.bp_id = cc.bp_id_tenant) bp_code,
               --经销商名称
               (select hbm.extra_nam
                  from hls_bp_master hbm
                 where hbm.bp_id = cc.bp_id_agent_level1) agent_name,
               --经销商编号
               (select hbm.bp_code
                  from hls_bp_master hbm
                 where hbm.bp_id = cc.bp_id_agent_level1) agent_code,
               cc.division,
               --租赁物
               (select v.code_value_name
                  from sys_code_values_v v
                 where v.code = 'DIVISION'
                   and v.code_value = cc.division) division_desc,
               --利息总额
               case
                 when gp.internal_period_num =
                      to_number(to_char(cc.inception_of_lease, 'yyyymm')) then
                  nvl((SELECT sum(ccw.interest)
                        FROM con_contract_cashflow ccw
                       WHERE ccw.contract_id = cc.contract_id
                         and ccw.cf_item = 1
                         and ccw.cf_direction = 'INFLOW'
                         and ccw.cf_status = 'RELEASE'),
                      0)
                 else
                  null
               end interest,
               --已核销利息总额
               case
                 when gp.internal_period_num =
                      to_number(to_char(cc.inception_of_lease, 'yyyymm')) then
                  nvl((SELECT sum(NVL(ccw.Received_Interest, 0))
                        FROM con_contract_cashflow ccw
                       WHERE ccw.contract_id = cc.contract_id
                         and ccw.cf_item = 1
                         and ccw.cf_direction = 'INFLOW'
                         and ccw.cf_status = 'RELEASE'),
                      0)
                 else
                  null
               end Received_Interest,
               --期间
               gp.internal_period_num,
               gp.period_name,
               --当期不可列支佣金(不含税)
               (SELECT ce.income_tax_amount
                  FROM con_unearned_comsn_income ce
                 WHERE ce.contract_id = cc.contract_id
                   and ce.internal_period_num = gp.internal_period_num) income_tax_amount,
               --当期可列支佣金(不含税)
               (SELECT ce.income_tax_diff_amount
                  FROM con_unearned_comsn_income ce
                 WHERE ce.contract_id = cc.contract_id
                   and ce.internal_period_num = gp.internal_period_num) income_tax_diff_amount,
               --当期资产管理费(不含税)
               (select SUM(round(ccc.due_amount /
                                 (nvl((select hf.vat_rate
                                        from hl_asset_manage_fee hf
                                       where hf.cashflow_id = ccc.cashflow_id),
                                      nvl(hlcm_monthend_je_pkg.get_acp_rate(cc.bp_id_agent_level1),
                                          0.06)) + 1),
                                 2))
                  from con_contract_cashflow ccc
                 where ccc.contract_id = cc.contract_id
                   and ccc.due_date >= gp.start_date
                   and ccc.due_date < gp.end_date + 1
                   and ccc.cf_item = 40) asset_not_due_amount,
               --当期费用合计
               round((COALESCE((SELECT ce.income_tax_amount
                                 FROM con_unearned_comsn_income ce
                                WHERE ce.contract_id = cc.contract_id
                                  AND ce.internal_period_num =
                                      gp.internal_period_num),
                               0) +
                     COALESCE((SELECT ce.income_tax_diff_amount
                                 FROM con_unearned_comsn_income ce
                                WHERE ce.contract_id = cc.contract_id
                                  AND ce.internal_period_num =
                                      gp.internal_period_num),
                               0) +
                     COALESCE((SELECT SUM(ROUND(ccc.due_amount /
                                                (nvl((select hf.vat_rate
                                        from hl_asset_manage_fee hf
                                       where hf.cashflow_id = ccc.cashflow_id),
                                      nvl(hlcm_monthend_je_pkg.get_acp_rate(cc.bp_id_agent_level1),
                                          0.06)) + 1),
                                                2))
                                 FROM con_contract_cashflow ccc
                                WHERE ccc.contract_id = cc.contract_id
                                  AND ccc.due_date >= gp.start_date
                                  AND ccc.due_date < gp.end_date + 1
                                  AND ccc.cf_item = 40),
                               0)),
                     2) sum_asset_commission,
               --当期收入合计
               (SELECT sum(ce.UNEARNED_FINANCE_INCOME)
                  FROM CON_UNEARNED_FINANCE_INCOME ce
                 WHERE ce.contract_id = cc.contract_id
                   and ce.internal_period_num = gp.internal_period_num) UNEARNED_FINANCE_INCOME,
               --不含税佣金
               case
                 when gp.internal_period_num =
                      to_number(to_char(cc.inception_of_lease, 'yyyymm')) then
                  nvl((SELECT sum(NVL(round(ccw.due_amount /
                                           (nvl((select hf.vat_rate
                                        from hlcm_comsn_reconcilia hf
                                       where hf.cashflow_id = ccw.cashflow_id),
                                      nvl(hlcm_monthend_je_pkg.get_acp_rate(cc.bp_id_agent_level1),
                                          0.06)) + 1),
                                           2),
                                     0))
                        FROM con_contract_cashflow ccw
                       WHERE ccw.contract_id = cc.contract_id
                         and ccw.cf_item = 911
                         and ccw.cf_direction = 'OUTFLOW'
                         and ccw.cf_status = 'RELEASE'),
                      0)
                 else
                  null
               end commission_not_due_amount,
               --含税佣金
               case
                 when gp.internal_period_num =
                      to_number(to_char(cc.inception_of_lease, 'yyyymm')) then
                  nvl((SELECT sum(NVL(round(ccw.due_amount, 2), 0))
                        FROM con_contract_cashflow ccw
                       WHERE ccw.contract_id = cc.contract_id
                         and ccw.cf_item = 911
                         and ccw.cf_direction = 'OUTFLOW'
                         and ccw.cf_status = 'RELEASE'),
                      0)
                 else
                  null
               end commission_due_amount,
               --支付日期
               case
                 when gp.internal_period_num =
                      to_number(to_char(cc.inception_of_lease, 'yyyymm')) then
                  (select to_char(ccc.last_received_date, 'yyyy-mm-dd')
                     from con_contract_cashflow ccc
                    where ccc.contract_id = cc.contract_id
                      and ccc.cf_item = 911)
                 else
                  null
               end commission_due_date,
               --发票号码
               case
                 when gp.internal_period_num =
                      to_number(to_char(cc.inception_of_lease, 'yyyymm')) then
                  (SELECT INVOICE_NUMBER
                     FROM hlcm_monthly_report_temp htp
                    WHERE htp.contract_id = cc.contract_id)
                 else
                  null
               end commission_invoice_number,
               --含税资产管理费
               case
                 when gp.internal_period_num =
                      to_number(to_char(cc.inception_of_lease, 'yyyymm')) then
                  (select SUM(round(ccc.due_amount, 2))
                     from con_contract_cashflow ccc
                    where ccc.contract_id = cc.contract_id
                      and ccc.cf_item = 40)
                 else
                  null
               end sum_asset_due_amount,
               --不含税资产管理费
               case
                 when gp.internal_period_num =
                      to_number(to_char(cc.inception_of_lease, 'yyyymm')) then
                  nvl((select SUM(round(ccc.due_amount /
                                       (nvl((select hf.vat_rate
                                        from hl_asset_manage_fee hf
                                       where hf.cashflow_id = ccc.cashflow_id),
                                      nvl(hlcm_monthend_je_pkg.get_acp_rate(cc.bp_id_agent_level1),
                                          0.06)) + 1),
                                       2))
                        from con_contract_cashflow ccc
                       where ccc.contract_id = cc.contract_id
                         and ccc.cf_item = 40),
                      0)
                 else
                  null
               end sum_asset_not_due_amount
          from con_contract cc, gld_periods gp
         where cc.data_class = 'NORMAL'
           and gp.adjustment_flag != 'Y'
           and cc.contract_status not IN ('NEW', 'INCEPT_RETURN', 'CANCEL')
           and gp.internal_period_num >=
               to_number(to_char(cc.inception_of_lease, 'yyyymm'))
           and gp.internal_period_num <=
               (select to_number(to_char(MAX(CCC.DUE_DATE), 'yyyymm'))
                  from con_contract_cashflow ccc
                 where ccc.contract_id = cc.contract_id
                   and ccc.cf_item in (1, 200, 250)
                   and ccc.cf_direction = 'INFLOW'
                   and ccc.cf_status = 'RELEASE')
           and exists (select 1
                  from con_contract_cashflow ccc
                 where ccc.contract_id = cc.contract_id
                   and ccc.cf_item in (40, 911))) t1 #WHERE_CLAUSE#
           order By t1.contract_number,t1.internal_period_num


                ]]>
            </bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="contract_number"
                        queryExpression="t1.contract_number like &apos;%&apos;||${@contract_number}||&apos;%&apos;"/>
        <bm:query-field name="bp_name"
                        queryExpression="t1.bp_name like &apos;%&apos;||${@bp_name}||&apos;%&apos;"/>
        <bm:query-field name="agent_name"
                        queryExpression="t1.agent_name like &apos;%&apos;||${@agent_name}||&apos;%&apos;"/>
        <bm:query-field name="period_name_from"
                        queryExpression="t1.period_name &gt;= nvl(${@period_name_from},t1.period_name)"/>
        <bm:query-field name="period_name_to"
                        queryExpression="t1.period_name &lt;= nvl(${@period_name_to},t1.period_name)"/>
    </bm:query-fields>
</bm:model>
