<?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,
       decode(reference6, NULL, 0, NVL(round(l.amount_cr,2),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),
                'CON_REPURCHASE',
              '违约金') 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),
                 'CON_REPURCHASE',
              h.journal_date) due_date,
       DECODE(h.je_transaction_code,
              'HL_CSH_CONSOLIDATION_DALAY',
              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),
                  decode(reference6, NULL, l.amount_dr, 0),
                    'CON_REPURCHASE',
             decode(reference6, NULL, l.amount_dr, 0)) due_amount,
       (SELECT principal
          FROM con_contract_cashflow
         WHERE cashflow_id = l.cashflow_id) principal,
       DECODE(h.je_transaction_code,
              'HL_CSH_CONSOLIDATION_DALAY',
              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),
                 'CON_REPURCHASE',
                              decode(reference6, NULL, l.amount_dr, 0)
) interest,
       DECODE(h.je_transaction_code,
              'HL_CSH_CONSOLIDATION_DALAY',
              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),
                   'CON_REPURCHASE',
                  decode(reference6, NULL, 0, NVL(l.amount_cr,0))) vat_due_amount,
 nvl(ROUND((DECODE(h.je_transaction_code,
                         'HL_CSH_CONSOLIDATION_DALAY',
                      l.amount_cr/(l.reference7-l.amount_cr),
                         'HL_SALES_TAX_ACT_NEW',
                         (SELECT vat_due_amount/(due_amount-vat_due_amount)
                            FROM con_contract_cashflow
                           WHERE cashflow_id = l.cashflow_id),
                            l.amount_cr/(l.reference7-l.amount_cr),
                             'CON_REPURCHASE',
                      l.amount_cr/(l.reference7-l.amount_cr))),2) * 100,0)|| '%' vat_rate,
           h.je_transaction_code,
           l.cashflow_id
  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 ga.account_id=l.account_id
   and ga.account_code in('2221001020','2221001023')
    and h.reversed_flag='N'
   AND ((h.je_transaction_code IN
       ('HL_SALES_TAX_ACT_NEW', 'HL_CSH_CONSOLIDATION_DALAY')
      )or(h.je_transaction_code = 'CON_REPURCHASE' and
       instr(h.description, '调平') > 0))
       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))
  UNION
        select vl.*
          from ET_VAT_JOURNAL_LV vl
         where 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 = vl.cashflow_id))
                   UNION
        select rl.*
          from REPURSE_VAT_JOURNAL_LV rl
         where 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 = rl.cashflow_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 hi.vat_interface_status = 'BACK'
                  AND li.cf_item = 2
                  and hi.contract_id = l.source_doc_id
                  AND ROWNUM = 1) invoice_date,
       round(l.amount_dr,2) 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/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,
           l.cashflow_id
  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.reversed_flag='N'
   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))))
           ) 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-filter enforceOperations="query" expression="t1.amount_tax > 0"/>
    </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-fields>
</bm:model>
