acr_invoice_pkg.pck

CREATE OR REPLACE Package acr_invoice_pkg Is

  -- Author  : DJ
  -- Created : 2013/7/5 15:00:02
  -- Purpose : 应收发票创建
  -- Version : 1.30

  Procedure tmp_delete_session(p_session_id Number);

  Procedure split_cashflow(p_cashflow_id                  Number,
                           p_session_id                   Number,
                           p_bp_id                        Number,
                           p_bp_name                      Varchar2,
                           p_invoice_title                Varchar2,
                           p_invoice_bp_address_phone_num Varchar2,
                           p_invoice_bp_bank_account      Varchar2,
                           p_invoice_bp_tax_registry_num  Varchar2,
                           p_head_description             Varchar2,
                           p_currency                     Varchar2,
                           p_exchange_rate_type           Varchar2,
                           p_exchange_rate_quotation      Varchar2,
                           p_exchange_rate                Number,
                           p_user_id                      Number);

  Procedure split_penalty_cashflow(p_contract_id                  Number,
                                   p_journal_header_id            Number,
                                   p_journal_line_id              Number,
                                   p_billing_amount               Number,
                                   p_session_id                   Number,
                                   p_bp_id                        Number,
                                   p_bp_name                      Varchar2,
                                   p_invoice_title                Varchar2,
                                   p_invoice_bp_address_phone_num Varchar2,
                                   p_invoice_bp_bank_account      Varchar2,
                                   p_invoice_bp_tax_registry_num  Varchar2,
                                   p_head_description             Varchar2,
                                   p_currency                     Varchar2,
                                   p_exchange_rate_type           Varchar2,
                                   p_exchange_rate_quotation      Varchar2,
                                   p_exchange_rate                Number,
                                   p_user_id                      Number);

  Procedure invoicetmp_update(p_record_id      Number,
                              p_invoice_kind   Varchar2,
                              p_billing_amount Number,
                              p_tax_amount     Number Default Null,
                              p_product_name   Varchar2,
                              p_user_id        Number);
  --创建发票
  Procedure create_invoice(p_session_id           Number,
                           p_company_id           Number,
                           p_group_billing_method Varchar2,
                           p_invoice_date         Date,
                           p_accounting_date      Date,
                           p_user_id              Number,
                           p_role_id              Number);

  --宏菱建机罚息开票
  Procedure create_penalty_invoice(p_session_id           Number,
                                   p_company_id           Number,
                                   p_group_billing_method Varchar2,
                                   p_invoice_date         Date,
                                   p_accounting_date      Date,
                                   p_user_id              Number,
                                   p_role_id              Number);
  --删除发票
  Procedure delete_invoice(p_invoice_hd_id Number, p_user_id Number);
  --确认发票
  Procedure confirm_invoice(p_invoice_hd_id Number, p_user_id Number);
  --反冲发票
  Procedure reverse_invoice(p_invoice_hd_id      Number,
                            p_reverse_date       Date,
                            p_vat_red_notice_num Varchar2,
                            p_user_id            Number,
                            p_role_id            Number);

  --发票行合并
  --add by Yenick 宏菱项目 增加发票行合并
  Procedure invoice_row_merge(p_invoice_hd_id In Number,
                              p_user_id       Number,
                              p_merge_flag    Varchar2);

  --发票提交
  Procedure acr_invoice_submit(p_batch_id Number, p_user_id Number);
  --发票审批通过
  Procedure acr_invoice_approved(p_batch_id Number, p_user_id Number);
  -- function ss(p_batch_id out number) return number;
  --发票审批拒绝
  Procedure acr_invoice_reject(p_batch_id Number, p_user_id Number);
  -- add by shen
  Procedure acr_invoice_status_change(p_invoice_hd  Number,
                                      p_want_status In Varchar2,
                                      p_user_id     In Number);
  Procedure get_invoice_number(p_document_type    In Out Varchar2,
                               p_transaction_date In Date,
                               p_company_id       In Number,
                               p_user_id          In Number,
                               p_document_number  Out Varchar2,
                               p_business_type    Out Varchar2,
                               p_ln_step_length   Out Number);
  --增值税台账 数据处理
  Procedure vat_account_data_process(p_base_period Varchar2,
                                     p_session_id  Number,
                                     p_user_id     Number);
End acr_invoice_pkg;
/
CREATE OR REPLACE Package Body acr_invoice_pkg Is

  g_round_n Number := 2;

  g_status_new        Constant Varchar2(30) := 'NEW';
  g_status_confirm    Constant Varchar2(30) := 'CONFIRM';
  g_status_confirming Constant Varchar2(30) := 'CONFIRMING';
  g_status_post       Constant Varchar2(30) := 'POST';

  e_get_invoice_number_err  Exception;
  e_billing_amt_over_err    Exception;
  e_billing_amt_split_err   Exception;
  e_invoice_limit_not_found Exception;
  e_period_not_found        Exception;
  e_htjs_tolerance_error    Exception;

  e_lock_error Exception;
  Pragma Exception_Init(e_lock_error, -54);

  Type invoice_hd_tmp_type Is Record(
    company_id              Number,
    spv_company_id          Number,
    contract_id             Number,
    project_id              Number,
    invoice_document_type   Varchar2(100),
    bp_id                   Number,
    bp_name                 Varchar2(2000),
    bp_tax_registry_num     Varchar2(2000),
    bp_address_phone_num    Varchar2(2000),
    bp_bank_account         Varchar2(2000),
    description             Varchar2(2000),
    invoice_kind            Varchar2(100),
    total_amount            Number,
    currency                Varchar2(100),
    exchange_rate_type      Varchar2(100),
    exchange_rate_quotation Varchar2(100),
    exchange_rate           Number,
    tax_type_id             Number,
    tax_type_rate           Number,
    times                   Number,
    record_id               number);

  Type invoice_ln_tmp_type Is Record(
    contract_id    Number,
    cashflow_id    Number,
    cf_item        Number,
    cf_type        Number,
    product_name   Varchar2(2000),
    billing_amount Number);

  Type invoice_penalty_tmp_type Is Record(
    contract_id       Number,
    cf_item           Number,
    cf_type           Number,
    product_name      Varchar2(2000),
    billing_amount    Number,
    tax_amount        Number,
    journal_header_id Number,
    journal_line_id   Number,
    due_amount        number);

  g_package_name Varchar2(255) := upper('ACR_INVOICE_PKG');

  --拆分类型
  --与syscode ACR510_BILL_DETAIL_METHOD 保持一致
  g_divide_t_b_equipment Varchar2(30) := 'BILLING_EQUIPMENT';
  g_divide_t_b_rental    Varchar2(30) := 'BILLING_RENTAL';
  g_divide_t_b_principal Varchar2(30) := 'BILLING_PRINCIPAL';
  g_divide_t_b_interest  Varchar2(30) := 'BILLING_INTEREST';
  g_divide_t_r_principal Varchar2(30) := 'RECEIPT_PRINCIPAL';
  g_divide_t_others      Varchar2(30) := 'OTHERS';

  g_bill_status_full    Varchar2(30) := 'FULL';
  g_bill_status_partial Varchar2(30) := 'PARTIAL';
  g_bill_status_not     Varchar2(30) := 'NOT';

  g_business_t_leaseback Varchar2(30) := 'LEASEBACK';
  g_business_t_lease     Varchar2(30) := 'LEASE';

  g_document_category Constant Varchar2(30) := 'AR_INVOICE';

  g_htjs_tolerance Number := 0.0625; --金税头税额与行税额合计的容差

  Function cashflow_get_record(p_cashflow_id Number)
    Return con_contract_cashflow%Rowtype Is
    v_record con_contract_cashflow%Rowtype;
  Begin
    Select *
      Into v_record
      From con_contract_cashflow c
     Where c.cashflow_id = p_cashflow_id;
    Return v_record;
  End;

  Procedure invoicetmp_insert(p_record acr_invoice_create_tmp%Rowtype) Is
  Begin
    Insert Into acr_invoice_create_tmp Values p_record;
  End;

  Procedure tmp_delete_session(p_session_id Number) Is
  Begin
    Delete From acr_invoice_create_tmp t Where t.session_id = p_session_id;
  End;

  Function invoice_get_id Return Number Is
    v_acr_invoice_create_tmp_id Number;
  Begin
    Select acr_invoice_create_tmp_s.nextval
      Into v_acr_invoice_create_tmp_id
      From dual;
    Return v_acr_invoice_create_tmp_id;
  End;

  Function contract_get(p_contract_id Number) Return con_contract%Rowtype Is
    v_record con_contract%Rowtype;
  Begin
    Select *
      Into v_record
      From con_contract c
     Where c.contract_id = p_contract_id;
    Return v_record;
  End;

  Function get_billmethod_rec(p_billing_method Varchar2)
    Return hls_billing_method%Rowtype Is
    r_rec hls_billing_method%Rowtype;
  Begin
    Select *
      Into r_rec
      From hls_billing_method
     Where billing_method = p_billing_method;

    Return r_rec;
  Exception
    When no_data_found Then
      Return Null;
  End;

  Function get_invoicekind_by_taxpayer(p_contract_id Number) Return Varchar2 Is
    v_taxpayer_type Varchar2(255);
    v_vat_flag      Varchar2(1);
  Begin
    Select m.taxpayer_type, c.vat_flag
      Into v_taxpayer_type, v_vat_flag
      From hls_bp_master m, con_contract c
     Where c.contract_id = p_contract_id
       And m.bp_id = c.bp_id_tenant;

    If v_vat_flag = 'Y' Then

      If v_taxpayer_type = 'GENERAL_TAXPAYER' Then
        --普通纳税人：专票
        Return '0';
      Elsif v_taxpayer_type = 'SMALL_SCALE_TAXPAYER' Then
        --小规模纳税人：普票
        Return '2';
      Else
        --其余算入 小规模纳税人：普票
        Return '2';
      End If;
    Else
      Return 'SALES_TAX';
    End If;
  End;

  --现金流处理之后

  Procedure cf_type_after_handle(p_tmp            acr_invoice_create_tmp%Rowtype,
                                 p_cashflow       con_contract_cashflow%Rowtype,
                                 p_billmethodprop hls_billing_method%Rowtype) Is
    v_tmp                      acr_invoice_create_tmp%Rowtype;
    v_contract                 con_contract%Rowtype;
    v_invoice_kind_by_taxpayer Varchar2(255);
    v_cf_billing_rule          Varchar2(255);
  Begin
    v_tmp      := p_tmp;
    v_contract := contract_get(p_cashflow.contract_id);

    Begin
      Select billing_rule
        Into v_cf_billing_rule
        From con_contract_cf_item
       Where contract_id = p_cashflow.contract_id
         And cf_item = p_cashflow.cf_item;
    Exception
      When no_data_found Then
        v_cf_billing_rule := '';
    End;

    If v_cf_billing_rule = 'BILLING_FIRST' Then

      v_tmp.billing_amount := nvl(v_tmp.due_amount, 0) -
                              v_tmp.cf_billing_amount;
    Else
      --modify by shen
      /*v_tmp.billing_amount := nvl(v_tmp.received_amount, 0) -
      v_tmp.cf_billing_amount;*/
      --开票金额改为应收减已开票，无论是否收到钱
      v_tmp.billing_amount := nvl(v_tmp.due_amount, 0) -
                              v_tmp.cf_billing_amount;
    End If;

    If nvl(v_tmp.billing_amount, 0) > 0 Then
      --发票金额为0，则不生成
      v_tmp.cashflow_id := p_cashflow.cashflow_id;
      v_tmp.record_id   := invoice_get_id;
      v_tmp.contract_id := p_cashflow.contract_id;
      --v_tmp.billing_object := p_divide_type;
      v_tmp.times := p_cashflow.times;

      invoicetmp_insert(v_tmp);

    End If;
  End;

  --现金流处理之前

  Procedure cf_type_before_handle(p_tmp            In Out acr_invoice_create_tmp%Rowtype,
                                  p_cashflow       con_contract_cashflow%Rowtype,
                                  p_billmethodprop hls_billing_method%Rowtype) Is
  Begin
    p_tmp.cf_type         := p_cashflow.cf_type;
    p_tmp.cf_item         := p_cashflow.cf_item;
    p_tmp.received_amount := nvl(p_cashflow.received_amount, 0);
  End;

  --设备款处理cf_type=109
  Procedure cf_type_109_handle(p_tmp            acr_invoice_create_tmp%Rowtype,
                               p_cashflow       con_contract_cashflow%Rowtype,
                               p_contract       con_contract%Rowtype,
                               p_billmethodprop hls_billing_method%Rowtype) Is
    v_tmp acr_invoice_create_tmp%Rowtype;
  Begin

    v_tmp := p_tmp;

    cf_type_before_handle(p_tmp            => v_tmp,
                          p_cashflow       => p_cashflow,
                          p_billmethodprop => p_billmethodprop);

    If p_billmethodprop.equipment_billing_flag = 'Y' Then
      v_tmp.due_amount        := nvl(p_cashflow.due_amount, 0);
      v_tmp.cf_billing_amount := nvl(p_cashflow.billing_amount, 0);

      --获取 invoice kind
      If p_billmethodprop.equipment_invoice_kind_tp = '10' Then

        v_tmp.invoice_kind := get_invoicekind_by_taxpayer(p_contract_id => p_contract.contract_id);

      Elsif p_billmethodprop.equipment_invoice_kind_tp = '20' Then

        v_tmp.invoice_kind := p_billmethodprop.equipment_invoice_kind;

      End If;

      --获取 tax rate
      If p_billmethodprop.equipment_tax_rate_tp = '10' Then
        If p_cashflow.tax_type_id Is Not Null Or
           p_cashflow.tax_type_rate Is Not Null Then
          v_tmp.tax_type_id   := p_cashflow.tax_type_id;
          v_tmp.tax_type_rate := p_cashflow.tax_type_rate;
        Else
          v_tmp.tax_type_id := p_contract.tax_type_id;
        End If;
      Elsif p_billmethodprop.equipment_tax_rate_tp = '20' Then
        v_tmp.tax_type_id := p_billmethodprop.equipment_tax_rate;
      End If;
      If v_tmp.tax_type_id Is Not Null And v_tmp.tax_type_rate Is Null Then
        Select tax_type_rate
          Into v_tmp.tax_type_rate
          From fnd_tax_type_codes
         Where tax_type_id = v_tmp.tax_type_id;
      End If;

      cf_type_after_handle(p_tmp            => v_tmp,
                           p_cashflow       => p_cashflow,
                           p_billmethodprop => p_billmethodprop);
    End If;

  End;

  --利息处理cf_type=101
  Procedure cf_type_101_handle(p_tmp            acr_invoice_create_tmp%Rowtype,
                               p_cashflow       con_contract_cashflow%Rowtype,
                               p_contract       con_contract%Rowtype,
                               p_billmethodprop hls_billing_method%Rowtype) Is
    v_tmp acr_invoice_create_tmp%Rowtype;
  Begin

    v_tmp := p_tmp;

    cf_type_before_handle(p_tmp            => v_tmp,
                          p_cashflow       => p_cashflow,
                          p_billmethodprop => p_billmethodprop);

    If p_billmethodprop.interest_billing_flag = 'Y' Then
      v_tmp.due_amount        := nvl(p_cashflow.interest, 0);
      v_tmp.cf_billing_amount := nvl(p_cashflow.billing_interest, 0);
      v_tmp.received_amount   := nvl(p_cashflow.received_interest, 0);
      v_tmp.cf_type           := 101;
      v_tmp.cf_item           := 101;

      --获取 invoice kind
      If p_billmethodprop.interest_invoice_kind_tp = '10' Then

        v_tmp.invoice_kind := get_invoicekind_by_taxpayer(p_contract_id => p_contract.contract_id);

      Elsif p_billmethodprop.interest_invoice_kind_tp = '20' Then

        v_tmp.invoice_kind := p_billmethodprop.interest_invoice_kind;

      End If;

      --获取 tax rate
      If p_billmethodprop.interest_tax_rate_tp = '10' Then
        If p_cashflow.tax_type_id Is Not Null Or
           p_cashflow.tax_type_rate Is Not Null Then
          v_tmp.tax_type_id   := p_cashflow.tax_type_id;
          v_tmp.tax_type_rate := p_cashflow.tax_type_rate;
        Else
          v_tmp.tax_type_id := p_contract.tax_type_id;
        End If;
      Elsif p_billmethodprop.interest_tax_rate_tp = '20' Then
        v_tmp.tax_type_id := p_billmethodprop.interest_tax_rate;
      End If;
      If v_tmp.tax_type_id Is Not Null And v_tmp.tax_type_rate Is Null Then
        Select tax_type_rate
          Into v_tmp.tax_type_rate
          From fnd_tax_type_codes
         Where tax_type_id = v_tmp.tax_type_id;
      End If;

      cf_type_after_handle(p_tmp            => v_tmp,
                           p_cashflow       => p_cashflow,
                           p_billmethodprop => p_billmethodprop);
    End If;

  End;

  --本金处理cf_type=100
  Procedure cf_type_100_handle(p_tmp            acr_invoice_create_tmp%Rowtype,
                               p_cashflow       con_contract_cashflow%Rowtype,
                               p_contract       con_contract%Rowtype,
                               p_billmethodprop hls_billing_method%Rowtype) Is
    v_tmp acr_invoice_create_tmp%Rowtype;
  Begin

    v_tmp := p_tmp;

    cf_type_before_handle(p_tmp            => v_tmp,
                          p_cashflow       => p_cashflow,
                          p_billmethodprop => p_billmethodprop);

    If p_billmethodprop.principal_billing_flag = 'Y' Then

      v_tmp.due_amount        := nvl(p_cashflow.principal, 0);
      v_tmp.cf_billing_amount := nvl(p_cashflow.billing_principal, 0);
      v_tmp.received_amount   := nvl(p_cashflow.received_principal, 0);
      v_tmp.cf_type           := 100;
      v_tmp.cf_item           := 100;

      --获取 invoice kind
      If p_billmethodprop.principal_invoice_kind_tp = '10' Then

        v_tmp.invoice_kind := get_invoicekind_by_taxpayer(p_contract_id => p_contract.contract_id);

      Elsif p_billmethodprop.principal_invoice_kind_tp = '20' Then

        v_tmp.invoice_kind := p_billmethodprop.principal_invoice_kind;

      End If;

      --获取 tax rate
      If p_billmethodprop.principal_tax_rate_tp = '10' Then
        If p_cashflow.tax_type_id Is Not Null Or
           p_cashflow.tax_type_rate Is Not Null Then
          v_tmp.tax_type_id   := p_cashflow.tax_type_id;
          v_tmp.tax_type_rate := p_cashflow.tax_type_rate;
        Else
          v_tmp.tax_type_id := p_contract.tax_type_id;
        End If;
      Elsif p_billmethodprop.principal_tax_rate_tp = '20' Then
        v_tmp.tax_type_id := p_billmethodprop.principal_tax_rate;
      End If;
      If v_tmp.tax_type_id Is Not Null And v_tmp.tax_type_rate Is Null Then
        Select tax_type_rate
          Into v_tmp.tax_type_rate
          From fnd_tax_type_codes
         Where tax_type_id = v_tmp.tax_type_id;
      End If;

      cf_type_after_handle(p_tmp            => v_tmp,
                           p_cashflow       => p_cashflow,
                           p_billmethodprop => p_billmethodprop);
    End If;

  End;

  --租金处理cf_type=1
  Procedure cf_type_1_handle(p_tmp            acr_invoice_create_tmp%Rowtype,
                             p_cashflow       con_contract_cashflow%Rowtype,
                             p_contract       con_contract%Rowtype,
                             p_billmethodprop hls_billing_method%Rowtype) Is
    v_tmp           acr_invoice_create_tmp%Rowtype;
    r_hls_bp_master hls_bp_master%Rowtype;
  Begin

    v_tmp := p_tmp;

    If p_billmethodprop.rental_billing_flag = 'Y' Then
      -- 按租金开票

      cf_type_before_handle(p_tmp            => v_tmp,
                            p_cashflow       => p_cashflow,
                            p_billmethodprop => p_billmethodprop);

      v_tmp.due_amount        := nvl(p_cashflow.due_amount, 0);
      v_tmp.received_amount   := nvl(p_cashflow.received_amount, 0);
      v_tmp.cf_billing_amount := nvl(p_cashflow.billing_amount, 0);

      --获取 invoice kind
      Select *
        Into r_hls_bp_master
        From hls_bp_master m
       Where m.bp_id = v_tmp.bp_id;

      If nvl(r_hls_bp_master.taxpayer_type, 'SMALL_SCALE_TAXPAYER') =
         'GENERAL_TAXPAYER' Then
        --普通纳税人：专票
        v_tmp.invoice_kind := '0';
      Elsif nvl(r_hls_bp_master.taxpayer_type, 'SMALL_SCALE_TAXPAYER') =
            'SMALL_SCALE_TAXPAYER' Then
        --小规模纳税人：普票
        v_tmp.invoice_kind := '2';
      End If;

      /* if p_billmethodprop.rental_invoice_kind_tp = '10' then

        v_tmp.invoice_kind := get_invoicekind_by_taxpayer(p_contract_id => p_contract.contract_id);

      elsif p_billmethodprop.rental_invoice_kind_tp = '20' then

        v_tmp.invoice_kind := p_billmethodprop.rental_invoice_kind;

      end if;*/

      --获取 tax rate
      If p_billmethodprop.rental_tax_rate_tp = '10' Then
        If p_cashflow.tax_type_id Is Not Null Or
           p_cashflow.tax_type_rate Is Not Null Then
          v_tmp.tax_type_id   := p_cashflow.tax_type_id;
          v_tmp.tax_type_rate := p_cashflow.tax_type_rate;
        Else
          v_tmp.tax_type_id := p_contract.tax_type_id;
        End If;
      Elsif p_billmethodprop.rental_tax_rate_tp = '20' Then
        v_tmp.tax_type_id := p_billmethodprop.rental_tax_rate;
      End If;
      If v_tmp.tax_type_id Is Not Null And v_tmp.tax_type_rate Is Null Then
        Select tax_type_rate
          Into v_tmp.tax_type_rate
          From fnd_tax_type_codes
         Where tax_type_id = v_tmp.tax_type_id;
      End If;

      cf_type_after_handle(p_tmp            => v_tmp,
                           p_cashflow       => p_cashflow,
                           p_billmethodprop => p_billmethodprop);

    Else

      --本金开票
      If p_billmethodprop.principal_billing_flag = 'Y' Then

        cf_type_100_handle(p_tmp, p_cashflow, p_contract, p_billmethodprop);
      End If;

      --利息开票
      If p_billmethodprop.interest_billing_flag = 'Y' Then

        cf_type_101_handle(p_tmp, p_cashflow, p_contract, p_billmethodprop);
      End If;

    End If;

  End;
  --利息处理cf_type=101 addby wuts
  Procedure cf_item_301_handle(p_tmp            acr_invoice_create_tmp%Rowtype,
                               p_cashflow       con_contract_cashflow%Rowtype,
                               p_contract       con_contract%Rowtype,
                               p_billmethodprop hls_billing_method%Rowtype) Is
    v_tmp acr_invoice_create_tmp%Rowtype;
  Begin

    v_tmp := p_tmp;

    cf_type_before_handle(p_tmp            => v_tmp,
                          p_cashflow       => p_cashflow,
                          p_billmethodprop => p_billmethodprop);

    If p_billmethodprop.interest_billing_flag = 'Y' Then
      v_tmp.due_amount        := nvl(p_cashflow.due_amount, 0);
      v_tmp.cf_billing_amount := nvl(p_cashflow.billing_amount, 0);
      v_tmp.received_amount   := nvl(p_cashflow.received_amount, 0);
      v_tmp.cf_type           := 301;
      v_tmp.cf_item           := 301;

      --获取 invoice kind
      /*   if p_billmethodprop.interest_invoice_kind_tp = '10' then

        v_tmp.invoice_kind := get_invoicekind_by_taxpayer(p_contract_id => p_contract.contract_id);

      elsif p_billmethodprop.interest_invoice_kind_tp = '20' then

        v_tmp.invoice_kind := p_billmethodprop.interest_invoice_kind;

      end if;*/
      --管理费 专用发票 modify by wuts
      v_tmp.invoice_kind := '0';
      --获取 tax rate
      If p_billmethodprop.interest_tax_rate_tp = '10' Then
        If p_cashflow.tax_type_id Is Not Null Or
           p_cashflow.tax_type_rate Is Not Null Then
          v_tmp.tax_type_id   := p_cashflow.tax_type_id;
          v_tmp.tax_type_rate := p_cashflow.tax_type_rate;
        Else
          v_tmp.tax_type_id := p_contract.tax_type_id;
        End If;
      Elsif p_billmethodprop.interest_tax_rate_tp = '20' Then
        v_tmp.tax_type_id := p_billmethodprop.interest_tax_rate;
      End If;
      If v_tmp.tax_type_id Is Not Null And v_tmp.tax_type_rate Is Null Then
        Select tax_type_rate
          Into v_tmp.tax_type_rate
          From fnd_tax_type_codes
         Where tax_type_id = v_tmp.tax_type_id;
      End If;

      cf_type_after_handle(p_tmp            => v_tmp,
                           p_cashflow       => p_cashflow,
                           p_billmethodprop => p_billmethodprop);
    End If;

  End;
  --租金处理cf_type=301
  Procedure cf_type_301_handle(p_tmp            acr_invoice_create_tmp%Rowtype,
                               p_cashflow       con_contract_cashflow%Rowtype,
                               p_contract       con_contract%Rowtype,
                               p_billmethodprop hls_billing_method%Rowtype) Is
    v_tmp acr_invoice_create_tmp%Rowtype;
  Begin

    v_tmp := p_tmp;

    If p_billmethodprop.rental_billing_flag = 'Y' Then
      -- 按租金开票

      cf_type_before_handle(p_tmp            => v_tmp,
                            p_cashflow       => p_cashflow,
                            p_billmethodprop => p_billmethodprop);

      v_tmp.due_amount        := nvl(p_cashflow.due_amount, 0);
      v_tmp.received_amount   := nvl(p_cashflow.received_amount, 0);
      v_tmp.cf_billing_amount := nvl(p_cashflow.billing_amount, 0);

      --获取 invoice kind
      /*if p_billmethodprop.rental_invoice_kind_tp = '10' then

          v_tmp.invoice_kind := get_invoicekind_by_taxpayer(p_contract_id => p_contract.contract_id);

        elsif p_billmethodprop.rental_invoice_kind_tp = '20' then

          v_tmp.invoice_kind := p_billmethodprop.rental_invoice_kind;

        end if;
      */
      --管理费 专用发票 modify by wuts
      v_tmp.invoice_kind := '0';
      --获取 tax rate
      If p_billmethodprop.rental_tax_rate_tp = '10' Then
        If p_cashflow.tax_type_id Is Not Null Or
           p_cashflow.tax_type_rate Is Not Null Then
          v_tmp.tax_type_id   := p_cashflow.tax_type_id;
          v_tmp.tax_type_rate := p_cashflow.tax_type_rate;
        Else
          v_tmp.tax_type_id := p_contract.tax_type_id;
        End If;
      Elsif p_billmethodprop.rental_tax_rate_tp = '20' Then
        v_tmp.tax_type_id := p_billmethodprop.rental_tax_rate;
      End If;
      If v_tmp.tax_type_id Is Not Null And v_tmp.tax_type_rate Is Null Then
        Select tax_type_rate
          Into v_tmp.tax_type_rate
          From fnd_tax_type_codes
         Where tax_type_id = v_tmp.tax_type_id;
      End If;

      cf_type_after_handle(p_tmp            => v_tmp,
                           p_cashflow       => p_cashflow,
                           p_billmethodprop => p_billmethodprop);

    Else
      --利息开票
      If p_billmethodprop.interest_billing_flag = 'Y' Then

        cf_item_301_handle(p_tmp, p_cashflow, p_contract, p_billmethodprop);
      End If;

    End If;

  End;
  --end wuts
  --其他处理
  Procedure cf_type_others_handle(p_tmp            acr_invoice_create_tmp%Rowtype,
                                  p_cashflow       con_contract_cashflow%Rowtype,
                                  p_contract       con_contract%Rowtype,
                                  p_billmethodprop hls_billing_method%Rowtype) Is
    v_tmp acr_invoice_create_tmp%Rowtype;

    v_cf_method hls_billing_method_cf%Rowtype;
  Begin
    v_tmp := p_tmp;

    cf_type_before_handle(p_tmp            => v_tmp,
                          p_cashflow       => p_cashflow,
                          p_billmethodprop => p_billmethodprop);

    v_tmp.due_amount        := nvl(p_cashflow.due_amount, 0);
    v_tmp.cf_billing_amount := nvl(p_cashflow.billing_amount, 0);

    Begin
      Select *
        Into v_cf_method
        From hls_billing_method_cf
       Where billing_method = p_billmethodprop.billing_method
         And cf_item = p_cashflow.cf_item;

      If v_cf_method.enabled_flag = 'Y' Then

        --获取 invoice kind
        If v_cf_method.invoice_kind_tp = '10' Then

          v_tmp.invoice_kind := get_invoicekind_by_taxpayer(p_contract_id => p_contract.contract_id);

        Elsif v_cf_method.invoice_kind_tp = '20' Then

          v_tmp.invoice_kind := v_cf_method.invoice_kind;

        End If;

        --获取 tax rate
        If v_cf_method.tax_rate_tp = '10' Then
          If p_cashflow.tax_type_id Is Not Null Or
             p_cashflow.tax_type_rate Is Not Null Then
            v_tmp.tax_type_id   := p_cashflow.tax_type_id;
            v_tmp.tax_type_rate := p_cashflow.tax_type_rate;
          Else
            v_tmp.tax_type_id := p_contract.tax_type_id;
          End If;
        Elsif v_cf_method.tax_rate_tp = '20' Then
          v_tmp.tax_type_id := v_cf_method.tax_rate;
        End If;

        If v_tmp.tax_type_id Is Not Null And v_tmp.tax_type_rate Is Null Then
          Select tax_type_rate
            Into v_tmp.tax_type_rate
            From fnd_tax_type_codes
           Where tax_type_id = v_tmp.tax_type_id;
        End If;

        cf_type_after_handle(p_tmp            => v_tmp,
                             p_cashflow       => p_cashflow,
                             p_billmethodprop => p_billmethodprop);

      End If;

    Exception
      When no_data_found Then

        If p_billmethodprop.other_billing_flag = 'Y' Then

          --获取 invoice kind
          If p_billmethodprop.other_invoice_kind_tp = '10' Then

            v_tmp.invoice_kind := get_invoicekind_by_taxpayer(p_contract_id => p_contract.contract_id);

          Elsif p_billmethodprop.other_invoice_kind_tp = '20' Then

            v_tmp.invoice_kind := p_billmethodprop.other_invoice_kind;

          End If;

          --获取 tax rate
          If p_billmethodprop.other_tax_rate_tp = '10' Then
            If p_cashflow.tax_type_id Is Not Null Or
               p_cashflow.tax_type_rate Is Not Null Then
              v_tmp.tax_type_id   := p_cashflow.tax_type_id;
              v_tmp.tax_type_rate := p_cashflow.tax_type_rate;
            Else
              v_tmp.tax_type_id := p_contract.tax_type_id;
            End If;
          Elsif p_billmethodprop.other_tax_rate_tp = '20' Then
            v_tmp.tax_type_id := p_billmethodprop.other_tax_rate;
          End If;
          If v_tmp.tax_type_id Is Not Null And v_tmp.tax_type_rate Is Null Then
            Select tax_type_rate
              Into v_tmp.tax_type_rate
              From fnd_tax_type_codes
             Where tax_type_id = v_tmp.tax_type_id;
          End If;

          cf_type_after_handle(p_tmp            => v_tmp,
                               p_cashflow       => p_cashflow,
                               p_billmethodprop => p_billmethodprop);

        End If;
    End;

  End;

  Procedure cf_type_9_handle(p_tmp            acr_invoice_create_tmp%Rowtype,
                             p_contract       con_contract%Rowtype,
                             p_billmethodprop hls_billing_method%Rowtype) Is
    v_tmp acr_invoice_create_tmp%Rowtype;

    v_cf_method hls_billing_method_cf%Rowtype;
  Begin
    v_tmp           := p_tmp;
    v_tmp.record_id := invoice_get_id;

    Begin
      Select *
        Into v_cf_method
        From hls_billing_method_cf
       Where billing_method = p_billmethodprop.billing_method
         And cf_item = 9;

      If v_cf_method.enabled_flag = 'Y' Then

        --获取 invoice kind
        If v_cf_method.invoice_kind_tp = '10' Then

          v_tmp.invoice_kind := get_invoicekind_by_taxpayer(p_contract_id => p_contract.contract_id);

        Elsif v_cf_method.invoice_kind_tp = '20' Then

          v_tmp.invoice_kind := v_cf_method.invoice_kind;

        End If;

        --获取 tax rate
        If v_cf_method.tax_rate_tp = '10' Then
          v_tmp.tax_type_id := p_contract.tax_type_id;
        Elsif v_cf_method.tax_rate_tp = '20' Then
          v_tmp.tax_type_id := v_cf_method.tax_rate;
        End If;

        If v_tmp.tax_type_id Is Not Null And v_tmp.tax_type_rate Is Null Then
          Select tax_type_rate
            Into v_tmp.tax_type_rate
            From fnd_tax_type_codes
           Where tax_type_id = v_tmp.tax_type_id;
        End If;

        invoicetmp_insert(v_tmp);

      End If;

    Exception
      When no_data_found Then

        If p_billmethodprop.other_billing_flag = 'Y' Then

          --获取 invoice kind
          If p_billmethodprop.other_invoice_kind_tp = '10' Then

            v_tmp.invoice_kind := get_invoicekind_by_taxpayer(p_contract_id => p_contract.contract_id);

          Elsif p_billmethodprop.other_invoice_kind_tp = '20' Then

            v_tmp.invoice_kind := p_billmethodprop.other_invoice_kind;

          End If;

          --获取 tax rate
          If p_billmethodprop.other_tax_rate_tp = '10' Then
            v_tmp.tax_type_id := p_contract.tax_type_id;
          Elsif p_billmethodprop.other_tax_rate_tp = '20' Then
            v_tmp.tax_type_id := p_billmethodprop.other_tax_rate;
          End If;
          If v_tmp.tax_type_id Is Not Null And v_tmp.tax_type_rate Is Null Then
            Select tax_type_rate
              Into v_tmp.tax_type_rate
              From fnd_tax_type_codes
             Where tax_type_id = v_tmp.tax_type_id;
          End If;

          invoicetmp_insert(v_tmp);

        End If;
    End;

  End;
  -- add by niminmin
  --罚息开票 不按现金流开票
  Procedure split_penalty_cashflow(p_contract_id                  Number,
                                   p_journal_header_id            Number,
                                   p_journal_line_id              Number,
                                   p_billing_amount               Number,
                                   p_session_id                   Number,
                                   p_bp_id                        Number,
                                   p_bp_name                      Varchar2,
                                   p_invoice_title                Varchar2,
                                   p_invoice_bp_address_phone_num Varchar2,
                                   p_invoice_bp_bank_account      Varchar2,
                                   p_invoice_bp_tax_registry_num  Varchar2,
                                   p_head_description             Varchar2,
                                   p_currency                     Varchar2,
                                   p_exchange_rate_type           Varchar2,
                                   p_exchange_rate_quotation      Varchar2,
                                   p_exchange_rate                Number,
                                   p_user_id                      Number) Is
    v_contract       con_contract%Rowtype;
    v_billmethodprop hls_billing_method%Rowtype;
    v_paras          acr_invoice_create_tmp%Rowtype;
    v_account_code   Varchar2(200);
    e_bill_status Exception;

    v_programe_name Varchar2(255) := upper('split_penalty_cashflow');
  Begin
    v_contract := contract_get(p_contract_id);
    Begin
      Select hl.amount_cr
        Into v_paras.tax_amount
        From gld_accounts_vl v, hls_journal_detail hl, con_contract ct
       Where hl.account_id = v.account_id
         And hl.journal_header_id = p_journal_header_id
         And v.account_code = '2221001020'
         And ct.contract_id = p_contract_id
         And ct.contract_number = hl.reference3;
    Exception
      When no_data_found Then
        Raise e_bill_status;
    End;
    v_billmethodprop := get_billmethod_rec(v_contract.billing_method);

    v_paras.creation_date                := Sysdate;
    v_paras.created_by                   := p_user_id;
    v_paras.last_update_date             := Sysdate;
    v_paras.last_updated_by              := p_user_id;
    v_paras.invoice_title                := p_invoice_title;
    v_paras.bp_id                        := p_bp_id;
    v_paras.bp_name                      := p_bp_name;
    v_paras.invoice_bp_address_phone_num := p_invoice_bp_address_phone_num;
    v_paras.invoice_bp_bank_account      := p_invoice_bp_bank_account;
    v_paras.head_description             := p_head_description;
    v_paras.session_id                   := p_session_id;
    v_paras.checked_flag                 := 'N';
    v_paras.invoice_bp_tax_registry_num  := p_invoice_bp_tax_registry_num;
    v_paras.currency                     := p_currency;
    v_paras.exchange_rate_type           := p_exchange_rate_type;
    v_paras.exchange_rate_quotation      := p_exchange_rate_quotation;
    v_paras.exchange_rate                := p_exchange_rate;
    v_paras.journal_header_id            := p_journal_header_id;
    v_paras.journal_line_id              := p_journal_line_id;
    v_paras.due_amount                   := p_billing_amount;
    v_paras.cf_billing_amount            := p_billing_amount;
    v_paras.contract_id                  := p_contract_id;
    v_paras.cf_item                      := 9;
    v_paras.cf_type                      := 9;
    cf_type_9_handle(v_paras, v_contract, v_billmethodprop);

  Exception
    When e_bill_status Then

      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '未找到对应销项税，请联系管理员！',
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => g_package_name,
                                                     p_procedure_function_name => v_programe_name);

      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);

    When Others Then

      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => dbms_utility.format_error_backtrace || ' ' ||
                                                                                  Sqlerrm,
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => g_package_name,
                                                     p_procedure_function_name => v_programe_name);

      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);

  End;

  Procedure split_cashflow(p_cashflow_id                  Number,
                           p_session_id                   Number,
                           p_bp_id                        Number,
                           p_bp_name                      Varchar2,
                           p_invoice_title                Varchar2,
                           p_invoice_bp_address_phone_num Varchar2,
                           p_invoice_bp_bank_account      Varchar2,
                           p_invoice_bp_tax_registry_num  Varchar2,
                           p_head_description             Varchar2,
                           p_currency                     Varchar2,
                           p_exchange_rate_type           Varchar2,
                           p_exchange_rate_quotation      Varchar2,
                           p_exchange_rate                Number,
                           p_user_id                      Number) Is

    v_cashflow       con_contract_cashflow%Rowtype;
    v_contract       con_contract%Rowtype;
    v_billmethodprop hls_billing_method%Rowtype;
    v_paras          acr_invoice_create_tmp%Rowtype;

    e_bill_status Exception;

    v_programe_name Varchar2(255) := upper('split_cashflow');
  Begin
    v_cashflow := cashflow_get_record(p_cashflow_id);
    v_contract := contract_get(v_cashflow.contract_id);

    If v_cashflow.billing_status = g_bill_status_full Then
      Raise e_bill_status;
    End If;

    v_billmethodprop := get_billmethod_rec(v_contract.billing_method);

    v_paras.creation_date                := Sysdate;
    v_paras.created_by                   := p_user_id;
    v_paras.last_update_date             := Sysdate;
    v_paras.last_updated_by              := p_user_id;
    v_paras.invoice_title                := p_invoice_title;
    v_paras.bp_id                        := p_bp_id;
    v_paras.bp_name                      := p_bp_name;
    v_paras.invoice_bp_address_phone_num := p_invoice_bp_address_phone_num;
    v_paras.invoice_bp_bank_account      := p_invoice_bp_bank_account;
    v_paras.head_description             := p_head_description;
    v_paras.session_id                   := p_session_id;
    v_paras.checked_flag                 := 'N';
    v_paras.invoice_bp_tax_registry_num  := p_invoice_bp_tax_registry_num;
    v_paras.currency                     := p_currency;
    v_paras.exchange_rate_type           := p_exchange_rate_type;
    v_paras.exchange_rate_quotation      := p_exchange_rate_quotation;
    v_paras.exchange_rate                := p_exchange_rate;

    If v_contract.currency != 'CNY' Then
      v_cashflow.due_amount         := v_cashflow.due_amount_cny;
      v_cashflow.principal          := v_cashflow.principal_cny;
      v_cashflow.interest           := v_cashflow.interest_cny;
      v_cashflow.received_amount    := v_cashflow.received_amount_cny;
      v_cashflow.received_principal := v_cashflow.received_principal_cny;
      v_cashflow.received_interest  := v_cashflow.received_interest_cny;
    End If;

    --管理费
    If v_cashflow.cf_type = 301 Then
      cf_type_301_handle(v_paras, v_cashflow, v_contract, v_billmethodprop);
      --租金
    Elsif v_cashflow.cf_type = 1 Then
      cf_type_1_handle(v_paras, v_cashflow, v_contract, v_billmethodprop);
    Elsif v_cashflow.cf_type = 100 Then
      cf_type_100_handle(v_paras, v_cashflow, v_contract, v_billmethodprop);
    Elsif v_cashflow.cf_type = 101 Then
      cf_type_101_handle(v_paras, v_cashflow, v_contract, v_billmethodprop);
    Elsif v_cashflow.cf_type = 109 Then
      cf_type_109_handle(v_paras, v_cashflow, v_contract, v_billmethodprop);
    Else
      cf_type_others_handle(v_paras,
                            v_cashflow,
                            v_contract,
                            v_billmethodprop);
    End If;

  Exception
    When e_bill_status Then
      Null;

    When Others Then

      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => dbms_utility.format_error_backtrace || ' ' ||
                                                                                  Sqlerrm,
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => g_package_name,
                                                     p_procedure_function_name => v_programe_name);

      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);

  End;

  Procedure invoicetmp_update(p_record_id      Number,
                              p_invoice_kind   Varchar2,
                              p_billing_amount Number,
                              p_tax_amount     Number Default Null,
                              p_product_name   Varchar2,
                              p_user_id        Number) Is
  Begin
    Update acr_invoice_create_tmp t
       Set t.invoice_kind     = p_invoice_kind,
           t.billing_amount   = p_billing_amount,
           t.product_name     = p_product_name,
           t.tax_amount       = p_tax_amount,
           t.checked_flag     = 'Y',
           t.last_updated_by  = p_user_id,
           t.last_update_date = Sysdate
     Where t.record_id = p_record_id;
  End;

  Procedure create_invoice_check(p_session_id In Number,
                                 p_company_id In Number,
                                 p_user_id    In Number) Is

    v_exists Varchar2(1) := 'N';

    e_leaseback_invoice_kind_err Exception;
    e_receipt_cf_type_err        Exception;
    e_bp_class_taxpayer_err      Exception;
    e_vat_flag_err               Exception;
  Begin

    /*begin
      select 'Y'
        into v_exists
        from dual
       where exists (select 1
                from acr_invoice_create_tmp t, con_contract c
               where t.session_id = p_session_id
                 and t.checked_flag = 'Y'
                 and t.contract_id = c.contract_id
                 and c.business_type = 'LEASEBACK'
                 and t.invoice_kind = '0'
                 and t.cf_type in (100, 2));

      raise e_leaseback_invoice_kind_err;--回租业务本金部分不能开具增值税专用发票
    exception
      when no_data_found then
        null;
    end;*/

    Begin
      Select 'Y'
        Into v_exists
        From dual
       Where Exists (Select 1
                From acr_invoice_create_tmp t
               Where t.session_id = p_session_id
                 And t.checked_flag = 'Y'
                 And t.invoice_kind <> 'RECEIPT'
                 And t.cf_type = 5);

      Raise e_receipt_cf_type_err; --保证金只能开收据
    Exception
      When no_data_found Then
        Null;
    End;

    Begin
      Select 'Y'
        Into v_exists
        From dual
       Where Exists
       (Select 1
                From acr_invoice_create_tmp t, hls_bp_master b
               Where t.session_id = p_session_id
                 And t.checked_flag = 'Y'
                 And t.bp_id = b.bp_id
                 And t.invoice_kind = '0'
                 And (b.bp_class = 'NP' Or
                     b.taxpayer_type = 'SMALL_SCALE_TAXPAYER'));
      Raise e_bp_class_taxpayer_err;
      --合同BP为自然人hls_bp_master.bp_class=NP、或纳税人类型为小规模纳税人hls_bp_master. taxpayer_type=SMALL_SCALE_TAXPAYER，不能开增值税专用发票
    Exception
      When no_data_found Then
        Null;
    End;

    Begin
      Select 'Y'
        Into v_exists
        From dual
       Where Exists (Select 1
                From acr_invoice_create_tmp t,
                     con_contract           c,
                     fnd_tax_type_codes     tt
               Where t.session_id = p_session_id
                 And t.checked_flag = 'Y'
                 And t.contract_id = c.contract_id
                 And c.tax_type_id = tt.tax_type_id
                 And nvl(tt.vat_flag, 'N') = 'N'
                 And t.invoice_kind In ('0', '2'));
      Raise e_vat_flag_err;
      --合同税种不为增值税VAT_FLAG=N，不能开增值税发票（InvKind≠0，2），只能开营业税发票或收据
    Exception
      When no_data_found Then
        Null;
    End;

  Exception
    When e_leaseback_invoice_kind_err Then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'ACR510_LEASEBACK_INVOICE_KIND_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'ACR_INVOICE_PKG',
                                                      p_procedure_function_name => 'create_invoice_check');

      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    When e_receipt_cf_type_err Then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'ACR510_RECEIPT_CF_TYPE_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'ACR_INVOICE_PKG',
                                                      p_procedure_function_name => 'create_invoice_check');

      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    When e_bp_class_taxpayer_err Then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'ACR510_CREATE_INVOICE_BP_CLASS_TAXPAYER_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'ACR_INVOICE_PKG',
                                                      p_procedure_function_name => 'create_invoice_check');

      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);

    When e_vat_flag_err Then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'ACR510_CREATE_INVOICE_VAT_FLAG_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'ACR_INVOICE_PKG',
                                                      p_procedure_function_name => 'create_invoice_check');

      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);

  End;

  Procedure check_invoice_date(p_company_id          In Number,
                               p_invoice_data        In Date,
                               p_accounting_date     In Date,
                               p_period_name         Out Varchar,
                               p_internal_period_num Out Number) Is
  Begin
    Select a.period_name, a.internal_period_num
      Into p_period_name, p_internal_period_num
      From gld_period_status a, gld_periods b
     Where a.company_id = p_company_id
       And a.period_set_code = b.period_set_code
       And a.period_name = b.period_name
       And p_accounting_date Between b.start_date And b.end_date
       And b.adjustment_flag = 'N';
    -- and a.period_status_code = 'O';
  Exception
    When no_data_found Then
      Raise e_period_not_found;
  End;

  --航天金税 税额检查
  Procedure check_htjs_tolerance(p_invoice_hd_rec acr_invoice_hd%Rowtype,
                                 p_tax_type_rate  Number) Is
    v_hd_tax_amt Number;

  Begin
    Null;
    /*v_hd_tax_amt := round(p_invoice_hd_rec.total_amount /
                          (1 + p_tax_type_rate) * p_tax_type_rate,
                          2);

    if abs(v_hd_tax_amt - p_invoice_hd_rec.tax_amount) > g_htjs_tolerance then

      raise e_htjs_tolerance_error;
    end if;*/

  End;

  --更新发票头上的4个组织字段
  Procedure update_org_info(p_invoice_hd_rec In Out acr_invoice_hd%Rowtype) Is
    v_penalty_count Number;
  Begin
    Select Count(1)
      Into v_penalty_count
      From acr_invoice_ln
     Where invoice_hd_id = p_invoice_hd_rec.invoice_hd_id
       And cf_item = 9;
    If v_penalty_count < 1 Then
      Begin
        Select c.billing_method
          Into p_invoice_hd_rec.billing_method
          From con_contract_cashflow cf, con_contract c, acr_invoice_ln l
         Where cf.cashflow_id = l.cashflow_id
           And cf.contract_id = c.contract_id
           And l.invoice_hd_id = p_invoice_hd_rec.invoice_hd_id
         Group By c.billing_method;
      Exception
        When too_many_rows Then
          p_invoice_hd_rec.billing_method := Null;
      End;

      Begin
        Select c.lease_organization
          Into p_invoice_hd_rec.lease_organization
          From con_contract_cashflow cf, con_contract c, acr_invoice_ln l
         Where cf.cashflow_id = l.cashflow_id
           And cf.contract_id = c.contract_id
           And l.invoice_hd_id = p_invoice_hd_rec.invoice_hd_id
         Group By c.lease_organization;
      Exception
        When too_many_rows Then
          p_invoice_hd_rec.lease_organization := Null;
      End;

      Begin
        Select c.lease_channel
          Into p_invoice_hd_rec.lease_channel
          From con_contract_cashflow cf, con_contract c, acr_invoice_ln l
         Where cf.cashflow_id = l.cashflow_id
           And cf.contract_id = c.contract_id
           And l.invoice_hd_id = p_invoice_hd_rec.invoice_hd_id
         Group By c.lease_channel;
      Exception
        When too_many_rows Then
          p_invoice_hd_rec.lease_channel := Null;
      End;

      Begin
        Select c.division
          Into p_invoice_hd_rec.division
          From con_contract_cashflow cf, con_contract c, acr_invoice_ln l
         Where cf.cashflow_id = l.cashflow_id
           And cf.contract_id = c.contract_id
           And l.invoice_hd_id = p_invoice_hd_rec.invoice_hd_id
         Group By c.division;
      Exception
        When too_many_rows Then
          p_invoice_hd_rec.division := Null;
      End;

      Begin
        Select c.project_id
          Into p_invoice_hd_rec.project_id
          From con_contract_cashflow cf, con_contract c, acr_invoice_ln l
         Where cf.cashflow_id = l.cashflow_id
           And cf.contract_id = c.contract_id
           And l.invoice_hd_id = p_invoice_hd_rec.invoice_hd_id
         Group By c.project_id;
      Exception
        When too_many_rows Then
          p_invoice_hd_rec.project_id := Null;
      End;

      Begin
        Select cf.contract_id
          Into p_invoice_hd_rec.contract_id
          From con_contract_cashflow cf, acr_invoice_ln l
         Where cf.cashflow_id = l.cashflow_id
           And l.invoice_hd_id = p_invoice_hd_rec.invoice_hd_id
         Group By cf.contract_id;
      Exception
        When too_many_rows Then
          p_invoice_hd_rec.contract_id := Null;
      End;
    End If;
  End;

  --更新发票头上的字段
  Procedure update_hd_info(p_invoice_hd_rec In Out Nocopy acr_invoice_hd%Rowtype) Is
    v_total_ln_tax_amt Number;
    v_total_ln_amt     Number;
    v_tax_type_rate    Number;
  Begin
    Select Sum(total_amount), Sum(tax_amount), Min(tax_type_rate)
      Into v_total_ln_amt, v_total_ln_tax_amt, v_tax_type_rate
      From acr_invoice_ln
     Where invoice_hd_id = p_invoice_hd_rec.invoice_hd_id;

    p_invoice_hd_rec.tax_amount   := v_total_ln_tax_amt;
    p_invoice_hd_rec.total_amount := v_total_ln_amt;

    If p_invoice_hd_rec.description Is Null Then
      p_invoice_hd_rec.description := acr_invoice_desc_custom_pkg.get_acr_invoice_head_desc(p_invoice_hd_id => p_invoice_hd_rec.invoice_hd_id,
                                                                                            p_company_id    => p_invoice_hd_rec.company_id);
    End If;

    check_htjs_tolerance(p_invoice_hd_rec => p_invoice_hd_rec,
                         p_tax_type_rate  => v_tax_type_rate);

    update_org_info(p_invoice_hd_rec => p_invoice_hd_rec);

  End;

  --插入单据流
  Procedure insert_doc_flow(p_invoice_hd_rec In Out acr_invoice_hd%Rowtype) Is
  Begin
    For c_ln In (Select a.invoice_ln_id,
                        c.document_category,
                        c.contract_number,
                        cf.cashflow_id,
                        c.contract_id
                   From acr_invoice_ln        a,
                        con_contract_cashflow cf,
                        con_contract          c
                  Where a.invoice_hd_id = p_invoice_hd_rec.invoice_hd_id
                    And a.cashflow_id = cf.cashflow_id
                    And cf.contract_id = c.contract_id) Loop
      hls_document_flow_pkg.insert_document_flow(p_doc_category        => p_invoice_hd_rec.document_category,
                                                 p_doc_id              => p_invoice_hd_rec.invoice_hd_id,
                                                 p_doc_line_id         => c_ln.invoice_ln_id,
                                                 p_doc_number          => p_invoice_hd_rec.invoice_number,
                                                 p_source_doc_category => c_ln.document_category,
                                                 p_source_doc_id       => c_ln.contract_id,
                                                 p_source_doc_line_id  => c_ln.cashflow_id,
                                                 p_source_doc_number   => c_ln.contract_number,
                                                 p_user_id             => p_invoice_hd_rec.created_by);
    End Loop;
  End;

  Procedure insert_trx_user_authority(p_invoice_hd_rec In Out acr_invoice_hd%Rowtype,
                                      p_user_id        In Number) Is
  Begin
    aut_document_authority_pkg.insert_trx_user_authority(p_company_id    => p_invoice_hd_rec.company_id,
                                                         p_owner_user_id => p_invoice_hd_rec.owner_user_id,
                                                         p_start_date    => trunc(Sysdate),
                                                         p_end_date      => to_date('30000101',
                                                                                    'yyyymmdd'),
                                                         p_user_id       => p_user_id,
                                                         p_trx_category  => p_invoice_hd_rec.document_category,
                                                         p_trx_id        => p_invoice_hd_rec.invoice_hd_id);
  End;

  --创建完的发票，整张检查
  Procedure created_invoice_check(p_invoice_hd_rec In Out acr_invoice_hd%Rowtype,
                                  p_user_id        In Number,
                                  p_role_id        In Number) Is
  Begin
    update_hd_info(p_invoice_hd_rec => p_invoice_hd_rec);

    Update acr_invoice_hd
       Set Row = p_invoice_hd_rec
     Where invoice_hd_id = p_invoice_hd_rec.invoice_hd_id;

    insert_doc_flow(p_invoice_hd_rec => p_invoice_hd_rec);

    insert_trx_user_authority(p_invoice_hd_rec => p_invoice_hd_rec,
                              p_user_id        => p_user_id);

  End;

  Procedure insert_invoice_hd(p_invoice_hd_rec acr_invoice_hd%Rowtype) Is
  Begin
    Insert Into acr_invoice_hd Values p_invoice_hd_rec;
  End;

  Procedure insert_invoice_ln(p_invoice_ln_rec acr_invoice_ln%Rowtype) Is
  Begin
    Insert Into acr_invoice_ln Values p_invoice_ln_rec;
  End;

  Function get_invoice_number(p_document_type    Varchar2,
                              p_transaction_date Date,
                              p_company_id       Number,
                              p_user_id          Number) Return Varchar2 Is
    v_no Varchar2(100);
  Begin
    /* haha_test_pkg.log(p_document_type);
    haha_test_pkg.log(p_company_id);*/
    v_no := fnd_code_rule_pkg.get_rule_next_auto_num(p_document_category => g_document_category,
                                                     p_document_type     => p_document_type,
                                                     p_company_id        => p_company_id,
                                                     p_operation_unit_id => Null,
                                                     p_operation_date    => p_transaction_date,
                                                     p_created_by        => p_user_id);
    If v_no = fnd_code_rule_pkg.c_error Then
      Raise e_get_invoice_number_err;
    End If;

    Return v_no;
  End;

  Procedure get_invoice_number(p_document_type    In Out Varchar2,
                               p_transaction_date In Date,
                               p_company_id       In Number,
                               p_user_id          In Number,
                               p_document_number  Out Varchar2,
                               p_business_type    Out Varchar2,
                               p_ln_step_length   Out Number) Is

    v_document_type Varchar2(30);
  Begin
    If p_document_type Is Null Then
      v_document_type := 'STD';
    Else
      v_document_type := p_document_type;
    End If;

    Select business_type, ln_step_length
      Into p_business_type, p_ln_step_length
      From hls_document_type
     Where document_category = g_document_category
       And document_type = v_document_type;

    p_document_number := get_invoice_number(p_document_type    => v_document_type,
                                            p_transaction_date => p_transaction_date,
                                            p_company_id       => p_company_id,
                                            p_user_id          => p_user_id);

    p_document_type := v_document_type;
  End;

  Procedure update_cashflow_ln(p_invoice_hd_rec acr_invoice_hd%Rowtype,
                               p_invoice_ln_rec acr_invoice_ln%Rowtype,
                               p_user_id        Number) Is
    r_cashflow_rec      con_contract_cashflow%Rowtype;
    v_contract_currency con_contract.currency%Type;
    r_billing_method    hls_billing_method%Rowtype;

    v_billing_amount    con_contract_cashflow.billing_amount%Type;
    v_billing_principal con_contract_cashflow.billing_principal%Type;
    v_billing_interest  con_contract_cashflow.billing_interest%Type;
    v_billing_status    con_contract_cashflow.billing_status%Type;
  Begin

    If p_invoice_ln_rec.cashflow_id Is Null Then
      Return;
    End If;

    Begin
      Select *
        Into r_billing_method
        From hls_billing_method
       Where billing_method =
             (Select billing_method
                From con_contract c, con_contract_cashflow ccc
               Where ccc.cashflow_id = p_invoice_ln_rec.cashflow_id
                 And ccc.contract_id = c.contract_id);
    Exception
      When no_data_found Then
        Return;
    End;

    Select *
      Into r_cashflow_rec
      From con_contract_cashflow
     Where cashflow_id = p_invoice_ln_rec.cashflow_id
       For Update Nowait;

    Select currency
      Into v_contract_currency
      From con_contract
     Where contract_id = r_cashflow_rec.contract_id;

    If p_invoice_hd_rec.currency = 'CNY' And v_contract_currency <> 'CNY' Then
      r_cashflow_rec.due_amount := r_cashflow_rec.due_amount_cny;
      r_cashflow_rec.principal  := r_cashflow_rec.principal_cny;
      r_cashflow_rec.interest   := r_cashflow_rec.interest_cny;
    End If;

    If p_invoice_ln_rec.cf_type = 100 Then
      --本金cf
      v_billing_principal := nvl(r_cashflow_rec.billing_principal, 0) +
                             nvl(p_invoice_ln_rec.total_amount, 0);
      v_billing_interest  := r_cashflow_rec.billing_interest;

      v_billing_amount := nvl(r_cashflow_rec.billing_amount, 0) +
                          nvl(p_invoice_ln_rec.total_amount, 0);

      If v_billing_principal > r_cashflow_rec.principal Then
        Raise e_billing_amt_over_err;
      End If;

      If r_billing_method.rental_billing_flag = 'Y' Or
         (r_billing_method.principal_billing_flag = 'Y' And
         r_billing_method.interest_billing_flag = 'Y') Then
        --本+利 or 租金
        If v_billing_amount = r_cashflow_rec.due_amount Then
          v_billing_status := g_bill_status_full;
        Elsif v_billing_amount = 0 Then
          v_billing_status := g_bill_status_not;
        Else
          v_billing_status := g_bill_status_partial;
        End If;

      Elsif r_billing_method.principal_billing_flag = 'Y' And
            nvl(r_billing_method.interest_billing_flag, 'N') = 'N' Then
        --本
        If v_billing_principal = r_cashflow_rec.principal Then
          v_billing_status := g_bill_status_full;
        Elsif v_billing_principal = 0 Then
          v_billing_status := g_bill_status_not;
        Else
          v_billing_status := g_bill_status_partial;
        End If;
      Elsif nvl(r_billing_method.principal_billing_flag, 'N') = 'N' And
            r_billing_method.interest_billing_flag = 'Y' Then
        --利
        If v_billing_interest = r_cashflow_rec.interest Then
          v_billing_status := g_bill_status_full;
        Elsif v_billing_interest = 0 Then
          v_billing_status := g_bill_status_not;
        Else
          v_billing_status := g_bill_status_partial;
        End If;
      End If;

    Elsif p_invoice_ln_rec.cf_type = 101 Then
      --利息cf
      v_billing_principal := r_cashflow_rec.billing_principal;

      v_billing_interest := nvl(r_cashflow_rec.billing_interest, 0) +
                            nvl(p_invoice_ln_rec.total_amount, 0);
      v_billing_amount   := nvl(r_cashflow_rec.billing_amount, 0) +
                            nvl(p_invoice_ln_rec.total_amount, 0);

      If v_billing_interest > r_cashflow_rec.interest Then
        Raise e_billing_amt_over_err;
      End If;

      If r_billing_method.rental_billing_flag = 'Y' Or
         (r_billing_method.principal_billing_flag = 'Y' And
         r_billing_method.interest_billing_flag = 'Y') Then
        --本+利
        If v_billing_amount = r_cashflow_rec.due_amount Then
          v_billing_status := g_bill_status_full;
        Elsif v_billing_amount = 0 Then
          v_billing_status := g_bill_status_not;
        Else
          v_billing_status := g_bill_status_partial;
        End If;

      Elsif r_billing_method.principal_billing_flag = 'Y' And
            nvl(r_billing_method.interest_billing_flag, 'N') = 'N' Then
        --本
        If v_billing_principal = r_cashflow_rec.principal Then
          v_billing_status := g_bill_status_full;
        Elsif v_billing_principal = 0 Then
          v_billing_status := g_bill_status_not;
        Else
          v_billing_status := g_bill_status_partial;
        End If;
      Elsif nvl(r_billing_method.principal_billing_flag, 'N') = 'N' And
            r_billing_method.interest_billing_flag = 'Y' Then
        --利
        If v_billing_interest = r_cashflow_rec.interest Then
          v_billing_status := g_bill_status_full;
        Elsif v_billing_interest = 0 Then
          v_billing_status := g_bill_status_not;
        Else
          v_billing_status := g_bill_status_partial;
        End If;
      End If;

    Else
      --其他类型cf
      v_billing_principal := r_cashflow_rec.billing_principal;

      v_billing_interest := r_cashflow_rec.billing_interest;

      v_billing_amount := nvl(r_cashflow_rec.billing_amount, 0) +
                          nvl(p_invoice_ln_rec.total_amount, 0);

      If v_billing_amount > r_cashflow_rec.due_amount Then
        Raise e_billing_amt_over_err;
      End If;

      If v_billing_amount = r_cashflow_rec.due_amount Then
        v_billing_status := g_bill_status_full;
      Elsif v_billing_amount = 0 Then
        v_billing_status := g_bill_status_not;
      Else
        v_billing_status := g_bill_status_partial;
      End If;

    End If;

    Update con_contract_cashflow
       Set billing_status    = v_billing_status,
           billing_amount    = v_billing_amount,
           billing_principal = v_billing_principal,
           billing_interest  = v_billing_interest,
           last_updated_by   = p_user_id,
           last_update_date  = Sysdate
     Where cashflow_id = p_invoice_ln_rec.cashflow_id;

  End;

  Procedure split_invoice_hd(p_invoice_tmp_hd_rec In invoice_hd_tmp_type,
                             p_invoice_hd_rec     In Out acr_invoice_hd%Rowtype,
                             p_ln_step_length     In Number,
                             p_session_id         In Number,
                             p_user_id            In Number,
                             p_role_id            In Number) Is

    v_total_net_amount Number;
    v_total_amount     Number;

    v_invoice_limit     Number;
    v_avg_split_qty     Number;
    v_avg_split_amt     Number;
    v_avg_split_tax_amt Number;
    v_avg_split_net_amt Number;

    v_remain_split_total_amt Number; --剩余拆分金额
    v_remain_split_tax_amt   Number; --剩余拆分税额
    v_remain_split_net_amt   Number; --剩余拆分不含税金额

    v_invoice_line_limit  hls_billing_method.invoice_line_limit%Type;
    v_bill_of_sale        hls_billing_method.bill_of_sale%Type;
    v_average_split       hls_billing_method.average_split%Type;
    v_invoice_split_limit hls_billing_method.invoice_split_limit%Type;
    v_split_rounding      hls_billing_method.split_rounding%Type;

    r_invoice_hd_new_rec acr_invoice_hd%Rowtype;
    r_invoice_ln_new_rec acr_invoice_ln%Rowtype;
    r_invoice_ln_rec     acr_invoice_ln%Rowtype;

    n Number := 0;

    v_ln_count          Number;
    v_acr_invoice_hd_id Number;
    v_acr_invoice_ln_id Number;

    v_cash_total_amount     Number; --应收金额总额
    v_cash_total_vat_amount Number; -- 税额总额
    Procedure amt_split Is

    Begin
      --金额拆分
      Select Sum(net_amount), Sum(total_amount)
        Into v_total_net_amount, v_total_amount
        From acr_invoice_ln
       Where invoice_hd_id = p_invoice_hd_rec.invoice_hd_id;

      -- @Description:现金流需开票总金额, 开票总税额  @Date:20/6.16  @Modified:Liyuan.Chen
      Select Sum(t.due_amount), Sum(t.vat_due_amount)
        Into v_cash_total_amount, v_cash_total_vat_amount
        From acr_invoice_ln l, con_contract_cashflow t
       Where l.invoice_hd_id = p_invoice_hd_rec.invoice_hd_id
         And l.cashflow_id = t.cashflow_id;
      If v_average_split = 'Y' Then

        If v_total_net_amount <= v_invoice_limit Then
          created_invoice_check(p_invoice_hd_rec => p_invoice_hd_rec,
                                p_user_id        => p_user_id,
                                p_role_id        => p_role_id);
          Return;
        End If;

        If v_invoice_limit Is Null Then
          Raise e_invoice_limit_not_found;
        End If;

        If v_ln_count > 1 Then
          Raise e_billing_amt_split_err;
        End If;

        Select *
          Into r_invoice_ln_rec
          From acr_invoice_ln
         Where invoice_hd_id = p_invoice_hd_rec.invoice_hd_id;

        ------------------平均拆分---------------------

        --拆分发票的数量
        v_avg_split_qty := floor(v_total_net_amount / v_invoice_limit) + 1;
        --拆分发票的平均金额

        If r_invoice_ln_rec.tax_included_flag = 'Y' Then

          v_avg_split_amt := trunc(v_total_amount / v_avg_split_qty,
                                   v_split_rounding);

          v_avg_split_tax_amt := round(v_avg_split_amt /
                                       (1 + r_invoice_ln_rec.tax_type_rate) *
                                       r_invoice_ln_rec.tax_type_rate,
                                       g_round_n);
          v_avg_split_net_amt := v_avg_split_amt - v_avg_split_tax_amt;

          --检查最后一张拆分发票的剩余金额是否还超过限额
          v_remain_split_total_amt := v_total_amount -
                                      (v_avg_split_amt *
                                      (v_avg_split_qty - 1));

          v_remain_split_tax_amt := v_remain_split_total_amt /
                                    (1 + r_invoice_ln_rec.tax_type_rate) *
                                    r_invoice_ln_rec.tax_type_rate;
          v_remain_split_net_amt := v_remain_split_total_amt -
                                    v_remain_split_tax_amt;

        Else
          v_avg_split_net_amt := trunc(v_total_net_amount / v_avg_split_qty,
                                       v_split_rounding);

          v_avg_split_tax_amt := round(v_avg_split_net_amt *
                                       r_invoice_ln_rec.tax_type_rate,
                                       g_round_n);

          v_avg_split_amt := v_avg_split_net_amt + v_avg_split_tax_amt;

          --检查最后一张拆分发票的剩余金额是否还超过限额
          v_remain_split_net_amt := v_total_net_amount -
                                    (v_avg_split_net_amt *
                                    (v_avg_split_qty - 1));

          v_remain_split_tax_amt := round(v_remain_split_net_amt *
                                          r_invoice_ln_rec.tax_type_rate,
                                          g_round_n);

          v_remain_split_total_amt := v_remain_split_net_amt +
                                      v_remain_split_tax_amt;

        End If;

        If v_avg_split_net_amt > v_invoice_limit Or
           v_remain_split_net_amt > v_invoice_limit Then

          v_avg_split_qty := v_avg_split_qty + 1;

          If r_invoice_ln_rec.tax_included_flag = 'Y' Then

            v_avg_split_amt := trunc(v_total_amount / v_avg_split_qty,
                                     v_split_rounding);

            v_avg_split_tax_amt := round(v_avg_split_amt / (1 +
                                         r_invoice_ln_rec.tax_type_rate) *
                                         r_invoice_ln_rec.tax_type_rate,
                                         g_round_n);
            v_avg_split_net_amt := v_avg_split_amt - v_avg_split_tax_amt;

            --最后一张拆分发票的剩余金额
            v_remain_split_total_amt := v_total_amount -
                                        (v_avg_split_amt *
                                        (v_avg_split_qty - 1));

            v_remain_split_tax_amt := v_remain_split_total_amt /
                                      (1 + r_invoice_ln_rec.tax_type_rate) *
                                      r_invoice_ln_rec.tax_type_rate;
            v_remain_split_net_amt := v_remain_split_total_amt -
                                      v_remain_split_tax_amt;

          Else
            v_avg_split_net_amt := trunc(v_total_net_amount /
                                         v_avg_split_qty,
                                         v_split_rounding);

            v_avg_split_tax_amt := round(v_avg_split_net_amt *
                                         r_invoice_ln_rec.tax_type_rate,
                                         g_round_n);

            v_avg_split_amt := v_avg_split_net_amt + v_avg_split_tax_amt;

            --最后一张拆分发票的剩余金额
            v_remain_split_net_amt := v_total_net_amount -
                                      (v_avg_split_net_amt *
                                      (v_avg_split_qty - 1));

            v_remain_split_tax_amt := round(v_remain_split_net_amt *
                                            r_invoice_ln_rec.tax_type_rate,
                                            g_round_n);

            v_remain_split_total_amt := v_remain_split_net_amt +
                                        v_remain_split_tax_amt;

          End If;

        End If;

      Else
        --最大限额拆分

        If v_invoice_split_limit Is Null Then
          created_invoice_check(p_invoice_hd_rec => p_invoice_hd_rec,
                                p_user_id        => p_user_id,
                                p_role_id        => p_role_id);
          Return;
        End If;

        If v_total_net_amount <= v_invoice_split_limit Then
          created_invoice_check(p_invoice_hd_rec => p_invoice_hd_rec,
                                p_user_id        => p_user_id,
                                p_role_id        => p_role_id);
          Return;
        End If;

        If v_ln_count > 1 Then
          Raise e_billing_amt_split_err;
        End If;

        Select *
          Into r_invoice_ln_rec
          From acr_invoice_ln
         Where invoice_hd_id = p_invoice_hd_rec.invoice_hd_id;

        If r_invoice_ln_rec.tax_included_flag = 'Y' Then

          v_avg_split_qty := ceil(v_total_net_amount /
                                  v_invoice_split_limit);

          v_avg_split_net_amt := v_invoice_split_limit;
          v_avg_split_tax_amt := round(v_avg_split_net_amt *
                                       r_invoice_ln_rec.tax_type_rate,
                                       g_round_n);

          v_avg_split_amt := v_avg_split_net_amt + v_avg_split_tax_amt;

          --检查最后一张拆分发票的剩余金额是否还超过限额

          /*v_remain_split_net_amt := v_total_net_amount -
                                    ((v_avg_split_qty - 1) *
                                    v_avg_split_net_amt);

          v_remain_split_tax_amt := (v_total_amount - v_total_net_amount) -
                                    ((v_avg_split_qty - 1) *
                                    v_avg_split_tax_amt);*/

          -- @Description:应业务要求, 用现金流需开票总金额相减得最后一张发票金额  @Date:20/6.16  @Modified:Liyuan.Chen

          v_remain_split_net_amt := v_cash_total_amount -
                                    v_cash_total_vat_amount -
                                    ((v_avg_split_qty - 1) *
                                    v_avg_split_net_amt);

          v_remain_split_tax_amt := v_cash_total_vat_amount -
                                    ((v_avg_split_qty - 1) *
                                    v_avg_split_tax_amt);

          v_remain_split_total_amt := v_remain_split_net_amt +
                                      v_remain_split_tax_amt;

        Else

          v_avg_split_qty := ceil(v_total_net_amount /
                                  v_invoice_split_limit);

          v_avg_split_net_amt := v_invoice_split_limit;

          v_avg_split_tax_amt := round(v_avg_split_net_amt *
                                       r_invoice_ln_rec.tax_type_rate,
                                       g_round_n);

          v_avg_split_amt := v_avg_split_net_amt + v_avg_split_tax_amt;

          --检查最后一张拆分发票的剩余金额是否还超过限额
          /*v_remain_split_net_amt := v_total_net_amount -
                                    (v_avg_split_net_amt *
                                    (v_avg_split_qty - 1));

          v_remain_split_tax_amt := (v_total_amount - v_total_net_amount) -
                                    ((v_avg_split_qty - 1) *
                                    v_avg_split_tax_amt);*/

          -- @Description:应业务要求, 用现金流需开票总金额相减得最后一张发票金额  @Date:20/6.16  @Modified:Liyuan.Chen

          v_remain_split_net_amt := v_cash_total_amount -
                                    v_cash_total_vat_amount -
                                    ((v_avg_split_qty - 1) *
                                    v_avg_split_net_amt);

          v_remain_split_tax_amt := v_cash_total_vat_amount -
                                    ((v_avg_split_qty - 1) *
                                    v_avg_split_tax_amt);

          v_remain_split_total_amt := v_remain_split_net_amt +
                                      v_remain_split_tax_amt;

        End If;

      End If;

      For i In 2 .. v_avg_split_qty Loop
        Select acr_invoice_hd_s.nextval Into v_acr_invoice_hd_id From dual;
        Select acr_invoice_ln_s.nextval Into v_acr_invoice_ln_id From dual;

        r_invoice_hd_new_rec                 := p_invoice_hd_rec;
        r_invoice_hd_new_rec.invoice_hd_id   := v_acr_invoice_hd_id;
        r_invoice_hd_new_rec.document_number := get_invoice_number(p_document_type    => p_invoice_hd_rec.document_type,
                                                                   p_transaction_date => p_invoice_hd_rec.accounting_date,
                                                                   p_company_id       => p_invoice_hd_rec.company_id,
                                                                   p_user_id          => p_invoice_hd_rec.created_by);

        r_invoice_hd_new_rec.total_amount := v_avg_split_amt;
        r_invoice_hd_new_rec.tax_amount   := v_avg_split_tax_amt;

        --创建新的发票行记录
        r_invoice_ln_new_rec               := r_invoice_ln_rec;
        r_invoice_ln_new_rec.invoice_ln_id := v_acr_invoice_ln_id;
        r_invoice_ln_new_rec.invoice_hd_id := r_invoice_hd_new_rec.invoice_hd_id;
        r_invoice_ln_new_rec.price         := v_avg_split_amt;
        r_invoice_ln_new_rec.total_amount  := v_avg_split_amt;
        r_invoice_ln_new_rec.tax_amount    := v_avg_split_tax_amt;
        r_invoice_ln_new_rec.net_amount    := v_avg_split_net_amt;
        r_invoice_ln_new_rec.net_price     := v_avg_split_net_amt;

        --插入新的发票头
        insert_invoice_hd(r_invoice_hd_new_rec);
        --插入新的发票行
        insert_invoice_ln(r_invoice_ln_new_rec);

        created_invoice_check(p_invoice_hd_rec => r_invoice_hd_new_rec,
                              p_user_id        => p_user_id,
                              p_role_id        => p_role_id);

      End Loop;

      --重算原（第1张）发票头（行）的金额字段

      p_invoice_hd_rec.total_amount := v_remain_split_total_amt;
      p_invoice_hd_rec.tax_amount   := v_remain_split_tax_amt;

      r_invoice_ln_rec.price        := v_remain_split_total_amt;
      r_invoice_ln_rec.total_amount := v_remain_split_total_amt;
      r_invoice_ln_rec.tax_amount   := v_remain_split_tax_amt;
      r_invoice_ln_rec.net_amount   := v_remain_split_net_amt;
      r_invoice_ln_rec.net_price    := v_remain_split_net_amt;

      Update acr_invoice_hd
         Set Row = p_invoice_hd_rec
       Where invoice_hd_id = p_invoice_hd_rec.invoice_hd_id;

      Update acr_invoice_ln
         Set Row = r_invoice_ln_rec
       Where invoice_ln_id = r_invoice_ln_rec.invoice_ln_id;

      created_invoice_check(p_invoice_hd_rec => p_invoice_hd_rec,
                            p_user_id        => p_user_id,
                            p_role_id        => p_role_id);

      /*else
          ------------------最大金额拆分  -----------------------
          loop
            exit when v_total_amount <= v_invoice_split_limit;

            --创建新的发票头记录
            r_invoice_hd_new_rec                 := p_invoice_hd_rec;
            r_invoice_hd_new_rec.invoice_hd_id   := acr_invoice_hd_s.nextval;
            r_invoice_hd_new_rec.document_number := get_invoice_number(p_document_type    => p_invoice_hd_rec.document_type,
                                                                       p_transaction_date => p_invoice_hd_rec.accounting_date,
                                                                       p_company_id       => p_invoice_hd_rec.company_id,
                                                                       p_user_id          => p_invoice_hd_rec.created_by);


            r_invoice_hd_new_rec.total_amount := v_invoice_split_limit;


            --创建新的发票行记录
            r_invoice_ln_new_rec               := r_invoice_ln_rec;
            r_invoice_ln_new_rec.invoice_ln_id := acr_invoice_ln_s.nextval;
            r_invoice_ln_new_rec.invoice_hd_id := r_invoice_hd_new_rec.invoice_hd_id;
            r_invoice_ln_new_rec.price         := r_invoice_hd_new_rec.total_amount;
            r_invoice_ln_new_rec.total_amount  := r_invoice_hd_new_rec.total_amount;
            r_invoice_ln_new_rec.tax_amount    := round(r_invoice_ln_new_rec.total_amount / (1 +
                                                        r_invoice_ln_new_rec.tax_type_rate) *
                                                        r_invoice_ln_new_rec.tax_type_rate,
                                                        g_round_n);

            r_invoice_ln_new_rec.net_amount := r_invoice_ln_new_rec.total_amount -
                                               r_invoice_ln_new_rec.net_amount;
            r_invoice_ln_new_rec.net_price  := r_invoice_ln_new_rec.net_amount;

            --插入新的发票头
            insert_invoice_hd(r_invoice_hd_new_rec);
            --插入新的发票行
            insert_invoice_ln(r_invoice_ln_new_rec);
            created_invoice_check(p_invoice_hd_rec => r_invoice_hd_new_rec,
                                  p_user_id        => p_user_id,
                                  p_role_id        => p_role_id);

            --重算原（第1张）发票头（行）的金额字段
            r_invoice_ln_rec.total_amount := r_invoice_ln_rec.total_amount - r_invoice_ln_new_rec.total_amount;
            r_invoice_ln_rec.net_amount := r_invoice_ln_rec.net_amount - r_invoice_ln_new_rec.net_amount;
            r_invoice_ln_rec.tax_amount := r_invoice_ln_rec.tax_amount - r_invoice_ln_new_rec.tax_amount;
            r_invoice_ln_rec.price      := r_invoice_ln_rec.total_amount;
            r_invoice_ln_rec.net_price  := r_invoice_ln_rec.net_amount;
          end loop;

          update acr_invoice_hd
             set row = p_invoice_hd_rec
           where invoice_hd_id = p_invoice_hd_rec.invoice_hd_id;

          update acr_invoice_ln
             set row = r_invoice_ln_rec
           where invoice_ln_id = r_invoice_ln_rec.invoice_ln_id;

          created_invoice_check(p_invoice_hd_rec => p_invoice_hd_rec,
                                p_user_id        => p_user_id,
                                p_role_id        => p_role_id);
        end if;

      end if;*/
    End;

    Procedure qty_split Is

    Begin
      If v_bill_of_sale = 'N' And v_ln_count > v_invoice_line_limit Then

        For i In 2 .. ceil(v_ln_count / v_invoice_line_limit) Loop
          Select acr_invoice_hd_s.nextval
            Into v_acr_invoice_hd_id
            From dual;

          r_invoice_hd_new_rec                 := p_invoice_hd_rec;
          r_invoice_hd_new_rec.invoice_hd_id   := v_acr_invoice_hd_id;
          r_invoice_hd_new_rec.document_number := get_invoice_number(p_document_type    => p_invoice_hd_rec.document_type,
                                                                     p_transaction_date => p_invoice_hd_rec.accounting_date,
                                                                     p_company_id       => p_invoice_hd_rec.company_id,
                                                                     p_user_id          => p_invoice_hd_rec.created_by);
          --得到第i张发票的总金额
          Select Sum(total_amount)
            Into r_invoice_hd_new_rec.total_amount
            From acr_invoice_ln
           Where invoice_hd_id = p_invoice_hd_rec.invoice_hd_id
             And line_number >
                 p_ln_step_length * v_invoice_line_limit * (i - 1)
             And line_number <= p_ln_step_length * v_invoice_line_limit * i;
          --将新发票的发票头id，以及发票行号 更新
          Update acr_invoice_ln
             Set invoice_hd_id = r_invoice_hd_new_rec.invoice_hd_id,
                 line_number   = line_number -
                                 (v_invoice_line_limit * p_ln_step_length)
           Where invoice_hd_id = p_invoice_hd_rec.invoice_hd_id
             And line_number >
                 p_ln_step_length * v_invoice_line_limit * (i - 1)
             And line_number <= p_ln_step_length * v_invoice_line_limit * i;
          --查询新发票头
          insert_invoice_hd(r_invoice_hd_new_rec);
          created_invoice_check(p_invoice_hd_rec => r_invoice_hd_new_rec,
                                p_user_id        => p_user_id,
                                p_role_id        => p_role_id);
          --将第1张发票头总金额重新设置

          Select Sum(total_amount)
            Into p_invoice_hd_rec.total_amount
            From acr_invoice_ln
           Where invoice_hd_id = p_invoice_hd_rec.invoice_hd_id;

          Update acr_invoice_hd
             Set Row = p_invoice_hd_rec
           Where invoice_hd_id = p_invoice_hd_rec.invoice_hd_id;

          created_invoice_check(p_invoice_hd_rec => p_invoice_hd_rec,
                                p_user_id        => p_user_id,
                                p_role_id        => p_role_id);
        End Loop;

      Else

        created_invoice_check(p_invoice_hd_rec => p_invoice_hd_rec,
                              p_user_id        => p_user_id,
                              p_role_id        => p_role_id);

      End If;
    End;

  Begin

    --收据 不用拆分
    If p_invoice_hd_rec.invoice_kind = 'RECEIPT' Then
      created_invoice_check(p_invoice_hd_rec => p_invoice_hd_rec,
                            p_user_id        => p_user_id,
                            p_role_id        => p_role_id);
      Return;
    End If;

    --总发票行数
    Select Count(1)
      Into v_ln_count
      From acr_invoice_ln
     Where invoice_hd_id = p_invoice_hd_rec.invoice_hd_id;

    If p_invoice_tmp_hd_rec.contract_id Is Not Null Then
      Select invoice_line_limit,
             bill_of_sale,
             average_split,
             decode(p_invoice_hd_rec.invoice_kind,
                    '0',
                    vat_invoice_split_limit,
                    '2',
                    invoice_split_limit,
                    'SALES_TAX',
                    sales_tax_invoice_split_limit,
                    Null),
             split_rounding
        Into v_invoice_line_limit,
             v_bill_of_sale,
             v_average_split,
             v_invoice_split_limit,
             v_split_rounding
        From hls_billing_method
       Where billing_method =
             (Select billing_method
                From con_contract
               Where contract_id = p_invoice_tmp_hd_rec.contract_id);
    Elsif v_ln_count = 1 Then
      Select invoice_line_limit,
             bill_of_sale,
             average_split,
             decode(p_invoice_hd_rec.invoice_kind,
                    '0',
                    vat_invoice_split_limit,
                    '2',
                    invoice_split_limit,
                    'SALES_TAX',
                    sales_tax_invoice_split_limit,
                    Null),
             split_rounding
        Into v_invoice_line_limit,
             v_bill_of_sale,
             v_average_split,
             v_invoice_split_limit,
             v_split_rounding
        From hls_billing_method
       Where billing_method =
             (Select c.billing_method
                From acr_invoice_ln        a,
                     con_contract_cashflow b,
                     con_contract          c
               Where a.invoice_hd_id = p_invoice_hd_rec.invoice_hd_id
                 And a.cashflow_id = b.cashflow_id
                 And b.contract_id = c.contract_id);
    Else

      Select invoice_line_limit,
             bill_of_sale,
             average_split,
             decode(p_invoice_hd_rec.invoice_kind,
                    '0',
                    vat_invoice_split_limit,
                    '2',
                    invoice_split_limit,
                    'SALES_TAX',
                    sales_tax_invoice_split_limit,
                    Null),
             split_rounding
        Into v_invoice_line_limit,
             v_bill_of_sale,
             v_average_split,
             v_invoice_split_limit,
             v_split_rounding
        From hls_billing_method
       Where billing_method =
             (Select c.billing_method
                From acr_invoice_ln        a,
                     con_contract_cashflow b,
                     con_contract          c
               Where a.invoice_hd_id = p_invoice_hd_rec.invoice_hd_id
                 And a.cashflow_id = b.cashflow_id
                 And b.contract_id = c.contract_id
                 And rownum = 1)
         And rownum = 1;
    End If;

    If v_invoice_split_limit Is Null Then
      Select decode(p_invoice_hd_rec.invoice_kind,
                    '0',
                    vat_invoice_limit,
                    '2',
                    invoice_limit,
                    'SALES_TAX',
                    sales_tax_invoice_limit,
                    Null)
        Into v_invoice_split_limit
        From fnd_companies
       Where company_id = nvl(p_invoice_hd_rec.spv_company_id,
                              p_invoice_hd_rec.company_id);

    End If;

    v_invoice_limit := v_invoice_split_limit;

    ------------------------金额拆分 ---------------------------------
    amt_split;

    ------------------------行数拆分 ---------------------------------
    qty_split;

  End;

  Procedure create_invoice_hd(p_invoice_tmp_hd_rec  In invoice_hd_tmp_type,
                              p_invoice_date        In Date,
                              p_accounting_date     In Date,
                              p_period_name         In Varchar2,
                              p_internal_period_num In Number,
                              p_invoice_hd_rec      Out acr_invoice_hd%Rowtype,
                              p_ln_step_length      Out Number,
                              p_user_id             In Number,
                              p_role_id             In Number) Is
    r_invoice_hd        acr_invoice_hd%Rowtype;
    v_acr_invoice_hd_id Number;
  Begin
    Select acr_invoice_hd_s.nextval Into v_acr_invoice_hd_id From dual;
    r_invoice_hd.invoice_hd_id     := v_acr_invoice_hd_id;
    r_invoice_hd.company_id        := p_invoice_tmp_hd_rec.company_id;
    r_invoice_hd.spv_company_id    := p_invoice_tmp_hd_rec.spv_company_id;
    r_invoice_hd.document_type     := p_invoice_tmp_hd_rec.invoice_document_type;
    r_invoice_hd.document_category := g_document_category;
    get_invoice_number(p_document_type    => r_invoice_hd.document_type,
                       p_transaction_date => p_accounting_date,
                       p_company_id       => p_invoice_tmp_hd_rec.company_id,
                       p_user_id          => p_user_id,
                       p_document_number  => r_invoice_hd.document_number,
                       p_ln_step_length   => p_ln_step_length,
                       p_business_type    => r_invoice_hd.business_type);

    r_invoice_hd.manual_invoice_flag      := 'N';
    r_invoice_hd.bp_id                    := p_invoice_tmp_hd_rec.bp_id;
    r_invoice_hd.bp_name                  := p_invoice_tmp_hd_rec.bp_name;
    r_invoice_hd.bp_tax_registry_num      := p_invoice_tmp_hd_rec.bp_tax_registry_num;
    r_invoice_hd.bp_address_phone_num     := p_invoice_tmp_hd_rec.bp_address_phone_num;
    r_invoice_hd.bp_bank_account          := p_invoice_tmp_hd_rec.bp_bank_account;
    r_invoice_hd.description              := p_invoice_tmp_hd_rec.description;
    r_invoice_hd.currency                 := p_invoice_tmp_hd_rec.currency;
    r_invoice_hd.exchange_rate_type       := p_invoice_tmp_hd_rec.exchange_rate_type;
    r_invoice_hd.exchange_rate_quotation  := p_invoice_tmp_hd_rec.exchange_rate_quotation;
    r_invoice_hd.exchange_rate            := p_invoice_tmp_hd_rec.exchange_rate;
    r_invoice_hd.distribution_set_id      := Null;
    r_invoice_hd.internal_period_num      := p_internal_period_num;
    r_invoice_hd.period_name              := p_period_name;
    r_invoice_hd.accounting_date          := p_accounting_date;
    r_invoice_hd.invoice_date             := p_invoice_date;
    r_invoice_hd.invoice_number           := '';
    r_invoice_hd.invoice_status           := 'NEW';
    r_invoice_hd.project_id               := p_invoice_tmp_hd_rec.project_id;
    r_invoice_hd.contract_id              := p_invoice_tmp_hd_rec.contract_id;
    r_invoice_hd.reversed_flag            := 'N';
    r_invoice_hd.source_invoice_header_id := Null;
    r_invoice_hd.create_je_flag           := 'N';
    r_invoice_hd.gld_interface_flag       := 'N';
    r_invoice_hd.confirmed_date           := Null;
    r_invoice_hd.confirmed_by             := Null;
    r_invoice_hd.posted_date              := Null;
    r_invoice_hd.posted_by                := Null;
    r_invoice_hd.invoice_kind             := p_invoice_tmp_hd_rec.invoice_kind;
    r_invoice_hd.vat_interface_status     := 'UNTRANSFERED';
    r_invoice_hd.vat_invoice_code         := Null;
    r_invoice_hd.vat_invoice_status       := Null;
    r_invoice_hd.vat_red_notice_num       := Null;
    r_invoice_hd.ref_vat_invoice_code     := Null;
    r_invoice_hd.ref_invoice_number       := Null;
    r_invoice_hd.owner_user_id            := p_user_id;
    r_invoice_hd.created_by               := p_user_id;
    r_invoice_hd.creation_date            := Sysdate;
    r_invoice_hd.last_updated_by          := p_user_id;
    r_invoice_hd.last_update_date         := Sysdate;

    insert_invoice_hd(r_invoice_hd);

    p_invoice_hd_rec := r_invoice_hd;

    hls_doc_operate_history_pkg.insert_doc_operate_history(p_document_category => r_invoice_hd.document_category,
                                                           p_document_id       => r_invoice_hd.invoice_hd_id,
                                                           p_operation_code    => hls_doc_operate_history_pkg.c_generate,
                                                           p_user_id           => p_user_id,
                                                           p_operation_time    => Sysdate,
                                                           p_description       => Null);

  End;
  --add by niminmin
  --罚息开票不记录现金流,取凭证维度
  Procedure create_invoice_ln_penalty(p_invoice_tmp_hd_rec In invoice_hd_tmp_type,
                                      p_invoice_hd_rec     In acr_invoice_hd%Rowtype,
                                      p_ln_step_length     In Number,
                                      p_session_id         In Number,
                                      p_user_id            In Number) Is

    r_invoice_tmp_ln invoice_penalty_tmp_type;
    r_invoice_ln     acr_invoice_ln%Rowtype;
    v_tax_amount     Number;
    v_net_amount     Number;
    v_invoice_hd_id  Number;
    billing_amount_error Exception;

    v_billlingmethod hls_billing_method%Rowtype;
    v_contract       con_contract%Rowtype;

    v_round_n              Number;
    v_old_sum_total_amount number; --本次开票之前已开票总额
    v_old_sum_tax_amount   number; --本次开票之前已开票税额
    v_old_tax_amount       number; --凭证上税额

    n                   Number := 0;
    v_acr_invoice_ln_id Number;

    Cursor cur_invoice_tmp_ln Is
      Select c.contract_id,
             t.cf_item,
             t.cf_type,
             t.product_name,
             t.billing_amount,
             t.tax_amount,
             t.journal_header_id,
             t.journal_line_id,
              t.due_amount
        From acr_invoice_create_tmp t,
             con_contract           c,
             hls_document_type      dt,
             fnd_tax_type_codes     tt
       Where t.session_id = p_session_id
         And t.contract_id = c.contract_id
         And c.document_type = dt.document_type
         And dt.document_category = 'CONTRACT'
            --and c.tax_type_id = tt.tax_type_id
         And t.tax_type_id = tt.tax_type_id
         And t.checked_flag = 'Y'
         And c.company_id = p_invoice_tmp_hd_rec.company_id
         And (c.spv_company_id = p_invoice_tmp_hd_rec.spv_company_id Or
             (c.spv_company_id Is Null And
             p_invoice_tmp_hd_rec.spv_company_id Is Null))
         And t.invoice_kind = p_invoice_tmp_hd_rec.invoice_kind
         And t.currency = p_invoice_tmp_hd_rec.currency
         And t.bp_id = p_invoice_tmp_hd_rec.bp_id
         And nvl(t.exchange_rate_type, 'MANUAL') =
             p_invoice_tmp_hd_rec.exchange_rate_type
         And nvl(t.exchange_rate_quotation, 'DIRECT QUOTATION') =
             p_invoice_tmp_hd_rec.exchange_rate_quotation
         And nvl(t.exchange_rate, 1) = p_invoice_tmp_hd_rec.exchange_rate
            --以下三个字段条件： 原始表上字段不会为空的
         And (c.contract_id = p_invoice_tmp_hd_rec.contract_id Or
             p_invoice_tmp_hd_rec.contract_id Is Null)
         And (c.project_id = p_invoice_tmp_hd_rec.project_id Or
             p_invoice_tmp_hd_rec.project_id Is Null)
         And (t.times = p_invoice_tmp_hd_rec.times Or
             (p_invoice_tmp_hd_rec.times Is Null))
            --以下字段条件：等号两边都有可能为空
         And (t.invoice_title = p_invoice_tmp_hd_rec.bp_name Or
             (t.invoice_title Is Null And
             p_invoice_tmp_hd_rec.bp_name Is Null))
         And (t.invoice_bp_address_phone_num =
             p_invoice_tmp_hd_rec.bp_address_phone_num Or
             (t.invoice_bp_address_phone_num Is Null And
             p_invoice_tmp_hd_rec.bp_address_phone_num Is Null))

         And (t.invoice_bp_tax_registry_num =
             p_invoice_tmp_hd_rec.bp_tax_registry_num Or
             (t.invoice_bp_tax_registry_num Is Null And
             p_invoice_tmp_hd_rec.bp_tax_registry_num Is Null))

         And (t.invoice_bp_bank_account =
             p_invoice_tmp_hd_rec.bp_bank_account Or
             (t.invoice_bp_bank_account Is Null And
             p_invoice_tmp_hd_rec.bp_bank_account Is Null))
         And (t.head_description = p_invoice_tmp_hd_rec.description Or
             (t.head_description Is Null And
             p_invoice_tmp_hd_rec.description Is Null))
         And (dt.ref_document_type =
             p_invoice_tmp_hd_rec.invoice_document_type Or
             (dt.ref_document_type Is Null Or
             p_invoice_tmp_hd_rec.invoice_document_type Is Null))

            --and c.tax_type_id = p_invoice_tmp_hd_rec.tax_type_id
         And t.tax_type_id = p_invoice_tmp_hd_rec.tax_type_id
         And tt.tax_type_rate = p_invoice_tmp_hd_rec.tax_type_rate

       Order By c.contract_number
         For Update Nowait;
  Begin

    v_invoice_hd_id := p_invoice_hd_rec.invoice_hd_id;

    If p_invoice_hd_rec.contract_id Is Not Null Then
      Select *
        Into v_contract
        From con_contract t
       Where t.contract_id = p_invoice_hd_rec.contract_id;
      v_billlingmethod := get_billmethod_rec(v_contract.billing_method);
    End If;

    Open cur_invoice_tmp_ln;
    Loop
      Fetch cur_invoice_tmp_ln
        Into r_invoice_tmp_ln;
      Exit When cur_invoice_tmp_ln%Notfound;
      n := n + 1;
      Begin
        Select currency_precision
          Into v_round_n
          From con_contract a
         Where a.contract_id = r_invoice_tmp_ln.contract_id;
      Exception
        When no_data_found Then
          v_round_n := Null;
      End;
      v_round_n := nvl(v_round_n, g_round_n);

      Select acr_invoice_ln_s.nextval Into v_acr_invoice_ln_id From dual;
      r_invoice_ln.invoice_ln_id := v_acr_invoice_ln_id;
      r_invoice_ln.invoice_hd_id := v_invoice_hd_id;
      r_invoice_ln.line_type     := 'SERVICE';
      r_invoice_ln.line_number   := n * p_ln_step_length;
      r_invoice_ln.cashflow_id   := Null;
      r_invoice_ln.cf_item       := r_invoice_tmp_ln.cf_item;
      r_invoice_ln.cf_type       := r_invoice_tmp_ln.cf_type;
      r_invoice_ln.product_id    := '';
      r_invoice_ln.product_name  := r_invoice_tmp_ln.product_name; --from tmp
      r_invoice_ln.specification := ''; --from tmp
      r_invoice_ln.quantity      := 1;
      r_invoice_ln.uom           := '';

      r_invoice_ln.price_quantity     := 1;
      r_invoice_ln.price_quantity_uom := '';
      r_invoice_ln.tax_type_id        := p_invoice_tmp_hd_rec.tax_type_id;
      r_invoice_ln.tax_type_rate      := p_invoice_tmp_hd_rec.tax_type_rate;
      r_invoice_ln.tax_included_flag  := 'Y';
      r_invoice_ln.total_amount       := r_invoice_tmp_ln.billing_amount;
      r_invoice_ln.tax_amount         := r_invoice_tmp_ln.tax_amount;
      r_invoice_ln.net_amount         := r_invoice_tmp_ln.billing_amount -
                                         r_invoice_tmp_ln.tax_amount;
      r_invoice_ln.net_price          := r_invoice_ln.net_amount;
      r_invoice_ln.price              := r_invoice_tmp_ln.billing_amount;

      r_invoice_ln.responsibility_center_id := '';
      r_invoice_ln.account_id               := '';
      r_invoice_ln.description              := '';
      r_invoice_ln.created_by               := p_user_id;
      r_invoice_ln.creation_date            := Sysdate;
      r_invoice_ln.last_updated_by          := p_user_id;
      r_invoice_ln.last_update_date         := Sysdate;
      r_invoice_ln.ref_n03                  := r_invoice_tmp_ln.journal_header_id;
      r_invoice_ln.ref_n04                  := r_invoice_tmp_ln.journal_line_id;

      insert_invoice_ln(r_invoice_ln);

      --  update_cashflow_ln(p_invoice_hd_rec => p_invoice_hd_rec,
      --                      p_invoice_ln_rec => r_invoice_ln,
      --                     p_user_id        => p_user_id);
    End Loop;

    Close cur_invoice_tmp_ln;

  Exception
    When billing_amount_error Then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => '开票金额有误！',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'ACR_INVOICE_PKG',
                                                      p_procedure_function_name => 'create_invoice_ln');

      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    When Others Then
      If cur_invoice_tmp_ln%Isopen Then
        Close cur_invoice_tmp_ln;
      End If;

      Raise;
  End;

  --modify by Spencer 3893 20161012 税率取根据规则获取的acr_invoice_create_tmp，不取合同表
  Procedure create_invoice_ln(p_invoice_tmp_hd_rec In invoice_hd_tmp_type,
                              p_invoice_hd_rec     In acr_invoice_hd%Rowtype,
                              p_ln_step_length     In Number,
                              p_session_id         In Number,
                              p_user_id            In Number) Is

    r_invoice_tmp_ln        invoice_ln_tmp_type;
    r_invoice_ln            acr_invoice_ln%Rowtype;
    r_con_contract_cashflow con_contract_cashflow%Rowtype;
    v_tax_amount            Number;
    v_net_amount            Number;
    v_invoice_hd_id         Number;
    billing_amount_error Exception;

    v_billlingmethod hls_billing_method%Rowtype;
    v_contract       con_contract%Rowtype;

    v_round_n Number;

    n                   Number := 0;
    v_acr_invoice_ln_id Number;
    Cursor cur_invoice_tmp_ln Is
      Select c.contract_id,
             t.cashflow_id,
             t.cf_item,
             t.cf_type,
             t.product_name,
             t.billing_amount
        From acr_invoice_create_tmp t,
             con_contract           c,
             con_contract_cashflow  cf,
             hls_document_type      dt,
             fnd_tax_type_codes     tt
       Where t.session_id = p_session_id
         And t.contract_id = c.contract_id
         And t.cashflow_id = cf.cashflow_id
         And c.document_type = dt.document_type
         And dt.document_category = 'CONTRACT'
            --and c.tax_type_id = tt.tax_type_id
         And t.tax_type_id = tt.tax_type_id
         And t.checked_flag = 'Y'
         And c.company_id = p_invoice_tmp_hd_rec.company_id
         And (c.spv_company_id = p_invoice_tmp_hd_rec.spv_company_id Or
             (c.spv_company_id Is Null And
             p_invoice_tmp_hd_rec.spv_company_id Is Null))
         And t.invoice_kind = p_invoice_tmp_hd_rec.invoice_kind
         And t.currency = p_invoice_tmp_hd_rec.currency
         And t.bp_id = p_invoice_tmp_hd_rec.bp_id
         And nvl(t.exchange_rate_type, 'MANUAL') =
             p_invoice_tmp_hd_rec.exchange_rate_type
         And nvl(t.exchange_rate_quotation, 'DIRECT QUOTATION') =
             p_invoice_tmp_hd_rec.exchange_rate_quotation
         And nvl(t.exchange_rate, 1) = p_invoice_tmp_hd_rec.exchange_rate
            --以下三个字段条件： 原始表上字段不会为空的
         And (c.contract_id = p_invoice_tmp_hd_rec.contract_id Or
             p_invoice_tmp_hd_rec.contract_id Is Null)
         And (c.project_id = p_invoice_tmp_hd_rec.project_id Or
             p_invoice_tmp_hd_rec.project_id Is Null)
         And (t.times = p_invoice_tmp_hd_rec.times Or
             (p_invoice_tmp_hd_rec.times Is Null))
            --以下字段条件：等号两边都有可能为空
         And (t.invoice_title = p_invoice_tmp_hd_rec.bp_name Or
             (t.invoice_title Is Null And
             p_invoice_tmp_hd_rec.bp_name Is Null))
         And (t.invoice_bp_address_phone_num =
             p_invoice_tmp_hd_rec.bp_address_phone_num Or
             (t.invoice_bp_address_phone_num Is Null And
             p_invoice_tmp_hd_rec.bp_address_phone_num Is Null))

         And (t.invoice_bp_tax_registry_num =
             p_invoice_tmp_hd_rec.bp_tax_registry_num Or
             (t.invoice_bp_tax_registry_num Is Null And
             p_invoice_tmp_hd_rec.bp_tax_registry_num Is Null))

         And (t.invoice_bp_bank_account =
             p_invoice_tmp_hd_rec.bp_bank_account Or
             (t.invoice_bp_bank_account Is Null And
             p_invoice_tmp_hd_rec.bp_bank_account Is Null))
         And (t.head_description = p_invoice_tmp_hd_rec.description Or
             (t.head_description Is Null And
             p_invoice_tmp_hd_rec.description Is Null))
         And (dt.ref_document_type =
             p_invoice_tmp_hd_rec.invoice_document_type Or
             (dt.ref_document_type Is Null Or
             p_invoice_tmp_hd_rec.invoice_document_type Is Null))

            --and c.tax_type_id = p_invoice_tmp_hd_rec.tax_type_id
         And t.tax_type_id = p_invoice_tmp_hd_rec.tax_type_id
         And tt.tax_type_rate = p_invoice_tmp_hd_rec.tax_type_rate

       Order By c.contract_number, t.times, cf.due_date
         For Update Nowait;
  Begin

    v_invoice_hd_id := p_invoice_hd_rec.invoice_hd_id;

    If p_invoice_hd_rec.contract_id Is Not Null Then
      Select *
        Into v_contract
        From con_contract t
       Where t.contract_id = p_invoice_hd_rec.contract_id;
      v_billlingmethod := get_billmethod_rec(v_contract.billing_method);
    End If;

    Open cur_invoice_tmp_ln;
    Loop
      Fetch cur_invoice_tmp_ln
        Into r_invoice_tmp_ln;
      Exit When cur_invoice_tmp_ln%Notfound;

      n := n + 1;
      Begin
        Select currency_precision
          Into v_round_n
          From con_contract a, con_contract_cashflow b
         Where a.contract_id = b.contract_id
           And b.cashflow_id = r_invoice_tmp_ln.cashflow_id;
      Exception
        When no_data_found Then
          v_round_n := Null;
      End;

      v_round_n := nvl(v_round_n, g_round_n);

      Select acr_invoice_ln_s.nextval Into v_acr_invoice_ln_id From dual;
      r_invoice_ln.invoice_ln_id := v_acr_invoice_ln_id;
      r_invoice_ln.invoice_hd_id := v_invoice_hd_id;
      r_invoice_ln.line_type     := 'SERVICE';
      r_invoice_ln.line_number   := n * p_ln_step_length;
      r_invoice_ln.cashflow_id   := r_invoice_tmp_ln.cashflow_id;
      r_invoice_ln.cf_item       := r_invoice_tmp_ln.cf_item;
      r_invoice_ln.cf_type       := r_invoice_tmp_ln.cf_type;
      r_invoice_ln.product_id    := '';
      r_invoice_ln.product_name  := r_invoice_tmp_ln.product_name; --from tmp
      r_invoice_ln.specification := ''; --from tmp
      r_invoice_ln.quantity      := 1;
      r_invoice_ln.uom           := '';

      r_invoice_ln.price_quantity     := 1;
      r_invoice_ln.price_quantity_uom := '';
      r_invoice_ln.tax_type_id        := p_invoice_tmp_hd_rec.tax_type_id;

      If p_invoice_hd_rec.invoice_kind = 'SALES_TAX' Then

        r_invoice_ln.tax_type_rate     := 0;
        r_invoice_ln.tax_included_flag := 'N';
        r_invoice_ln.net_amount        := r_invoice_tmp_ln.billing_amount;
        r_invoice_ln.tax_amount        := round(r_invoice_tmp_ln.billing_amount *
                                                r_invoice_ln.tax_type_rate,
                                                v_round_n);

        r_invoice_ln.total_amount := nvl(r_invoice_ln.net_amount, 0) +
                                     nvl(r_invoice_ln.tax_amount, 0);
        r_invoice_ln.net_price    := r_invoice_ln.net_amount;
        r_invoice_ln.price        := r_invoice_ln.total_amount;

      Else

        r_invoice_ln.tax_type_rate     := p_invoice_tmp_hd_rec.tax_type_rate;
        r_invoice_ln.tax_included_flag := 'Y';
        r_invoice_ln.total_amount      := r_invoice_tmp_ln.billing_amount;
        --modify by shen 税额取现金流表上对应的税额
        Select *
          Into r_con_contract_cashflow
          From con_contract_cashflow ccc
         Where ccc.cashflow_id = r_invoice_ln.cashflow_id;

        If (r_con_contract_cashflow.cf_item = 1 And
           r_con_contract_cashflow.cf_type = 1) Or
           (r_con_contract_cashflow.cf_item = 2 And
           r_con_contract_cashflow.cf_type = 2) Or
           (r_con_contract_cashflow.cf_item = 8 And
           r_con_contract_cashflow.cf_type = 8) Or
           (r_con_contract_cashflow.cf_item = 11 And
           r_con_contract_cashflow.cf_type = 11) Or
           (r_con_contract_cashflow.cf_item = 200 And
           r_con_contract_cashflow.cf_type = 1) Or
           (r_con_contract_cashflow.cf_item = 250 And
           r_con_contract_cashflow.cf_type = 1) Then

          If r_invoice_tmp_ln.billing_amount =
             r_con_contract_cashflow.principal Then
            v_tax_amount := nvl(r_con_contract_cashflow.vat_principal, 0);
            v_net_amount := nvl(r_con_contract_cashflow.net_principal, 0);
            --modify  by shen 利息税等于租金税减去本金税，利息不含税等于利息-利息税
          Elsif r_invoice_tmp_ln.billing_amount =
                r_con_contract_cashflow.interest Then
            v_tax_amount := nvl(r_con_contract_cashflow.vat_due_amount, 0) -
                            nvl(r_con_contract_cashflow.vat_principal, 0);
            v_net_amount := nvl(r_con_contract_cashflow.interest, 0) -
                            v_tax_amount;

          Elsif r_invoice_tmp_ln.billing_amount =
                r_con_contract_cashflow.due_amount Then
            v_tax_amount := nvl(r_con_contract_cashflow.vat_due_amount, 0);
            v_net_amount := nvl(r_con_contract_cashflow.net_due_amount, 0);
          Else
            Raise billing_amount_error;
          End If;

          r_invoice_ln.tax_amount := v_tax_amount;
          r_invoice_ln.net_amount := v_net_amount;
        Elsif r_con_contract_cashflow.cf_item = 9 And
              r_con_contract_cashflow.cf_type = 9 Then

          Select round(Sum(coo.write_off_due_amount) / 1.13, 2),
                 round(Sum(coo.write_off_due_amount) -
                       Sum(coo.write_off_due_amount) / 1.13,
                       2)
            Into v_net_amount, v_tax_amount
            From csh_write_off coo
           Where coo.cashflow_id = r_con_contract_cashflow.cashflow_id;
          r_invoice_ln.tax_amount := v_tax_amount;
          r_invoice_ln.net_amount := v_net_amount;

        Else
          r_invoice_ln.tax_amount := round(r_invoice_ln.total_amount / (1 +
                                           p_invoice_tmp_hd_rec.tax_type_rate) *
                                           p_invoice_tmp_hd_rec.tax_type_rate,
                                           v_round_n);
          r_invoice_ln.net_amount := r_invoice_ln.total_amount -
                                     r_invoice_ln.tax_amount;
        End If;
        r_invoice_ln.net_price := r_invoice_ln.net_amount;
        r_invoice_ln.price     := r_invoice_tmp_ln.billing_amount;

      End If;

      r_invoice_ln.responsibility_center_id := '';
      r_invoice_ln.account_id               := '';
      r_invoice_ln.description              := '';
      r_invoice_ln.created_by               := p_user_id;
      r_invoice_ln.creation_date            := Sysdate;
      r_invoice_ln.last_updated_by          := p_user_id;
      r_invoice_ln.last_update_date         := Sysdate;

      insert_invoice_ln(r_invoice_ln);

      update_cashflow_ln(p_invoice_hd_rec => p_invoice_hd_rec,
                         p_invoice_ln_rec => r_invoice_ln,
                         p_user_id        => p_user_id);

    End Loop;

    Close cur_invoice_tmp_ln;

  Exception
    When billing_amount_error Then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => '开票金额有误！',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'ACR_INVOICE_PKG',
                                                      p_procedure_function_name => 'create_invoice_ln');

      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    When Others Then
      If cur_invoice_tmp_ln%Isopen Then
        Close cur_invoice_tmp_ln;
      End If;

      Raise;
  End;
  Procedure save_acr_invoice_batch(p_invoice_hd_rec In acr_invoice_hd%Rowtype,
                                   p_user_id        Number) Is
    v_id Number;
  Begin
    For acr_invoice_ln_rec In (Select *
                                 From acr_invoice_ln t
                                Where t.invoice_hd_id =
                                      p_invoice_hd_rec.invoice_hd_id) Loop
      Select acr_invoice_batch_ln_s.nextval Into v_id From dual;
      --单据复制
      hls_document_transfer_pkg.doc_to_doc(p_from_doc_table => 'ACR_INVOICE_LN',
                                           p_from_doc_pk    => acr_invoice_ln_rec.invoice_ln_id,
                                           p_to_doc_table   => 'ACR_INVOICE_BATCH_LN',
                                           p_to_doc_pk      => v_id,
                                           p_copy_method    => 'DOC_TO_HISTORY',
                                           p_user_id        => p_user_id);
    End Loop;
    --null;
  End;
  --modify by Spencer 3893 20161012 税率取根据规则获取的acr_invoice_create_tmp，不取合同表
  Procedure create_invoice_group_by_times(p_session_id          Number,
                                          p_invoice_date        Date,
                                          p_accounting_date     Date,
                                          p_period_name         Varchar2,
                                          p_internal_period_num Number,
                                          p_user_id             Number,
                                          p_role_id             Number) Is
    r_invoice_tmp_hd invoice_hd_tmp_type;
    r_invoice_hd     acr_invoice_hd%Rowtype;

    v_ln_step_length Number;

    Cursor cur_group_by_times_hd Is
      Select c.company_id,
             c.spv_company_id,
             t.contract_id,
             Null As project_id,
             dt.ref_document_type As invoice_document_type,
             t.bp_id,
             t.invoice_title As bp_name,
             hbm.tax_registry_num As bp_tax_registry_num,
             t.invoice_bp_address_phone_num,
             t.invoice_bp_bank_account,
             t.head_description,
             t.invoice_kind,
             Sum(t.billing_amount) As total_amount,
             t.currency,
             nvl(t.exchange_rate_type, 'MANUAL') As exchange_rate_type,
             nvl(t.exchange_rate_quotation, 'DIRECT QUOTATION') As exchange_rate_quotation,
             nvl(t.exchange_rate, 1) As exchange_rate,
             t.tax_type_id, --c.tax_type_id,
             tt.tax_type_rate,
             t.times
        From acr_invoice_create_tmp t,
             con_contract           c,
             hls_bp_master          hbm,
             hls_document_type      dt,
             fnd_tax_type_codes     tt
       Where t.contract_id = c.contract_id
         And c.data_class = 'NORMAL'
         And t.bp_id = hbm.bp_id
         And c.document_type = dt.document_type
         And dt.document_category = 'CONTRACT'
         And t.tax_type_id = tt.tax_type_id
            --and c.tax_type_id = tt.tax_type_id
         And t.session_id = p_session_id
         And t.checked_flag = 'Y'
       Group By c.company_id,
                c.spv_company_id,
                t.invoice_kind,
                t.contract_id,
                t.bp_id,
                dt.ref_document_type,
                t.invoice_title,
                hbm.tax_registry_num,
                t.invoice_bp_address_phone_num,
                t.invoice_bp_bank_account,
                t.head_description,
                t.currency,
                nvl(t.exchange_rate_type, 'MANUAL'),
                nvl(t.exchange_rate_quotation, 'DIRECT QUOTATION'),
                nvl(t.exchange_rate, 1),
                --c.tax_type_id,
                t.tax_type_id,
                tt.tax_type_rate,
                t.times;
  Begin

    Open cur_group_by_times_hd;
    Loop
      Fetch cur_group_by_times_hd
        Into r_invoice_tmp_hd;
      Exit When cur_group_by_times_hd%Notfound;

      create_invoice_hd(p_invoice_tmp_hd_rec  => r_invoice_tmp_hd,
                        p_invoice_date        => p_invoice_date,
                        p_accounting_date     => p_accounting_date,
                        p_period_name         => p_period_name,
                        p_internal_period_num => p_internal_period_num,
                        p_invoice_hd_rec      => r_invoice_hd,
                        p_ln_step_length      => v_ln_step_length,
                        p_user_id             => p_user_id,
                        p_role_id             => p_role_id);

      create_invoice_ln(p_invoice_tmp_hd_rec => r_invoice_tmp_hd,
                        p_invoice_hd_rec     => r_invoice_hd,
                        p_ln_step_length     => v_ln_step_length,
                        p_session_id         => p_session_id,
                        p_user_id            => p_user_id);

      split_invoice_hd(p_invoice_tmp_hd_rec => r_invoice_tmp_hd,
                       p_invoice_hd_rec     => r_invoice_hd,
                       p_ln_step_length     => v_ln_step_length,
                       p_session_id         => p_session_id,
                       p_user_id            => p_user_id,
                       p_role_id            => p_role_id);
      --插入发票行合并表
      save_acr_invoice_batch(p_invoice_hd_rec => r_invoice_hd,
                             p_user_id        => p_user_id);

    End Loop;
    Close cur_group_by_times_hd;
  End;
  Procedure create_penalty_no_group_by(p_session_id          Number,
                                       p_invoice_date        Date,
                                       p_accounting_date     Date,
                                       p_period_name         Varchar2,
                                       p_internal_period_num Number,
                                       p_user_id             Number,
                                       p_role_id             Number) Is
    r_invoice_tmp_hd invoice_hd_tmp_type;
    r_invoice_hd     acr_invoice_hd%Rowtype;
    v_ln_step_length Number;

    Cursor cur_group_by_contract_hd Is
      Select c.company_id,
             c.spv_company_id,
             t.contract_id,
             Null As project_id,
             dt.ref_document_type As invoice_document_type,
             t.bp_id,
             t.invoice_title As bp_name,
             hbm.tax_registry_num As bp_tax_registry_num,
             t.invoice_bp_address_phone_num,
             t.invoice_bp_bank_account,
             t.head_description,
             t.invoice_kind,
             t.billing_amount As total_amount,
             t.currency,
             nvl(t.exchange_rate_type, 'MANUAL') As exchange_rate_type,
             nvl(t.exchange_rate_quotation, 'DIRECT QUOTATION') As exchange_rate_quotation,
             nvl(t.exchange_rate, 1) As exchange_rate,
             t.tax_type_id,
             tt.tax_type_rate,
             Null As times
        From acr_invoice_create_tmp t,
             con_contract           c,
             hls_bp_master          hbm,
             hls_document_type      dt,
             fnd_tax_type_codes     tt
       Where t.contract_id = c.contract_id
         And c.data_class = 'NORMAL'
         And t.bp_id = hbm.bp_id
         And c.document_type = dt.document_type
         And dt.document_category = 'CONTRACT'
         And t.tax_type_id = tt.tax_type_id
         And t.session_id = p_session_id
         And t.checked_flag = 'Y';

  Begin
    Open cur_group_by_contract_hd;
    Loop
      Fetch cur_group_by_contract_hd
        Into r_invoice_tmp_hd;
      Exit When cur_group_by_contract_hd%Notfound;

      create_invoice_hd(p_invoice_tmp_hd_rec  => r_invoice_tmp_hd,
                        p_invoice_date        => p_invoice_date,
                        p_accounting_date     => p_accounting_date,
                        p_period_name         => p_period_name,
                        p_internal_period_num => p_internal_period_num,
                        p_invoice_hd_rec      => r_invoice_hd,
                        p_ln_step_length      => v_ln_step_length,
                        p_user_id             => p_user_id,
                        p_role_id             => p_role_id);

      create_invoice_ln_penalty(p_invoice_tmp_hd_rec => r_invoice_tmp_hd,
                                p_invoice_hd_rec     => r_invoice_hd,
                                p_ln_step_length     => v_ln_step_length,
                                p_session_id         => p_session_id,
                                p_user_id            => p_user_id);

      split_invoice_hd(p_invoice_tmp_hd_rec => r_invoice_tmp_hd,
                       p_invoice_hd_rec     => r_invoice_hd,
                       p_ln_step_length     => v_ln_step_length,
                       p_session_id         => p_session_id,
                       p_user_id            => p_user_id,
                       p_role_id            => p_role_id);
      --插入发票行合并表
      save_acr_invoice_batch(p_invoice_hd_rec => r_invoice_hd,
                             p_user_id        => p_user_id);
    End Loop;
    Close cur_group_by_contract_hd;
  End;
  Procedure create_penalty_group_by_con(p_session_id          Number,
                                        p_invoice_date        Date,
                                        p_accounting_date     Date,
                                        p_period_name         Varchar2,
                                        p_internal_period_num Number,
                                        p_user_id             Number,
                                        p_role_id             Number) Is
    r_invoice_tmp_hd invoice_hd_tmp_type;
    r_invoice_hd     acr_invoice_hd%Rowtype;
    v_ln_step_length Number;

    Cursor cur_group_by_contract_hd Is
      Select c.company_id,
             c.spv_company_id,
             t.contract_id,
             Null As project_id,
             dt.ref_document_type As invoice_document_type,
             t.bp_id,
             t.invoice_title As bp_name,
             hbm.tax_registry_num As bp_tax_registry_num,
             t.invoice_bp_address_phone_num,
             t.invoice_bp_bank_account,
             t.head_description,
             t.invoice_kind,
             Sum(t.billing_amount) As total_amount,
             t.currency,
             nvl(t.exchange_rate_type, 'MANUAL') As exchange_rate_type,
             nvl(t.exchange_rate_quotation, 'DIRECT QUOTATION') As exchange_rate_quotation,
             nvl(t.exchange_rate, 1) As exchange_rate,
             t.tax_type_id,
             tt.tax_type_rate,
             Null As times
        From acr_invoice_create_tmp t,
             con_contract           c,
             hls_bp_master          hbm,
             hls_document_type      dt,
             fnd_tax_type_codes     tt
       Where t.contract_id = c.contract_id
         And c.data_class = 'NORMAL'
         And t.bp_id = hbm.bp_id
         And c.document_type = dt.document_type
         And dt.document_category = 'CONTRACT'
         And t.tax_type_id = tt.tax_type_id
         And t.session_id = p_session_id
         And t.checked_flag = 'Y'
       Group By c.company_id,
                c.spv_company_id,
                t.invoice_kind,
                t.contract_id,
                t.bp_id,
                dt.ref_document_type,
                t.invoice_title,
                hbm.tax_registry_num,
                t.invoice_bp_address_phone_num,
                t.invoice_bp_bank_account,
                t.head_description,
                t.currency,
                nvl(t.exchange_rate_type, 'MANUAL'),
                nvl(t.exchange_rate_quotation, 'DIRECT QUOTATION'),
                nvl(t.exchange_rate, 1),
                t.tax_type_id,
                tt.tax_type_rate,
                Null;
  Begin
    Open cur_group_by_contract_hd;
    Loop
      Fetch cur_group_by_contract_hd
        Into r_invoice_tmp_hd;
      Exit When cur_group_by_contract_hd%Notfound;

      create_invoice_hd(p_invoice_tmp_hd_rec  => r_invoice_tmp_hd,
                        p_invoice_date        => p_invoice_date,
                        p_accounting_date     => p_accounting_date,
                        p_period_name         => p_period_name,
                        p_internal_period_num => p_internal_period_num,
                        p_invoice_hd_rec      => r_invoice_hd,
                        p_ln_step_length      => v_ln_step_length,
                        p_user_id             => p_user_id,
                        p_role_id             => p_role_id);

      create_invoice_ln_penalty(p_invoice_tmp_hd_rec => r_invoice_tmp_hd,
                                p_invoice_hd_rec     => r_invoice_hd,
                                p_ln_step_length     => v_ln_step_length,
                                p_session_id         => p_session_id,
                                p_user_id            => p_user_id);

      split_invoice_hd(p_invoice_tmp_hd_rec => r_invoice_tmp_hd,
                       p_invoice_hd_rec     => r_invoice_hd,
                       p_ln_step_length     => v_ln_step_length,
                       p_session_id         => p_session_id,
                       p_user_id            => p_user_id,
                       p_role_id            => p_role_id);
      --插入发票行合并表
      save_acr_invoice_batch(p_invoice_hd_rec => r_invoice_hd,
                             p_user_id        => p_user_id);
    End Loop;
    Close cur_group_by_contract_hd;
  End;

  Procedure create_invoice_group_by_con(p_session_id          Number,
                                        p_invoice_date        Date,
                                        p_accounting_date     Date,
                                        p_period_name         Varchar2,
                                        p_internal_period_num Number,
                                        p_user_id             Number,
                                        p_role_id             Number) Is
    r_invoice_tmp_hd invoice_hd_tmp_type;
    r_invoice_hd     acr_invoice_hd%Rowtype;
    v_ln_step_length Number;

    Cursor cur_group_by_contract_hd Is
      Select c.company_id,
             c.spv_company_id,
             t.contract_id,
             Null As project_id,
             dt.ref_document_type As invoice_document_type,
             t.bp_id,
             t.invoice_title As bp_name,
             hbm.tax_registry_num As bp_tax_registry_num,
             t.invoice_bp_address_phone_num,
             t.invoice_bp_bank_account,
             t.head_description,
             t.invoice_kind,
             Sum(t.billing_amount) As total_amount,
             t.currency,
             nvl(t.exchange_rate_type, 'MANUAL') As exchange_rate_type,
             nvl(t.exchange_rate_quotation, 'DIRECT QUOTATION') As exchange_rate_quotation,
             nvl(t.exchange_rate, 1) As exchange_rate,
             t.tax_type_id,
             tt.tax_type_rate,
             Null As times
        From acr_invoice_create_tmp t,
             con_contract           c,
             hls_bp_master          hbm,
             hls_document_type      dt,
             fnd_tax_type_codes     tt
       Where t.contract_id = c.contract_id
         And c.data_class = 'NORMAL'
         And t.bp_id = hbm.bp_id
         And c.document_type = dt.document_type
         And dt.document_category = 'CONTRACT'
         And t.tax_type_id = tt.tax_type_id
         And t.session_id = p_session_id
         And t.checked_flag = 'Y'
       Group By c.company_id,
                c.spv_company_id,
                t.invoice_kind,
                t.contract_id,
                t.bp_id,
                dt.ref_document_type,
                t.invoice_title,
                hbm.tax_registry_num,
                t.invoice_bp_address_phone_num,
                t.invoice_bp_bank_account,
                t.head_description,
                t.currency,
                nvl(t.exchange_rate_type, 'MANUAL'),
                nvl(t.exchange_rate_quotation, 'DIRECT QUOTATION'),
                nvl(t.exchange_rate, 1),
                t.tax_type_id,
                tt.tax_type_rate,
                Null;
  Begin
    Open cur_group_by_contract_hd;
    Loop
      Fetch cur_group_by_contract_hd
        Into r_invoice_tmp_hd;
      Exit When cur_group_by_contract_hd%Notfound;

      create_invoice_hd(p_invoice_tmp_hd_rec  => r_invoice_tmp_hd,
                        p_invoice_date        => p_invoice_date,
                        p_accounting_date     => p_accounting_date,
                        p_period_name         => p_period_name,
                        p_internal_period_num => p_internal_period_num,
                        p_invoice_hd_rec      => r_invoice_hd,
                        p_ln_step_length      => v_ln_step_length,
                        p_user_id             => p_user_id,
                        p_role_id             => p_role_id);

      create_invoice_ln(p_invoice_tmp_hd_rec => r_invoice_tmp_hd,
                        p_invoice_hd_rec     => r_invoice_hd,
                        p_ln_step_length     => v_ln_step_length,
                        p_session_id         => p_session_id,
                        p_user_id            => p_user_id);

      split_invoice_hd(p_invoice_tmp_hd_rec => r_invoice_tmp_hd,
                       p_invoice_hd_rec     => r_invoice_hd,
                       p_ln_step_length     => v_ln_step_length,
                       p_session_id         => p_session_id,
                       p_user_id            => p_user_id,
                       p_role_id            => p_role_id);
      --插入发票行合并表
      save_acr_invoice_batch(p_invoice_hd_rec => r_invoice_hd,
                             p_user_id        => p_user_id);
    End Loop;
    Close cur_group_by_contract_hd;
  End;

  Procedure create_invoice_group_by_prj(p_session_id          Number,
                                        p_invoice_date        Date,
                                        p_accounting_date     Date,
                                        p_period_name         Varchar2,
                                        p_internal_period_num Number,
                                        p_user_id             Number,
                                        p_role_id             Number) Is
    r_invoice_tmp_hd invoice_hd_tmp_type;
    r_invoice_hd     acr_invoice_hd%Rowtype;
    v_ln_step_length Number;

    Cursor cur_group_by_project_hd Is
      Select c.company_id,
             c.spv_company_id,
             Null As contract_id,
             c.project_id,
             dt.ref_document_type As invoice_document_type,
             t.bp_id,
             t.invoice_title As bp_name,
             hbm.tax_registry_num As bp_tax_registry_num,
             t.invoice_bp_address_phone_num,
             t.invoice_bp_bank_account,
             t.head_description,
             t.invoice_kind,
             Sum(t.billing_amount) As total_amount,
             t.currency,
             nvl(t.exchange_rate_type, 'MANUAL') As exchange_rate_type,
             nvl(t.exchange_rate_quotation, 'DIRECT QUOTATION') As exchange_rate_quotation,
             nvl(t.exchange_rate, 1) As exchange_rate,
             c.tax_type_id,
             tt.tax_type_rate,
             Null As times
        From acr_invoice_create_tmp t,
             con_contract           c,
             hls_bp_master          hbm,
             hls_document_type      dt,
             fnd_tax_type_codes     tt
       Where t.contract_id = c.contract_id
         And c.data_class = 'NORMAL'
         And t.bp_id = hbm.bp_id
         And c.document_type = dt.document_type
         And dt.document_category = 'CONTRACT'
         And c.tax_type_id = tt.tax_type_id
         And t.session_id = p_session_id
         And t.checked_flag = 'Y'
       Group By c.company_id,
                c.spv_company_id,
                t.invoice_kind,
                c.project_id,
                t.bp_id,
                dt.ref_document_type,
                t.invoice_title,
                hbm.tax_registry_num,
                t.invoice_bp_address_phone_num,
                t.invoice_bp_bank_account,
                t.head_description,
                t.currency,
                nvl(t.exchange_rate_type, 'MANUAL'),
                nvl(t.exchange_rate_quotation, 'DIRECT QUOTATION'),
                nvl(t.exchange_rate, 1),
                c.tax_type_id,
                tt.tax_type_rate,
                Null;
  Begin
    Open cur_group_by_project_hd;
    Loop
      Fetch cur_group_by_project_hd
        Into r_invoice_tmp_hd;
      Exit When cur_group_by_project_hd%Notfound;

      create_invoice_hd(p_invoice_tmp_hd_rec  => r_invoice_tmp_hd,
                        p_invoice_date        => p_invoice_date,
                        p_accounting_date     => p_accounting_date,
                        p_period_name         => p_period_name,
                        p_internal_period_num => p_internal_period_num,
                        p_invoice_hd_rec      => r_invoice_hd,
                        p_ln_step_length      => v_ln_step_length,
                        p_user_id             => p_user_id,
                        p_role_id             => p_role_id);

      create_invoice_ln(p_invoice_tmp_hd_rec => r_invoice_tmp_hd,
                        p_invoice_hd_rec     => r_invoice_hd,
                        p_ln_step_length     => v_ln_step_length,
                        p_session_id         => p_session_id,
                        p_user_id            => p_user_id);

      split_invoice_hd(p_invoice_tmp_hd_rec => r_invoice_tmp_hd,
                       p_invoice_hd_rec     => r_invoice_hd,
                       p_ln_step_length     => v_ln_step_length,
                       p_session_id         => p_session_id,
                       p_user_id            => p_user_id,
                       p_role_id            => p_role_id);
      --插入发票行合并表
      save_acr_invoice_batch(p_invoice_hd_rec => r_invoice_hd,
                             p_user_id        => p_user_id);
    End Loop;
    Close cur_group_by_project_hd;
  End;

  Procedure create_invoice_group_by_bp(p_session_id          Number,
                                       p_invoice_date        Date,
                                       p_accounting_date     Date,
                                       p_period_name         Varchar2,
                                       p_internal_period_num Number,
                                       p_user_id             Number,
                                       p_role_id             Number) Is
    r_invoice_tmp_hd invoice_hd_tmp_type;
    r_invoice_hd     acr_invoice_hd%Rowtype;
    v_ln_step_length Number;

    Cursor cur_group_by_tenant_hd Is
      Select c.company_id,
             c.spv_company_id,
             Null As contract_id,
             Null As project_id,
             dt.ref_document_type As invoice_document_type,
             t.bp_id,
             t.invoice_title As bp_name,
             hbm.tax_registry_num As bp_tax_registry_num,
             t.invoice_bp_address_phone_num,
             t.invoice_bp_bank_account,
             t.head_description,
             t.invoice_kind,
             Sum(t.billing_amount) As total_amount,
             t.currency,
             nvl(t.exchange_rate_type, 'MANUAL') As exchange_rate_type,
             nvl(t.exchange_rate_quotation, 'DIRECT QUOTATION') As exchange_rate_quotation,
             nvl(t.exchange_rate, 1) As exchange_rate,
             t.tax_type_id,
             tt.tax_type_rate,
             Null As times
        From acr_invoice_create_tmp t,
             con_contract           c,
             hls_bp_master          hbm,
             hls_document_type      dt,
             fnd_tax_type_codes     tt
       Where t.contract_id = c.contract_id
         And c.data_class = 'NORMAL'
         And t.bp_id = hbm.bp_id
         And c.document_type = dt.document_type
         And dt.document_category = 'CONTRACT'
         And t.tax_type_id = tt.tax_type_id
         And t.session_id = p_session_id
         And t.checked_flag = 'Y'
       Group By c.company_id,
                c.spv_company_id,
                t.invoice_kind,
                t.bp_id,
                dt.ref_document_type,
                t.invoice_title,
                hbm.tax_registry_num,
                t.invoice_bp_address_phone_num,
                t.invoice_bp_bank_account,
                t.head_description,
                t.currency,
                nvl(t.exchange_rate_type, 'MANUAL'),
                nvl(t.exchange_rate_quotation, 'DIRECT QUOTATION'),
                nvl(t.exchange_rate, 1),
                t.tax_type_id,
                tt.tax_type_rate;
  Begin
    Open cur_group_by_tenant_hd;
    Loop
      Fetch cur_group_by_tenant_hd
        Into r_invoice_tmp_hd;
      Exit When cur_group_by_tenant_hd%Notfound;

      create_invoice_hd(p_invoice_tmp_hd_rec  => r_invoice_tmp_hd,
                        p_invoice_date        => p_invoice_date,
                        p_accounting_date     => p_accounting_date,
                        p_period_name         => p_period_name,
                        p_internal_period_num => p_internal_period_num,
                        p_invoice_hd_rec      => r_invoice_hd,
                        p_ln_step_length      => v_ln_step_length,
                        p_user_id             => p_user_id,
                        p_role_id             => p_role_id);

      create_invoice_ln(p_invoice_tmp_hd_rec => r_invoice_tmp_hd,
                        p_invoice_hd_rec     => r_invoice_hd,
                        p_ln_step_length     => v_ln_step_length,
                        p_session_id         => p_session_id,
                        p_user_id            => p_user_id);

      split_invoice_hd(p_invoice_tmp_hd_rec => r_invoice_tmp_hd,
                       p_invoice_hd_rec     => r_invoice_hd,
                       p_ln_step_length     => v_ln_step_length,
                       p_session_id         => p_session_id,
                       p_user_id            => p_user_id,
                       p_role_id            => p_role_id);
      --插入发票行合并表
      save_acr_invoice_batch(p_invoice_hd_rec => r_invoice_hd,
                             p_user_id        => p_user_id);
    End Loop;
    Close cur_group_by_tenant_hd;
  End;

  --宏菱建机罚息开票
  Procedure create_penalty_invoice(p_session_id           Number,
                                   p_company_id           Number,
                                   p_group_billing_method Varchar2,
                                   p_invoice_date         Date,
                                   p_accounting_date      Date,
                                   p_user_id              Number,
                                   p_role_id              Number) Is
    v_period_name         gld_periods.period_name%Type;
    v_internal_period_num gld_periods.internal_period_num%Type;
  Begin

    create_invoice_check(p_session_id => p_session_id,
                         p_company_id => p_company_id,
                         p_user_id    => p_user_id);

    check_invoice_date(p_company_id          => p_company_id,
                       p_invoice_data        => p_invoice_date,
                       p_accounting_date     => p_accounting_date,
                       p_period_name         => v_period_name,
                       p_internal_period_num => v_internal_period_num);
    If p_group_billing_method = 'GROUP_BY_CONTRACT' Then
      create_penalty_group_by_con(p_session_id          => p_session_id,
                                  p_invoice_date        => p_invoice_date,
                                  p_accounting_date     => p_accounting_date,
                                  p_period_name         => v_period_name,
                                  p_internal_period_num => v_internal_period_num,
                                  p_user_id             => p_user_id,
                                  p_role_id             => p_role_id);
    End If;
    If p_group_billing_method = 'NO_GROUP' Then
      create_penalty_no_group_by(p_session_id          => p_session_id,
                                 p_invoice_date        => p_invoice_date,
                                 p_accounting_date     => p_accounting_date,
                                 p_period_name         => v_period_name,
                                 p_internal_period_num => v_internal_period_num,
                                 p_user_id             => p_user_id,
                                 p_role_id             => p_role_id);
    End If;

    Delete From acr_invoice_create_tmp Where session_id = p_session_id;

  Exception
    When Others Then
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => dbms_utility.format_error_backtrace || ' ' ||
                                                                                  Sqlerrm,
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'ACR_INVOICE_PKG',
                                                     p_procedure_function_name => 'create_invoice');

      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);

  End;

  Procedure create_invoice(p_session_id           Number,
                           p_company_id           Number,
                           p_group_billing_method Varchar2,
                           p_invoice_date         Date,
                           p_accounting_date      Date,
                           p_user_id              Number,
                           p_role_id              Number) Is

    v_period_name         gld_periods.period_name%Type;
    v_internal_period_num gld_periods.internal_period_num%Type;

  Begin

    create_invoice_check(p_session_id => p_session_id,
                         p_company_id => p_company_id,
                         p_user_id    => p_user_id);

    check_invoice_date(p_company_id          => p_company_id,
                       p_invoice_data        => p_invoice_date,
                       p_accounting_date     => p_accounting_date,
                       p_period_name         => v_period_name,
                       p_internal_period_num => v_internal_period_num);

    If p_group_billing_method = 'GROUP_BY_TIMES' Then
      create_invoice_group_by_times(p_session_id          => p_session_id,
                                    p_invoice_date        => p_invoice_date,
                                    p_accounting_date     => p_accounting_date,
                                    p_period_name         => v_period_name,
                                    p_internal_period_num => v_internal_period_num,
                                    p_user_id             => p_user_id,
                                    p_role_id             => p_role_id);
    End If;

    If p_group_billing_method = 'GROUP_BY_CONTRACT' Then
      create_invoice_group_by_con(p_session_id          => p_session_id,
                                  p_invoice_date        => p_invoice_date,
                                  p_accounting_date     => p_accounting_date,
                                  p_period_name         => v_period_name,
                                  p_internal_period_num => v_internal_period_num,
                                  p_user_id             => p_user_id,
                                  p_role_id             => p_role_id);
    End If;

    If p_group_billing_method = 'GROUP_BY_PROJECT' Then
      create_invoice_group_by_prj(p_session_id          => p_session_id,
                                  p_invoice_date        => p_invoice_date,
                                  p_accounting_date     => p_accounting_date,
                                  p_period_name         => v_period_name,
                                  p_internal_period_num => v_internal_period_num,
                                  p_user_id             => p_user_id,
                                  p_role_id             => p_role_id);
    End If;

    If p_group_billing_method = 'GROUP_BY_TENANT' Then
      create_invoice_group_by_bp(p_session_id          => p_session_id,
                                 p_invoice_date        => p_invoice_date,
                                 p_accounting_date     => p_accounting_date,
                                 p_period_name         => v_period_name,
                                 p_internal_period_num => v_internal_period_num,
                                 p_user_id             => p_user_id,
                                 p_role_id             => p_role_id);
    End If;

    Delete From acr_invoice_create_tmp Where session_id = p_session_id;

  Exception
    When e_period_not_found Then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'ACR510_PERIOD_NOT_FOUND_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'ACR_INVOICE_PKG',
                                                      p_procedure_function_name => 'create_invoice');

      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    When e_get_invoice_number_err Then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'ACR510_INVOICE_NUMBER_CODING_RULE_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'ACR_INVOICE_PKG',
                                                      p_procedure_function_name => 'create_invoice');

      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    When e_billing_amt_over_err Then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'ACR510_BILLING_AMOUNT_OVER_AMT_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'ACR_INVOICE_PKG',
                                                      p_procedure_function_name => 'create_invoice');

      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    When e_billing_amt_split_err Then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'ACR510_BILLING_AMOUNT_SPLIT_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'ACR_INVOICE_PKG',
                                                      p_procedure_function_name => 'create_invoice');

      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    When e_invoice_limit_not_found Then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'ACR510_COMPANY_INVOICE_LIMIT_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'ACR_INVOICE_PKG',
                                                      p_procedure_function_name => 'create_invoice');

      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    When e_htjs_tolerance_error Then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'ACR510_HTJS_TOLERANCE_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'ACR_INVOICE_PKG',
                                                      p_procedure_function_name => 'create_invoice');

      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    When Others Then
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => dbms_utility.format_error_backtrace || ' ' ||
                                                                                  Sqlerrm,
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'ACR_INVOICE_PKG',
                                                     p_procedure_function_name => 'create_invoice');

      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  End;

  Function lock_acr_invoice(p_invoice_hd_id Number)
    Return acr_invoice_hd%Rowtype Is
    r_acr_invoice_hd_rec acr_invoice_hd%Rowtype;
  Begin
    Select *
      Into r_acr_invoice_hd_rec
      From acr_invoice_hd
     Where invoice_hd_id = p_invoice_hd_id
       For Update Nowait;
    Return r_acr_invoice_hd_rec;
  End;

  Procedure delete_invoice(p_invoice_hd_id Number, p_user_id Number) Is
    r_acr_invoice_hd_rec acr_invoice_hd%Rowtype;
    e_invoice_delete_status_err Exception;
  Begin
    --锁表
    r_acr_invoice_hd_rec := lock_acr_invoice(p_invoice_hd_id => p_invoice_hd_id);

    --状态检查
    If r_acr_invoice_hd_rec.invoice_status != g_status_new And
       r_acr_invoice_hd_rec.invoice_status != 'REJECT' Then
      Raise e_invoice_delete_status_err;
    End If;

    For c_acr_invoice_ln In (Select *
                               From acr_invoice_ln
                              Where invoice_hd_id = p_invoice_hd_id) Loop
      --以负数 重新更新cashflow行状态
      c_acr_invoice_ln.total_amount := -c_acr_invoice_ln.total_amount;

      update_cashflow_ln(p_invoice_hd_rec => r_acr_invoice_hd_rec,
                         p_invoice_ln_rec => c_acr_invoice_ln,
                         p_user_id        => p_user_id);
      --删除发票行
      Delete From acr_invoice_ln
       Where invoice_ln_id = c_acr_invoice_ln.invoice_ln_id;
    End Loop;

    --删除发票头
    Delete From acr_invoice_hd Where invoice_hd_id = p_invoice_hd_id;

    --删除单据流
    hls_document_flow_pkg.delete_document_flow(p_doc_category => 'AR_INVOICE',
                                               p_doc_id       => p_invoice_hd_id,
                                               p_doc_line_id  => Null);

    --删除权限表
    aut_document_authority_pkg.delete_trx_authority(p_document_category => 'AR_INVOICE',
                                                    p_document_id       => p_invoice_hd_id);

    --删除操作表
    hls_doc_operate_history_pkg.delete_doc_operate_history_all(p_document_category => 'AR_INVOICE',
                                                               p_document_id       => p_invoice_hd_id);

  Exception
    When e_invoice_delete_status_err Then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => '复核中的单据不能删除',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'ACR_INVOICE_PKG',
                                                      p_procedure_function_name => 'delete_invoice');

      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    When e_lock_error Then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'ACR510_INVOICE_LOCK_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'ACR_INVOICE_PKG',
                                                      p_procedure_function_name => 'delete_invoice');

      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    When Others Then
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => dbms_utility.format_error_backtrace || ' ' ||
                                                                                  Sqlerrm,
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'ACR_INVOICE_PKG',
                                                     p_procedure_function_name => 'delete_invoice');

      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  End;

  Procedure confirm_invoice(p_invoice_hd_id Number, p_user_id Number) Is
    r_acr_invoice_hd_rec acr_invoice_hd%Rowtype;

    e_invoice_confirm_status_err Exception;
  Begin
    --锁表
    r_acr_invoice_hd_rec := lock_acr_invoice(p_invoice_hd_id => p_invoice_hd_id);

    --状态检查
    If Not r_acr_invoice_hd_rec.invoice_status = g_status_confirming Then
      Raise e_invoice_confirm_status_err;
    End If;

    --更新状态
    Update acr_invoice_hd
       Set invoice_status = g_status_confirm,
           confirmed_date = Sysdate,
           confirmed_by   = p_user_id
     Where invoice_hd_id = p_invoice_hd_id;

    --生成凭证
    --按照吉利要求，回传之后才生成凭证
    /*acr_invoice_je_pkg.create_acr_invoice_je(p_invoice_hd_id => p_invoice_hd_id,
    p_user_id       => p_user_id);*/

    hls_doc_operate_history_pkg.insert_doc_operate_history(p_document_category => r_acr_invoice_hd_rec.document_category,
                                                           p_document_id       => p_invoice_hd_id,
                                                           p_operation_code    => hls_doc_operate_history_pkg.c_confrim,
                                                           p_user_id           => p_user_id,
                                                           p_operation_time    => Sysdate,
                                                           p_description       => Null);

  Exception
    When e_invoice_confirm_status_err Then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'ACR510_CONFIRM_INVOICE_STATUS_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'ACR_INVOICE_PKG',
                                                      p_procedure_function_name => 'confirm_invoice');

      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    When e_lock_error Then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'ACR510_INVOICE_LOCK_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'ACR_INVOICE_PKG',
                                                      p_procedure_function_name => 'confirm_invoice');

      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    When Others Then
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => dbms_utility.format_error_backtrace || ' ' ||
                                                                                  Sqlerrm,
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'ACR_INVOICE_PKG',
                                                     p_procedure_function_name => 'confirm_invoice');

      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  End;

  Procedure reverse_invoice(p_invoice_hd_id      Number,
                            p_reverse_date       Date,
                            p_vat_red_notice_num Varchar2,
                            p_user_id            Number,
                            p_role_id            Number) Is
    v_period_name         gld_periods.period_name%Type;
    v_internal_period_num gld_periods.internal_period_num%Type;
    v_acr_invoice_hd_id   Number;
    v_acr_invoice_ln_id   Number;
    r_acr_invoice_hd_rec  acr_invoice_hd%Rowtype;

    r_acr_invoice_hd_r_rec acr_invoice_hd%Rowtype; --反冲的发票头记录

    e_reverse_date_error         Exception;
    e_invoice_reverse_status_err Exception;
    e_invoice_reverse_flag_err   Exception;
  Begin

    --锁表
    r_acr_invoice_hd_rec := lock_acr_invoice(p_invoice_hd_id => p_invoice_hd_id);

    If p_reverse_date < r_acr_invoice_hd_rec.accounting_date Then
      Raise e_reverse_date_error;
    End If;

    check_invoice_date(p_company_id          => r_acr_invoice_hd_rec.company_id,
                       p_invoice_data        => p_reverse_date,
                       p_accounting_date     => p_reverse_date,
                       p_period_name         => v_period_name,
                       p_internal_period_num => v_internal_period_num);

    --状态检查
    If Not r_acr_invoice_hd_rec.invoice_status = g_status_confirm Then
      Raise e_invoice_reverse_status_err;
    End If;

    If Not nvl(r_acr_invoice_hd_rec.reversed_flag, 'N') = 'N' Then
      Raise e_invoice_reverse_flag_err;
    End If;

    Select acr_invoice_hd_s.nextval Into v_acr_invoice_hd_id From dual;
    --插入反冲的发票头和行记录
    r_acr_invoice_hd_r_rec                          := r_acr_invoice_hd_rec;
    r_acr_invoice_hd_r_rec.invoice_hd_id            := v_acr_invoice_hd_id;
    r_acr_invoice_hd_r_rec.document_number          := get_invoice_number(p_document_type    => r_acr_invoice_hd_r_rec.document_type,
                                                                          p_transaction_date => r_acr_invoice_hd_r_rec.accounting_date,
                                                                          p_company_id       => r_acr_invoice_hd_r_rec.company_id,
                                                                          p_user_id          => p_user_id);
    r_acr_invoice_hd_r_rec.invoice_number           := Null;
    r_acr_invoice_hd_r_rec.total_amount             := -r_acr_invoice_hd_r_rec.total_amount;
    r_acr_invoice_hd_r_rec.tax_amount               := -r_acr_invoice_hd_r_rec.tax_amount;
    r_acr_invoice_hd_r_rec.invoice_date             := r_acr_invoice_hd_rec.invoice_date;
    r_acr_invoice_hd_r_rec.accounting_date          := p_reverse_date;
    r_acr_invoice_hd_r_rec.period_name              := v_period_name;
    r_acr_invoice_hd_r_rec.internal_period_num      := v_internal_period_num;
    r_acr_invoice_hd_r_rec.create_je_flag           := 'N';
    r_acr_invoice_hd_r_rec.gld_interface_flag       := 'N';
    r_acr_invoice_hd_r_rec.reversed_flag            := 'R';
    r_acr_invoice_hd_r_rec.source_invoice_header_id := p_invoice_hd_id;
    r_acr_invoice_hd_r_rec.vat_interface_status     := 'UNTRANSFERED';
    r_acr_invoice_hd_r_rec.vat_invoice_code         := Null;
    r_acr_invoice_hd_r_rec.vat_invoice_status       := Null;
    r_acr_invoice_hd_r_rec.vat_red_notice_num       := p_vat_red_notice_num;
    r_acr_invoice_hd_r_rec.ref_vat_invoice_code     := r_acr_invoice_hd_rec.vat_invoice_code;
    r_acr_invoice_hd_r_rec.ref_invoice_number       := r_acr_invoice_hd_rec.invoice_number;
    r_acr_invoice_hd_r_rec.created_by               := p_user_id;
    r_acr_invoice_hd_r_rec.creation_date            := Sysdate;
    r_acr_invoice_hd_r_rec.last_updated_by          := p_user_id;
    r_acr_invoice_hd_r_rec.last_update_date         := Sysdate;

    insert_invoice_hd(r_acr_invoice_hd_r_rec);

    For c_acr_invoice_ln_r_rec In (Select *
                                     From acr_invoice_ln
                                    Where invoice_hd_id = p_invoice_hd_id) Loop
      Select acr_invoice_ln_s.nextval Into v_acr_invoice_ln_id From dual;
      c_acr_invoice_ln_r_rec.invoice_hd_id    := r_acr_invoice_hd_r_rec.invoice_hd_id;
      c_acr_invoice_ln_r_rec.invoice_ln_id    := v_acr_invoice_ln_id;
      c_acr_invoice_ln_r_rec.quantity         := -c_acr_invoice_ln_r_rec.quantity;
      c_acr_invoice_ln_r_rec.net_price        := -c_acr_invoice_ln_r_rec.net_price;
      c_acr_invoice_ln_r_rec.tax_amount       := -c_acr_invoice_ln_r_rec.tax_amount;
      c_acr_invoice_ln_r_rec.net_amount       := -c_acr_invoice_ln_r_rec.net_amount;
      c_acr_invoice_ln_r_rec.total_amount     := -c_acr_invoice_ln_r_rec.total_amount;
      c_acr_invoice_ln_r_rec.created_by       := p_user_id;
      c_acr_invoice_ln_r_rec.creation_date    := Sysdate;
      c_acr_invoice_ln_r_rec.last_updated_by  := p_user_id;
      c_acr_invoice_ln_r_rec.last_update_date := Sysdate;
      insert_invoice_ln(c_acr_invoice_ln_r_rec);

      update_cashflow_ln(p_invoice_hd_rec => r_acr_invoice_hd_r_rec,
                         p_invoice_ln_rec => c_acr_invoice_ln_r_rec,
                         p_user_id        => p_user_id);

    End Loop;

    insert_doc_flow(p_invoice_hd_rec => r_acr_invoice_hd_r_rec);

    insert_trx_user_authority(p_invoice_hd_rec => r_acr_invoice_hd_r_rec,
                              p_user_id        => p_user_id);

    --更新状态
    Update acr_invoice_hd
       Set reversed_flag            = 'W',
           reverse_date             = p_reverse_date,
           source_invoice_header_id = r_acr_invoice_hd_r_rec.invoice_hd_id,
           last_updated_by          = p_user_id,
           last_update_date         = Sysdate
     Where invoice_hd_id = p_invoice_hd_id;
    --生成反冲凭证
    acr_invoice_je_pkg.create_acr_invoice_reverse_je(p_invoice_hd_id => r_acr_invoice_hd_r_rec.invoice_hd_id,
                                                     p_user_id       => p_user_id);

    hls_doc_operate_history_pkg.insert_doc_operate_history(p_document_category => r_acr_invoice_hd_rec.document_category,
                                                           p_document_id       => p_invoice_hd_id,
                                                           p_operation_code    => hls_doc_operate_history_pkg.c_reverse,
                                                           p_user_id           => p_user_id,
                                                           p_operation_time    => Sysdate,
                                                           p_description       => Null);

  Exception
    When e_period_not_found Then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'ACR510_PERIOD_NOT_FOUND_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'ACR_INVOICE_PKG',
                                                      p_procedure_function_name => 'reverse_invoice');

      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    When e_reverse_date_error Then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'ACR510_REVERSE_DATE_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'ACR_INVOICE_PKG',
                                                      p_procedure_function_name => 'reverse_invoice');

      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    When e_invoice_reverse_status_err Then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'ACR510_REVERSE_INVOICE_STATUS_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'ACR_INVOICE_PKG',
                                                      p_procedure_function_name => 'reverse_invoice');

      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);

    When e_invoice_reverse_flag_err Then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'ACR510_REVERSE_INVOICE_REVERSE_FLAG_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'ACR_INVOICE_PKG',
                                                      p_procedure_function_name => 'reverse_invoice');

      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);

    When e_lock_error Then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'ACR510_INVOICE_LOCK_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'ACR_INVOICE_PKG',
                                                      p_procedure_function_name => 'reverse_invoice');

      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    When Others Then
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => dbms_utility.format_error_backtrace || ' ' ||
                                                                                  Sqlerrm,
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'ACR_INVOICE_PKG',
                                                     p_procedure_function_name => 'reverse_invoice');

      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  End;
  --add by Yenick 宏菱项目 增加发票行合并
  Procedure invoice_row_merge(p_invoice_hd_id In Number,
                              p_user_id       Number,
                              p_merge_flag    Varchar2) Is
    r_acr_invoice_batch_ln acr_invoice_batch_ln%Rowtype;
    r_acr_invoice_ln       acr_invoice_ln%Rowtype;
    r_acr_invoice_hd       acr_invoice_hd%Rowtype;
    v_price                Number;
    v_net_price            Number;
    v_tax_type_id          Number;
    v_tax_type_rate        Number;
    v_tax_included_flag    Varchar2(100);
    v_tax_amount           Number;
    v_net_amount           Number;
    v_total_amount         Number;
  Begin
    Select t.*
      Into r_acr_invoice_hd
      From acr_invoice_hd t
     Where t.invoice_hd_id = p_invoice_hd_id;
    If r_acr_invoice_hd.ref_merge_flag = p_merge_flag Then
      Return;
    End If;
    Update acr_invoice_hd t
       Set t.ref_merge_flag = t.merge_flag
     Where t.invoice_hd_id = p_invoice_hd_id;
    Select t.*
      Into r_acr_invoice_ln
      From acr_invoice_ln t
     Where t.invoice_hd_id = p_invoice_hd_id
       And rownum = 1;
    --删除行合并表中的行明细记录
    Delete From acr_invoice_batch_ln t
     Where t.invoice_hd_id = p_invoice_hd_id;
    If r_acr_invoice_hd.merge_flag = 'Y' Then

      Select Sum(t.price),
             Sum(t.net_price),
             Sum(t.tax_amount),
             Sum(t.net_amount),
             Sum(t.total_amount)
        Into v_price,
             v_net_price,
             v_tax_amount,
             v_net_amount,
             v_total_amount
        From acr_invoice_ln t
       Where t.invoice_hd_id = p_invoice_hd_id;
      r_acr_invoice_batch_ln.invoice_batch_ln_id := acr_invoice_batch_ln_s.nextval;
      r_acr_invoice_batch_ln.invoice_hd_id       := p_invoice_hd_id;
      r_acr_invoice_batch_ln.line_type           := 'SERVICE';
      r_acr_invoice_batch_ln.line_number         := 10;
      r_acr_invoice_batch_ln.quantity            := 1;
      r_acr_invoice_batch_ln.price               := v_price;
      r_acr_invoice_batch_ln.price_quantity      := 1;
      r_acr_invoice_batch_ln.net_price           := v_net_price;
      r_acr_invoice_batch_ln.tax_type_id         := r_acr_invoice_ln.tax_type_id;
      r_acr_invoice_batch_ln.tax_type_rate       := r_acr_invoice_ln.tax_type_rate;
      r_acr_invoice_batch_ln.tax_included_flag   := r_acr_invoice_ln.tax_included_flag;
      r_acr_invoice_batch_ln.product_name        := r_acr_invoice_ln.product_name;
      r_acr_invoice_batch_ln.tax_amount          := v_tax_amount;
      r_acr_invoice_batch_ln.net_amount          := v_net_amount;
      r_acr_invoice_batch_ln.total_amount        := v_total_amount;
      r_acr_invoice_batch_ln.created_by          := p_user_id;
      r_acr_invoice_batch_ln.creation_date       := Sysdate;
      r_acr_invoice_batch_ln.last_updated_by     := p_user_id;
      r_acr_invoice_batch_ln.last_update_date    := Sysdate;
      Insert Into acr_invoice_batch_ln Values r_acr_invoice_batch_ln;
    Else
      /*update acr_invoice_hd t
        set t.merge_flag       = 'N',
            t.last_updated_by  = p_user_id,
            t.last_update_date = sysdate
      where t.invoice_hd_id = p_invoice_hd_id;*/
      For rec_acr_invoice_ln In (Select *
                                   From acr_invoice_ln t
                                  Where t.invoice_hd_id = p_invoice_hd_id) Loop
        r_acr_invoice_batch_ln.invoice_batch_ln_id := acr_invoice_batch_ln_s.nextval;
        r_acr_invoice_batch_ln.invoice_hd_id       := p_invoice_hd_id;
        r_acr_invoice_batch_ln.line_type           := 'SERVICE';
        r_acr_invoice_batch_ln.line_number         := rec_acr_invoice_ln.line_number;
        r_acr_invoice_batch_ln.quantity            := 1;
        r_acr_invoice_batch_ln.price               := rec_acr_invoice_ln.price;
        r_acr_invoice_batch_ln.price_quantity      := 1;
        r_acr_invoice_batch_ln.net_price           := rec_acr_invoice_ln.net_price;
        r_acr_invoice_batch_ln.tax_type_id         := r_acr_invoice_ln.tax_type_id;
        r_acr_invoice_batch_ln.tax_type_rate       := r_acr_invoice_ln.tax_type_rate;
        r_acr_invoice_batch_ln.tax_included_flag   := r_acr_invoice_ln.tax_included_flag;
        r_acr_invoice_batch_ln.product_name        := rec_acr_invoice_ln.product_name;
        r_acr_invoice_batch_ln.tax_amount          := rec_acr_invoice_ln.tax_amount;
        r_acr_invoice_batch_ln.net_amount          := rec_acr_invoice_ln.net_amount;
        r_acr_invoice_batch_ln.total_amount        := rec_acr_invoice_ln.total_amount;
        r_acr_invoice_batch_ln.created_by          := p_user_id;
        r_acr_invoice_batch_ln.creation_date       := Sysdate;
        r_acr_invoice_batch_ln.last_updated_by     := p_user_id;
        r_acr_invoice_batch_ln.last_update_date    := Sysdate;
        Insert Into acr_invoice_batch_ln Values r_acr_invoice_batch_ln;
      End Loop;
    End If;
  Exception
    When Others Then
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => dbms_utility.format_error_backtrace || ' ' ||
                                                                                  Sqlerrm,
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'ACR_INVOICE_PKG',
                                                     p_procedure_function_name => 'invoice_row_merge');

      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  End invoice_row_merge;
  --发票提交
  Procedure acr_invoice_submit(p_batch_id Number, p_user_id Number) Is
    r_acr_invoice_hd acr_invoice_hd%Rowtype;
    v_instance_id    Number;
    v_document_info  Varchar2(1000);
    v_count          Number;
    v_bp_name        Varchar2(100);
  Begin

    Update acr_invoice_hd t
       Set t.invoice_status = 'APPROVING'
     Where t.batch_id = p_batch_id;
    Select Count(1)
      Into v_count
      From acr_invoice_hd t
     Where t.batch_id = p_batch_id;
    Select t.invoice_bp_name
      Into v_bp_name
      From acr_invoice_hd_v t
     Where t.batch_id = p_batch_id
       And rownum = 1;
    If v_count = 1 Then

      v_document_info := v_bp_name || '提交发票审批';
    Elsif v_count > 1 Then
      v_document_info := v_bp_name || '等提交发票审批';
    End If;
    --提交审批
    hls_workflow_pkg.workflow_start(p_instance_id       => v_instance_id,
                                    p_document_category => 'AR_INVOICE',
                                    p_document_type     => 'STD',
                                    p_company_id        => 1,
                                    p_user_id           => p_user_id,
                                    p_parameter_1       => 'USER_ID',
                                    p_parameter_1_value => p_user_id,
                                    p_parameter_2       => 'DOCUMENT_INFO',
                                    p_parameter_2_value => v_document_info,
                                    p_parameter_3       => 'BATCH_ID',
                                    p_parameter_3_value => p_batch_id,
                                    p_parameter_4       => 'SUBMIT_DATE',
                                    p_parameter_4_value => to_char(Sysdate,
                                                                   'yyyy"年"mm"月"dd"日"'));

  End acr_invoice_submit;
  --更新为同一批次
  Procedure acr_invoice_update_batch(p_user_id       Number,
                                     p_batch_id      Number,
                                     p_invoice_hd_id In Number) Is
    --   r_acr_invoice_hd_batch acr_invoice_hd_batch%rowtype;
  Begin
    Update acr_invoice_hd t
       Set t.batch_id = p_batch_id
     Where t.invoice_hd_id = p_invoice_hd_id;
  End;
  /*  function ss(p_batch_id out number) return number is
    v_id number;
  begin
    select ACR_INVOICE_HD_BATCH_S.NEXTVAL INTO v_id from dual;
    p_batch_id := v_id;
    return p_batch_id;

  end;*/
  --发票审批通过
  Procedure acr_invoice_approved(p_batch_id Number, p_user_id Number) Is
  Begin
    Update acr_invoice_hd t
       Set t.invoice_status   = 'CONFIRM',
           t.last_update_date = Sysdate,
           t.last_updated_by  = p_user_id
     Where t.batch_id = p_batch_id;
  End;
  --发票审批拒绝
  Procedure acr_invoice_reject(p_batch_id Number, p_user_id Number) Is
  Begin
    Update acr_invoice_hd t
       Set t.invoice_status   = 'REJECT',
           t.last_update_date = Sysdate,
           t.last_updated_by  = p_user_id
     Where t.batch_id = p_batch_id;
  End;
  Procedure acr_invoice_status_change(p_invoice_hd  Number,
                                      p_want_status In Varchar2,
                                      p_user_id     In Number) Is
    v_count Number;
    e_exists_err Exception;
    e_status_err Exception;
  Begin

    If p_want_status Is Null Then
      Raise e_status_err;
    End If;
    Select Count(1)
      Into v_count
      From acr_invoice_batch_ln bn
     Where bn.invoice_hd_id = p_invoice_hd;
    If v_count < 1 Then
      Raise e_exists_err;
    End If;

    Update acr_invoice_hd t
       Set t.invoice_status = p_want_status
     Where t.invoice_hd_id = p_invoice_hd;

  Exception
    When e_exists_err Then

      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '发票明细行有误，请检查！',
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'ACR_INVOICE_PKG',
                                                     p_procedure_function_name => 'acr_invoice_status_change');

      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    When e_status_err Then

      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '发票状态有误！',
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'ACR_INVOICE_PKG',
                                                     p_procedure_function_name => 'acr_invoice_status_change');

      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);

  End;
  Procedure vat_account_data_process(p_base_period Varchar2,
                                     p_session_id  Number,
                                     p_user_id     Number) Is

    vat_account_tmp_cur    vat_account_tmp%Rowtype;
    v_exist_flag           Number;
    v_purchase_amount_flag Number;
    v_penalty_amount_flag  Number;
  Begin
    Select Count(1)
      Into v_exist_flag
      From vat_account_tmp t
     Where t.session_id = p_session_id;
    If v_exist_flag > 0 Then
      Delete From vat_account_tmp t Where t.session_id = p_session_id;
    End If;
    For cur_temp In (Select ah.invoice_kind,
                            to_char(ah.invoice_date, 'yyyymm') invoice_month,
                            ah.invoice_date,
                            ah.invoice_hd_id,
                            al.invoice_ln_id,
                            ah.invoice_number,
                            ah.total_amount,
                            Case
                              When al.cf_item = 2 Then
                               Case
                                 When (Select to_char(h.journal_date, 'yyyymm')
                                         From hls_journal_header h,
                                              hls_journal_detail l
                                        Where h.journal_header_id =
                                              l.journal_header_id
                                          And h.je_transaction_code =
                                              'LEASE_INCEPT'
                                          And l.je_template_ln_id = 8797
                                          And l.source_doc_id = ah.contract_id
                                          And rownum = 1) >= p_base_period Then
                                  'N'
                                 Else
                                  'Y'
                               End
                              When al.cf_item <> 9 Then
                               Case
                                 When (Select to_char(h.journal_date, 'yyyymm')
                                         From hls_journal_header h,
                                              hls_journal_detail l
                                        Where h.journal_header_id =
                                              l.journal_header_id
                                          And h.je_transaction_code =
                                              'HL_SALES_TAX_ACT_NEW'
                                          And l.cashflow_id = al.cashflow_id
                                          And rownum = 1) >= p_base_period Then
                                  'N'
                                 Else
                                  'Y'
                               End
                              Else
                               Case
                                 When (Select to_char(h.journal_date, 'yyyymm')
                                         From hls_journal_header h,
                                              hls_journal_detail l
                                        Where h.journal_header_id =
                                              l.journal_header_id
                                          And h.je_transaction_code =
                                              'HL_CSH_CONSOLIDATION_DALAY'
                                          And l.journal_line_id = al.ref_n04
                                          And rownum = 1) >= p_base_period Then
                                  'N'
                                 Else
                                  'Y'
                               End
                            End for_invoice_flag,
                            ah.bp_name,
                            nvl(al.contract_number,
                                (Select l.reference3
                                   From hls_journal_header h,
                                        hls_journal_detail l
                                  Where h.journal_header_id =
                                        l.journal_header_id
                                    And h.je_transaction_code =
                                        'HL_CSH_CONSOLIDATION_DALAY'
                                    And l.cashflow_id = al.ref_n04)) contract_number,
                            ah.return_tax_amount,
                            Case
                              When al.cf_item = 2 Then
                               (Select to_char(h.journal_date, 'yyyymm')
                                  From hls_journal_header h,
                                       hls_journal_detail l
                                 Where h.journal_header_id =
                                       l.journal_header_id
                                   And h.je_transaction_code = 'LEASE_INCEPT'
                                   And l.je_template_ln_id = 8797
                                   And l.source_doc_id = ah.contract_id
                                   And rownum = 1)
                              When al.cf_item <> 9 Then
                               (Select to_char(h.journal_date, 'yyyymm')
                                  From hls_journal_header h,
                                       hls_journal_detail l
                                 Where h.journal_header_id =
                                       l.journal_header_id
                                   And h.je_transaction_code =
                                       'HL_SALES_TAX_ACT_NEW'
                                   And l.cashflow_id = al.cashflow_id
                                   And rownum = 1)
                              Else
                               (Select to_char(h.journal_date, 'yyyymm')
                                  From hls_journal_header h,
                                       hls_journal_detail l
                                 Where h.journal_header_id =
                                       l.journal_header_id
                                   And h.je_transaction_code =
                                       'HL_CSH_CONSOLIDATION_DALAY'
                                   And l.journal_line_id = al.ref_n04
                                   And rownum = 1)
                            End je_month,
                            Case
                              When al.cf_item <> 9 Then
                               (Select vat_due_amount
                                  From con_contract_cashflow
                                 Where cashflow_id = al.cashflow_id)
                              Else
                               (Select l.amount_cr
                                  From hls_journal_header h,
                                       hls_journal_detail l,
                                       con_contract       t
                                 Where h.journal_header_id =
                                       l.journal_header_id
                                   And h.je_transaction_code =
                                       'HL_CSH_CONSOLIDATION_DALAY'
                                   And t.contract_id = ah.contract_id
                                   And t.contract_number = l.reference3
                                   And h.journal_header_id = al.ref_n03
                                   And l.reference6 = 'A5'
                                   And rownum = 1)
                            End amount_cr,
                            Case
                              When al.cf_item = 2 Then
                               (Select h.journal_date
                                  From hls_journal_header h,
                                       hls_journal_detail l
                                 Where h.journal_header_id =
                                       l.journal_header_id
                                   And h.je_transaction_code = 'LEASE_INCEPT'
                                   And l.je_template_ln_id = 8797
                                   And l.source_doc_id = ah.contract_id
                                   And rownum = 1)
                              When al.cf_item <> 9 Then
                               (Select h.journal_date
                                  From hls_journal_header h,
                                       hls_journal_detail l
                                 Where h.journal_header_id =
                                       l.journal_header_id
                                   And h.je_transaction_code =
                                       'HL_SALES_TAX_ACT_NEW'
                                   And l.cashflow_id = al.cashflow_id
                                   And rownum = 1)
                              Else
                               (Select h.journal_date
                                  From hls_journal_header h,
                                       hls_journal_detail l
                                 Where h.journal_header_id =
                                       l.journal_header_id
                                   And h.je_transaction_code =
                                       'HL_CSH_CONSOLIDATION_DALAY'
                                   And l.journal_line_id = al.ref_n04
                                   And rownum = 1)
                            End je_date,
                            Case
                              When al.ref_n04 Is Not Null Then
                               (Select h.sbo_num
                                  From hls_journal_header h,
                                       hls_journal_detail l
                                 Where h.journal_header_id =
                                       l.journal_header_id
                                   And h.je_transaction_code =
                                       'HL_CSH_CONSOLIDATION_DALAY'
                                   And l.journal_line_id = al.ref_n04)
                              Else
                               decode(al.cf_item,
                                      2,
                                      (Select h.sbo_num
                                         From hls_journal_header h,
                                              hls_journal_detail l
                                        Where h.journal_header_id =
                                              l.journal_header_id
                                          And h.je_transaction_code =
                                              'LEASE_INCEPT'
                                          And l.je_template_ln_id = 8797
                                          And l.source_doc_id = ah.contract_id
                                          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'))))),
                                      (Select h.sbo_num
                                         From hls_journal_header h,
                                              hls_journal_detail l
                                        Where h.journal_header_id =
                                              l.journal_header_id
                                          And h.je_transaction_code =
                                              'HL_SALES_TAX_ACT_NEW'
                                          And l.cashflow_id = al.cashflow_id
                                          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 rownum = 1))
                            End journal_num,
                            decode(al.cf_item,
                                   1,
                                   to_char(al.times),
                                   2,
                                   '首付款',
                                   8,
                                   '留购金',
                                   9,
                                   '违约金') times,
                            al.cashflow_id,
                            al.cf_item,
                            Case
                              When al.cf_item <> 9 Then
                               (Select due_date
                                  From con_contract_cashflow
                                 Where cashflow_id = al.cashflow_id)
                              Else
                               (Select h.journal_date
                                  From hls_journal_header h,
                                       hls_journal_detail l
                                 Where h.journal_header_id =
                                       l.journal_header_id
                                   And h.je_transaction_code =
                                       'HL_CSH_CONSOLIDATION_DALAY'
                                   And l.journal_line_id = al.ref_n04)
                            End due_date,
                            Case
                              When al.cf_item <> 9 Then
                               (Select due_amount
                                  From con_contract_cashflow
                                 Where cashflow_id = al.cashflow_id)
                              Else
                               (Select l.amount_dr
                                  From hls_journal_header h,
                                       hls_journal_detail l
                                 Where h.journal_header_id =
                                       l.journal_header_id
                                   And h.je_transaction_code =
                                       'HL_CSH_CONSOLIDATION_DALAY'
                                   And l.journal_line_id = al.ref_n04)
                            End due_amount,
                            decode(cf_item,
                                   2,
                                   (Select due_amount
                                      From con_contract_cashflow
                                     Where cashflow_id = al.cashflow_id),
                                   (Select principal
                                      From con_contract_cashflow
                                     Where cashflow_id = al.cashflow_id)) principal,
                            decode(cf_item,
                                   8,
                                   (Select due_amount
                                      From con_contract_cashflow
                                     Where cashflow_id = al.cashflow_id),
                                   9,
                                   (Select l.amount_dr
                                      From hls_journal_header h,
                                           hls_journal_detail l
                                     Where h.journal_header_id =
                                           l.journal_header_id
                                       And h.je_transaction_code =
                                           'HL_CSH_CONSOLIDATION_DALAY'
                                       And l.journal_line_id = al.ref_n04),
                                   (Select interest
                                      From con_contract_cashflow
                                     Where cashflow_id = al.cashflow_id)) interest,
                            Case
                              When al.cf_item <> 9 Then
                               (Select vat_due_amount
                                  From con_contract_cashflow
                                 Where cashflow_id = al.cashflow_id)
                              Else
                               (Select l.amount_cr
                                  From hls_journal_header h,
                                       hls_journal_detail l,
                                       con_contract       t
                                 Where h.journal_header_id =
                                       l.journal_header_id
                                   And h.je_transaction_code =
                                       'HL_CSH_CONSOLIDATION_DALAY'
                                   And t.contract_id = ah.contract_id
                                   And t.contract_number = l.reference3
                                   And h.journal_header_id = al.ref_n03
                                   And l.reference6 = 'A5'
                                   And rownum = 1)
                            End vat_due_amount
                       From acr_invoice_hd ah, acr_invoice_ln_lv al
                      Where ah.invoice_hd_id = al.invoice_hd_id
                        And ah.vat_interface_status = 'BACK'
                        And ah.reversed_flag = 'N'
                        And to_char(ah.invoice_date, 'yyyymm') =
                            p_base_period
                        And (Exists
                             (Select 1
                                From hls_journal_header hh,
                                     hls_journal_detail hl
                               Where hh.journal_header_id =
                                     hl.journal_header_id
                                 And hh.sbo_num Is Not Null
                                 And ((hh.je_transaction_code =
                                     'HL_SALES_TAX_ACT_NEW' And
                                     hl.cashflow_id = al.cashflow_id And
                                     ((trunc(hh.journal_date) <=
                                     trunc(to_date('2020-01-31',
                                                       'yyyy-mm-dd')) And
                                     hh.description = 'MANUAL_IMPORT') Or
                                     (trunc(hh.journal_date) >
                                     trunc(to_date('2020-01-31',
                                                       'yyyy-mm-dd'))))

                                     ) Or
                                     (hh.je_transaction_code = 'LEASE_INCEPT' And
                                     hl.je_template_ln_id = 8797 And
                                     hl.source_doc_id = ah.contract_id And
                                     al.cf_item = 2 And
                                     ((trunc(hh.journal_date) <=
                                     trunc(to_date('2020-01-31',
                                                       'yyyy-mm-dd')) And
                                     hh.description = 'MANUAL_IMPORT') Or
                                     (trunc(hh.journal_date) >
                                     trunc(to_date('2020-01-31',
                                                       'yyyy-mm-dd'))))))
                                  Or (al.ref_n04 Is Not Null And
                                     al.ref_n04 = hl.journal_line_id And
                                     (hh.je_transaction_code =
                                     'HL_CSH_CONSOLIDATION_DALAY' And
                                     ((trunc(hh.journal_date) <=
                                     trunc(to_date('2020-01-31',
                                                       'yyyy-mm-dd')) And
                                     hh.description = 'MANUAL_IMPORT') Or
                                     (trunc(hh.journal_date) >
                                     trunc(to_date('2020-01-31',
                                                       'yyyy-mm-dd'))))))))) Loop

      vat_account_tmp_cur.vct_id          := vat_account_tmp_s.nextval;
      vat_account_tmp_cur.session_id      := p_session_id;
      vat_account_tmp_cur.invoice_hd_id   := cur_temp.invoice_hd_id;
      vat_account_tmp_cur.invoice_line_id := cur_temp.invoice_ln_id;
      vat_account_tmp_cur.contract_id     := Null;
      vat_account_tmp_cur.cashflow_id     := cur_temp.cashflow_id;
      vat_account_tmp_cur.invoice_kind    := cur_temp.invoice_kind;
      vat_account_tmp_cur.tax_apply_month := cur_temp.invoice_month;
      vat_account_tmp_cur.invoice_date    := cur_temp.invoice_date;
      vat_account_tmp_cur.invoice_number  := cur_temp.invoice_number;
      --含税金额
      vat_account_tmp_cur.invoice_tax_amount := cur_temp.total_amount;
      vat_account_tmp_cur.apply_flag         := cur_temp.for_invoice_flag;
      vat_account_tmp_cur.tenant_name        := cur_temp.bp_name;
      vat_account_tmp_cur.contract_number    := cur_temp.contract_number;
      vat_account_tmp_cur.acr_tax_amount     := cur_temp.return_tax_amount;
      vat_account_tmp_cur.journal_tax_amount := cur_temp.amount_cr;
      vat_account_tmp_cur.journal_month      := cur_temp.je_month;
      vat_account_tmp_cur.journal_date       := cur_temp.je_date;
      vat_account_tmp_cur.journal_num        := cur_temp.journal_num;
      vat_account_tmp_cur.times              := cur_temp.times;
      vat_account_tmp_cur.cf_item            := cur_temp.cf_item;
      vat_account_tmp_cur.due_date           := cur_temp.due_date;
      vat_account_tmp_cur.due_amount         := cur_temp.due_amount;
      vat_account_tmp_cur.principal          := cur_temp.principal;
      vat_account_tmp_cur.interest           := cur_temp.interest;
      vat_account_tmp_cur.tax_amount         := cur_temp.vat_due_amount;
      vat_account_tmp_cur.created_by         := p_user_id;
      vat_account_tmp_cur.creation_date      := Sysdate;
      vat_account_tmp_cur.last_updated_by    := p_user_id;
      vat_account_tmp_cur.last_update_date   := Sysdate;

      Insert Into vat_account_tmp Values vat_account_tmp_cur;

    End Loop;
    --插入调差凭证
    For cur_distance_je In (Select l.amount_cr,
                                   ach.invoice_hd_id,
                                   to_char(h.journal_date, 'yyyymm') je_month,
                                   h.journal_date,
                                   h.sbo_num
                              From hls_journal_header h,
                                   hls_journal_detail l,
                                   acr_invoice_hd     ach
                             Where l.journal_header_id = h.journal_header_id
                               And h.source_id = ach.invoice_hd_id
                               And h.sbo_num Is Not Null
                               And ach.invoice_hd_id In
                                   (Select Distinct t.invoice_hd_id
                                      From vat_account_tmp t
                                     Where t.session_id = p_session_id)
                               And l.reference6 = 'A5') Loop
      Insert Into vat_account_tmp
        (vct_id,
         session_id,
         invoice_hd_id,
         journal_tax_amount,
         journal_month,
         journal_date,
         journal_num,
         description,
         created_by,
         creation_date,
         last_updated_by,
         last_update_date)
      Values
        (vat_account_tmp_s.nextval,
         p_session_id,
         cur_distance_je.invoice_hd_id,
         cur_distance_je.amount_cr,
         cur_distance_je.je_month,
         cur_distance_je.journal_date,
         cur_distance_je.sbo_num,
         --调差凭证标志
         'ADJUST_JOURNAL',
         p_user_id,
         Sysdate,
         p_user_id,
         Sysdate);
    End Loop;
    --更新表中的数据
    --统计不是本金利益分开开票的情况
    For acr_invoice_ln In (Select trunc(Min(t.due_date)) min_due_date,
                                  t.invoice_hd_id
                             From vat_account_tmp       t,
                                  acr_invoice_ln        l,
                                  con_contract_cashflow ccc
                            Where t.session_id = p_session_id
                              And t.cashflow_id = ccc.cashflow_id
                              And l.invoice_ln_id = t.invoice_line_id
                              And l.total_amount = ccc.due_amount
                            Group By t.invoice_hd_id) Loop

      Update vat_account_tmp vat
         Set vat.invoice_kind       = '',
             vat.apply_flag         = '',
             vat.invoice_date       = '',
             vat.invoice_number     = '',
             vat.invoice_tax_amount = '',
             vat.tax_apply_month    = '',
             vat.tenant_name        = '',
             vat.contract_number    = '',
             vat.acr_tax_amount     = ''
       Where vat.invoice_hd_id = acr_invoice_ln.invoice_hd_id
         And vat.session_id = p_session_id
         And trunc(vat.due_date) > trunc(acr_invoice_ln.min_due_date);
      --单独判断最后一期租金和留购金的情况，删除留购金的数据
      Select Count(1)
        Into v_purchase_amount_flag
        From vat_account_tmp t
       Where t.session_id = p_session_id
         And t.invoice_hd_id = acr_invoice_ln.invoice_hd_id
         And trunc(t.due_date) = trunc(acr_invoice_ln.min_due_date);
      If v_purchase_amount_flag > 1 Then
        Update vat_account_tmp vat
           Set vat.invoice_kind       = '',
               vat.apply_flag         = '',
               vat.invoice_date       = '',
               vat.invoice_number     = '',
               vat.invoice_tax_amount = '',
               vat.tax_apply_month    = '',
               vat.tenant_name        = '',
               vat.contract_number    = '',
               vat.acr_tax_amount     = ''
         Where vat.invoice_hd_id = acr_invoice_ln.invoice_hd_id
           And vat.session_id = p_session_id
           And trunc(vat.due_date) = trunc(acr_invoice_ln.min_due_date)
           And vat.cf_item = 8;
      End If;
    End Loop;
    --本利拆开
    For acr_invoice_ln_dif In (Select t.invoice_line_id,
                                      t.invoice_tax_amount,
                                      ccc.interest,
                                      ccc.principal
                                 From vat_account_tmp       t,
                                      con_contract_cashflow ccc,
                                      acr_invoice_ln        l
                                Where t.session_id = p_session_id
                                  And t.cashflow_id = ccc.cashflow_id
                                  And l.invoice_ln_id = t.invoice_line_id
                                  And l.total_amount <> ccc.due_amount) Loop
      --将利息置空
      If acr_invoice_ln_dif.invoice_tax_amount =
         acr_invoice_ln_dif.interest Then
        Update vat_account_tmp vat
           Set vat.journal_tax_amount = '',
               vat.journal_month      = '',
               vat.journal_date       = '',
               vat.journal_num        = '',
               vat.times              = '',
               vat.due_date           = '',
               vat.due_amount         = '',
               vat.principal          = '',
               vat.interest           = '',
               vat.tax_amount         = ''
         Where vat.invoice_line_id = acr_invoice_ln_dif.invoice_line_id;
      End If;

    End Loop;

    --罚息的数据单独处理
    For acr_invoice_ln In (Select trunc(Min(t.due_date)) min_due_date,
                                  t.invoice_hd_id,
                                  Min(t.invoice_line_id) min_invoice_ln_id
                             From vat_account_tmp t
                            Where t.session_id = p_session_id
                              And t.cf_item = 9
                            Group By t.invoice_hd_id) Loop
      Update vat_account_tmp vat
         Set vat.invoice_kind       = '',
             vat.apply_flag         = '',
             vat.invoice_date       = '',
             vat.invoice_number     = '',
             vat.invoice_tax_amount = '',
             vat.tax_apply_month    = '',
             vat.tenant_name        = '',
             vat.contract_number    = '',
             vat.acr_tax_amount     = ''
       Where vat.invoice_hd_id = acr_invoice_ln.invoice_hd_id
         And vat.session_id = p_session_id
         And trunc(vat.due_date) > trunc(acr_invoice_ln.min_due_date)
         And vat.cf_item = 9;

      --如果罚息是同一天的，也需要单独处理
      Select Count(1)
        Into v_penalty_amount_flag
        From vat_account_tmp vat
       Where vat.invoice_hd_id = acr_invoice_ln.invoice_hd_id
         And vat.session_id = p_session_id
         And trunc(vat.due_date) = trunc(acr_invoice_ln.min_due_date);
      If v_penalty_amount_flag > 1 Then
      
        Update vat_account_tmp vat
           Set vat.invoice_kind       = '',
               vat.apply_flag         = '',
               vat.invoice_date       = '',
               vat.invoice_number     = '',
               vat.invoice_tax_amount = '',
               vat.tax_apply_month    = '',
               vat.tenant_name        = '',
               vat.contract_number    = '',
               vat.acr_tax_amount     = ''
         Where vat.invoice_hd_id = acr_invoice_ln.invoice_hd_id
           And vat.session_id = p_session_id
           And trunc(vat.due_date) = trunc(acr_invoice_ln.min_due_date)
           And vat.invoice_line_id > acr_invoice_ln.min_invoice_ln_id;
      End If;
    End Loop;
  
  End;
End acr_invoice_pkg;
/