<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: ZLF
    $Date: 2015-1-21 下午3:09:38
    $Revision: 1.0
    $Purpose:
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[

with temp as
(
SELECT to_char(add_months(trunc(t1.journal_date, 'Q'), 2), 'yyyymm') tax_month,
       t1.due_amount - t1.amount_tax sale_amount,
       2  order_seq,
       T1.*

  FROM (

        SELECT h.period_year,
                h.period_num,
                h.internal_period_num,
                h.journal_date,
                h.sbo_num journal_num,
                l.reference2,
                l.line_description,
                h.journal_num journal_number,
                reference3 contract_number,
                reference1 bp_code,
                (SELECT BP_NAME
                   FROM hls_bp_master
                  WHERE bp_code = reference1
                    AND ROWNUM = 1) bp_name,
                l.journal_header_id,
                (SELECT hi.invoice_date
                   FROM acr_invoice_hd hi, acr_invoice_ln li
                  WHERE hi.invoice_hd_id = li.invoice_hd_id
                    AND hi.reversed_flag = 'N'
                    AND (li.cashflow_id = l.cashflow_id OR
                        li.ref_n03 = l.journal_header_id)
                    AND ROWNUM = 1) invoice_date,
                l.amount_cr  amount_tax,
                '首付款' times,
                (select ccc.due_date
                   from con_contract_cashflow ccc
                  where ccc.contract_id = h.source_id
                    and ccc.cf_item = 2) due_date,
                (select ccc.due_amount
                   from con_contract_cashflow ccc
                  where ccc.contract_id = h.source_id
                    and ccc.cf_item = 2) due_amount,
                (select ccc.due_amount
                   from con_contract_cashflow ccc
                  where ccc.contract_id = h.source_id
                    and ccc.cf_item = 2) principal,
                null interest,
                l.amount_cr  vat_due_amount,
                (select case
                          when nvl(ccc.due_amount - ccc.vat_due_amount, 0) <> 0 then
                           round(ccc.vat_due_amount /
                                 (ccc.due_amount - ccc.vat_due_amount),
                                 2)
                          else
                           null
                        end
                   from con_contract_cashflow ccc
                  where ccc.contract_id = h.source_id
                    and ccc.cf_item = 2) vat_rate,
                h.je_transaction_code,
                l.cashflow_id,
                'N' fixed_data_flag
          FROM hls_journal_header h, hls_journal_detail l, gld_accounts ga
         WHERE h.journal_header_id = l.journal_header_id
           and h.sbo_num is not null
           and h.total_amount_dr != 0
           and ga.account_id = l.account_id
           and ga.account_code in ('2221001020', '2221001023', '2221001024')
           and h.reversed_flag = 'N' and h.post_gl_status = 'Y'
           and h.internal_period_num <= to_number(${@base_period})
           AND (h.je_transaction_code = 'LEASE_INCEPT')
           and exists
         (select 1
                  from gld_je_template_hds hd
                 where hd.je_template_hd_id = h.je_template_hd_id
                   and hd.je_template_code = 'HL_INCEPT_DOWN_PAYMENT_CF')

        union
        SELECT h.period_year,
                h.period_num,
                h.internal_period_num,
                h.journal_date,
                h.sbo_num journal_num,
                l.reference2,
                l.line_description,
                h.journal_num journal_number,
                reference3 contract_number,
                reference1 bp_code,
                (SELECT BP_NAME
                   FROM hls_bp_master
                  WHERE bp_code = reference1
                    AND ROWNUM = 1) bp_name,
                l.journal_header_id,
                (SELECT hi.invoice_date
                   FROM acr_invoice_hd hi, acr_invoice_ln li
                  WHERE hi.invoice_hd_id = li.invoice_hd_id
                    AND hi.reversed_flag = 'N'
                    AND (li.cashflow_id = l.cashflow_id OR
                        li.ref_n03 = l.journal_header_id)
                    AND ROWNUM = 1) invoice_date,
                l.amount_cr  amount_tax,
                (select decode(ccc.cf_item, 8, '留购价',200,'提前结清租金',11,'提前结清手续费' , to_char(ccc.times))
                   from con_contract_cashflow ccc
                  where ccc.cashflow_id = l.cashflow_id) times,
                (select ccc.due_date
                   from con_contract_cashflow ccc
                  where ccc.cashflow_id = l.cashflow_id) due_date,

                (select
                           ccc.due_amount

                   from con_contract cc, con_contract_cashflow ccc
                  where ccc.cashflow_id = l.cashflow_id
                    and ccc.contract_id = cc.contract_id) due_amount,
                (select decode(ccc.cf_item,8,0,ccc.principal)

                   from con_contract cc, con_contract_cashflow ccc
                  where ccc.cashflow_id = l.cashflow_id
                    and ccc.contract_id = cc.contract_id) principal,
                (select decode(ccc.cf_item,8,ccc.due_amount,nvl(ccc.interest, 0))

                   from con_contract cc, con_contract_cashflow ccc
                  where ccc.cashflow_id = l.cashflow_id
                    and ccc.contract_id = cc.contract_id) interest,
                l.amount_cr  vat_due_amount,
                (select case
                          when ccc.cf_item = 1 and
                               cc.business_type = 'LEASEBACK' and
                               nvl(ccc.interest - ccc.vat_interest, 0) != 0 then
                           round(ccc.vat_interest /
                                 (ccc.interest - ccc.vat_interest),
                                 2)

                          when ((ccc.cf_item = 1 and
                               cc.business_type != 'LEASEBACK') or
                               ccc.cf_item != 1) and
                               nvl(ccc.due_amount - ccc.vat_due_amount, 0) != 0 then
                           round(ccc.vat_due_amount /
                                 (ccc.due_amount - ccc.vat_due_amount),
                                 2)
                          else
                           null
                        end
                   from con_contract cc, con_contract_cashflow ccc
                  where ccc.cashflow_id = l.cashflow_id
                    and cc.contract_id = ccc.contract_id) vat_rate,
                h.je_transaction_code,
                l.cashflow_id,
                'N' fixed_data_flag
          FROM hls_journal_header h, hls_journal_detail l, gld_accounts ga
         WHERE h.journal_header_id = l.journal_header_id
           and h.sbo_num is not null
           and h.total_amount_dr != 0
           and ga.account_id = l.account_id
           and ga.account_code in ('2221001020', '2221001023', '2221001024','2221001026')
           and h.reversed_flag = 'N' and h.post_gl_status = 'Y'
           and h.internal_period_num <= to_number(${@base_period})
           AND (h.je_transaction_code in ('HL_SALES_TAX_ACT_NEW','FIN_INCOME_RECOGNITION_OP'))
           and ((trunc(h.journal_date) <=
               trunc(to_date('2020-01-31', 'yyyy-mm-dd')) and
               h.description = 'MANUAL_IMPORT') or
               (trunc(h.journal_date) >
               trunc(to_date('2020-01-31', 'yyyy-mm-dd'))))


        union
        SELECT h.period_year,
                h.period_num,
                h.internal_period_num,
                h.journal_date,
                h.sbo_num journal_num,
                l.reference2,
                l.line_description,
                h.journal_num journal_number,
                reference3 contract_number,
                reference1 bp_code,
                (SELECT BP_NAME
                   FROM hls_bp_master
                  WHERE bp_code = reference1
                    AND ROWNUM = 1) bp_name,
                l.journal_header_id,
                (SELECT hi.invoice_date
                   FROM acr_invoice_hd hi, acr_invoice_ln li
                  WHERE hi.invoice_hd_id = li.invoice_hd_id
                    AND hi.reversed_flag = 'N'
                    AND (li.cashflow_id = l.cashflow_id OR
                        li.ref_n03 = l.journal_header_id)
                    AND ROWNUM = 1) invoice_date,
                round(l.amount_cr ,
                      2) amount_tax,
                '违约金' times,
                h.journal_date due_date,
                case
                  when ghd.je_template_code in
                       ('HL_RECEIVABLES_AGENT_REPUR_LB',
                        'HL_RECEIVABLES_AGENT_REPUR'
                        --,'HL_GLD_BALANCE_PENALTY'  取 ('1122000001', '1122000002')金额
                        ) then
                   (select d.amount_cr
                      from hls_journal_detail d
                     where d.journal_header_id = h.journal_header_id
                       and d.reference3 = l.reference3
                       and exists (select 1
                              from gld_accounts g
                             where g.account_id = d.account_id
                               and g.account_code in ('6041001000','6041001100'))
                       and rownum = 1)
                  when ghd.je_template_code in ('HL_REPURCHASE_VAT') then
                   (select -1 * round(cc.ccr_due_amount -
                                      cc.ccr_due_amount /
                                      (1 + DECODE(CC.BUSINESS_TYPE,
                                                  'LEASEBACK',
                                                  0.06,
                                                  0.13)) *
                                      DECODE(CC.BUSINESS_TYPE,
                                             'LEASEBACK',
                                             0.06,
                                             0.13),
                                      2)
                      from con_contract cc
                     WHERE cc.contract_number = l.reference3
                       and cc.data_class = 'NORMAL')
                  else
                   (select nvl(d.amount_dr, d.amount_cr)
                      from hls_journal_detail d
                     where d.journal_header_id = h.journal_header_id
                       and d.reference3 = l.reference3
                       and exists
                     (select 1
                              from gld_accounts g
                             where g.account_id = d.account_id
                               and g.account_code in ('1122000001', '1122000002','1122000005'))
                       and rownum = 1)
                end due_amount,
                null principal,
                case
                  when ghd.je_template_code in
                       ('HL_RECEIVABLES_AGENT_REPUR_LB',
                        'HL_RECEIVABLES_AGENT_REPUR'
                        --,'HL_GLD_BALANCE_PENALTY'  取 ('1122000001', '1122000002')金额
                        ) then
                   (select d.amount_cr
                      from hls_journal_detail d
                     where d.journal_header_id = h.journal_header_id
                       and d.reference3 = l.reference3
                       and exists (select 1
                              from gld_accounts g
                             where g.account_id = d.account_id
                               and g.account_code in ('6041001000','6041001100'))
                       and rownum = 1)
                  when ghd.je_template_code in ('HL_REPURCHASE_VAT') then
                   (select -1 * round(cc.ccr_due_amount -
                                      cc.ccr_due_amount /
                                      (1 + DECODE(CC.BUSINESS_TYPE,
                                                  'LEASEBACK',
                                                  0.06,
                                                  0.13)) *
                                      DECODE(CC.BUSINESS_TYPE,
                                             'LEASEBACK',
                                             0.06,
                                             0.13),
                                      2)
                      from con_contract cc
                     WHERE cc.contract_number = l.reference3
                       and cc.data_class = 'NORMAL')
                  else
                   (select nvl(d.amount_dr, d.amount_cr)
                      from hls_journal_detail d
                     where d.journal_header_id = h.journal_header_id
                       and d.reference3 = l.reference3
                       and exists
                     (select 1
                              from gld_accounts g
                             where g.account_id = d.account_id
                               and g.account_code in ('1122000001', '1122000002','1122000005'))
                       and rownum = 1)
                end interest,
                round(l.amount_cr,
                      2) vat_due_amount,
                (select DECODE(CC.BUSINESS_TYPE, 'LEASEBACK', 0.06, 0.13)
                   from con_contract cc
                  WHERE cc.contract_number = l.reference3
                    and cc.data_class = 'NORMAL') vat_rate,
                h.je_transaction_code,
                l.cashflow_id,
                case
                  when ghd.je_template_code in ('HL_REPURCHASE_VAT') then
                   'Y'
                  else
                   'N'
                end fixed_data_flag
          FROM hls_journal_header  h,
                hls_journal_detail  l,
                gld_accounts        ga,
                gld_je_template_hds ghd
         WHERE h.journal_header_id = l.journal_header_id
           and h.je_template_hd_id = ghd.je_template_hd_id
           and h.sbo_num is not null
           and h.total_amount_dr != 0
           and ga.account_id = l.account_id
           and ga.account_code in ('2221001020', '2221001023', '2221001024','2221001026')
           and h.reversed_flag = 'N' and h.post_gl_status = 'Y'
           and h.internal_period_num <= to_number(${@base_period})
           And (h.je_transaction_code = 'HL_CSH_CONSOLIDATION_DALAY' or
               ghd.je_template_code in
               ('HL_GLD_BALANCE_REPURCHASE',
                 'HL_GLD_BALANCE_REPURCHASE_LB',
                 'HL_RECEIVABLES_AGENT_REPUR_LB',
                 'HL_RECEIVABLES_AGENT_REPUR',
                 'HL_GLD_BALANCE_PENALTY',
                 'HL_PENALTY_R_ET',
                 'HL_PENALTY_R_ET_LB') or
               (ghd.je_template_code in ('HL_REPURCHASE_VAT')
               and l.line_description like '%违约金销项税'
               ))
           and ((trunc(h.journal_date) <=
               trunc(to_date('2020-01-31', 'yyyy-mm-dd')) and
               h.description = 'MANUAL_IMPORT') or
               (trunc(h.journal_date) >
               trunc(to_date('2020-01-31', 'yyyy-mm-dd'))))

        union
        SELECT h.period_year,
                h.period_num,
                h.internal_period_num,
                h.journal_date,
                h.sbo_num journal_num,
                l.reference2,
                l.line_description,
                h.journal_num journal_number,
                reference3 contract_number,
                reference1 bp_code,
                (SELECT BP_NAME
                   FROM hls_bp_master
                  WHERE bp_code = reference1
                    AND ROWNUM = 1) bp_name,
                l.journal_header_id,
                (SELECT hi.invoice_date
                   FROM acr_invoice_hd hi, acr_invoice_ln li
                  WHERE hi.invoice_hd_id = li.invoice_hd_id
                    AND hi.reversed_flag = 'N'
                    AND (li.cashflow_id = l.cashflow_id OR
                        li.ref_n03 = l.journal_header_id)
                    AND ROWNUM = 1) invoice_date,
                l.amount_cr  amount_tax,
                (select decode(ccc.cf_item, 8, '留购价',200,'提前结清租金',11,'提前结清手续费' ,to_char(ccc.times))
                   from con_contract_cashflow ccc
                  where ccc.cashflow_id = l.cashflow_id) times,
                (select ccc.due_date
                   from con_contract_cashflow ccc
                  where ccc.cashflow_id = l.cashflow_id) due_date,
                (select case
                          when ccc.cf_item = 200 then
                           ccc.due_amount
                          else
                           ccc.due_amount
                        end
                   from con_contract_cashflow ccc
                  where ccc.cashflow_id = l.cashflow_id) due_amount,
                (select case
                          when ccc.cf_item = 200 then
                           ccc.principal
                          else
                           0
                        end
                   from con_contract_cashflow ccc
                  where ccc.cashflow_id = l.cashflow_id) principal,
                (select decode(ccc.cf_item, '200', ccc.interest, ccc.due_amount)
                   from con_contract_cashflow ccc
                  where ccc.cashflow_id = l.cashflow_id) interest,
                l.amount_cr  vat_due_amount,
                (select case
                          when nvl(ccc.interest, 0) != 0 and (ccc.interest -ccc.vat_interest) != 0  then
                           round(ccc.vat_interest / (ccc.interest -ccc.vat_interest), 2)
                          when nvl(ccc.due_amount - ccc.vat_due_amount, 0) != 0 then
                           round(ccc.vat_due_amount /
                                 (ccc.due_amount - ccc.vat_due_amount),
                                 2)
                          else
                           null
                        end
                   from con_contract_cashflow ccc
                  where ccc.cashflow_id = l.cashflow_id) vat_rate,
                h.je_transaction_code,
                l.cashflow_id,
                'N' fixed_data_flag
          FROM hls_journal_header h, hls_journal_detail l, gld_accounts ga
         WHERE h.journal_header_id = l.journal_header_id
           and h.sbo_num is not null
           and h.total_amount_dr != 0
           and ga.account_id = l.account_id
           and ga.account_code in ('2221001020', '2221001023', '2221001024')
           and h.reversed_flag = 'N' and h.post_gl_status = 'Y'
           and h.internal_period_num <= to_number(${@base_period})
           And exists (selEct 1
                  from gld_je_template_hds hd
                 where hd.je_template_hd_id = h.je_template_hd_id
                   and hd.je_template_code in
                       ('HL_CON_TAX_ET',
                        'HL_CON_TAX_ET_N',
                        'HL_CON_FIN_ETFEE',
                        'HL_CON_FIN_ETFEE_N',
                        'HL_CON_ETREPUR_TAX',
                        'HL_CON_ETREPUR_TAX_LB',
                        'HL_CON_ETFEE',
                        'HL_CON_ETFEE_LB',
                        'HL_CON_ET_TAX',
                        'HL_CON_ET_TAX_LB'))
           and ((trunc(h.journal_date) <=
               trunc(to_date('2020-01-31', 'yyyy-mm-dd')) and
               h.description = 'MANUAL_IMPORT') or
               (trunc(h.journal_date) >
               trunc(to_date('2020-01-31', 'yyyy-mm-dd'))))

        union
        SELECT h.period_year,
                h.period_num,
                h.internal_period_num,
                h.journal_date,
                h.sbo_num journal_num,
                l.reference2,
                l.line_description,
                h.journal_num journal_number,
                reference3 contract_number,
                reference1 bp_code,
                (SELECT BP_NAME
                   FROM hls_bp_master
                  WHERE bp_code = reference1
                    AND ROWNUM = 1) bp_name,
                l.journal_header_id,
                (SELECT hi.invoice_date
                   FROM acr_invoice_hd hi, acr_invoice_ln li
                  WHERE hi.invoice_hd_id = li.invoice_hd_id
                    AND hi.reversed_flag = 'N'
                    AND (li.cashflow_id = l.cashflow_id OR
                        li.ref_n03 = l.journal_header_id)
                    AND ROWNUM = 1) invoice_date,
                l.amount_cr amount_tax,
                (select decode(ccc.cf_item,
                               8,
                               '留购价',
                               '解约回购利息/' || to_char(ccc.times))
                   from con_contract_cashflow ccc
                  where ccc.cashflow_id = l.cashflow_id) times,
                (select ccc.due_date
                   from con_contract_cashflow ccc
                  where ccc.cashflow_id = l.cashflow_id) due_date,
                (select decode(ccc.cf_item, 250, ccc.interest, ccc.due_amount)
                   from con_contract_cashflow ccc
                  where ccc.cashflow_id = l.cashflow_id) due_amount,
                null principal,
                (select decode(ccc.cf_item, 250, ccc.interest, ccc.due_amount)
                   from con_contract_cashflow ccc
                  where ccc.cashflow_id = l.cashflow_id) interest,
                l.amount_cr vat_due_amount,
                (select case
                          when ccc.cf_item = 250 and
                               nvl(ccc.interest - ccc.vat_interest, 0) != 0 then
                           round(ccc.vat_interest /
                                 (ccc.interest - ccc.vat_interest),
                                 2)
                          when ccc.cf_item != 250 and
                               nvl(ccc.due_amount - ccc.vat_due_amount, 0) != 0 then
                           round(ccc.vat_due_amount /
                                 (ccc.due_amount - ccc.vat_due_amount),
                                 2)
                          else
                           null
                        end
                   from con_contract_cashflow ccc
                  where ccc.cashflow_id = l.cashflow_id) vat_rate,
                h.je_transaction_code,
                l.cashflow_id,
                'N' fixed_data_flag
          FROM hls_journal_header h, hls_journal_detail l, gld_accounts ga
         WHERE h.journal_header_id = l.journal_header_id
           and h.sbo_num is not null
           and h.total_amount_dr != 0
           and ga.account_id = l.account_id
           and ga.account_code in ('2221001020', '2221001023', '2221001024')
           and h.reversed_flag = 'N' and h.post_gl_status = 'Y'
           and h.internal_period_num <= to_number(${@base_period})
           And exists (selEct 1
                  from gld_je_template_hds hd
                 where hd.je_template_hd_id = h.je_template_hd_id
                   and hd.je_template_code in
                       ('HL_FIN_INCOME_REPURCHASE',
                        'HL_FIN_INCOME_REPURCHASE_N',
                        'HL_FIN_INCOME_REPURCHASE_ET',
                        'HL_FIN_INCOME_REPURCHASE_ET_N',
                        'HL_CON_FIN_ETREPUR_INT',
                        'HL_CON_FIN_ETREPUR_INT_N',
                        'HL_CON_ETREPUR_FEE',
                        'HL_CON_ETREPUR_FEE_LB'))
           and ((trunc(h.journal_date) <=
               trunc(to_date('2020-01-31', 'yyyy-mm-dd')) and
               h.description = 'MANUAL_IMPORT') or
               (trunc(h.journal_date) >
               trunc(to_date('2020-01-31', 'yyyy-mm-dd'))))



  union
  SELECT h.period_year,
       h.period_num,
       h.internal_period_num,
       h.journal_date,
       h.sbo_num journal_num,
       l.reference2,
       l.line_description,
       h.journal_num journal_number,
       reference3 contract_number,
       reference1 bp_code,
       (SELECT BP_NAME
          FROM hls_bp_master
         WHERE bp_code = reference1
           AND ROWNUM = 1) bp_name,
       l.journal_header_id,
       (SELECT hi.invoice_date
          FROM acr_invoice_hd hi, acr_invoice_ln li
         WHERE hi.invoice_hd_id = li.invoice_hd_id
           AND hi.reversed_flag = 'N'
           AND (li.cashflow_id = l.cashflow_id OR
               li.ref_n03 = l.journal_header_id)
           AND ROWNUM = 1) invoice_date,
       l.amount_cr amount_tax,
       (select to_char(ccc.times)
          from con_contract_cashflow ccc
         where ccc.cashflow_id = l.cashflow_id) times,
       h.journal_date due_date,
       l.amount_cr due_amount,
       l.amount_cr principal,
       0 interest,
       l.amount_cr vat_due_amount,
       null vat_rate,
       h.je_transaction_code,
       l.cashflow_id,
       'N' fixed_data_flag
  FROM hls_journal_header h, hls_journal_detail l, gld_accounts ga
 WHERE h.journal_header_id = l.journal_header_id
   and h.sbo_num is not null
   and h.total_amount_dr != 0
   and ga.account_id = l.account_id
   and l.line_description like '%本金%'
   and ga.account_code in ('2221001020', '2221001023', '2221001024')
   and h.reversed_flag = 'N'
   and h.post_gl_status = 'Y'
   and h.internal_period_num <= to_number(${@base_period})
   And exists
 (selEct 1
          from gld_je_template_hds hd
         where hd.je_template_hd_id = h.je_template_hd_id
           and hd.je_template_code in
               ('HL_CON_REPURCHASE_ET', 'HL_CON_REPURCHASE_ET_LB'))

union
SELECT h.period_year,
       h.period_num,
       h.internal_period_num,
       h.journal_date,
       h.sbo_num journal_num,
       l.reference2,
       l.line_description,
       h.journal_num journal_number,
       reference3 contract_number,
       reference1 bp_code,
       (SELECT BP_NAME
          FROM hls_bp_master
         WHERE bp_code = reference1
           AND ROWNUM = 1) bp_name,
       l.journal_header_id,
       (SELECT hi.invoice_date
          FROM acr_invoice_hd hi, acr_invoice_ln li
         WHERE hi.invoice_hd_id = li.invoice_hd_id
           AND hi.reversed_flag = 'N'
           AND (li.cashflow_id = l.cashflow_id OR
               li.ref_n03 = l.journal_header_id)
           AND ROWNUM = 1) invoice_date,
       l.amount_cr amount_tax,
       '解约回购本金' || (select ccc.times
                      from con_contract_cashflow ccc
                     where ccc.cashflow_id = l.cashflow_id) times,
       h.journal_date due_date,
       --decode(hd.je_template_code,
       --       'HL_CON_CHANGE_REPURCHASE_VAT',
       --       to_number(l.reference7),
       --       l.amount_cr) due_amount,
       to_number(l.reference7)-
       (select nvl(sum(cw.write_off_due_amount), 0)
          from csh_write_off cw, con_contract cc
         where cw.cashflow_id = l.cashflow_id
           and cw.contract_id = cc.contract_id
           and cw.write_off_date < cc.repurchase_date)  due_amount,
      -- decode(hd.je_template_code,
      --        'HL_CON_CHANGE_REPURCHASE_VAT',
      --        to_number(l.reference7),
      --        l.amount_cr) principal,
      to_number(l.reference7)-
       (select nvl(sum(cw.write_off_due_amount), 0)
          from csh_write_off cw, con_contract cc
         where cw.cashflow_id = l.cashflow_id
           and cw.contract_id = cc.contract_id
           and cw.write_off_date < cc.repurchase_date) principal,
       0 interest,
       l.amount_cr vat_due_amount,
       case
         when nvl(l.reference7 - abs(l.amount_cr), 0) != 0 then
          round(abs(l.amount_cr / (l.reference7 - abs(l.amount_cr))), 2)
         else
          null
       end vat_rate,
       h.je_transaction_code,
       l.cashflow_id,
       'N' fixed_data_flag
  FROM hls_journal_header  h,
       hls_journal_detail  l,
       gld_accounts        ga,
       gld_je_template_hds hd
 WHERE h.journal_header_id = l.journal_header_id
   and h.sbo_num is not null
   and h.total_amount_dr != 0
   and ga.account_id = l.account_id
   and l.line_description like '%本金%'
   and ga.account_code in ('2221001020', '2221001023', '2221001024')
   and h.reversed_flag = 'N'
   and h.post_gl_status = 'Y'
   and h.internal_period_num <= to_number(${@base_period})
   And hd.je_template_hd_id = h.je_template_hd_id
   and hd.je_template_code in
       ('HL_CON_CHANGE_REPURCHASE_VAT', 'HL_REPURCHASE_VAT')

        union
        SELECT h.period_year,
                h.period_num,
                h.internal_period_num,
                h.journal_date,
                h.sbo_num journal_num,
                l.reference2,
                l.line_description,
                h.journal_num journal_number,
                reference3 contract_number,
                reference1 bp_code,
                (SELECT BP_NAME
                   FROM hls_bp_master
                  WHERE bp_code = reference1
                    AND ROWNUM = 1) bp_name,
                l.journal_header_id,
                (SELECT hi.invoice_date
                   FROM acr_invoice_hd hi, acr_invoice_ln li
                  WHERE hi.invoice_hd_id = li.invoice_hd_id
                    AND hi.reversed_flag = 'N'
                    AND (li.cashflow_id = l.cashflow_id OR
                        li.ref_n03 = l.journal_header_id)
                    AND ROWNUM = 1) invoice_date,
                l.amount_cr amount_tax,
                '留购金' times,
                h.journal_date due_date,
                to_number(l.reference7) due_amount,
                null principal,
                to_number(l.reference7) interest,
                l.amount_cr vat_due_amount,
                case
                  when nvl(l.reference7 - abs(l.amount_cr), 0) != 0 then
                   round(abs(l.amount_cr / (l.reference7 - abs(l.amount_cr))),
                         2)
                  else
                   null
                end vat_rate,
                h.je_transaction_code,
                l.cashflow_id,
                'N' fixed_data_flag
          FROM hls_journal_header h, hls_journal_detail l, gld_accounts ga
         WHERE h.journal_header_id = l.journal_header_id
           and h.sbo_num is not null
           and h.total_amount_dr != 0
           and ga.account_id = l.account_id
           and l.line_description like '%留购金%'
           and ga.account_code in ('2221001020', '2221001023', '2221001024')
           and h.reversed_flag = 'N' and h.post_gl_status = 'Y'
           and h.internal_period_num <= to_number(${@base_period})
           And exists
         (selEct 1
                  from gld_je_template_hds hd
                 where hd.je_template_hd_id = h.je_template_hd_id
                   and hd.je_template_code in ('HL_CON_CHANGE_REPURCHASE_VAT'))

       union
        SELECT h.period_year,
                h.period_num,
                h.internal_period_num,
                h.journal_date,
                h.sbo_num journal_num,
                l.reference2,
                l.line_description,
                h.journal_num journal_number,
                reference3 contract_number,
                reference1 bp_code,
                (SELECT BP_NAME
                   FROM hls_bp_master
                  WHERE bp_code = reference1
                    AND ROWNUM = 1) bp_name,
                l.journal_header_id,
                (SELECT hi.invoice_date
                   FROM acr_invoice_hd hi, acr_invoice_ln li
                  WHERE hi.invoice_hd_id = li.invoice_hd_id
                    AND hi.reversed_flag = 'N'
                    AND (li.cashflow_id = l.cashflow_id OR
                        li.ref_n03 = l.journal_header_id)
                    AND ROWNUM = 1) invoice_date,
                l.amount_cr amount_tax,
                '增值税调差' times,
                h.journal_date due_date,
                0 due_amount,
                0 principal,
                0 interest,
                l.amount_cr vat_due_amount,
                null vat_rate,
                h.je_transaction_code,
                l.cashflow_id,
                'N' fixed_data_flag
          FROM hls_journal_header h, hls_journal_detail l, gld_accounts ga
         WHERE h.journal_header_id = l.journal_header_id
           and h.sbo_num is not null
           --and h.total_amount_dr != 0
           and ga.account_id = l.account_id
           --and l.line_description like '%留购金%'
           and ga.account_code in ('2221001020', '2221001023', '2221001024','2221001026')
           and h.reversed_flag = 'N' and h.post_gl_status = 'Y'
           and h.internal_period_num <= to_number(${@base_period})
           And exists
         (selEct 1
                  from gld_je_template_hds hd
                 where hd.je_template_hd_id = h.je_template_hd_id
                   and hd.je_template_code in ('HL_TAX_ADJUST'))
        union
        SELECT h.period_year,
                h.period_num,
                h.internal_period_num,
                h.journal_date,
                h.sbo_num journal_num,
                l.reference2,
                l.line_description,
                h.journal_num journal_number,
                reference3 contract_number,
                reference1 bp_code,
                (SELECT BP_NAME
                   FROM hls_bp_master
                  WHERE bp_code = reference1
                    AND ROWNUM = 1) bp_name,
                l.journal_header_id,
                (SELECT hi.invoice_date
                   FROM acr_invoice_hd hi, acr_invoice_ln li
                  WHERE hi.invoice_hd_id = li.invoice_hd_id
                    AND hi.reversed_flag = 'N'
                    AND (li.cashflow_id = l.cashflow_id OR
                        li.ref_n03 = l.journal_header_id)
                    AND ROWNUM = 1) invoice_date,
                -1 * abs(l.amount_cr) amount_tax,
                (select decode(ccc.cf_item, 8, '留购价',200,'提前结清租金',11,'提前结清手续费' , to_char(ccc.times))
                   from con_contract_cashflow ccc
                  where ccc.cashflow_id = l.cashflow_id) times,
                (select ccc.due_date
                   from con_contract_cashflow ccc
                  where ccc.cashflow_id = l.cashflow_id) due_date,

                -1 * (select case
                               when ccc.cf_item = 1 and
                                    cc.business_type = 'LEASEBACK' then
                                    nvl(ccc.interest, 0)
                               when ccc.cf_item = 1 and
                                    cc.business_type != 'LEASEBACK' then
                                    decode(hd.je_template_code,'HL_REPURCHASE_VAT_REVERSE',
                                      ccc.due_amount
                                   -  (select nvl(sum(cw.write_off_due_amount), 0)
                                      from csh_write_off cw, con_contract cc
                                     where cw.cashflow_id = l.cashflow_id
                                       and cw.contract_id = cc.contract_id
                                       and cw.write_off_date < cc.repurchase_date),
                                       ccc.due_amount
                                    )
                               else
                                ccc.due_amount
                             end
                        from con_contract cc, con_contract_cashflow ccc
                       where ccc.cashflow_id = l.cashflow_id
                         and ccc.contract_id = cc.contract_id) due_amount,
                -1 * (select case
                               when ccc.cf_item = 1 and
                                    cc.business_type = 'LEASEBACK' then
                                0
                               when ccc.cf_item = 1 and
                                    cc.business_type != 'LEASEBACK' then
                                    decode(hd.je_template_code,'HL_REPURCHASE_VAT_REVERSE',
                                       nvl(ccc.principal, 0)
                                   -  (select nvl(sum(cw.write_off_principal), 0)
                                      from csh_write_off cw, con_contract cc
                                     where cw.cashflow_id = l.cashflow_id
                                       and cw.contract_id = cc.contract_id
                                       and cw.write_off_date < cc.repurchase_date),
                                        nvl(ccc.principal, 0)
                                    )

                               else
                                ccc.principal
                             end
                        from con_contract cc, con_contract_cashflow ccc
                       where ccc.cashflow_id = l.cashflow_id
                         and ccc.contract_id = cc.contract_id) principal,
                -1 * (select case
                               when ccc.cf_item = 1 then

                                    decode(hd.je_template_code,'HL_REPURCHASE_VAT_REVERSE',
                                       nvl(ccc.interest, 0)
                                   -  (select nvl(sum(cw.write_off_interest), 0)
                                      from csh_write_off cw, con_contract cc
                                     where cw.cashflow_id = l.cashflow_id
                                       and cw.contract_id = cc.contract_id
                                       and cw.write_off_date < cc.repurchase_date),
                                        nvl(ccc.interest, 0)
                                    )
                               else
                                ccc.due_amount
                             end
                        from con_contract cc, con_contract_cashflow ccc
                       where ccc.cashflow_id = l.cashflow_id
                         and ccc.contract_id = cc.contract_id) interest,
                -1 * abs(l.amount_cr) vat_due_amount,
                (select case
                          when ccc.cf_item = 1 and
                               cc.business_type = 'LEASEBACK' and
                               nvl(ccc.interest - ccc.vat_interest, 0) != 0 then
                           round(ccc.vat_interest /
                                 (ccc.interest - ccc.vat_interest),
                                 2)

                          when ((ccc.cf_item = 1 and
                               cc.business_type != 'LEASEBACK') or
                               ccc.cf_item != 1) and
                               nvl(ccc.due_amount - ccc.vat_due_amount, 0) != 0 then
                           round(ccc.vat_due_amount /
                                 (ccc.due_amount - ccc.vat_due_amount),
                                 2)
                          else
                           null
                        end
                   from con_contract cc, con_contract_cashflow ccc
                  where ccc.cashflow_id = l.cashflow_id
                    and cc.contract_id = ccc.contract_id) vat_rate,
                h.je_transaction_code,
                l.cashflow_id,
                'Y' fixed_data_flag
          FROM hls_journal_header h, hls_journal_detail l, gld_accounts ga,gld_je_template_hds hd
         WHERE h.journal_header_id = l.journal_header_id
           and h.sbo_num is not null
           and h.total_amount_dr != 0
           and ga.account_id = l.account_id
           and ga.account_code in ('2221001020', '2221001023', '2221001024')
           and h.reversed_flag = 'N' and h.post_gl_status = 'Y'
           and h.internal_period_num <= to_number(${@base_period})
           and  hd.je_template_hd_id = h.je_template_hd_id
           and hd.je_template_code in
                       ('HL_CON_VAT_REVERSE_REPURCHASE',
                        'HL_VAT_REVERSE_REPURCHASE_LB',
                        'HL_CON_VAT_REVERSE_ET',
                        'HL_CON_VAT_REVERSE_ET_LB',
                        'HL_REPURCHASE_VAT_REVERSE')
union
      SELECT h.period_year,
                h.period_num,
                h.internal_period_num,
                h.journal_date,
                h.sbo_num journal_num,
                l.reference2,
                l.line_description,
                h.journal_num journal_number,
                reference3 contract_number,
                reference1 bp_code,
                (SELECT BP_NAME
                   FROM hls_bp_master
                  WHERE bp_code = reference1
                    AND ROWNUM = 1) bp_name,
                l.journal_header_id,
                (SELECT hi.invoice_date
                   FROM acr_invoice_hd hi, acr_invoice_ln li
                  WHERE hi.invoice_hd_id = li.invoice_hd_id
                    AND hi.reversed_flag = 'N'
                    AND (li.cashflow_id = l.cashflow_id OR
                        li.ref_n03 = l.journal_header_id)
                    AND ROWNUM = 1) invoice_date,
                (select
          round(l.reference7/(1+cc.vat_rate_of_interest)*cc.vat_rate_of_interest,2)
       from
       con_contract cc where
       cc.contract_number=l.reference3
      and cc.data_class='NORMAL') amount_tax,
      (select
        to_char(ccc.times)
       from
       con_contract_cashflow ccc where
       ccc.cashflow_id=l.cashflow_id) times,
       h.journal_date due_date,
       to_number(l.reference7) due_amount,
       null principal,
       to_number(l.reference7) interest,
       (select
          round(l.reference7/(1+cc.vat_rate_of_interest)*cc.vat_rate_of_interest,2)
       from
       con_contract cc where
       cc.contract_number=l.reference3
      and cc.data_class='NORMAL') vat_due_amount,
       (select
         cc.vat_rate_of_interest
       from
       con_contract cc where
       cc.contract_number=l.reference3
      and cc.data_class='NORMAL') vat_rate,
                h.je_transaction_code,
                l.cashflow_id,
                'Y' fixed_data_flag
          FROM hls_journal_header h, hls_journal_detail l, gld_accounts ga,gld_je_template_hds hd
         WHERE h.journal_header_id = l.journal_header_id
           and h.sbo_num is not null
           and h.total_amount_dr != 0
           and ga.account_id = l.account_id
           and ga.account_code in ('2221001020', '2221001023', '2221001024')
           and h.reversed_flag = 'N' and h.post_gl_status = 'Y'
           and h.internal_period_num <= to_number(${@base_period})
           and  hd.je_template_hd_id = h.je_template_hd_id
           and hd.je_template_code='HL_REPURCHASE_INTEREST_REVERSE'
           and l.reference6 is not null
                        ) t1
                         #WHERE_CLAUSE#
)
select * from (
select '合计' tax_month,
       sum(sale_amount) sale_amount,
       1 order_seq,
       null period_year,
       null period_num,
       null internal_period_num,
       null journal_date,
       null journal_num,
       null reference2,
       null line_description,
       null journal_number,
       null contract_number,
       null bp_code,
       null bp_name,
       null journal_header_id,
       null invoice_date,
       sum(amount_tax) amount_tax,
       null times,
       null due_date,
       sum(decode((select cc.business_type
                from con_contract_cashflow ca, con_contract cc
               where ca.cashflow_id = t.cashflow_id
                 and ca.contract_id = cc.contract_id),
              'LEASEBACK',
              due_amount -nvl(t.principal,0),
              t.due_amount)) due_amount,
       sum(decode((select cc.business_type
                    from con_contract_cashflow ca, con_contract cc
                   where ca.cashflow_id = t.cashflow_id
                     and ca.contract_id = cc.contract_id),
                  'LEASEBACK',
                  0,
                  t.principal)) principal,
       sum(interest) interest,
       sum(vat_due_amount) vat_due_amount,
       null vat_rate,
       null je_transaction_code,
       null cashflow_id,
       null fixed_data_flag
  from temp t
union
select tax_month,
       sale_amount,
       order_seq,
       period_year,
       period_num,
       internal_period_num,
       journal_date,
       journal_num,
       reference2,
       line_description,
       journal_number,
       contract_number,
       bp_code,
       bp_name,
       journal_header_id,
       invoice_date,
       amount_tax,
       times,
       due_date,
       decode((select cc.business_type
                from con_contract_cashflow ca, con_contract cc
               where ca.cashflow_id = t.cashflow_id
                 and ca.contract_id = cc.contract_id),
              'LEASEBACK',
              due_amount -nvl(t.principal,0),
              t.due_amount) due_amount,
       decode((select cc.business_type
                from con_contract_cashflow ca, con_contract cc
               where ca.cashflow_id = t.cashflow_id
                 and ca.contract_id = cc.contract_id),
              'LEASEBACK',
              0,
              t.principal) principal,
       interest,
       vat_due_amount,
       vat_rate,
       je_transaction_code,
       cashflow_id,
       fixed_data_flag
  from temp t

) data order by data.order_seq
   ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:data-filters>
<!--        <bm:data-filter enforceOperations="query" expression="(t1.amount_tax > 0 or t1.fixed_data_flag='Y')"/>-->
        <bm:data-filter enforceOperations="query" expression="(exists(select 1 from con_contract cc where cc.contract_number=t1.contract_number and cc.contract_status!='CANCEL' and cc.data_class='NORMAL') or t1.fixed_data_flag='Y')"/>
    </bm:data-filters>
    <bm:query-fields>
        <bm:query-field name="contract_number" queryExpression="t1.contract_number = ${@contract_number}"/>
        <bm:query-field name="bp_name" queryExpression="t1.bp_name = ${@bp_name}"/>
        <bm:query-field name="journal_date" queryExpression="to_char(t1.journal_date,'yyyy-mm-dd') = ${@journal_date}"/>
        <bm:query-field name="base_period"
                        queryExpression="to_number(to_char(t1.journal_date,'yyyymm')) &lt;= to_number(${@base_period})"/>
        <bm:query-field name="invoice_date" queryExpression="to_char(t1.invoice_date,'yyyy-mm-dd') = ${@invoice_date}"/>
        <!--        <bm:query-field name="vat_6_flag" queryExpression="((${@vat_6_flag}='Y' and t1.vat_rate=0.06) or (${@vat_6_flag}!='Y' and nvl(t1.vat_rate,0)!=0.06))"/>-->
        <bm:query-field name="journal_num" queryExpression="t1.journal_num = ${@journal_num}"/>
        <bm:query-field name="journal_number" queryExpression="t1.journal_number = ${@journal_number}"/>
        <bm:query-field name="bp_code" queryExpression="t1.bp_code = ${@bp_code}"/>
        <bm:query-field name="internal_period_num" queryExpression="t1.internal_period_num = ${@internal_period_num}"/>
        <bm:query-field name="journal_date_f" queryExpression="to_char(t1.journal_date,'yyyy-mm-dd') &gt;= ${@journal_date_f}"/>
        <bm:query-field name="journal_date_t" queryExpression="to_char(t1.journal_date,'yyyy-mm-dd') &lt;= ${@journal_date_t}"/>
    </bm:query-fields>
</bm:model>
