<?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[
            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,
       SUM(decode(reference6, NULL, 0, NVL(l.amount_cr,0))) amount_tax,
       DECODE(h.je_transaction_code,
              'HL_CSH_CONSOLIDATION_DALAY',
              '违约金',
              'HL_SALES_TAX_ACT_NEW',
              (SELECT DECODE(cf_item,
                             2,
                             '首付款',
                             8,
                             '留购价',
                             1,
                             times,
                             200,
                             '提前结清款',
                             250,
                             '回购款',
                             0,
                             '设备款')
                 FROM con_contract_cashflow
                WHERE cashflow_id = l.cashflow_id)) times,
       DECODE(h.je_transaction_code,
              'HL_CSH_CONSOLIDATION_DALAY',
              h.journal_date,
              'HL_SALES_TAX_ACT_NEW',
              (SELECT due_date
                 FROM con_contract_cashflow
                WHERE cashflow_id = l.cashflow_id)) due_date,
       DECODE(h.je_transaction_code,
              'HL_CSH_CONSOLIDATION_DALAY',
               SUM(decode(reference6, NULL, nvl(l.amount_dr,0), 0)),
              (SELECT due_amount
                 FROM con_contract_cashflow
                WHERE cashflow_id = l.cashflow_id)) due_amount,
       (SELECT principal
          FROM con_contract_cashflow
         WHERE cashflow_id = l.cashflow_id) principal,
       DECODE(h.je_transaction_code,
              'HL_CSH_CONSOLIDATION_DALAY',
              SUM(decode(reference6, NULL, l.amount_dr, 0)),
              'HL_SALES_TAX_ACT_NEW',
              (SELECT decode(cf_item, 8, due_amount, interest)
                 FROM con_contract_cashflow
                WHERE cashflow_id = l.cashflow_id)) interest,
       DECODE(h.je_transaction_code,
              'HL_CSH_CONSOLIDATION_DALAY',
              SUM(decode(reference6, NULL, 0, NVL(l.amount_cr,0))),
              'HL_SALES_TAX_ACT_NEW',
              (SELECT vat_due_amount
                 FROM con_contract_cashflow
                WHERE cashflow_id = l.cashflow_id)) vat_due_amount,
       nvl(ROUND((DECODE(h.je_transaction_code,
                         'HL_CSH_CONSOLIDATION_DALAY',
                         SUM(decode(reference6, NULL, 0, NVL(l.amount_cr,0))),
                         'HL_SALES_TAX_ACT_NEW',
                         (SELECT vat_due_amount
                            FROM con_contract_cashflow
                           WHERE cashflow_id = l.cashflow_id))) /
                 (DECODE(h.je_transaction_code,
                         'HL_CSH_CONSOLIDATION_DALAY',
                         SUM(decode(reference6, NULL, l.amount_dr, 0)),
                         'HL_SALES_TAX_ACT_NEW',
                         (SELECT due_amount
                            FROM con_contract_cashflow
                           WHERE cashflow_id = l.cashflow_id
                             AND due_amount > 0)) -
                 DECODE(h.je_transaction_code,
                         'HL_CSH_CONSOLIDATION_DALAY',
                         SUM(decode(reference6, NULL, 0, NVL(l.amount_cr,0))),
                         'HL_SALES_TAX_ACT_NEW',
                         (SELECT vat_due_amount
                            FROM con_contract_cashflow
                           WHERE cashflow_id = l.cashflow_id
                             AND vat_due_amount > 0))),
                 2) * 100,
           0) || '%' vat_rate,
           h.je_transaction_code
  FROM hls_journal_header h, hls_journal_detail l
 WHERE h.journal_header_id = l.journal_header_id
   and h.sbo_num is not null
   AND h.je_transaction_code ='HL_SALES_TAX_ACT_NEW'
   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  to_char(hi.invoice_date,'yyyymm') < ${@base_period}
           AND (li.cashflow_id = l.cashflow_id OR
               li.ref_n04 = l.journal_line_id))
 GROUP BY l.reference1,
          reference3,
          reference4,
          h.sbo_num,
          l.cashflow_id,
          l.journal_header_id,
          h.je_transaction_code,
          h.period_year,
          h.period_num,
          h.internal_period_num,
          h.journal_date

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 hi.vat_interface_status = 'BACK'
                  AND li.cf_item = 2
                  and hi.contract_id = l.source_doc_id
                  AND ROWNUM = 1) invoice_date,
       l.amount_dr amount_tax,
      '首付款' times,

              (SELECT due_date
                 FROM con_contract_cashflow
                WHERE contract_id = l.source_doc_id
                  AND cf_item = 2
                  AND ROWNUM = 1) due_date,

              (SELECT due_amount
                 FROM con_contract_cashflow
                WHERE contract_id = l.source_doc_id
                  AND cf_item = 2
                  AND ROWNUM = 1)
           due_amount,

              (SELECT due_amount
                 FROM con_contract_cashflow
                WHERE contract_id = l.source_doc_id
                  AND cf_item = 2
                  AND ROWNUM = 1) principal,

              (SELECT interest
                 FROM con_contract_cashflow
                WHERE contract_id = l.source_doc_id
                  AND cf_item = 2
                  AND ROWNUM = 1) interest,

              (SELECT vat_due_amount
                 FROM con_contract_cashflow
                WHERE contract_id = l.source_doc_id
                  AND cf_item = 2
                  AND ROWNUM = 1) vat_due_amount,
       NVL(ROUND((
                         (SELECT vat_due_amount
                            FROM con_contract_cashflow
                           WHERE contract_id = l.source_doc_id
                             AND cf_item = 2
                             AND ROWNUM = 1)
                        /

                          (SELECT net_due_amount
                             FROM con_contract_cashflow
                            WHERE contract_id = l.source_doc_id
                              AND cf_item = 2
                              AND ROWNUM = 1)
                         ),
                 2) * 100,
           0) || '%' vat_rate,
           h.je_transaction_code
  FROM hls_journal_header h, hls_journal_detail l
 WHERE h.journal_header_id = l.journal_header_id
   and h.sbo_num is not null
   AND h.je_transaction_code = 'LEASE_INCEPT'
   AND l.je_template_ln_id = 8797
   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  to_char(hi.invoice_date,'yyyymm') <= ${@base_period}
           AND ((hi.contract_id = l.source_doc_id AND li.cf_item IN (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,
       SUM(calc_tax_amount(l.amount_dr)) amount_tax,
       '违约金' times,
       h.journal_date due_date,
       SUM(l.amount_dr) due_amount,
       NULL principal,
       SUM(l.amount_dr) interest,
       SUM(calc_tax_amount(l.amount_dr)) vat_due_amount,
       nvl(ROUND(  SUM(calc_tax_amount(l.amount_dr))/
                 (SUM(amount_dr) -SUM(calc_tax_amount(l.amount_dr))
                 ),
                 2) * 100,
           0) || '%' vat_rate,
           h.je_transaction_code
  FROM hls_journal_header h, hls_journal_detail l
 WHERE h.journal_header_id = l.journal_header_id
   and h.sbo_num is not null
   AND h.je_transaction_code ='HL_CSH_CONSOLIDATION_DALAY'
   AND l.amount_dr IS NOT NULL
   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 to_char(hi.invoice_date,'yyyymm') < ${@base_period}
           AND li.ref_n04 = l.journal_line_id)
 GROUP BY l.reference1,
          reference3,
          reference4,
          h.sbo_num,
          l.cashflow_id,
          l.journal_header_id,
          h.je_transaction_code,
          h.period_year,
          h.period_num,
          h.internal_period_num,
          h.journal_date
) t1
        #WHERE_CLAUSE#
   ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:data-filters>
        <bm:data-filter enforceOperations="query" expression="t1.journal_date &lt;= add_months(last_day(to_date(${@base_period},'yyyymm')),-1)"/>
    </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_char(t1.journal_date,'yyyymm') &lt; ${@base_period}"/>
        <bm:query-field name="invoice_date" queryExpression="to_char(t1.invoice_date,'yyyy-mm-dd') = ${@invoice_date}"/>
    </bm:query-fields>
</bm:model>
