<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: wangwei
    $Date: 2023-3-16 下午2:20:47
    $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[
            SELECT * FROM (
SELECT h.period_year,
       h.period_num,
       h.internal_period_num,
       h.journal_date,
       h.sbo_num journal_num,
       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-nvl((select sum(al.tax_amount) from  acr_invoice_hd ah,acr_invoice_ln al where ah.contract_id=h.source_id
       and ah.invoice_hd_id=al.invoice_hd_id
       and ah.reversed_flag='N'
       and ah.invoice_status='CONFIRM'
       and al.cf_item = 2
      and ah.invoice_kind in ('0', '2')
      AND  to_char(ah.invoice_date,'yyyymm') <= ${@end_period}
      and ah.invoice_status = 'CONFIRM'),0) 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-nvl((select sum(al.tax_amount) from  acr_invoice_hd ah,acr_invoice_ln al where ah.contract_id=h.source_id
       and ah.invoice_hd_id=al.invoice_hd_id
       and ah.reversed_flag='N'
       and ah.invoice_status='CONFIRM'
       and al.cf_item = 2
       AND  to_char(ah.invoice_date,'yyyymm') < =${@end_period}
      and ah.invoice_kind in ('0', '2')
      AND ah.vat_interface_status = 'BACK'
      and ah.invoice_status = 'CONFIRM'),0) 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.internal_period_num<to_number(${@start_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')
   AND  not EXISTS (SELECT 1
          FROM acr_invoice_hd hi, acr_invoice_ln li
         WHERE hi.invoice_hd_id = li.invoice_hd_id
           AND hi.vat_interface_status = 'BACK'
           and hi.invoice_status='CONFIRM'
           AND  to_char(hi.invoice_date,'yyyymm') <= ${@end_period}
           AND hi.contract_id=h.source_id
           and li.cf_item=2 )
union
SELECT h.period_year,
       h.period_num,
       h.internal_period_num,
       h.journal_date,
       h.sbo_num journal_num,
       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-nvl((select sum(al.tax_amount) from  acr_invoice_hd ah,acr_invoice_ln al where
     ah.invoice_hd_id=al.invoice_hd_id
       and ah.reversed_flag='N'
       and al.cashflow_id=l.cashflow_id
      and ah.invoice_kind in ('0', '2')
      AND  to_char(ah.invoice_date,'yyyymm') <= ${@end_period}
      and ah.invoice_status = 'CONFIRM'),0) 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 case
                 when ccc.cf_item = 1 and cc.business_type = 'LEASEBACK' then
                  nvl(ccc.interest, 0) -
                  nvl((select sum(al.tax_amount)
                        from acr_invoice_hd ah, acr_invoice_ln al
                       where ah.invoice_hd_id = al.invoice_hd_id
                         and ah.reversed_flag = 'N'
                         and al.cashflow_id = l.cashflow_id
                         and ah.invoice_kind in ('0', '2')
                         AND to_char(ah.invoice_date, 'yyyymm') <= ${@end_period}
                         and ah.invoice_status = 'CONFIRM'),
                      0)
                 when ccc.cf_item = 1 and cc.business_type != 'LEASEBACK' then
                  ccc.due_amount -
                  nvl((select sum(al.total_amount)
                        from acr_invoice_hd ah, acr_invoice_ln al
                       where ah.invoice_hd_id = al.invoice_hd_id
                         and ah.reversed_flag = 'N'
                         and al.cashflow_id = l.cashflow_id
                         and ah.invoice_kind in ('0', '2')
                         AND to_char(ah.invoice_date, 'yyyymm') <= ${@end_period}
                         and ah.invoice_status = 'CONFIRM'),
                      0)
                 else
                  ccc.due_amount -
                  nvl((select sum(al.total_amount)
                        from acr_invoice_hd ah, acr_invoice_ln al
                       where ah.invoice_hd_id = al.invoice_hd_id
                         and ah.reversed_flag = 'N'
                         and al.cashflow_id = l.cashflow_id
                         and ah.invoice_kind in ('0', '2')
                         AND to_char(ah.invoice_date, 'yyyymm') <= ${@end_period}
                         and ah.invoice_status = 'CONFIRM'),
                      0)
               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,
       (select case
                 when ccc.cf_item = 1 and cc.business_type = 'LEASEBACK' then
                  0
                 when ccc.cf_item = 1 and cc.business_type != 'LEASEBACK' and
                      nvl((select sum(al.total_amount)
                            from acr_invoice_hd ah, acr_invoice_ln al
                           where ah.invoice_hd_id = al.invoice_hd_id
                             and ah.reversed_flag = 'N'
                             and al.cashflow_id = l.cashflow_id
                             and ah.invoice_kind in ('0', '2')
                             AND to_char(ah.invoice_date, 'yyyymm') <=
                                 ${@end_period}
                             and ah.invoice_status = 'CONFIRM'),
                          0) > nvl(ccc.interest,0) then
                  ccc.due_amount-nvl((select sum(al.total_amount)
                            from acr_invoice_hd ah, acr_invoice_ln al
                           where ah.invoice_hd_id = al.invoice_hd_id
                             and ah.reversed_flag = 'N'
                             and al.cashflow_id = l.cashflow_id
                             and ah.invoice_kind in ('0', '2')
                             AND to_char(ah.invoice_date, 'yyyymm') <=
                                 ${@end_period}
                             and ah.invoice_status = 'CONFIRM'),
                          0)
                 when ccc.cf_item = 1 and cc.business_type != 'LEASEBACK' and
                      nvl((select sum(al.total_amount)
                            from acr_invoice_hd ah, acr_invoice_ln al
                           where ah.invoice_hd_id = al.invoice_hd_id
                             and ah.reversed_flag = 'N'
                             and al.cashflow_id = l.cashflow_id
                             and ah.invoice_kind in ('0', '2')
                             AND to_char(ah.invoice_date, 'yyyymm') <=
                                 ${@end_period}
                             and ah.invoice_status = 'CONFIRM'),
                          0) <=nvl(ccc.interest,0) then
                  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,
       (select case
                 when ccc.cf_item = 1 and cc.business_type = 'LEASEBACK' then
                  nvl(ccc.interest, 0) -
                  nvl((select sum(al.tax_amount)
                        from acr_invoice_hd ah, acr_invoice_ln al
                       where ah.invoice_hd_id = al.invoice_hd_id
                         and ah.reversed_flag = 'N'
                         and al.cashflow_id = l.cashflow_id
                         and ah.invoice_kind in ('0', '2')
                         AND to_char(ah.invoice_date, 'yyyymm') <= ${@end_period}
                         and ah.invoice_status = 'CONFIRM'),
                      0)
                 when ccc.cf_item = 1 and cc.business_type != 'LEASEBACK' and
                      nvl((select sum(al.total_amount)
                            from acr_invoice_hd ah, acr_invoice_ln al
                           where ah.invoice_hd_id = al.invoice_hd_id
                             and ah.reversed_flag = 'N'
                             and al.cashflow_id = l.cashflow_id
                             and ah.invoice_kind in ('0', '2')
                             AND to_char(ah.invoice_date, 'yyyymm') <=
                                 ${@end_period}
                             and ah.invoice_status = 'CONFIRM'),
                          0) >nvl(ccc.interest,0) then
                  0
                 when ccc.cf_item = 1 and cc.business_type != 'LEASEBACK' and
                      nvl((select sum(al.total_amount)
                            from acr_invoice_hd ah, acr_invoice_ln al
                           where ah.invoice_hd_id = al.invoice_hd_id
                             and ah.reversed_flag = 'N'
                             and al.cashflow_id = l.cashflow_id
                             and ah.invoice_kind in ('0', '2')
                             AND to_char(ah.invoice_date, 'yyyymm') <=
                                 ${@end_period}
                             and ah.invoice_status = 'CONFIRM'),
                          0) <= nvl(ccc.interest,0) then
                  nvl(ccc.interest, 0)-nvl((select sum(al.total_amount)
                            from acr_invoice_hd ah, acr_invoice_ln al
                           where ah.invoice_hd_id = al.invoice_hd_id
                             and ah.reversed_flag = 'N'
                             and al.cashflow_id = l.cashflow_id
                             and ah.invoice_kind in ('0', '2')
                             AND to_char(ah.invoice_date, 'yyyymm') <=
                                 ${@end_period}
                             and ah.invoice_status = 'CONFIRM'),
                          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,
      l.amount_cr-nvl((select sum(al.tax_amount) from  acr_invoice_hd ah,acr_invoice_ln al where
     ah.invoice_hd_id=al.invoice_hd_id
       and ah.reversed_flag='N'
       and al.cashflow_id=l.cashflow_id
      and ah.invoice_kind in ('0', '2')
      AND  to_char(ah.invoice_date,'yyyymm') < =${@end_period}
      and ah.invoice_status = 'CONFIRM'),0) 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.internal_period_num<to_number(${@start_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'))))
   AND  ( not EXISTS (SELECT 1
          FROM acr_invoice_hd hi, acr_invoice_ln li
         WHERE hi.invoice_hd_id = li.invoice_hd_id
           AND hi.vat_interface_status = 'BACK'
           and hi.invoice_status='CONFIRM'
           AND  to_char(hi.invoice_date,'yyyymm') <= ${@end_period}
           and li.cashflow_id=l.cashflow_id )
           or
           --日立返还金额外判断
           exists( SELECT 1
          FROM con_contract_cashflow ccc
         WHERE
            ccc.cashflow_id=l.cashflow_id
           and ccc.cf_item=1
           and acr_invoice_pkg.check_rl_flag(ccc.contract_id) = 'Y' and
                      acr_invoice_pkg.get_rl_amount(ccc.contract_id,
                                                     1,
                                                    ccc.times) > 0
           ))
   union
      SELECT h.period_year,
       h.period_num,
       h.internal_period_num,
       h.journal_date,
       h.sbo_num journal_num,
       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 -
       nvl((select sum(al.tax_amount)
             from acr_invoice_hd ah, acr_invoice_ln al
            where ah.contract_id = h.source_id
              and ah.invoice_hd_id = al.invoice_hd_id
              and ah.reversed_flag = 'N'
              and al.ref_n03 = l.journal_header_id
              and al.ref_n04 = l.journal_line_id
              and ah.invoice_kind in ('0', '2')
              AND to_char(ah.invoice_date, 'yyyymm') <= ${@end_period}
              and ah.invoice_status = 'CONFIRM'),
           0),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') 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'))
           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') 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'))
           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 -
       nvl((select sum(al.tax_amount)
             from acr_invoice_hd ah, acr_invoice_ln al
            where ah.contract_id = h.source_id
              and ah.invoice_hd_id = al.invoice_hd_id
              and ah.reversed_flag = 'N'
              and al.ref_n03 = l.journal_header_id
              and al.ref_n04 = l.journal_line_id
              and ah.invoice_kind in ('0', '2')
              AND to_char(ah.invoice_date, 'yyyymm') <= ${@end_period}
              and ah.invoice_status = 'CONFIRM'),
           0),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.internal_period_num < to_number(${@start_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'))))
   AND not EXISTS
 (SELECT 1
          FROM acr_invoice_hd hi, acr_invoice_ln li
         WHERE hi.invoice_hd_id = li.invoice_hd_id
           AND hi.vat_interface_status = 'BACK'
           and hi.invoice_status = 'CONFIRM'
           AND to_char(hi.invoice_date, 'yyyymm') < =${@end_period}
           and li.ref_n03 = l.journal_header_id
           and li.ref_n04 = l.journal_line_id)
  union
SELECT h.period_year,
       h.period_num,
       h.internal_period_num,
       h.journal_date,
       h.sbo_num journal_num,
       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-nvl((select sum(al.tax_amount) from  acr_invoice_hd ah,acr_invoice_ln al where
     ah.invoice_hd_id=al.invoice_hd_id
       and ah.reversed_flag='N'
       and al.cashflow_id=l.cashflow_id
      and ah.invoice_kind in ('0', '2')
      AND  to_char(ah.invoice_date,'yyyymm') < =${@end_period}
      and ah.invoice_status = 'CONFIRM'),0) 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
      case when ccc.cf_item=200 then
      ccc.due_amount-nvl((select sum(al.total_amount) from  acr_invoice_hd ah,acr_invoice_ln al where
     ah.invoice_hd_id=al.invoice_hd_id
       and ah.reversed_flag='N'
       and al.cashflow_id=l.cashflow_id
      and ah.invoice_kind in ('0', '2')
      AND  to_char(ah.invoice_date,'yyyymm') < =${@end_period}
      and ah.invoice_status = 'CONFIRM'),0)
      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.due_amount-nvl((select sum(al.total_amount) from  acr_invoice_hd ah,acr_invoice_ln al where
     ah.invoice_hd_id=al.invoice_hd_id
       and ah.reversed_flag='N'
       and al.cashflow_id=l.cashflow_id
      and ah.invoice_kind in ('0', '2')
      AND  to_char(ah.invoice_date,'yyyymm') < =${@end_period}
      and ah.invoice_status = 'CONFIRM'),0)
      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-nvl((select sum(al.tax_amount) from  acr_invoice_hd ah,acr_invoice_ln al where
     ah.invoice_hd_id=al.invoice_hd_id
       and ah.reversed_flag='N'
       and al.cashflow_id=l.cashflow_id
      and ah.invoice_kind in ('0', '2')
      AND  to_char(ah.invoice_date,'yyyymm') <= ${@end_period}
      and ah.invoice_status = 'CONFIRM'),0) vat_due_amount,
       (select
       case when nvl(ccc.interest,0)!=0 then
       round(ccc.vat_interest/ccc.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.internal_period_num<to_number(${@start_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'))))
   AND  ( not EXISTS (SELECT 1
          FROM acr_invoice_hd hi, acr_invoice_ln li
         WHERE hi.invoice_hd_id = li.invoice_hd_id
           AND hi.vat_interface_status = 'BACK'
           and hi.invoice_status='CONFIRM'
           AND  to_char(hi.invoice_date,'yyyymm') <= ${@end_period}
           and li.cashflow_id=l.cashflow_id )
           or
           --日立返还金额外判断
           exists( SELECT 1
          FROM con_contract_cashflow ccc
         WHERE
            ccc.cashflow_id=l.cashflow_id
           and ccc.cf_item=200
           and acr_invoice_pkg.check_rl_flag(ccc.contract_id) = 'Y' and
                      acr_invoice_pkg.get_rl_amount(ccc.contract_id,
                                                     200,
                                                    ccc.times) > 0
           ))
union
SELECT h.period_year,
       h.period_num,
       h.internal_period_num,
       h.journal_date,
       h.sbo_num journal_num,
       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.internal_period_num<to_number(${@start_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_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'))))
   AND    not EXISTS (SELECT 1
          FROM acr_invoice_hd hi, acr_invoice_ln li
         WHERE hi.invoice_hd_id = li.invoice_hd_id
           AND hi.vat_interface_status = 'BACK'
           and hi.invoice_status='CONFIRM'
           AND  to_char(hi.invoice_date,'yyyymm') <= ${@end_period}
           and li.cashflow_id=l.cashflow_id )

   union
 SELECT h.period_year,
              h.period_num,
              h.internal_period_num,
              h.journal_date,
              h.sbo_num journal_num,
              t.contract_number,
              (SELECT bp.bp_code
                 FROM hls_bp_master bp, hls_journal_detail d
                WHERE bp.bp_code = d.reference1
                  and d.journal_header_id = h.journal_header_id
                  AND ROWNUM = 1) bp_code,
              (SELECT bp.bp_name
                 FROM hls_bp_master bp, hls_journal_detail d
                WHERE bp.bp_code = d.reference1
                  and d.journal_header_id = h.journal_header_id
                  AND ROWNUM = 1) bp_name,
              h.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 = t.cashflow_id OR
                      li.ref_n03 = h.journal_header_id)
                  AND ROWNUM = 1) invoice_date,
              t.un_bill_vat_principal amount_tax,
              '解约回购本金' || t.times times,
              h.journal_date due_date,
              t.un_bill_principal due_amount,
              t.un_bill_principal principal,
              null interest,
              t.un_bill_vat_principal vat_due_amount,
              t.tax_type_rate vat_rate,
              h.je_transaction_code,
              t.cashflow_id,
              'N' fixed_data_flag
         FROM hls_journal_header h, VAT_ACCOUNT_250_PRIN_TMP t
        WHERE h.journal_header_id = t.journal_header_id
          and floor(t.session_id/10)=${/session/@session_id}
          and nvl(t.un_bill_principal,0)>0
          and h.internal_period_num < to_number(${@start_period})
  union
     SELECT h.period_year,
       h.period_num,
       h.internal_period_num,
       h.journal_date,
       h.sbo_num journal_num,
       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.internal_period_num<to_number(${@start_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'))

   AND   EXISTS (SELECT 1
          FROM con_contract cc,con_contract_cashflow ccc
           where cc.contract_number=l.reference3
          and cc.data_class='NORMAL'
      and cc.contract_id=ccc.contract_id
          and ccc.cf_item=250
          and ccc.cf_status='RELEASE'
          and ccc.cf_direction='INFLOW'
          and ccc.billing_status!='FULL' )
   union
     SELECT h.period_year,
       h.period_num,
       h.internal_period_num,
       h.journal_date,
       h.journal_num,
       h.contract_number,
       h.bp_code,
       (select bp.bp_name from hls_bp_master bp where bp.bp_code=h.bp_code) bp_name,
        null journal_header_id,
       h.invoice_date,
       h.amount_tax,
       h.times,
      h.due_date,
      h.due_amount,
      h.principal,
      h.interest,
      h.vat_due_amount,
      h.vat_rate,
      null je_transaction_code,
      null cashflow_id,
      'Y' fixed_data_flag
    from hl_cm_rpt555_fixed_data  h
    union
    SELECT h.period_year,
       h.period_num,
       h.internal_period_num,
       h.journal_date,
       h.sbo_num journal_num,
       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,
                                   '留购价',
                                   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
                  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
                 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
                  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
 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.internal_period_num <to_number(${@start_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_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,
       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,
      null 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
 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.internal_period_num <to_number(${@start_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='HL_REPURCHASE_INTEREST_REVERSE')
and l.reference6 is not null
AND    not EXISTS (SELECT 1
          FROM acr_invoice_hd hi, acr_invoice_ln li
         WHERE hi.invoice_hd_id = li.invoice_hd_id
           AND hi.vat_interface_status = 'BACK'
           and hi.invoice_status='CONFIRM'
           AND  to_char(hi.invoice_date,'yyyymm') <= ${@end_period}
           and li.cashflow_id=l.cashflow_id )
 ) t1
        #WHERE_CLAUSE#
   ]]></bm:query-sql>
        </bm:operation>
        <bm:operation name="execute">
            <bm:parameters>
                <bm:parameter name="start_period" output="true" outputPath="@start_period"/>
                <bm:parameter name="end_period" output="true" outputPath="@end_period"/>
            </bm:parameters>
            <bm:update-sql><![CDATA[
                begin
                hl_cm_vat_report_pkg.get_period_quarter(p_base_period => ${@base_period},
                p_start_period => ${@start_period},
                p_end_period => ${@end_period});
                end;
            ]]></bm:update-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-fields>
</bm:model>
