CREATE OR REPLACE PACKAGE csh_payment_req_pkg IS

  -- Author  : qwm
  -- Created : 2013/5/24 16:35:21
  -- Purpose :
  /*procedure csh_payment_req_amount_check(p_cashflow_id        number,
  p_amount             number,
  p_company_id         number,
  p_user_id            number,
  p_transaction_date   date,
  p_amount_flag        out varchar2,
  p_payment_req_number out varchar2,
  p_document_type      varchar2);*/
  FUNCTION get_bp_id(p_contract_id NUMBER) RETURN NUMBER;

  PROCEDURE check_cf_after_ins_temp(p_session_id NUMBER, p_user_id NUMBER);

  PROCEDURE check_cf_0_total_amt(p_contract_id NUMBER, p_user_id NUMBER);

  FUNCTION get_bp_category_desc(p_contract_id NUMBER, p_bp_id NUMBER)
    RETURN VARCHAR2;

  FUNCTION get_payment_req_number(p_document_type     VARCHAR2,
                                  p_transaction_date  DATE,
                                  p_company_id        NUMBER,
                                  p_user_id           NUMBER,
                                  p_document_category VARCHAR2)
    RETURN VARCHAR2;

  PROCEDURE payment_submit(p_payment_req_id NUMBER, p_user_id NUMBER);

  PROCEDURE change_print_status(p_contract_id NUMBER, p_user_id NUMBER);

  PROCEDURE change_printed_status(p_contract_id NUMBER, p_user_id NUMBER);

  PROCEDURE change_applied_status(p_contract_id NUMBER, p_user_id NUMBER);

  PROCEDURE csh_payment_apply(p_contract_id NUMBER, p_user_id NUMBER);

  PROCEDURE csh_payment_print(p_contract_id NUMBER, p_user_id NUMBER);

  PROCEDURE upd_csh_payment_after_reverse(p_transaction_id csh_transaction.transaction_id%TYPE,
                                          p_user_id        NUMBER);

  PROCEDURE close_csh_payment_req_hd(p_payment_req_id NUMBER,
                                     p_closed_date    DATE,
                                     p_closed_note    VARCHAR2,
                                     p_user_id        NUMBER);

  PROCEDURE check_csh_payment_bp_unique(p_payment_req_id NUMBER,
                                        p_user_id        NUMBER);

  PROCEDURE check_csh_payment_bp(p_payment_req_id NUMBER, p_user_id NUMBER);

  --保证金退还 付款申请 校验 add by Harry 9952 2017/2/24
  PROCEDURE check_csh_payment_bond(p_payment_req_id NUMBER,
                                   p_user_id        NUMBER);
  PROCEDURE payment_csh_payment(p_payment_req_id          NUMBER,
                                p_payment_req_ln_id       NUMBER,
                                p_pay_date                DATE,
                                p_pay_amount              NUMBER,
                                p_company_id              NUMBER,
                                p_internal_period_num     csh_transaction.internal_period_num%TYPE,
                                p_period_name             csh_transaction.period_name%TYPE,
                                p_exchange_rate_type      csh_transaction.exchange_rate_type%TYPE,
                                p_exchange_rate_quotation csh_transaction.exchange_rate_quotation%TYPE,
                                p_exchange_rate           csh_transaction.exchange_rate%TYPE,
                                p_bank_account_id         csh_transaction.bank_account_id%TYPE,
                                p_bank_account_num        VARCHAR2,
                                p_description             csh_transaction.description%TYPE,
                                p_user_id                 NUMBER,
                                p_cashflow_id             NUMBER,
                                p_bank_slip_num           csh_transaction.bank_slip_num%TYPE,
                                p_bp_bank_account_id      csh_transaction.bp_bank_account_id%TYPE,
                                p_bp_bank_account_num     csh_transaction.bp_bank_account_num%TYPE,
                                p_handling_charge         csh_transaction.handling_charge%TYPE,
                                p_payment_method_id       csh_transaction.payment_method_id%TYPE,
                                p_merge_flag              VARCHAR2 DEFAULT 'N',
                                p_payment_behalf_flag     VARCHAR2 DEFAULT 'N',
                                p_payment_status          OUT VARCHAR2,
                                p_transaction_id          IN OUT NUMBER,
                                p_bp_category             varchar2 default null,
                                p_due_amount              NUMBER DEFAULT NULL);
  PROCEDURE payment_csh_payment(p_payment_req_id          NUMBER,
                                p_session_id              NUMBER,
                                p_pay_date                DATE,
                                p_pay_amount              NUMBER,
                                p_company_id              NUMBER,
                                p_internal_period_num     csh_transaction.internal_period_num%TYPE,
                                p_period_name             csh_transaction.period_name%TYPE,
                                p_exchange_rate_type      csh_transaction.exchange_rate_type%TYPE,
                                p_exchange_rate_quotation csh_transaction.exchange_rate_quotation%TYPE,
                                p_exchange_rate           csh_transaction.exchange_rate%TYPE,
                                p_bank_account_id         csh_transaction.bank_account_id%TYPE,
                                p_bank_account_num        VARCHAR2,
                                p_description             csh_transaction.description%TYPE,
                                p_user_id                 NUMBER,
                                p_cashflow_id             NUMBER,
                                p_bank_slip_num           csh_transaction.bank_slip_num%TYPE,
                                p_bp_bank_account_id      csh_transaction.bp_bank_account_id%TYPE,
                                p_bp_bank_account_num     csh_transaction.bp_bank_account_num%TYPE,
                                p_handling_charge         csh_transaction.handling_charge%TYPE,
                                p_payment_method_id       csh_transaction.payment_method_id%TYPE,
                                p_payment_status          OUT VARCHAR2,
                                p_merge_flag              VARCHAR2 DEFAULT 'N',
                                p_payment_behalf_flag     VARCHAR2 DEFAULT 'N',
                                p_this_pay_agent_amount   NUMBER,
                                p_this_pay_vender_amount  NUMBER,
                                p_bank_flag               VARCHAR2 DEFAULT 'N',
                                p_bp_category             varchar2 default null,
                                p_due_amount              NUMBER DEFAULT NULL);

  PROCEDURE ins_csh_pay_cashflow_id_temp(p_session_id  NUMBER,
                                         p_cashflow_id NUMBER,
                                         p_user_id     NUMBER);

  PROCEDURE del_csh_pay_cashflow_id_temp(p_session_id NUMBER,
                                         p_user_id    NUMBER);

  PROCEDURE ins_csh_payment_req_hd(p_payment_req_id       IN OUT NUMBER,
                                   p_company_id           NUMBER,
                                   p_document_type        VARCHAR2,
                                   p_payment_req_number   VARCHAR2,
                                   p_req_date             DATE,
                                   p_apply_pay_date       DATE,
                                   p_transaction_category VARCHAR2,
                                   p_distribution_set_id  NUMBER,
                                   p_payment_method_id    NUMBER,
                                   p_bp_id                NUMBER,
                                   p_bp_category          VARCHAR2,
                                   p_amount               NUMBER,
                                   p_currency_code        VARCHAR2,
                                   p_description          VARCHAR2,
                                   p_submitted_flag       VARCHAR2,
                                   p_printed_flag         VARCHAR2,
                                   p_printed_times        NUMBER,
                                   p_last_print_date      DATE,
                                   p_closed_flag          VARCHAR2,
                                   p_closed_date          DATE,
                                   p_closed_note          VARCHAR2,
                                   p_approval_status      VARCHAR2,
                                   p_approval_date        DATE,
                                   p_approval_note        VARCHAR2,
                                   p_bp_bank_account_id   NUMBER DEFAULT NULL,
                                   p_bp_bank_account_num  VARCHAR2 DEFAULT NULL,
                                   p_bp_bank_account_name VARCHAR2 DEFAULT NULL,
                                   p_business_type        VARCHAR2 DEFAULT NULL,
                                   p_branch_id            NUMBER,
                                   p_city_id              NUMBER,
                                   p_user_id              NUMBER,
                                   p_csh_bp_name          VARCHAR2 DEFAULT NULL);

  PROCEDURE ins_csh_payment_req_ln(p_payment_req_ln_id      IN OUT NUMBER,
                                   p_payment_req_id         NUMBER,
                                   p_payment_req_ln_type    VARCHAR2,
                                   p_ref_doc_category       VARCHAR2,
                                   p_ref_doc_id             NUMBER,
                                   p_ref_doc_line_id        NUMBER,
                                   p_bp_id                  NUMBER,
                                   p_currency_code          VARCHAR2,
                                   p_amount                 NUMBER,
                                   p_amount_paid            NUMBER,
                                   p_description            VARCHAR2,
                                   p_payment_status         VARCHAR2,
                                   p_payment_completed_date DATE,
                                   p_apply_pay_date         DATE,
                                   p_payment_method_id      NUMBER,
                                   p_bank_account_id        NUMBER,
                                   p_bank_account_num       VARCHAR2,
                                   p_bank_account_name      VARCHAR2,
                                   p_bank_branch_name       VARCHAR2,
                                   p_bp_bank_account_id     NUMBER DEFAULT NULL,
                                   p_bp_bank_account_num    VARCHAR2 DEFAULT NULL,
                                   p_bp_bank_account_name   VARCHAR2 DEFAULT NULL,
                                   p_user_id                NUMBER,
                                   p_company_id             NUMBER);

  PROCEDURE del_csh_payment_req_ln(p_payment_req_ln_id NUMBER,
                                   p_user_id           NUMBER);

  PROCEDURE del_csh_payment_req_hd(p_payment_req_id NUMBER,
                                   p_user_id        NUMBER);

  PROCEDURE update_csh_payment_flag(p_payment_req_id NUMBER,
                                    p_payment_flag   VARCHAR2,
                                    p_user_id        NUMBER);

  PROCEDURE update_payment_req_number(p_payment_req_id     IN NUMBER,
                                      p_payment_req_number IN VARCHAR2);

  --add by zxf                               
  PROCEDURE update_payment_req_contract_id(p_payment_req_id IN NUMBER,
                                           p_contract_id    IN NUMBER);

  PROCEDURE check_ddct_amount(p_payment_req_id IN NUMBER);

  PROCEDURE check_contract_payment(p_contract_id    NUMBER,
                                   p_payment_req_id NUMBER,
                                   p_user_id        NUMBER);

  --add by 9188 检验付款申请工作流的所有合同是否全部核销
  PROCEDURE check_sch_payment_con(p_payment_req_id NUMBER,
                                  p_user_id        NUMBER);

  --add by Harry 9952 保证金退还-付款申请校验
  PROCEDURE check_status_and_flag(p_cashflow_id NUMBER, p_user_id NUMBER);

  PROCEDURE create_cdd(p_payment_req_id NUMBER,
                       p_user_id        NUMBER,
                       p_cdd_list_id    IN OUT NUMBER);
  PROCEDURE save_cdd_item_from_templt(p_document_category VARCHAR2,
                                      p_document_type     VARCHAR2,
                                      p_document_id       NUMBER,
                                      p_company_id        NUMBER,
                                      p_user_id           NUMBER,
                                      p_cdd_list_id       IN OUT NUMBER);

END csh_payment_req_pkg;
/
CREATE OR REPLACE PACKAGE BODY csh_payment_req_pkg IS

  v_err_code VARCHAR2(2000);

  e_lock_trx_error EXCEPTION;

  e_lock_error EXCEPTION;

  PRAGMA EXCEPTION_INIT(e_lock_trx_error, -54);

  PRAGMA EXCEPTION_INIT(e_lock_error, -54);

  FUNCTION get_bp_id(p_contract_id NUMBER) RETURN NUMBER IS
    v_bp_id hls_bp_master.bp_id%TYPE;
    --v_contract_rec con_contract%ROWTYPE;
  BEGIN
    --库融和保理的经销商不必输,如果为空则取bp_id_agent_level1
    /*SELECT nvl(t.bp_id_agent_level1,
    t.bp_id_tenant)*/
    /*SELECT t.bp_id_tenant
     INTO v_bp_id
     FROM con_contract t
    WHERE t.contract_id = p_contract_id;*/
    SELECT t3.bp_id
      INTO v_bp_id
      FROM con_contract_bp t2, hls_bp_master t3
      --delete by
    WHERE
       -- t2.bp_category = 'AGENT' AND 
       t3.bp_id = t2.bp_id
       AND t2.contract_id = p_contract_id;
    RETURN v_bp_id;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
  END;

  PROCEDURE check_cf_after_ins_temp(p_session_id NUMBER, p_user_id NUMBER) IS
    v_62_count  NUMBER;
    v_52_count  NUMBER;
    v_cf_count  NUMBER;
    v_con_count NUMBER;
  BEGIN
    --应付保费个数
    SELECT COUNT(1)
      INTO v_62_count
      FROM csh_payment_cashflow_id_temp t1, con_contract_cashflow ccc
     WHERE t1.cashflow_id = ccc.cashflow_id
       AND ccc.cf_item = 62
       AND t1.session_id = p_session_id;
  
    --现金流个数
    SELECT COUNT(1)
      INTO v_cf_count
      FROM csh_payment_cashflow_id_temp t1, con_contract_cashflow ccc
     WHERE t1.cashflow_id = ccc.cashflow_id
       AND t1.session_id = p_session_id;
  
    --校验
    --cancel by shen
    --IF v_62_count > 0 AND v_cf_count <> v_62_count THEN
     -- sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '应付保费只能单独发起付款申请!',
       --                                              p_created_by              => p_user_id,
     --                                                p_package_name            => 'csh_payment_req_pkg',
     --                                                p_procedure_function_name => 'check_cf_after_ins_temp');
     -- raise_application_error(sys_raise_app_error_pkg.c_error_number,
       --                       sys_raise_app_error_pkg.g_err_line_id);
    --END IF;
  
    IF v_62_count > 0 THEN
      NULL;
    ELSE
      --合同个数
    /*  SELECT COUNT(1)
        INTO v_cf_count
        FROM (SELECT ccc.contract_id
                FROM csh_payment_cashflow_id_temp t1,
                     con_contract_cashflow        ccc
               WHERE t1.cashflow_id = ccc.cashflow_id
                 AND t1.session_id = p_session_id
               GROUP BY ccc.contract_id) t;
    
      IF v_cf_count > 1 THEN
        sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '请选择合同号相同的单据!',
                                                       p_created_by              => p_user_id,
                                                       p_package_name            => 'csh_payment_req_pkg',
                                                       p_procedure_function_name => 'check_cf_after_ins_temp');
        raise_application_error(sys_raise_app_error_pkg.c_error_number,
                                sys_raise_app_error_pkg.g_err_line_id);
      END IF;*/
    
      SELECT COUNT(1)
        INTO v_52_count
        FROM csh_payment_cashflow_id_temp t1, con_contract_cashflow ccc
       WHERE t1.cashflow_id = ccc.cashflow_id
         AND ccc.cf_item IN ('52', '509')
         AND t1.session_id = p_session_id;
      IF v_52_count > 0 AND v_cf_count <> v_52_count THEN
        sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '保证金退还只能单独发起付款申请!',
                                                       p_created_by              => p_user_id,
                                                       p_package_name            => 'csh_payment_req_pkg',
                                                       p_procedure_function_name => 'check_cf_after_ins_temp');
        raise_application_error(sys_raise_app_error_pkg.c_error_number,
                                sys_raise_app_error_pkg.g_err_line_id);
      END IF;
    END IF;
  END;

  PROCEDURE check_cf_0_total_amt(p_contract_id NUMBER, p_user_id NUMBER) IS
    v_cf_0_total_amt NUMBER;
    e_cf_0_total_amt_err EXCEPTION;
    v_contract_rec con_contract%ROWTYPE;
    v_count        NUMBER;
    v_count1       NUMBER;
    v_count2       NUMBER;
    v_count3       NUMBER;
    v_count4       NUMBER;
    v_count5       NUMBER;
    v_count6       NUMBER;
  BEGIN
    SELECT *
      INTO v_contract_rec
      FROM con_contract t
     WHERE t.contract_id = p_contract_id
       FOR UPDATE NOWAIT;
  
    SELECT COUNT(1)
      INTO v_count5
      FROM csh_payment_cashflow_id_temp t1
     WHERE t1.cashflow_id IN
           (SELECT ccc.cashflow_id
              FROM con_contract_cashflow ccc
             WHERE ccc.contract_id = p_contract_id
               AND ccc.cf_item IN ('52', '509'));
    SELECT COUNT(1)
      INTO v_count6
      FROM csh_payment_cashflow_id_temp t1
     WHERE t1.cashflow_id IN
           (SELECT ccc.cashflow_id
              FROM con_contract_cashflow ccc
             WHERE ccc.contract_id = p_contract_id);
  
    IF v_count5 > 0 AND v_count5 <> v_count6 THEN
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '保证金退还只能单独发起付款申请!',
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'csh_payment_req_pkg',
                                                     p_procedure_function_name => 'check_cf_0_total_amt');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    END IF;
    /* --判断首付款是否支付
    SELECT COUNT(1)
      INTO v_count
      FROM con_contract_cashflow tt
     WHERE tt.contract_id = p_contract_id
       AND tt.cf_item = 2
       AND tt.cf_direction = 'INFLOW'
       AND tt.cf_status = 'RELEASE'
       AND tt.write_off_flag = 'FULL';
    --除首付款延期的合同外,如果首付款现金流(2)没有支付,则不能发起付款申请
    IF nvl(v_contract_rec.hd_user_col_v09,
           0) NOT IN ('20',
                      '30')
       AND v_count = 0 THEN
    
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '该合同首付款没有支付,不能发起付款申请!',
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'csh_payment_req_pkg',
                                                     p_procedure_function_name => 'check_cf_0_total_amt');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    END IF;*/
  
    --判断合同进项发票是否上传
    --未开票时
    /*IF v_contract_rec.invoice_uploaded_flag = 'N' THEN
      --查询现金流表未付款的破碎器成本、机械成本
      --获取临时表中符合现金流条件的个数
      SELECT COUNT(1)
        INTO v_count1
        FROM csh_payment_cashflow_id_temp t1
       WHERE t1.cashflow_id IN (SELECT ccc.cashflow_id
                                  FROM con_contract_cashflow ccc
                                 WHERE ccc.contract_id = p_contract_id
                                   AND ccc.cf_item IN ('5',
                                                       '998')
                                   AND ccc.write_off_flag = 'NOT');
      --获取现金流表未付款的破碎器成本、机械成本的个数
      SELECT COUNT(1)
        INTO v_count2
        FROM con_contract_cashflow ccc
       WHERE ccc.contract_id = p_contract_id
         AND ccc.cf_item IN ('5',
                             '998')
         AND ccc.write_off_flag = 'NOT';
      --如果选择的现金流不包含破碎器成本或者机械成本,则报错
      IF v_count1 < v_count2 THEN
        sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '该合同未未开票,请勾选正确的成本进行发起!',
                                                       p_created_by              => p_user_id,
                                                       p_package_name            => 'csh_payment_req_pkg',
                                                       p_procedure_function_name => 'check_cf_0_total_amt');
        raise_application_error(sys_raise_app_error_pkg.c_error_number,
                                sys_raise_app_error_pkg.g_err_line_id);
      END IF;
    ELSE
      --已开票时
      --查询现金流表未付款的破碎器成本&利润、机械成本&利润
      --获取临时表中符合现金流条件的个数
      SELECT COUNT(1)
        INTO v_count3
        FROM csh_payment_cashflow_id_temp t1
       WHERE t1.cashflow_id IN (SELECT ccc.cashflow_id
                                  FROM con_contract_cashflow ccc
                                 WHERE ccc.contract_id = p_contract_id
                                   AND ccc.cf_item IN ('5',
                                                       '997',
                                                       '995',
                                                       '998')
                                   AND ccc.write_off_flag = 'NOT');
      --获取现金流表未付款的破碎器成本、机械成本的个数
      SELECT COUNT(1)
        INTO v_count4
        FROM con_contract_cashflow ccc
       WHERE ccc.contract_id = p_contract_id
         AND ccc.cf_item IN ('5',
                             '997',
                             '995',
                             '998')
         AND ccc.write_off_flag = 'NOT';
      --如果选择的现金流不包含破碎器成本或者机械成本,则报错
      IF v_count3 < v_count4 THEN
        sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '该合同未未开票,请勾选正确的成本和利润进行发起!',
                                                       p_created_by              => p_user_id,
                                                       p_package_name            => 'csh_payment_req_pkg',
                                                       p_procedure_function_name => 'check_cf_0_total_amt');
        raise_application_error(sys_raise_app_error_pkg.c_error_number,
                                sys_raise_app_error_pkg.g_err_line_id);
      END IF;
    END IF;*/
  
  EXCEPTION
    WHEN e_cf_0_total_amt_err THEN
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CON_CONTRACT_VERIFY_PKG.CONTRACT_UPDATE_CF_0_TOTAL_AMT_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'CON_CONTRACT_VERIFY_PKG',
                                                      p_procedure_function_name => 'CONTRACT_UPDATE_CHECK',
                                                      p_token_1                 => '#contract_number',
                                                      p_token_value_1           => v_contract_rec.contract_number);
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  END;

  FUNCTION get_con_last_writeoff_date(p_cashflow_id NUMBER) RETURN DATE IS
    v_last_write_off_date DATE;
  BEGIN
    SELECT MAX(w.write_off_date)
      INTO v_last_write_off_date
      FROM csh_write_off w
     WHERE w.cashflow_id = p_cashflow_id;
    RETURN v_last_write_off_date;
  END;

  FUNCTION get_bp_category_desc(p_contract_id NUMBER, p_bp_id NUMBER)
    RETURN VARCHAR2 IS
    v_attribute VARCHAR2(32767);
  BEGIN
    FOR r_bp_category_rec IN (SELECT c.description
                                FROM con_contract_bp t, hls_bp_category c
                               WHERE t.bp_category = c.bp_category
                                 AND t.contract_id = p_contract_id
                                 AND t.bp_id = p_bp_id) LOOP
      IF instr(v_attribute, r_bp_category_rec.description) = 0 OR
         instr(v_attribute, r_bp_category_rec.description) IS NULL THEN
        v_attribute := v_attribute || ',' || r_bp_category_rec.description;
      END IF;
    END LOOP;
    IF v_attribute IS NOT NULL THEN
      v_attribute := substr(v_attribute, 2);
    END IF;
    RETURN v_attribute;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
  END;

  --索合同行表
  PROCEDURE lock_con_contract_cashflow(p_cashflow_id      con_contract_cashflow.cashflow_id%TYPE,
                                       p_user_id          NUMBER,
                                       p_con_cashflow_rec OUT con_contract_cashflow%ROWTYPE) IS
  BEGIN
    SELECT *
      INTO p_con_cashflow_rec
      FROM con_contract_cashflow t
     WHERE t.cashflow_id = p_cashflow_id
       FOR UPDATE NOWAIT;
  EXCEPTION
    WHEN e_lock_error THEN
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CSH_WRITE_OFF_PKG.CON_LOCK_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'csh_payment_req_pkg',
                                                      p_procedure_function_name => 'lock_con_contract_cashflow');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  END;

  --锁csh_transaction
  PROCEDURE lock_csh_payment_req_hd(p_payment_req_id         IN NUMBER,
                                    p_user_id                IN NUMBER,
                                    p_csh_payment_req_hd_rec OUT csh_payment_req_hd%ROWTYPE) IS
  BEGIN
    SELECT *
      INTO p_csh_payment_req_hd_rec
      FROM csh_payment_req_hd t
     WHERE t.payment_req_id = p_payment_req_id
       FOR UPDATE NOWAIT;
  EXCEPTION
    WHEN e_lock_trx_error THEN
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CSH_TRANSACTION_PKG.CSH510_LOCK',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'csh_payment_req_pkg',
                                                      p_procedure_function_name => 'lock_csh_payment_req_hd');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  END;

  ---zlf
  ---锁合同
  PROCEDURE lock_con_contract_rec(p_contract_id      IN NUMBER,
                                  p_user_id          IN NUMBER,
                                  p_con_contract_rec OUT con_contract%ROWTYPE) IS
  BEGIN
    SELECT *
      INTO p_con_contract_rec
      FROM con_contract t
     WHERE t.contract_id = p_contract_id
       FOR UPDATE NOWAIT;
  EXCEPTION
    WHEN e_lock_trx_error THEN
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CSH_TRANSACTION_PKG.CSH510_LOCK',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'csh_payment_req_pkg',
                                                      p_procedure_function_name => 'lock_con_contract_rec');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  END;

  PROCEDURE lock_csh_payment_req_ln(p_payment_req_ln_id      IN NUMBER,
                                    p_user_id                IN NUMBER,
                                    p_csh_payment_req_ln_rec OUT csh_payment_req_ln%ROWTYPE) IS
  BEGIN
    SELECT *
      INTO p_csh_payment_req_ln_rec
      FROM csh_payment_req_ln t
     WHERE t.payment_req_ln_id = p_payment_req_ln_id
       FOR UPDATE NOWAIT;
  EXCEPTION
    WHEN e_lock_trx_error THEN
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CSH_TRANSACTION_PKG.CSH510_LOCK',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'csh_payment_req_pkg',
                                                      p_procedure_function_name => 'lock_csh_payment_req_ln');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  END;

  FUNCTION is_payment_close(v_csh_payment_req_hd_rec IN csh_payment_req_hd%ROWTYPE)
    RETURN BOOLEAN IS
  BEGIN
    IF v_csh_payment_req_hd_rec.closed_flag = 'Y' THEN
      RETURN FALSE;
    ELSE
      RETURN TRUE;
    END IF;
  END;

  FUNCTION is_payment_submitted(v_csh_payment_req_hd_rec IN csh_payment_req_hd%ROWTYPE)
    RETURN BOOLEAN IS
  BEGIN
    IF v_csh_payment_req_hd_rec.submitted_flag = 'Y' THEN
      RETURN FALSE;
    ELSE
      RETURN TRUE;
    END IF;
  END;

  FUNCTION is_payment_pay(v_csh_payment_req_hd_rec IN csh_payment_req_hd%ROWTYPE)
    RETURN BOOLEAN IS
  BEGIN
    IF v_csh_payment_req_hd_rec.approval_status <> 'APPROVED' THEN
      RETURN FALSE;
    ELSE
      RETURN TRUE;
    END IF;
  END;

  PROCEDURE check_csh_payment_bp_unique(p_payment_req_id NUMBER,
                                        p_user_id        NUMBER) IS
    v_bp_id NUMBER;
  BEGIN
    SELECT DISTINCT l.bp_id
      INTO v_bp_id
      FROM csh_payment_req_ln l
     WHERE l.payment_req_id = p_payment_req_id;
  EXCEPTION
    WHEN too_many_rows THEN
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '付款对象必须唯一',
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'csh_payment_req_pkg',
                                                     p_procedure_function_name => 'check_csh_payment_bp_unique');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  END;

  PROCEDURE check_csh_payment_bp(p_payment_req_id NUMBER, p_user_id NUMBER) IS
    v_bp_name VARCHAR2(200);
    e_ln_bp_error EXCEPTION;
  BEGIN
    SELECT hbm.bp_name
      INTO v_bp_name
      FROM csh_payment_req_ln l, hls_bp_master hbm
     WHERE l.payment_req_id = p_payment_req_id
       AND l.bp_id = hbm.bp_id
       AND l.ref_doc_category = 'CONTRACT'
       AND NOT EXISTS (SELECT 1
              FROM con_contract_bp ccb
             WHERE ccb.contract_id = l.ref_doc_id
               AND ccb.bp_id = l.bp_id
               AND ccb.enabled_flag = 'Y')
       AND rownum = 1;
    RAISE e_ln_bp_error;
  EXCEPTION
    WHEN no_data_found THEN
      NULL;
    WHEN e_ln_bp_error THEN
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '该付款对象在对应的合同中不存在--' ||
                                                                                  v_bp_name,
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'csh_payment_req_pkg',
                                                     p_procedure_function_name => 'check_csh_payment_bp');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  END;

  --保证金退还 付款申请 校验 add by Harry 9952 2017/2/24
  PROCEDURE check_csh_payment_bond(p_payment_req_id NUMBER,
                                   p_user_id        NUMBER) IS
    v_bp_name VARCHAR2(200);
    e_ln_bp_error EXCEPTION;
    v_write_off_flag VARCHAR2(30);
    v_count          NUMBER;
    v_cashflow_id    NUMBER;
    e_flag_or_status_error EXCEPTION;
  BEGIN
    SELECT l.ref_doc_line_id
      INTO v_cashflow_id
      FROM csh_payment_req_ln l
     WHERE l.payment_req_id = p_payment_req_id;
    SELECT c.write_off_flag
      INTO v_write_off_flag
      FROM con_contract_cashflow c
     WHERE c.cashflow_id = v_cashflow_id;
    SELECT COUNT(*)
      INTO v_count
      FROM csh_payment_req_hd h
     WHERE h.payment_req_id IN
           (SELECT l.payment_req_id
              FROM csh_payment_req_ln l
             WHERE l.ref_doc_line_id = v_cashflow_id)
       AND h.approval_status = 'APPROVING';
    IF v_write_off_flag = 'FULL' OR v_count <> 0 THEN
      RAISE e_flag_or_status_error;
    END IF;
    SELECT hbm.bp_name
      INTO v_bp_name
      FROM csh_payment_req_ln l, hls_bp_master hbm
     WHERE l.payment_req_id = p_payment_req_id
       AND l.bp_id = hbm.bp_id
       AND l.ref_doc_category = 'CONTRACT'
       AND NOT EXISTS (SELECT 1
              FROM con_contract_bp ccb
             WHERE ccb.contract_id = l.ref_doc_id
               AND ccb.bp_id = l.bp_id
               AND ccb.enabled_flag = 'Y')
       AND rownum = 1;
    RAISE e_ln_bp_error;
  EXCEPTION
    WHEN no_data_found THEN
      NULL;
    WHEN e_ln_bp_error THEN
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '该付款对象在对应的合同中不存在--' ||
                                                                                  v_bp_name,
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'csh_payment_req_pkg',
                                                     p_procedure_function_name => 'check_csh_payment_bond');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    WHEN e_flag_or_status_error THEN
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '该现金流已核销完成或已提交申请!',
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'csh_payment_req_pkg',
                                                     p_procedure_function_name => 'check_csh_payment_bond');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  END;

  --关闭申请头
  PROCEDURE close_csh_payment_req_hd(p_payment_req_id NUMBER,
                                     p_closed_date    DATE,
                                     p_closed_note    VARCHAR2,
                                     p_user_id        NUMBER) IS
    v_csh_payment_req_hd_rec csh_payment_req_hd%ROWTYPE;
    e_status_error EXCEPTION;
  BEGIN
    lock_csh_payment_req_hd(p_payment_req_id         => p_payment_req_id,
                            p_user_id                => p_user_id,
                            p_csh_payment_req_hd_rec => v_csh_payment_req_hd_rec);
    IF NOT is_payment_close(v_csh_payment_req_hd_rec) THEN
      RAISE e_status_error;
    END IF;
    UPDATE csh_payment_req_hd
       SET closed_flag      = 'Y',
           closed_note      = p_closed_note,
           closed_date      = SYSDATE,
           last_update_date = SYSDATE,
           last_updated_by  = p_user_id
     WHERE payment_req_id = p_payment_req_id;
  EXCEPTION
    WHEN e_status_error THEN
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => '',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'csh_payment_req_pkg',
                                                      p_procedure_function_name => 'close_csh_payment_req_hd');
      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            => 'csh_payment_req_pkg',
                                                     p_procedure_function_name => 'close_csh_payment_req_hd');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  END;

  FUNCTION get_payment_req_number(p_document_type     VARCHAR2,
                                  p_transaction_date  DATE,
                                  p_company_id        NUMBER,
                                  p_user_id           NUMBER,
                                  p_document_category VARCHAR2)
    RETURN VARCHAR2 IS
  BEGIN
    RETURN fnd_code_rule_pkg.get_rule_next_auto_num(p_document_category => p_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);
  END;

  PROCEDURE csh_payment_req_amount_check(p_cashflow_id NUMBER,
                                         p_amount      NUMBER,
                                         p_company_id  NUMBER,
                                         p_user_id     NUMBER) IS
    v_residual_amount NUMBER;
    e_req_amount_check      EXCEPTION;
    e_req_amount_zero_check EXCEPTION;
    v_amount_flag      VARCHAR2(1);
    v_req_amount       NUMBER;
    v_due_amount       NUMBER;
    v_amount           NUMBER;
    v_amount_paid      NUMBER;
    v_amount_approving NUMBER;
  BEGIN
    SELECT nvl(f.due_amount, 0)
      INTO v_due_amount
      FROM con_contract_cashflow f
     WHERE f.cashflow_id = p_cashflow_id;
    SELECT SUM(l.amount)
      INTO v_amount
      FROM csh_payment_req_ln l, csh_payment_req_hd h
     WHERE l.ref_doc_line_id = p_cashflow_id
       AND l.payment_req_id = h.payment_req_id
       AND h.submitted_flag = 'Y'
       AND h.approval_status = 'APPROVED'
       AND nvl(h.closed_flag, 'N') <> 'Y';
    SELECT SUM(nvl(l.amount_paid, 0))
      INTO v_amount_paid
      FROM csh_payment_req_ln l, csh_payment_req_hd h
     WHERE l.ref_doc_line_id = p_cashflow_id
       AND l.payment_req_id = h.payment_req_id
       AND h.submitted_flag = 'Y'
       AND h.approval_status = 'APPROVED'
       AND nvl(h.closed_flag, 'N') = 'Y';
    SELECT SUM(l.amount)
      INTO v_amount_approving
      FROM csh_payment_req_ln l, csh_payment_req_hd h
     WHERE l.ref_doc_line_id = p_cashflow_id
       AND l.payment_req_id = h.payment_req_id
       AND h.submitted_flag = 'Y'
       AND h.approval_status = 'APPROVING';
    IF p_amount > (v_due_amount - nvl(v_amount, 0) - nvl(v_amount_paid, 0) -
       nvl(v_amount_approving, 0)) THEN
      RAISE e_req_amount_check;
    ELSIF p_amount = 0 OR p_amount IS NULL THEN
      RAISE e_req_amount_zero_check;
    END IF;
  EXCEPTION
    WHEN e_req_amount_check THEN
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CSH_PAYMENT_REQ_PKG.CSH501_REQ_AMOUNT_CHECK',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'csh_payment_req_pkg',
                                                      p_procedure_function_name => 'csh_payment_req_amount_check');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    WHEN e_req_amount_zero_check THEN
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CSH_PAYMENT_REQ_PKG.CSH501_REQ_AMOUNT_ZERO_CHECK',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'csh_payment_req_pkg',
                                                      p_procedure_function_name => 'csh_payment_req_amount_check');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  END;

  PROCEDURE upd_csh_payment_after_reverse(p_transaction_id csh_transaction.transaction_id%TYPE,
                                          p_user_id        NUMBER) IS
    v_csh_transaction_rec    csh_transaction%ROWTYPE;
    v_csh_payment_req_ln_rec csh_payment_req_ln%ROWTYPE;
    v_amount_paid            NUMBER;
    e_bp_category_err exception;
  BEGIN
    csh_transaction_pkg.lock_csh_transaction(p_transaction_id      => p_transaction_id,
                                             p_user_id             => p_user_id,
                                             p_csh_transaction_rec => v_csh_transaction_rec);
    lock_csh_payment_req_ln(p_payment_req_ln_id      => v_csh_transaction_rec.source_doc_line_id,
                            p_user_id                => p_user_id,
                            p_csh_payment_req_ln_rec => v_csh_payment_req_ln_rec);
    --付款总金额字段减少
    v_amount_paid := nvl(v_csh_payment_req_ln_rec.amount_paid, 0) -
                     nvl(v_csh_transaction_rec.transaction_amount, 0);
   
    IF v_amount_paid - v_csh_payment_req_ln_rec.amount < 0 THEN
      UPDATE csh_payment_req_ln l
         SET l.amount_paid            = v_amount_paid,
             --UPDATE BY SHEN 
             --l.payment_status         = 'PORTION',
             l.payment_status         = 'PARTIAL',
             l.payment_completed_date = SYSDATE,
             l.last_update_date       = SYSDATE,
             l.last_updated_by        = p_user_id
       WHERE l.payment_req_ln_id = v_csh_transaction_rec.source_doc_line_id;
    ELSIF v_amount_paid = 0 THEN
      UPDATE csh_payment_req_ln l
         SET l.amount_paid            = v_amount_paid,
             l.payment_status         = 'NOT',
             l.payment_completed_date = SYSDATE,
             l.last_update_date       = SYSDATE,
             l.last_updated_by        = p_user_id
       WHERE l.payment_req_ln_id = v_csh_transaction_rec.source_doc_line_id;
    END IF;
    --根据bp_category 更新付款行的代理商已付金额和厂商已付金额
    --add by shen
    if v_csh_transaction_rec.bp_category='AGENT' THEN
        UPDATE csh_payment_req_ln cprl
          set cprl.amount_agent_paid=nvl(cprl.amount_agent_paid,0)-v_csh_transaction_rec.transaction_amount
          where cprl.payment_req_ln_id= v_csh_transaction_rec.source_doc_line_id;
    elsif  v_csh_transaction_rec.bp_category='VENDER' THEN
        UPDATE csh_payment_req_ln cprl
          set cprl.amount_vender_paid=nvl(cprl.amount_vender_paid,0)-v_csh_transaction_rec.transaction_amount
          where cprl.payment_req_ln_id= v_csh_transaction_rec.source_doc_line_id;
    --不是厂商和代理店则报错
    Else  raise e_bp_category_err;
      
    end if;
      
    UPDATE csh_payment_req_hd h
       SET h.closed_flag = 'N'
     WHERE h.payment_req_id = v_csh_transaction_rec.source_doc_id;
   --异常处理
   Exception
     WHEN e_bp_category_err THEN
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '付款反冲的对象必须为代理店或者厂商!',
                                                                                 
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'csh_payment_req_pkg',
                                                     p_procedure_function_name => 'upd_csh_payment_after_reverse');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  END;

  PROCEDURE insert_csh_trx_source(p_transaction_id      NUMBER,
                                  p_source_doc_category VARCHAR2,
                                  p_source_doc_type     VARCHAR2,
                                  p_source_doc_id       NUMBER,
                                  p_source_doc_line_id  NUMBER,
                                  p_source_amt          NUMBER,
                                  p_user_id             NUMBER) IS
  BEGIN
    INSERT INTO csh_transaction_source
      (transaction_source_id,
       transaction_id,
       source_doc_category,
       source_doc_type,
       source_doc_id,
       source_doc_line_id,
       source_amt,
       creation_date,
       created_by,
       last_update_date,
       last_updated_by)
    VALUES
      (csh_transaction_source_s.nextval,
       p_transaction_id,
       p_source_doc_category,
       p_source_doc_type,
       p_source_doc_id,
       p_source_doc_line_id,
       p_source_amt,
       SYSDATE,
       p_user_id,
       SYSDATE,
       p_user_id);
  END;

  PROCEDURE payment_csh_payment_debt(p_csh_payment_req_hd_rec  csh_payment_req_hd%ROWTYPE,
                                     p_csh_payment_req_ln_rec  csh_payment_req_ln%ROWTYPE,
                                     p_con_cashflow_rec        con_contract_cashflow%ROWTYPE,
                                     p_pay_date                DATE,
                                     p_pay_amount              NUMBER,
                                     p_company_id              NUMBER,
                                     p_internal_period_num     csh_transaction.internal_period_num%TYPE,
                                     p_period_name             csh_transaction.period_name%TYPE,
                                     p_exchange_rate_type      csh_transaction.exchange_rate_type%TYPE,
                                     p_exchange_rate_quotation csh_transaction.exchange_rate_quotation%TYPE,
                                     p_exchange_rate           csh_transaction.exchange_rate%TYPE,
                                     p_bank_account_id         csh_transaction.bank_account_id%TYPE,
                                     p_bank_account_num        VARCHAR2,
                                     p_description             csh_transaction.description%TYPE,
                                     p_user_id                 NUMBER,
                                     p_cashflow_id             NUMBER,
                                     p_bank_slip_num           csh_transaction.bank_slip_num%TYPE,
                                     p_bp_bank_account_id      csh_transaction.bp_bank_account_id%TYPE,
                                     p_bp_bank_account_num     csh_transaction.bp_bank_account_num%TYPE,
                                     p_handling_charge         csh_transaction.handling_charge%TYPE,
                                     p_payment_method_id       csh_transaction.payment_method_id%TYPE,
                                     p_merge_flag              VARCHAR2,
                                     p_payment_behalf_flag     VARCHAR2, --todo Spencer 3893 add 20160722 for 是否代付核销
                                     p_transaction_id          IN OUT NUMBER,
                                     p_bp_category             varchar2 default null,
                                     p_due_amount              NUMBER DEFAULT NULL) IS
    e_trx_num_error    EXCEPTION;
    e_req_amount_check EXCEPTION;
    v_csh_transaction_rec  csh_transaction%ROWTYPE;
    v_transaction_id       NUMBER;
    v_write_off_id         NUMBER;
    v_count_status         NUMBER;
    v_bp_category          VARCHAR2(100);
    v_amount               NUMBER;
    v_amount_paid          NUMBER;
    v_deduction_flag       VARCHAR2(1) := 'N';
    v_deduction_amount     NUMBER := 0;
    v_session_id           NUMBER;
    v_bp_bank_account_name csh_transaction.bp_bank_account_name%TYPE;
    v_ddct_transaction_id  NUMBER;
  BEGIN
    BEGIN
      SELECT ba.bank_account_name
        INTO v_bp_bank_account_name
        FROM hls_bp_master_bank_account ba
       WHERE ba.bank_account_id = p_bp_bank_account_id;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
    /* 不修改参数,使用不重复的id,代替session_id */
    v_session_id := -1 * p_csh_payment_req_ln_rec.payment_req_ln_id;
    BEGIN
      SELECT 'Y'
        INTO v_deduction_flag
        FROM dual
       WHERE EXISTS (SELECT 1
                FROM csh_payment_req_ln_ddct a
               WHERE a.payment_req_ln_id =
                     p_csh_payment_req_ln_rec.payment_req_ln_id
                 AND nvl(a.deduction_flag, 'N') = 'N');
    EXCEPTION
      WHEN no_data_found THEN
        v_deduction_flag := 'N';
    END;
    FOR c_deduction_rec IN (SELECT *
                              FROM csh_payment_req_ln_ddct a
                             WHERE a.payment_req_ln_id =
                                   p_csh_payment_req_ln_rec.payment_req_ln_id
                               AND nvl(a.deduction_flag, 'N') = 'N') LOOP
      --insert into csh_write_off_temp
      IF c_deduction_rec.amount > 0 THEN
        INSERT INTO csh_write_off_temp
          (session_id,
           write_off_type,
           transaction_category,
           transaction_type,
           write_off_date,
           write_off_due_amount,
           write_off_principal,
           write_off_interest,
           write_off_due_amount_cny,
           write_off_principal_cny,
           write_off_interest_cny,
           exchange_rate,
           company_id,
           document_category,
           document_id,
           document_line_id,
           opposite_doc_category,
           opposite_doc_type,
           opposite_doc_id,
           opposite_doc_line_id,
           opposite_doc_detail_id,
           opposite_write_off_amount,
           description,
           creation_date,
           created_by,
           last_update_date,
           last_updated_by)
        VALUES
          (v_session_id,
           csh_write_off_pkg.write_off_type_receipt_credit,
           'CSH_TRX',
           csh_transaction_pkg.csh_trx_type_deduction,
           p_pay_date,
           c_deduction_rec.amount,
           c_deduction_rec.principal,
           c_deduction_rec.interest,
           NULL,
           NULL,
           NULL,
           p_exchange_rate,
           p_csh_payment_req_hd_rec.company_id,
           'CONTRACT',
           c_deduction_rec.ref_doc_id,
           c_deduction_rec.ref_doc_line_id,
           'PAYMENT_REQ_DDCT', --opposite_doc_category ,
           'PAYMENT_REQ_DDCT', --opposite_doc_type ,
           p_csh_payment_req_ln_rec.payment_req_id, --opposite_doc_id ,
           p_csh_payment_req_ln_rec.payment_req_ln_id, --opposite_doc_line_id ,
           c_deduction_rec.payment_req_ln_ddct_id, --opposite_doc_detail_id ,
           NULL, --opposite_write_off_amount ,
           NULL,
           SYSDATE,
           p_user_id,
           SYSDATE,
           p_user_id);
      END IF;
      v_deduction_amount := v_deduction_amount +
                            nvl(c_deduction_rec.amount, 0);
      UPDATE csh_payment_req_ln_ddct
         SET deduction_flag = 'Y'
       WHERE payment_req_ln_ddct_id =
             c_deduction_rec.payment_req_ln_ddct_id;
    END LOOP;
    IF v_deduction_flag = 'Y' AND v_deduction_amount > 0 THEN
      INSERT INTO csh_write_off_temp
        (session_id,
         write_off_type,
         transaction_category,
         transaction_type,
         write_off_date,
         write_off_due_amount,
         write_off_principal,
         write_off_interest,
         write_off_due_amount_cny,
         write_off_principal_cny,
         write_off_interest_cny,
         exchange_rate,
         company_id,
         document_category,
         document_id,
         document_line_id,
         description,
         creation_date,
         created_by,
         last_update_date,
         last_updated_by)
      VALUES
        (v_session_id,
         csh_write_off_pkg.write_off_type_payment_debt,
         'CSH_TRX',
         csh_transaction_pkg.csh_trx_type_deduction,
         p_pay_date,
         v_deduction_amount,
         NULL,
         NULL,
         NULL,
         NULL,
         NULL,
         p_exchange_rate,
         p_csh_payment_req_hd_rec.company_id,
         'CONTRACT',
         p_csh_payment_req_ln_rec.ref_doc_id,
         p_csh_payment_req_ln_rec.ref_doc_line_id,
         NULL,
         SYSDATE,
         p_user_id,
         SYSDATE,
         p_user_id);
      csh_write_off_pkg.deduction_write_off(p_session_id          => v_session_id,
                                            p_company_id          => p_csh_payment_req_hd_rec.company_id,
                                            p_bp_id               => p_csh_payment_req_ln_rec.bp_id,
                                            p_transaction_date    => p_pay_date,
                                            p_description         => '',
                                            p_source_csh_trx_type => '',
                                            p_source_csh_trx_id   => '',
                                            p_source_doc_category => p_csh_payment_req_hd_rec.document_category,
                                            p_source_doc_type     => p_csh_payment_req_hd_rec.document_type,
                                            p_source_doc_id       => p_csh_payment_req_hd_rec.payment_req_id,
                                            p_source_doc_line_id  => p_csh_payment_req_ln_rec.payment_req_ln_id,
                                            p_user_id             => p_user_id,
                                            p_transaction_id      => v_ddct_transaction_id);
      UPDATE csh_payment_req_ln t
         SET amount_paid = nvl(amount_paid, 0) + v_deduction_amount
       WHERE t.payment_req_ln_id =
             p_csh_payment_req_ln_rec.payment_req_ln_id;
    END IF;
    --预付款抵扣
    FOR c_prepay IN (SELECT p.prepayment_trx_id,
                            p.write_off_amt,
                            p.payment_req_ln_prepay_id
                       FROM csh_payment_req_ln_prepay p
                      WHERE p.payment_req_ln_id =
                            p_csh_payment_req_ln_rec.payment_req_ln_id
                        AND nvl(p.write_off_flag, 'N') = 'N') LOOP
      csh_write_off_pkg.insert_csh_write_off(p_write_off_id                => v_write_off_id,
                                             p_write_off_type              => csh_write_off_pkg.write_off_type_pre_debt,
                                             p_write_off_date              => p_pay_date,
                                             p_internal_period_num         => p_internal_period_num,
                                             p_period_name                 => p_period_name,
                                             p_csh_transaction_id          => c_prepay.prepayment_trx_id,
                                             p_csh_write_off_amount        => c_prepay.write_off_amt,
                                             p_subsequent_csh_trx_id       => '',
                                             p_subseq_csh_write_off_amount => '',
                                             p_reversed_flag               => 'N',
                                             p_reversed_write_off_id       => '',
                                             p_reversed_date               => '',
                                             p_cashflow_id                 => p_con_cashflow_rec.cashflow_id,
                                             p_contract_id                 => p_con_cashflow_rec.contract_id,
                                             p_times                       => p_con_cashflow_rec.times,
                                             p_cf_item                     => p_con_cashflow_rec.cf_item,
                                             p_cf_type                     => p_con_cashflow_rec.cf_type,
                                             p_penalty_calc_date           => '',
                                             p_write_off_due_amount        => c_prepay.write_off_amt,
                                             p_write_off_principal         => '',
                                             p_write_off_interest          => '',
                                             p_opposite_doc_category       => '',
                                             p_opposite_doc_type           => '',
                                             p_opposite_doc_id             => '',
                                             p_opposite_doc_line_id        => '',
                                             p_opposite_doc_detail_id      => '',
                                             p_opposite_write_off_amount   => '',
                                             p_create_je_mothed            => '',
                                             p_create_je_flag              => 'N',
                                             p_gld_interface_flag          => 'N',
                                             p_description                 => p_description,
                                             p_user_id                     => p_user_id);
      csh_write_off_pkg.execute_write_off(p_write_off_id => v_write_off_id,
                                          p_user_id      => p_user_id);
      UPDATE csh_payment_req_ln_prepay p
         SET p.write_off_flag = 'Y'
       WHERE p.payment_req_ln_prepay_id = c_prepay.payment_req_ln_prepay_id;
      UPDATE csh_payment_req_ln t
         SET amount_paid = nvl(amount_paid, 0) + c_prepay.write_off_amt
       WHERE t.payment_req_ln_id =
             p_csh_payment_req_ln_rec.payment_req_ln_id;
    END LOOP;
    IF p_pay_amount > 0 THEN
      --付款核销  delete by shen bp_category 由前面付款的对象决定
      /*SELECT m.bp_category
        INTO v_bp_category
        FROM hls_bp_master m
       WHERE m.bp_id = p_csh_payment_req_ln_rec.bp_id;*/
       
       v_bp_category := p_bp_category;
      IF p_merge_flag = 'Y' THEN
        IF p_transaction_id IS NULL THEN
          --第一条
          csh_transaction_pkg.insert_csh_transaction(p_transaction_id          => v_transaction_id,
                                                     p_transaction_num         => '',
                                                     p_transaction_category    => csh_transaction_pkg.csh_trx_category_business,
                                                     p_transaction_type        => csh_transaction_pkg.csh_trx_type_payment,
                                                     p_transaction_date        => p_pay_date,
                                                     p_penalty_calc_date       => '',
                                                     p_bank_slip_num           => p_bank_slip_num,
                                                     p_company_id              => p_company_id,
                                                     p_internal_period_num     => p_internal_period_num,
                                                     p_period_name             => p_period_name,
                                                     p_payment_method_id       => p_payment_method_id,
                                                     p_distribution_set_id     => '',
                                                     p_cashflow_amount         => -1 *
                                                                                  p_pay_amount,
                                                     p_currency_code           => p_csh_payment_req_ln_rec.currency_code,
                                                     p_transaction_amount      => p_pay_amount,
                                                     p_exchange_rate_type      => p_exchange_rate_type,
                                                     p_exchange_rate_quotation => 'DIRECT QUOTATION',
                                                     p_exchange_rate           => p_exchange_rate,
                                                     p_bank_account_id         => p_bank_account_id,
                                                     p_bp_category             => v_bp_category,
                                                     p_bp_id                   => p_csh_payment_req_ln_rec.bp_id,
                                                     p_bp_bank_account_id      => nvl(p_bp_bank_account_id,
                                                                                      p_csh_payment_req_ln_rec.bp_bank_account_id),
                                                     p_bp_bank_account_num     => nvl(p_bp_bank_account_num,
                                                                                      p_csh_payment_req_ln_rec.bp_bank_account_num),
                                                     p_description             => p_description,
                                                     p_handling_charge         => p_handling_charge,
                                                     --不需要凭证,直接将过账标志置为Y
                                                     p_posted_flag             => 'Y',
                                                     p_reversed_flag           => 'N',
                                                     p_reversed_date           => '',
                                                     p_returned_flag           => 'NOT',
                                                     p_returned_amount         => '',
                                                     p_write_off_flag          => 'FULL',
                                                     p_write_off_amount        => '',
                                                     p_full_write_off_date     => SYSDATE,
                                                     p_twin_csh_trx_id         => '',
                                                     p_return_from_csh_trx_id  => '',
                                                     p_reversed_csh_trx_id     => '',
                                                     p_source_csh_trx_type     => '',
                                                     p_source_csh_trx_id       => '',
                                                     p_source_doc_category     => p_csh_payment_req_hd_rec.document_category,
                                                     p_source_doc_type         => p_csh_payment_req_hd_rec.document_type,
                                                     p_source_doc_id           => p_csh_payment_req_hd_rec.payment_req_id,
                                                     p_source_doc_line_id      => p_csh_payment_req_ln_rec.payment_req_ln_id,
                                                     p_create_je_mothed        => '',
                                                     p_create_je_flag          => 'N',
                                                     p_gld_interface_flag      => 'N',
                                                     p_user_id                 => p_user_id,
                                                     p_csh_bp_name             => '',
                                                     p_paid_byother_flag       => NULL,
                                                     p_ref_v05                 => '付款核销');
          p_transaction_id := v_transaction_id;
        ELSE
          v_transaction_id := p_transaction_id;
          UPDATE csh_transaction t
             SET t.cashflow_amount    = t.cashflow_amount +
                                        (-1) * p_pay_amount,
                 t.transaction_amount = t.transaction_amount + p_pay_amount
           WHERE t.transaction_id = v_transaction_id;
        END IF;
        csh_write_off_pkg.insert_csh_write_off(p_write_off_id                => v_write_off_id,
                                               p_write_off_type              => csh_write_off_pkg.write_off_type_payment_debt,
                                               p_write_off_date              => p_pay_date,
                                               p_internal_period_num         => p_internal_period_num,
                                               p_period_name                 => p_period_name,
                                               p_csh_transaction_id          => v_transaction_id,
                                               p_csh_write_off_amount        => p_pay_amount,
                                               p_subsequent_csh_trx_id       => '',
                                               p_subseq_csh_write_off_amount => '',
                                               p_reversed_flag               => 'N',
                                               p_reversed_write_off_id       => '',
                                               p_reversed_date               => '',
                                               p_cashflow_id                 => p_con_cashflow_rec.cashflow_id,
                                               p_contract_id                 => p_con_cashflow_rec.contract_id,
                                               p_times                       => p_con_cashflow_rec.times,
                                               p_cf_item                     => p_con_cashflow_rec.cf_item,
                                               p_cf_type                     => p_con_cashflow_rec.cf_type,
                                               p_penalty_calc_date           => '',
                                               p_write_off_due_amount        => p_pay_amount,
                                               p_write_off_principal         => '',
                                               p_write_off_interest          => '',
                                               p_opposite_doc_category       => '',
                                               p_opposite_doc_type           => '',
                                               p_opposite_doc_id             => '',
                                               p_opposite_doc_line_id        => '',
                                               p_opposite_doc_detail_id      => '',
                                               p_opposite_write_off_amount   => '',
                                               p_create_je_mothed            => '',
                                               p_create_je_flag              => 'N',
                                               p_gld_interface_flag          => 'N',
                                               p_description                 => p_description,
                                               p_user_id                     => p_user_id,
                                               p_due_amount                  => p_due_amount);
        csh_write_off_pkg.execute_write_off(p_write_off_id => v_write_off_id,
                                            p_user_id      => p_user_id);
      ELSE
        -- 不合并,逐行处理
        csh_transaction_pkg.insert_csh_transaction(p_transaction_id          => v_transaction_id,
                                                   p_transaction_num         => '',
                                                   p_transaction_category    => csh_transaction_pkg.csh_trx_category_business,
                                                   p_transaction_type        => csh_transaction_pkg.csh_trx_type_payment,
                                                   p_transaction_date        => p_pay_date,
                                                   p_penalty_calc_date       => '',
                                                   p_bank_slip_num           => p_bank_slip_num,
                                                   p_company_id              => p_company_id,
                                                   p_internal_period_num     => p_internal_period_num,
                                                   p_period_name             => p_period_name,
                                                   p_payment_method_id       => p_payment_method_id,
                                                   p_distribution_set_id     => '',
                                                   p_cashflow_amount         => -1 *
                                                                                p_pay_amount,
                                                   p_currency_code           => p_csh_payment_req_ln_rec.currency_code,
                                                   p_transaction_amount      => p_pay_amount,
                                                   p_exchange_rate_type      => p_exchange_rate_type,
                                                   p_exchange_rate_quotation => 'DIRECT QUOTATION',
                                                   p_exchange_rate           => p_exchange_rate,
                                                   p_bank_account_id         => p_bank_account_id,
                                                   p_bp_category             => v_bp_category,
                                                   p_bp_id                   => p_csh_payment_req_ln_rec.bp_id,
                                                   p_bp_bank_account_id      => nvl(p_bp_bank_account_id,
                                                                                    p_csh_payment_req_ln_rec.bp_bank_account_id),
                                                   p_bp_bank_account_num     => nvl(p_bp_bank_account_num,
                                                                                    p_csh_payment_req_ln_rec.bp_bank_account_num),
                                                   p_description             => p_description,
                                                   p_handling_charge         => p_handling_charge,
                                                   p_posted_flag             => 'Y',
                                                   p_reversed_flag           => 'N',
                                                   p_reversed_date           => '',
                                                   p_returned_flag           => 'NOT',
                                                   p_returned_amount         => '',
                                                   p_write_off_flag          => 'FULL',
                                                   p_write_off_amount        => '',
                                                   p_full_write_off_date     => SYSDATE,
                                                   p_twin_csh_trx_id         => '',
                                                   p_return_from_csh_trx_id  => '',
                                                   p_reversed_csh_trx_id     => '',
                                                   p_source_csh_trx_type     => '',
                                                   p_source_csh_trx_id       => '',
                                                   p_source_doc_category     => p_csh_payment_req_hd_rec.document_category,
                                                   p_source_doc_type         => p_csh_payment_req_hd_rec.document_type,
                                                   p_source_doc_id           => p_csh_payment_req_hd_rec.payment_req_id,
                                                   p_source_doc_line_id      => p_csh_payment_req_ln_rec.payment_req_ln_id,
                                                   p_create_je_mothed        => '',
                                                   p_create_je_flag          => 'N',
                                                   p_gld_interface_flag      => 'N',
                                                   p_user_id                 => p_user_id,
                                                   p_csh_bp_name             => '',
                                                   p_paid_byother_flag       => NULL,
                                                   p_ref_v05                 => '付款核销');
        csh_write_off_pkg.insert_csh_write_off(p_write_off_id                => v_write_off_id,
                                               p_write_off_type              => csh_write_off_pkg.write_off_type_payment_debt,
                                               p_write_off_date              => p_pay_date,
                                               p_internal_period_num         => p_internal_period_num,
                                               p_period_name                 => p_period_name,
                                               p_csh_transaction_id          => v_transaction_id,
                                               p_csh_write_off_amount        => p_pay_amount,
                                               p_subsequent_csh_trx_id       => '',
                                               p_subseq_csh_write_off_amount => '',
                                               p_reversed_flag               => 'N',
                                               p_reversed_write_off_id       => '',
                                               p_reversed_date               => '',
                                               p_cashflow_id                 => p_con_cashflow_rec.cashflow_id,
                                               p_contract_id                 => p_con_cashflow_rec.contract_id,
                                               p_times                       => p_con_cashflow_rec.times,
                                               p_cf_item                     => p_con_cashflow_rec.cf_item,
                                               p_cf_type                     => p_con_cashflow_rec.cf_type,
                                               p_penalty_calc_date           => '',
                                               p_write_off_due_amount        => p_pay_amount,
                                               p_write_off_principal         => '',
                                               p_write_off_interest          => '',
                                               p_opposite_doc_category       => '',
                                               p_opposite_doc_type           => '',
                                               p_opposite_doc_id             => '',
                                               p_opposite_doc_line_id        => '',
                                               p_opposite_doc_detail_id      => '',
                                               p_opposite_write_off_amount   => '',
                                               p_create_je_mothed            => '',
                                               p_create_je_flag              => 'N',
                                               p_gld_interface_flag          => 'N',
                                               p_description                 => p_description,
                                               p_user_id                     => p_user_id,
                                               p_due_amount                  => p_due_amount);
        csh_write_off_pkg.execute_write_off(p_write_off_id => v_write_off_id,
                                            p_user_id      => p_user_id);
        csh_transaction_pkg.post_csh_transaction(p_transaction_id => v_transaction_id,
                                                 p_user_id        => p_user_id);
        /* --现金事务核销时,现金事务及核销行 合并生成的凭证
        csh_transaction_je_pkg.create_trx_write_off_je(p_transaction_id => v_transaction_id,
                                                       p_user_id        => p_user_id);*/
      END IF;
      p_transaction_id := v_transaction_id;
      insert_csh_trx_source(p_transaction_id      => p_transaction_id,
                            p_source_doc_category => p_csh_payment_req_hd_rec.document_category,
                            p_source_doc_type     => p_csh_payment_req_hd_rec.document_type,
                            p_source_doc_id       => p_csh_payment_req_hd_rec.payment_req_id,
                            p_source_doc_line_id  => p_csh_payment_req_ln_rec.payment_req_ln_id,
                            p_source_amt          => p_pay_amount,
                            p_user_id             => p_user_id);
    END IF;
  END;

  PROCEDURE payment_csh_payment(p_payment_req_id          NUMBER,
                                p_payment_req_ln_id       NUMBER,
                                p_pay_date                DATE,
                                p_pay_amount              NUMBER,
                                p_company_id              NUMBER,
                                p_internal_period_num     csh_transaction.internal_period_num%TYPE,
                                p_period_name             csh_transaction.period_name%TYPE,
                                p_exchange_rate_type      csh_transaction.exchange_rate_type%TYPE,
                                p_exchange_rate_quotation csh_transaction.exchange_rate_quotation%TYPE,
                                p_exchange_rate           csh_transaction.exchange_rate%TYPE,
                                p_bank_account_id         csh_transaction.bank_account_id%TYPE,
                                p_bank_account_num        VARCHAR2,
                                p_description             csh_transaction.description%TYPE,
                                p_user_id                 NUMBER,
                                p_cashflow_id             NUMBER,
                                p_bank_slip_num           csh_transaction.bank_slip_num%TYPE,
                                p_bp_bank_account_id      csh_transaction.bp_bank_account_id%TYPE,
                                p_bp_bank_account_num     csh_transaction.bp_bank_account_num%TYPE,
                                p_handling_charge         csh_transaction.handling_charge%TYPE,
                                p_payment_method_id       csh_transaction.payment_method_id%TYPE,
                                p_merge_flag              VARCHAR2 DEFAULT 'N',
                                p_payment_behalf_flag     VARCHAR2 DEFAULT 'N',
                                p_payment_status          OUT VARCHAR2,
                                p_transaction_id          IN OUT NUMBER,
                                p_bp_category             varchar2 default null,
                                p_due_amount              NUMBER DEFAULT NULL) IS
    v_amount                 NUMBER;
    v_amount_paid            NUMBER;
    v_amt_tmp                NUMBER;
    v_csh_payment_req_hd_rec csh_payment_req_hd%ROWTYPE;
    v_csh_payment_req_ln_rec csh_payment_req_ln%ROWTYPE;
    e_trx_num_error    EXCEPTION;
    e_req_amount_check EXCEPTION;
    v_count_status     NUMBER;
    v_con_cashflow_rec con_contract_cashflow%ROWTYPE;
    v_deduction_flag   VARCHAR2(1) := 'N';
    v_deduction_amount NUMBER := 0;
    v_session_id       NUMBER;
    e_status_error EXCEPTION;
  BEGIN
    lock_csh_payment_req_hd(p_payment_req_id         => p_payment_req_id,
                            p_user_id                => p_user_id,
                            p_csh_payment_req_hd_rec => v_csh_payment_req_hd_rec);
    lock_csh_payment_req_ln(p_payment_req_ln_id      => p_payment_req_ln_id,
                            p_user_id                => p_user_id,
                            p_csh_payment_req_ln_rec => v_csh_payment_req_ln_rec);
    IF v_csh_payment_req_ln_rec.payment_req_ln_type = 'DEBT' THEN
      lock_con_contract_cashflow(p_cashflow_id      => v_csh_payment_req_ln_rec.ref_doc_line_id,
                                 p_user_id          => p_user_id,
                                 p_con_cashflow_rec => v_con_cashflow_rec);
    END IF;
    --判断是否是审批通过的单据
    IF NOT is_payment_pay(v_csh_payment_req_hd_rec) THEN
      RAISE e_status_error;
    END IF;
    --本次申请金额字段,分开为厂商和代理店
    IF p_pay_amount < 0 THEN
      RAISE e_req_amount_check;
    END IF;
    SELECT nvl((SELECT SUM(d.amount)
                 FROM csh_payment_req_ln_ddct d, con_contract_cashflow cf
                WHERE d.payment_req_ln_id = p_payment_req_ln_id
                  AND nvl(d.deduction_flag, 'N') = 'N'
                  AND nvl(cf.payment_deduction_flag, 'N') = 'Y'
                  AND cf.write_off_flag != 'FULL'
                  AND d.ref_doc_category = 'CONTRACT'
                  AND d.ref_doc_id = cf.contract_id
                  AND d.ref_doc_line_id = cf.cashflow_id),
                  --预支付,付款这里没有用到
               0) + nvl((SELECT SUM(p.write_off_amt)
                          FROM csh_payment_req_ln_prepay p
                         WHERE p.payment_req_ln_id = p_payment_req_ln_id
                           AND nvl(p.write_off_flag, 'N') = 'N'),
                        0)
      INTO v_amt_tmp
      FROM dual;
      --已付款加上本次付款加上抵扣金额 和申请金额进行比较
    IF v_amt_tmp + p_pay_amount +
       nvl(v_csh_payment_req_ln_rec.amount_paid, 0) >
       v_csh_payment_req_ln_rec.amount THEN
      RAISE e_req_amount_check;
    END IF;
    IF v_csh_payment_req_ln_rec.payment_req_ln_type = 'DEBT' THEN
      payment_csh_payment_debt(p_csh_payment_req_hd_rec  => v_csh_payment_req_hd_rec,
                               p_csh_payment_req_ln_rec  => v_csh_payment_req_ln_rec,
                               p_con_cashflow_rec        => v_con_cashflow_rec,
                               p_pay_date                => p_pay_date,
                               p_pay_amount              => p_pay_amount,
                               p_company_id              => p_company_id,
                               p_internal_period_num     => p_internal_period_num,
                               p_period_name             => p_period_name,
                               p_exchange_rate_type      => p_exchange_rate_type,
                               p_exchange_rate_quotation => p_exchange_rate_quotation,
                               p_exchange_rate           => p_exchange_rate,
                               p_bank_account_id         => p_bank_account_id,
                               p_bank_account_num        => p_bank_account_num,
                               p_description             => p_description,
                               p_user_id                 => p_user_id,
                               p_cashflow_id             => p_cashflow_id,
                               p_bank_slip_num           => p_bank_slip_num,
                               p_bp_bank_account_id      => p_bp_bank_account_id,
                               p_bp_bank_account_num     => p_bp_bank_account_num,
                               p_handling_charge         => p_handling_charge,
                               p_payment_method_id       => p_payment_method_id,
                               p_merge_flag              => p_merge_flag,
                               p_payment_behalf_flag     => p_payment_behalf_flag,
                               p_transaction_id          => p_transaction_id,
                               -- by shen
                               p_bp_category             => p_bp_category,
                               p_due_amount              => p_due_amount);
    ELSE
      RETURN;
    END IF;
    SELECT nvl(amount_paid, 0), nvl(amount, 0)
      INTO v_amount_paid, v_amount
      FROM csh_payment_req_ln
     WHERE payment_req_ln_id = p_payment_req_ln_id;
    IF p_pay_amount + v_amount_paid = v_amount THEN
      UPDATE csh_payment_req_ln l
         SET l.payment_status         = 'FULL',
             l.amount_paid            = nvl(l.amount_paid, 0) +
                                        nvl(p_pay_amount, 0),
             l.payment_completed_date = SYSDATE,
             l.last_update_date       = SYSDATE,
             l.last_updated_by        = p_user_id
       WHERE l.payment_req_ln_id = p_payment_req_ln_id;
      p_payment_status := 'FULL';
    ELSIF p_pay_amount + v_amount_paid > v_amount THEN
      RAISE e_req_amount_check;
    ELSE
      UPDATE csh_payment_req_ln l
         SET l.payment_status         = 'PARTIAL',
             l.amount_paid            = nvl(l.amount_paid, 0) +
                                        nvl(p_pay_amount, 0),
             l.payment_completed_date = NULL,
             l.last_update_date       = SYSDATE,
             l.last_updated_by        = p_user_id
       WHERE l.payment_req_ln_id = p_payment_req_ln_id;
      p_payment_status := 'PARTIAL';
    END IF;
    SELECT COUNT(*)
      INTO v_count_status
      FROM csh_payment_req_ln l
     WHERE l.payment_req_id = p_payment_req_id
       AND l.payment_status <> 'FULL';
    --关闭申请头状态
    IF v_count_status = 0 THEN
      UPDATE csh_payment_req_hd
         SET closed_flag      = 'Y',
             closed_date      = SYSDATE,
             last_update_date = SYSDATE,
             last_updated_by  = p_user_id
       WHERE payment_req_id = p_payment_req_id;
    END IF;
  EXCEPTION
    WHEN e_status_error THEN
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CSH_PAYMENT_REQ_PKG.PAYMENT_CSH_PAYMENT_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'csh_payment_req_pkg',
                                                      p_procedure_function_name => 'payment_csh_payment');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    WHEN e_req_amount_check THEN
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CSH_PAYMENT_REQ_PKG.CSH501_PAYMENT_AMOUNT_CHECK',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'csh_payment_req_pkg',
                                                      p_procedure_function_name => 'payment_csh_payment');
      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            => 'csh_payment_req_pkg',
                                                     p_procedure_function_name => 'payment_csh_payment');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  END;

  PROCEDURE payment_csh_payment(p_payment_req_id          NUMBER,
                                p_session_id              NUMBER,
                                p_pay_date                DATE,
                                p_pay_amount              NUMBER,
                                p_company_id              NUMBER,
                                p_internal_period_num     csh_transaction.internal_period_num%TYPE,
                                p_period_name             csh_transaction.period_name%TYPE,
                                p_exchange_rate_type      csh_transaction.exchange_rate_type%TYPE,
                                p_exchange_rate_quotation csh_transaction.exchange_rate_quotation%TYPE,
                                p_exchange_rate           csh_transaction.exchange_rate%TYPE,
                                p_bank_account_id         csh_transaction.bank_account_id%TYPE,
                                p_bank_account_num        VARCHAR2,
                                p_description             csh_transaction.description%TYPE,
                                p_user_id                 NUMBER,
                                p_cashflow_id             NUMBER,
                                p_bank_slip_num           csh_transaction.bank_slip_num%TYPE,
                                p_bp_bank_account_id      csh_transaction.bp_bank_account_id%TYPE,
                                p_bp_bank_account_num     csh_transaction.bp_bank_account_num%TYPE,
                                p_handling_charge         csh_transaction.handling_charge%TYPE,
                                p_payment_method_id       csh_transaction.payment_method_id%TYPE,
                                p_payment_status          OUT VARCHAR2,
                                p_merge_flag              VARCHAR2 DEFAULT 'N',
                                p_payment_behalf_flag     VARCHAR2 DEFAULT 'N',
                                p_this_pay_agent_amount   number,
                                p_this_pay_vender_amount  number,
                                p_bank_flag               VARCHAR2 DEFAULT 'N',
                                p_bp_category             varchar2 default null,
                                p_due_amount              NUMBER DEFAULT NULL) IS
    v_payment_status VARCHAR2(30);
    v_transaction_id NUMBER;
    e_pay_check_error EXCEPTION;
    v_bank_status VARCHAR2(30);
    v_already_agent_paid number;
    v_already_vender_paid number;
  BEGIN
    /*for c_req_group in (select l.payment_req_ln_type,sum(t.amt) as pay_amt
                          from csh_payment_req_ln_tmp t,
                               csh_payment_req_ln l
                         where t.session_id = p_sessioN_id
                           and t.type = 'PAYMENT_REQ_PAYMENT'
                           and l.payment_req_ln_id = t.id
                           and l.payment_req_id = p_payment_req_id
                          group by l.payment_req_ln_type)
    loop
      if c_req_group.payment_req_ln_type = 'PREPAYMENT' then
        null;
      elsif c_req_group.payment_req_ln_type = 'DEBT' then
        null;
      end if;
    end loop; */
    IF p_bank_flag = 'N' THEN
      --如果不是银行付款,需要检测是否已经发起银行交易
      SELECT h.nc_pay_state
        INTO v_bank_status
        FROM csh_payment_req_hd h
       WHERE h.payment_req_id = p_payment_req_id;
      IF v_bank_status IS NOT NULL THEN
        RAISE e_pay_check_error;
      END IF;
    END IF;
    FOR c_req_group IN (SELECT l.payment_req_ln_type,
                               l.bp_id,
                               l.currency_code
                          FROM csh_payment_req_ln_tmp t,
                               csh_payment_req_ln     l
                         WHERE t.session_id = p_session_id
                           AND t.type = 'PAYMENT_REQ_PAYMENT'
                           AND t.id = l.payment_req_ln_id
                         GROUP BY l.payment_req_ln_type,
                                  l.bp_id,
                                  l.currency_code) LOOP
      v_transaction_id := NULL;
      FOR c_req IN (SELECT t.id, t.amt
                      FROM csh_payment_req_ln_tmp t, csh_payment_req_ln l
                     WHERE t.session_id = p_session_id
                       AND t.type = 'PAYMENT_REQ_PAYMENT'
                       AND t.id = l.payment_req_ln_id
                       AND l.payment_req_ln_type =
                           c_req_group.payment_req_ln_type
                       AND l.bp_id = c_req_group.bp_id
                       AND l.currency_code = c_req_group.currency_code) LOOP
        payment_csh_payment(p_payment_req_id          => p_payment_req_id,
                            p_payment_req_ln_id       => c_req.id,
                            p_pay_date                => p_pay_date,
                            p_pay_amount              => c_req.amt,
                            p_company_id              => p_company_id,
                            p_internal_period_num     => p_internal_period_num,
                            p_period_name             => p_period_name,
                            p_exchange_rate_type      => p_exchange_rate_type,
                            p_exchange_rate_quotation => p_exchange_rate_quotation,
                            p_exchange_rate           => p_exchange_rate,
                            p_bank_account_id         => p_bank_account_id,
                            p_bank_account_num        => p_bank_account_num,
                            p_description             => p_description,
                            p_user_id                 => p_user_id,
                            p_cashflow_id             => p_cashflow_id,
                            p_bank_slip_num           => p_bank_slip_num,
                            p_bp_bank_account_id      => p_bp_bank_account_id,
                            p_bp_bank_account_num     => p_bp_bank_account_num,
                            p_handling_charge         => p_handling_charge,
                            p_payment_method_id       => p_payment_method_id,
                            p_payment_status          => v_payment_status,
                            p_merge_flag              => p_merge_flag,
                            p_payment_behalf_flag     => p_payment_behalf_flag,
                            p_transaction_id          => v_transaction_id,
                            --add by shen 
                            p_bp_category             => p_bp_category,
                            p_due_amount              => p_due_amount);
       --查询已付款金额
       select cprl.amount_agent_paid,cprl.amount_vender_paid
          into v_already_agent_paid,v_already_vender_paid
          from csh_payment_req_ln cprl
          where cprl.payment_req_ln_id=c_req.id
          for update;
        --更新金额字段
       update csh_payment_req_ln  cprl
       set cprl.amount_agent_paid=nvl(v_already_agent_paid,0)+nvl(p_this_pay_agent_amount,0),
           cprl.amount_vender_paid=nvl(v_already_vender_paid,0)+nvl(p_this_pay_vender_amount,0)
        where cprl.payment_req_ln_id=c_req.id;
       
      END LOOP;
      IF p_merge_flag = 'Y' THEN
        csh_transaction_pkg.post_csh_transaction(p_transaction_id => v_transaction_id,
                                                 p_user_id        => p_user_id);
        csh_transaction_je_pkg.create_trx_write_off_je(p_transaction_id => v_transaction_id,
                                                       p_user_id        => p_user_id);
      END IF;
    END LOOP;
    DELETE FROM csh_payment_req_ln_tmp
     WHERE session_id = p_session_id
       AND TYPE = 'PAYMENT_REQ_PAYMENT';
    --20170216 JAINGLEI  放款成功短信  
    /*hls_sys_sms_job_pkg.generate_pay_succuess_sms(p_payment_req_id => p_payment_req_id,
    p_user_id        => p_user_id);*/
  EXCEPTION
    WHEN e_pay_check_error THEN
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '已经发起银行付款,不允许手工支付',
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'csh_payment_req_pkg',
                                                     p_procedure_function_name => 'payment_csh_payment');
      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            => 'csh_payment_req_pkg',
                                                     p_procedure_function_name => 'payment_csh_payment');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  END;

  PROCEDURE payment_submit(p_payment_req_id NUMBER, p_user_id NUMBER) IS
    v_csh_payment_req_hd_rec csh_payment_req_hd%ROWTYPE;
    e_status_error EXCEPTION;
    v_approval_method hls_document_type.approval_method%TYPE;
    v_credit_flag     VARCHAR2(100);
    e_credit_error EXCEPTION;
    v_hd_user_col_v05 NUMBER;
    v_count           NUMBER;
    v_mortgage_flag   VARCHAR2(1);
    e_mortgage_error EXCEPTION;
    v_division         VARCHAR2(2);
    v_con_contract_rec con_contract%ROWTYPE;
  BEGIN
    lock_csh_payment_req_hd(p_payment_req_id         => p_payment_req_id,
                            p_user_id                => p_user_id,
                            p_csh_payment_req_hd_rec => v_csh_payment_req_hd_rec);
  
    --付款前校验支付表代扣账户信息是否维护 modify by lpc for deron
    /*BEGIN
      SELECT cc.*
        INTO v_con_contract_rec
        FROM con_contract cc
       WHERE cc.contract_id = v_csh_payment_req_hd_rec.contract_id;
      IF v_con_contract_rec.deduction_flag IS NULL
         OR v_con_contract_rec.bankacctname IS NULL
         OR v_con_contract_rec.bankacctid IS NULL
         OR v_con_contract_rec.bankid IS NULL
         OR v_con_contract_rec.acctype IS NULL
         OR v_con_contract_rec.bank_account_id IS NULL THEN
        sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '付款前请先去支付表代扣账户维护功能维护账户信息!',
                                                       p_created_by              => p_user_id,
                                                       p_package_name            => 'prj_project_workflow_pkg',
                                                       p_procedure_function_name => 'workflow_start');
        raise_application_error(sys_raise_app_error_pkg.c_error_number,
                                sys_raise_app_error_pkg.g_err_line_id);
      END IF;
    EXCEPTION
      WHEN no_data_found THEN
        sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '未找到合同!',
                                                       p_created_by              => p_user_id,
                                                       p_package_name            => 'prj_project_workflow_pkg',
                                                       p_procedure_function_name => 'workflow_start');
        raise_application_error(sys_raise_app_error_pkg.c_error_number,
                                sys_raise_app_error_pkg.g_err_line_id);
    END;
    --校验授信额度 add by zhuxianfei 20180130
    v_credit_flag := hls_bp_master_credit_pkg.calc_overdue_amount_credit(p_bp_id          => v_csh_payment_req_hd_rec.bp_id,
                                                                         p_finance_amount => v_csh_payment_req_hd_rec.amount);
    IF v_credit_flag = 'N' THEN
      RAISE e_credit_error;
    END IF;
    
    --产品方案中是先抵押后放款时需要检验抵押信息 add by zhuxianfei 20180226
    --modify begin
    BEGIN
      SELECT --ppd.PRODUCT_NUMBER,
       ppd.hd_user_col_v05
        INTO v_hd_user_col_v05
        FROM hls_product_plan_definition ppd
       WHERE ppd.product_plan_id IN (SELECT pq.product_id
                                       FROM prj_quotation pq
                                      WHERE pq.document_id = (SELECT cc.project_id
                                                                FROM con_contract cc
                                                               WHERE cc.contract_id = v_csh_payment_req_hd_rec.contract_id));
    EXCEPTION
      WHEN no_data_found THEN
        NULL;
    END;
    SELECT division
      INTO v_division
      FROM con_contract
     WHERE contract_id = v_csh_payment_req_hd_rec.contract_id;
    --  1:先抵押后付款
    IF v_hd_user_col_v05 = 1
       AND v_division <> '02' THEN
      FOR lease_rec IN (SELECT ci.contract_lease_item_id
                          FROM con_contract_lease_item ci
                         WHERE ci.contract_id = v_csh_payment_req_hd_rec.contract_id)
      LOOP
        BEGIN
          SELECT clm.contract_mortgage_id
            INTO v_count
            FROM con_contract_lease_mortgage clm
           WHERE clm.contract_lease_item_id = lease_rec.contract_lease_item_id;
        EXCEPTION
          WHEN no_data_found THEN
            v_count := 0;
        END;
        IF v_count = 0 THEN
          RAISE e_mortgage_error;
        END IF;
      END LOOP;
    END IF;
    --modify end
    --校验可抵扣金额
    check_ddct_amount(p_payment_req_id => p_payment_req_id);
    */
    IF NOT is_payment_submitted(v_csh_payment_req_hd_rec) THEN
      RAISE e_status_error;
    END IF;
    FOR csh_payment_req_ln_rec IN (SELECT *
                                     FROM csh_payment_req_ln l
                                    WHERE l.payment_req_id =
                                          p_payment_req_id) LOOP
      csh_payment_req_amount_check(p_cashflow_id => csh_payment_req_ln_rec.ref_doc_line_id,
                                   p_amount      => csh_payment_req_ln_rec.amount,
                                   p_company_id  => NULL,
                                   p_user_id     => p_user_id);
    END LOOP;
  
    SELECT COUNT(1)
      INTO v_count
      FROM csh_payment_req_ln t
     WHERE t.payment_req_id = p_payment_req_id;
  
    IF nvl(v_count, 0) < 1 THEN
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '未找到付款申请数据,请检查数据',
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'csh_payment_req_pkg',
                                                     p_procedure_function_name => 'payment_submit');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    END IF;
    SELECT approval_method
      INTO v_approval_method
      FROM hls_document_type
     WHERE document_category = v_csh_payment_req_hd_rec.document_category
       AND document_type = v_csh_payment_req_hd_rec.document_type;
    /*if v_approval_method = 'WORK_FLOW' then*/
    --启动工作流
    csh_payment_workflow_pkg.workflow_start(p_payment_req_id, p_user_id);
    /*else
      update csh_payment_req_hd
         set approval_status  = 'APPROVING',
             last_updated_by  = p_user_id,
             last_update_date = sysdate
       where payment_req_id = p_payment_req_id;
    end if;*/
    UPDATE csh_payment_req_hd t
       SET t.submitted_flag = 'Y'
     WHERE t.payment_req_id = p_payment_req_id;
  EXCEPTION
    WHEN e_status_error THEN
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CSH_PAYMENT_REQ_PKG.SUBMIT_CSH_PAYMENT_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'csh_payment_req_pkg',
                                                      p_procedure_function_name => 'payment_submit');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    WHEN e_credit_error THEN
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '该商业伙伴的授信额度已占用完毕,无可使用额度,请核查!',
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'prj_project_workflow_pkg',
                                                     p_procedure_function_name => 'workflow_start');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    WHEN e_mortgage_error THEN
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '该产品为先抵押后付款模式,请先维护租赁物抵押信息!',
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'prj_project_workflow_pkg',
                                                     p_procedure_function_name => 'workflow_start');
      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            => 'csh_payment_req_pkg',
                                                     p_procedure_function_name => 'payment_submit');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  END;

  PROCEDURE ins_csh_pay_cashflow_id_temp(p_session_id  NUMBER,
                                         p_cashflow_id NUMBER,
                                         p_user_id     NUMBER) IS
    v_contract_cashflow_rec con_contract_cashflow%ROWTYPE;
    e_amount_before_confirm EXCEPTION;
    v_contract_id NUMBER;
  BEGIN
  
    v_contract_cashflow_rec := con_contract_pkg.get_cashflow_rec(p_cashflow_id => p_cashflow_id,
                                                                 p_user_id     => p_user_id);
  
    /* IF v_contract_cashflow_rec.estimated_due_amount IS NULL
       AND v_contract_cashflow_rec.cf_status = 'RELEASE' THEN
      RAISE e_amount_before_confirm;
    END IF;*/
  
    INSERT INTO csh_payment_cashflow_id_temp
    VALUES
      (p_session_id,
       p_cashflow_id,
       SYSDATE,
       p_user_id,
       SYSDATE,
       p_user_id,
       v_contract_cashflow_rec.contract_id);
    --插入付款头表
  EXCEPTION
    WHEN e_amount_before_confirm THEN
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CSH_PAYMENT_REQ_PKG.AMOUNT_BEFORE_CONFIRM',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'csh_payment_req_pkg',
                                                      p_procedure_function_name => 'ins_csh_pay_cashflow_id_temp');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  END;

  PROCEDURE del_csh_pay_cashflow_id_temp(p_session_id NUMBER,
                                         p_user_id    NUMBER) IS
  BEGIN
    DELETE FROM csh_payment_cashflow_id_temp t
     WHERE t.session_id = p_session_id
        OR (SYSDATE - t.creation_date) > 1;
  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            => 'csh_payment_req_pkg',
                                                     p_procedure_function_name => 'del_csh_pay_cashflow_id_temp');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  END del_csh_pay_cashflow_id_temp;

  PROCEDURE ins_csh_payment_req_hd(p_payment_req_id       IN OUT NUMBER,
                                   p_company_id           NUMBER,
                                   p_document_type        VARCHAR2,
                                   p_payment_req_number   VARCHAR2,
                                   p_req_date             DATE,
                                   p_apply_pay_date       DATE,
                                   p_transaction_category VARCHAR2,
                                   p_distribution_set_id  NUMBER,
                                   p_payment_method_id    NUMBER,
                                   p_bp_id                NUMBER,
                                   p_bp_category          VARCHAR2,
                                   p_amount               NUMBER,
                                   p_currency_code        VARCHAR2,
                                   p_description          VARCHAR2,
                                   p_submitted_flag       VARCHAR2,
                                   p_printed_flag         VARCHAR2,
                                   p_printed_times        NUMBER,
                                   p_last_print_date      DATE,
                                   p_closed_flag          VARCHAR2,
                                   p_closed_date          DATE,
                                   p_closed_note          VARCHAR2,
                                   p_approval_status      VARCHAR2,
                                   p_approval_date        DATE,
                                   p_approval_note        VARCHAR2,
                                   p_bp_bank_account_id   NUMBER DEFAULT NULL,
                                   p_bp_bank_account_num  VARCHAR2 DEFAULT NULL,
                                   p_bp_bank_account_name VARCHAR2 DEFAULT NULL,
                                   p_business_type        VARCHAR2 DEFAULT NULL,
                                   p_branch_id            NUMBER,
                                   p_city_id              NUMBER,
                                   p_user_id              NUMBER,
                                   p_csh_bp_name          VARCHAR2 DEFAULT NULL) IS
    v_payment_req_id         NUMBER;
    v_csh_payment_req_hd_rec csh_payment_req_hd%ROWTYPE;
    v_payment_req_number     VARCHAR2(100);
    v_bank_full_name         VARCHAR2(1000);
  BEGIN
    IF p_payment_req_id IS NULL THEN
      v_payment_req_number := get_payment_req_number(p_document_type     => p_document_type,
                                                     p_transaction_date  => SYSDATE,
                                                     p_company_id        => p_company_id,
                                                     p_user_id           => p_user_id,
                                                     p_document_category => 'PAYMENT_REQ');
      SELECT csh_payment_req_hd_s.nextval INTO p_payment_req_id FROM dual;
      --add by Harry 9952 2017/2/28  创建付款申请时,添加头表开户行字段
      SELECT hp.bank_branch_name
        INTO v_bank_full_name
        FROM hls_bp_master_bank_account hp
       WHERE hp.bank_account_id = p_bp_bank_account_id;
      INSERT INTO csh_payment_req_hd
        (payment_req_id,
         company_id,
         document_type,
         document_category,
         payment_req_number,
         req_date,
         apply_pay_date,
         transaction_category,
         distribution_set_id,
         payment_method_id,
         bp_id,
         bp_category,
         amount,
         currency_code,
         description,
         submitted_flag,
         printed_flag,
         printed_times,
         last_print_date,
         closed_flag,
         closed_date,
         closed_note,
         approval_status,
         approval_date,
         approval_note,
         owner_user_id,
         creation_date,
         created_by,
         last_update_date,
         last_updated_by,
         bp_bank_account_id,
         bp_bank_account_num,
         bp_bank_account_name,
         business_type,
         bank_full_name,
         branch_id,
         city_id)
      VALUES
        (p_payment_req_id,
         p_company_id,
         p_document_type,
         'PAYMENT_REQ',
         v_payment_req_number,
         nvl(p_req_date, SYSDATE),
         p_apply_pay_date,
         'BUSINESS',
         '',
         '',
         p_bp_id,
         p_bp_category,
         p_amount,
         p_currency_code,
         p_description,
         'N',
         'N',
         '',
         '',
         'N',
         '',
         '',
         'NEW',
         '',
         '',
         p_user_id,
         SYSDATE,
         p_user_id,
         SYSDATE,
         p_user_id,
         p_bp_bank_account_id,
         p_bp_bank_account_num,
         p_bp_bank_account_name,
         p_business_type,
         v_bank_full_name,
         p_branch_id,
         p_city_id);
      --增加权限
      aut_document_authority_pkg.insert_trx_user_authority(p_company_id    => p_company_id,
                                                           p_owner_user_id => p_user_id,
                                                           p_trx_category  => 'PAYMENT_REQ',
                                                           p_trx_id        => p_payment_req_id,
                                                           p_start_date    => trunc(SYSDATE),
                                                           p_end_date      => to_date('30000101',
                                                                                      'yyyymmdd'),
                                                           p_user_id       => p_user_id);
      --提交状态更新
      /* payment_submit(p_payment_req_id => p_payment_req_id,
      p_user_id        => p_user_id);*/
    ELSE
      UPDATE csh_payment_req_hd t1
         SET t1.req_date         = nvl(p_req_date, SYSDATE),
             t1.apply_pay_date   = p_apply_pay_date,
             t1.bp_id            = p_bp_id,
             t1.bp_category      = p_bp_category,
             t1.amount           = p_amount,
             t1.currency_code    = p_currency_code,
             t1.description      = p_description,
             t1.last_update_date = SYSDATE,
             t1.last_updated_by  = p_user_id
       WHERE t1.payment_req_id = p_payment_req_id;
    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            => 'csh_payment_req_pkg',
                                                     p_procedure_function_name => 'ins_csh_payment_req_hd');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  END;

  PROCEDURE ins_csh_payment_req_ln(p_payment_req_ln_id      IN OUT NUMBER,
                                   p_payment_req_id         NUMBER,
                                   p_payment_req_ln_type    VARCHAR2,
                                   p_ref_doc_category       VARCHAR2,
                                   p_ref_doc_id             NUMBER,
                                   p_ref_doc_line_id        NUMBER,
                                   p_bp_id                  NUMBER,
                                   p_currency_code          VARCHAR2,
                                   p_amount                 NUMBER,
                                   p_amount_paid            NUMBER,
                                   p_description            VARCHAR2,
                                   p_payment_status         VARCHAR2,
                                   p_payment_completed_date DATE,
                                   p_apply_pay_date         DATE,
                                   p_payment_method_id      NUMBER,
                                   p_bank_account_id        NUMBER,
                                   p_bank_account_num       VARCHAR2,
                                   p_bank_account_name      VARCHAR2,
                                   p_bank_branch_name       VARCHAR2,
                                   p_bp_bank_account_id     NUMBER DEFAULT NULL,
                                   p_bp_bank_account_num    VARCHAR2 DEFAULT NULL,
                                   p_bp_bank_account_name   VARCHAR2 DEFAULT NULL,
                                   p_user_id                NUMBER,
                                   p_company_id             NUMBER) IS
    --v_amount_flag varchar2(1) ;
    v_document_category con_contract.document_category%TYPE;
  BEGIN
    --申请金额校验
    csh_payment_req_amount_check(p_cashflow_id => p_ref_doc_line_id,
                                 p_amount      => p_amount,
                                 p_company_id  => p_company_id,
                                 p_user_id     => p_user_id);
    SELECT t.document_category
      INTO v_document_category
      FROM con_contract t
     WHERE t.contract_id = p_ref_doc_id
       FOR UPDATE NOWAIT;
    IF p_payment_req_ln_id IS NULL THEN
      SELECT csh_payment_req_ln_s.nextval
        INTO p_payment_req_ln_id
        FROM dual;
      INSERT INTO csh_payment_req_ln
        (payment_req_ln_id,
         payment_req_id,
         payment_req_ln_type,
         ref_doc_category,
         ref_doc_id,
         ref_doc_line_id,
         bp_id,
         currency_code,
         amount,
         amount_paid,
         description,
         payment_status,
         payment_completed_date,
         apply_pay_date,
         payment_method_id,
         bank_account_id,
         bank_account_num,
         bank_account_name,
         bank_branch_name,
         bp_bank_account_id,
         bp_bank_account_num,
         bp_bank_account_name,
         creation_date,
         created_by,
         last_update_date,
         last_updated_by)
      VALUES
        (p_payment_req_ln_id,
         p_payment_req_id,
         'DEBT',
         v_document_category,
         p_ref_doc_id,
         p_ref_doc_line_id,
         p_bp_id,
         p_currency_code,
         p_amount,
         p_amount_paid,
         p_description,
         'NOT',
         p_payment_completed_date,
         p_apply_pay_date,
         p_payment_method_id,
         p_bank_account_id,
         p_bank_account_num,
         p_bank_account_name,
         p_bank_branch_name,
         p_bp_bank_account_id,
         p_bp_bank_account_num,
         p_bp_bank_account_name,
         SYSDATE,
         p_user_id,
         SYSDATE,
         p_user_id);
    ELSE
      UPDATE csh_payment_req_ln t1
         SET t1.bp_id                = p_bp_id,
             t1.currency_code        = p_currency_code,
             t1.amount               = p_amount,
             t1.amount_paid          = p_amount_paid,
             t1.description          = p_description,
             t1.apply_pay_date       = p_apply_pay_date,
             t1.payment_method_id    = p_payment_method_id,
             t1.bank_account_id      = p_bank_account_id,
             t1.bank_account_num     = p_bank_account_num,
             t1.bank_account_name    = p_bank_account_name,
             t1.bank_branch_name     = p_bank_branch_name,
             t1.bp_bank_account_id   = p_bp_bank_account_id,
             t1.bp_bank_account_num  = p_bp_bank_account_num,
             t1.bp_bank_account_name = p_bp_bank_account_name,
             t1.last_update_date     = SYSDATE,
             t1.last_updated_by      = p_user_id
       WHERE t1.payment_req_ln_id = p_payment_req_ln_id;
    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            => 'csh_payment_req_pkg',
                                                     p_procedure_function_name => 'ins_csh_payment_req_ln');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  END;

  PROCEDURE del_csh_payment_req_ln(p_payment_req_ln_id NUMBER,
                                   p_user_id           NUMBER) IS
    v_csh_payment_req_hd_rec csh_payment_req_hd%ROWTYPE;
    v_csh_payment_req_ln_rec csh_payment_req_ln%ROWTYPE;
    e_status_error EXCEPTION;
  BEGIN
    lock_csh_payment_req_ln(p_payment_req_ln_id      => p_payment_req_ln_id,
                            p_user_id                => p_user_id,
                            p_csh_payment_req_ln_rec => v_csh_payment_req_ln_rec);
    lock_csh_payment_req_hd(p_payment_req_id         => v_csh_payment_req_ln_rec.payment_req_id,
                            p_user_id                => p_user_id,
                            p_csh_payment_req_hd_rec => v_csh_payment_req_hd_rec);
    /*if not is_payment_submitted(v_csh_payment_req_hd_rec) then
      raise e_status_error;
    end if;*/
    IF nvl(v_csh_payment_req_ln_rec.payment_status, 'NOT') <> 'NOT' THEN
      RAISE e_status_error;
    END IF;
    DELETE FROM csh_payment_req_ln l
     WHERE l.payment_req_ln_id = p_payment_req_ln_id;
  EXCEPTION
    WHEN e_status_error THEN
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '该付款申请行已支付,不能删除',
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'csh_payment_req_pkg',
                                                     p_procedure_function_name => 'del_csh_payment_req_ln');
      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            => 'csh_payment_req_pkg',
                                                     p_procedure_function_name => 'del_csh_payment_req_ln');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  END;

  PROCEDURE del_csh_payment_req_hd(p_payment_req_id NUMBER,
                                   p_user_id        NUMBER) IS
    v_csh_payment_req_hd_rec csh_payment_req_hd%ROWTYPE;
    e_status_error EXCEPTION;
  BEGIN
    lock_csh_payment_req_hd(p_payment_req_id         => p_payment_req_id,
                            p_user_id                => p_user_id,
                            p_csh_payment_req_hd_rec => v_csh_payment_req_hd_rec);
    IF NOT is_payment_submitted(v_csh_payment_req_hd_rec) THEN
      RAISE e_status_error;
    END IF;
    --删除抵扣信息  modify by zhuxianfei 20180323 --begin
    DELETE FROM csh_payment_req_ln_ddct ld
     WHERE ld.ref_doc_line_id IN
           (SELECT t1.ref_doc_line_id
              FROM csh_payment_req_ln_ddct_lv t1
             WHERE payment_req_id = p_payment_req_id);
    --end
    DELETE FROM csh_payment_req_hd h
     WHERE h.payment_req_id = p_payment_req_id;
    DELETE FROM csh_payment_req_ln l
     WHERE l.payment_req_id = p_payment_req_id;
  EXCEPTION
    WHEN e_status_error THEN
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CSH_PAYMENT_REQ_PKG.DELETE_CSH_PAYMENT_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'csh_payment_req_pkg',
                                                      p_procedure_function_name => 'del_csh_payment_req_hd');
      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            => 'csh_payment_req_pkg',
                                                     p_procedure_function_name => 'del_csh_payment_req_hd');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  END;

  PROCEDURE change_print_status(p_contract_id NUMBER, p_user_id NUMBER) IS
    v_con_contract_rec con_contract%ROWTYPE;
  BEGIN
    lock_con_contract_rec(p_contract_id      => p_contract_id,
                          p_user_id          => p_user_id,
                          p_con_contract_rec => v_con_contract_rec);
    UPDATE con_contract t
       SET t.pay_req_print_status = 'PRINT',
           t.last_update_date     = SYSDATE,
           t.last_updated_by      = p_user_id
     WHERE t.contract_id = v_con_contract_rec.contract_id;
  END;

  PROCEDURE change_printed_status(p_contract_id NUMBER, p_user_id NUMBER) IS
    v_con_contract_rec con_contract%ROWTYPE;
  BEGIN
    /*    lock_con_contract_rec(p_contract_id      => p_contract_id,
    p_user_id          => p_user_id,
    p_con_contract_rec => v_con_contract_rec);*/
    UPDATE con_contract t
       SET t.pay_req_print_status = 'PRINTED',
           t.last_update_date     = SYSDATE,
           t.last_updated_by      = p_user_id
     WHERE t.contract_id = p_contract_id;
  END;

  PROCEDURE change_applied_status(p_contract_id NUMBER, p_user_id NUMBER) IS
    v_con_contract_rec con_contract%ROWTYPE;
  BEGIN
    lock_con_contract_rec(p_contract_id      => p_contract_id,
                          p_user_id          => p_user_id,
                          p_con_contract_rec => v_con_contract_rec);
    UPDATE con_contract t
       SET t.pay_req_print_status = 'APPLIED',
           t.last_update_date     = SYSDATE,
           t.last_updated_by      = p_user_id
     WHERE t.contract_id = v_con_contract_rec.contract_id;
  END;

  PROCEDURE csh_payment_apply(p_contract_id NUMBER, p_user_id NUMBER) IS
    v_con_contract_rec     con_contract%ROWTYPE;
    v_pay_req_print_status VARCHAR2(30);
    v_inception_of_lease   DATE;
    e_lease_date_null    EXCEPTION;
    e_print_status_error EXCEPTION;
  BEGIN
    lock_con_contract_rec(p_contract_id      => p_contract_id,
                          p_user_id          => p_user_id,
                          p_con_contract_rec => v_con_contract_rec);
    v_pay_req_print_status := v_con_contract_rec.pay_req_print_status;
    v_inception_of_lease   := v_con_contract_rec.inception_of_lease;
    IF v_pay_req_print_status IN ('PRINT', 'PRINTED', 'APPLIED') THEN
      RAISE e_print_status_error;
    END IF;
    IF v_inception_of_lease IS NULL OR v_inception_of_lease = '' THEN
      RAISE e_lease_date_null;
    END IF;
    change_applied_status(p_contract_id => v_con_contract_rec.contract_id,
                          p_user_id     => p_user_id);
  EXCEPTION
    WHEN e_print_status_error THEN
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CSH_PAYMENT_REQ_PKG.PAYMENT_REQ_PRINT_STATUS_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'csh_payment_req_pkg',
                                                      p_procedure_function_name => 'csh_payment_apply');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    WHEN e_lease_date_null THEN
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CSH_PAYMENT_REQ_PKG.LEASE_DATE_NULL',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'csh_payment_req_pkg',
                                                      p_procedure_function_name => 'csh_payment_apply');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  END;

  PROCEDURE csh_payment_print(p_contract_id NUMBER, p_user_id NUMBER) IS
    v_con_contract_rec     con_contract%ROWTYPE;
    v_pay_req_print_status VARCHAR2(30);
    e_req_print_status_error EXCEPTION;
  BEGIN
    lock_con_contract_rec(p_contract_id      => p_contract_id,
                          p_user_id          => p_user_id,
                          p_con_contract_rec => v_con_contract_rec);
    v_pay_req_print_status := v_con_contract_rec.pay_req_print_status;
    /*  if v_pay_req_print_status is null or v_pay_req_print_status != 'PRINT' then
      raise e_req_print_status_error;
    end if;*/
    change_printed_status(p_contract_id => v_con_contract_rec.contract_id,
                          p_user_id     => p_user_id);
    --for doc status
    yonda_doc_history_pkg.yonda_insert_doc_status(p_document_id       => v_con_contract_rec.contract_id,
                                                  p_document_type     => v_con_contract_rec.document_type,
                                                  p_document_category => v_con_contract_rec.document_category,
                                                  p_doc_status        => yonda_doc_history_pkg.yonda_con_payment_doc_print,
                                                  p_instance_id       => NULL,
                                                  p_user_id           => p_user_id);
  EXCEPTION
    WHEN e_req_print_status_error THEN
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CSH_PAYMENT_REQ_PKG.REQ_PRINT_STATUS_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'csh_payment_req_pkg',
                                                      p_procedure_function_name => 'csh_payment_print');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  END;

  PROCEDURE update_csh_payment_flag(p_payment_req_id NUMBER,
                                    p_payment_flag   VARCHAR2,
                                    p_user_id        NUMBER) IS
  BEGIN
    UPDATE csh_payment_req_hd hd
       SET hd.payment_flag     = p_payment_flag,
           hd.last_update_date = SYSDATE,
           hd.last_updated_by  = p_user_id
     WHERE hd.payment_req_id = p_payment_req_id;
  END;

  PROCEDURE update_payment_req_contract_id(p_payment_req_id     IN NUMBER,
                                           p_payment_req_number IN VARCHAR2) IS
  BEGIN
    UPDATE csh_payment_req_hd h
       SET h.payment_req_number = p_payment_req_number
     WHERE h.payment_req_id = p_payment_req_id;
  END;

  --创建cdd_list_id
  PROCEDURE create_cdd(p_payment_req_id NUMBER,
                       p_user_id        NUMBER,
                       p_cdd_list_id    IN OUT NUMBER) IS
    v_csh_payment_req_record csh_payment_req_hd%ROWTYPE;
  BEGIN
    lock_csh_payment_req_hd(p_payment_req_id         => p_payment_req_id,
                            p_user_id                => p_user_id,
                            p_csh_payment_req_hd_rec => v_csh_payment_req_record);
    IF p_cdd_list_id IS NULL THEN
      save_cdd_item_from_templt(p_document_category => v_csh_payment_req_record.document_category,
                                p_document_type     => v_csh_payment_req_record.document_type,
                                p_document_id       => p_payment_req_id,
                                p_company_id        => v_csh_payment_req_record.company_id,
                                p_user_id           => p_user_id,
                                p_cdd_list_id       => p_cdd_list_id);
      UPDATE csh_payment_req_hd d
         SET d.cdd_list_id      = p_cdd_list_id,
             d.last_updated_by  = p_user_id,
             d.last_update_date = SYSDATE
       WHERE d.payment_req_id = p_payment_req_id
         AND d.cdd_list_id IS NULL;
    END IF;
  END;

  --创建cdd
  PROCEDURE save_cdd_item_from_templt(p_document_category VARCHAR2,
                                      p_document_type     VARCHAR2,
                                      p_document_id       NUMBER,
                                      p_company_id        NUMBER,
                                      p_user_id           NUMBER,
                                      p_cdd_list_id       IN OUT NUMBER) IS
    v_csh_payment_req_record csh_payment_req_hd%ROWTYPE;
    v_cdd_item_id            NUMBER;
    v_templet_head_code      VARCHAR2(1000);
    v_templet_head_id        NUMBER;
    v_cond_para1             VARCHAR2(255) := 'NEW';
    v_cond_para2             VARCHAR2(255);
    v_cond_para3             VARCHAR2(255);
    v_cond_para4             VARCHAR2(255);
    v_cond_para5             VARCHAR2(255);
    v_bp_class               VARCHAR2(20);
  BEGIN
    lock_csh_payment_req_hd(p_payment_req_id         => p_document_id,
                            p_user_id                => p_user_id,
                            p_csh_payment_req_hd_rec => v_csh_payment_req_record);
    SELECT h.bp_class
      INTO v_bp_class
      FROM con_contract t, hls_bp_master h
     WHERE t.bp_id_tenant = h.bp_id
       AND t.contract_id = v_csh_payment_req_record.contract_id;
  
    sys_condition_cdd_pkg.matching_condition(p_condition_code    => 'CDD00',
                                             p_company_id        => v_csh_payment_req_record.company_id,
                                             p_role_id           => '',
                                             p_user_id           => p_user_id,
                                             p_function_code     => '',
                                             p_currency          => '',
                                             p_lease_org         => '',
                                             p_lease_channel     => '',
                                             p_division          => '',
                                             p_business_type     => '',
                                             p_document_category => '',
                                             p_document_type     => '',
                                             p_bp_class          => v_bp_class,
                                             p_cond_para1        => v_cond_para1,
                                             p_cond_para2        => v_cond_para2,
                                             p_cond_para3        => v_cond_para3,
                                             p_cond_para4        => v_cond_para4,
                                             p_cond_para5        => v_cond_para5,
                                             p_cdd_code          => v_templet_head_code);
    BEGIN
      SELECT h.templet_head_id
        INTO v_templet_head_id
        FROM prj_cdd_item_templet_hd h
       WHERE h.templet_code = v_templet_head_code
         AND h.enabled_flag = 'Y';
      prj_cdd_item_pkg.create_cdd_item_from_templet(p_templet_head_id => v_templet_head_id,
                                                    p_cdd_list_id     => p_cdd_list_id,
                                                    p_user_id         => p_user_id);
    EXCEPTION
      WHEN no_data_found THEN
        NULL;
    END;
  END;

  PROCEDURE update_payment_req_number(p_payment_req_id     IN NUMBER,
                                      p_payment_req_number IN VARCHAR2) IS
  BEGIN
    UPDATE csh_payment_req_hd h
       SET h.payment_req_number = p_payment_req_number
     WHERE h.payment_req_id = p_payment_req_id;
  END;

  /*Function get_payment_record(p_payment_req_id Number, p_user_id Number)
    Return csh_payment_req_hd%Rowtype Is
    v_record csh_payment_req_hd%Rowtype;
  Begin
  
    Select *
      Into v_record
      From csh_payment_req_hd v
     Where v.payment_req_id = p_payment_req_id
       For Update Nowait;
    Return v_record;
  
  End;*/
  PROCEDURE save_cdd_item_doc_ref(p_source_document_table VARCHAR2,
                                  p_source_document_id    NUMBER,
                                  p_to_document_table     VARCHAR2,
                                  p_to_document_id        NUMBER,
                                  p_user_id               NUMBER) IS
  BEGIN
    --save prj_cdd_item_doc_ref
    INSERT INTO prj_cdd_item_doc_ref
      (doc_ref_id,
       document_table,
       document_id,
       check_id,
       created_by,
       creation_date,
       last_updated_by,
       last_update_date)
      (SELECT prj_cdd_item_doc_ref_s.nextval,
              p_to_document_table,
              p_to_document_id,
              check_id,
              p_user_id,
              SYSDATE,
              p_user_id,
              SYSDATE
         FROM prj_cdd_item_doc_ref
        WHERE document_table = p_source_document_table
          AND document_id = p_source_document_id);
  END;

  --插入合同id
  PROCEDURE update_payment_req_contract_id(p_payment_req_id IN NUMBER,
                                           p_contract_id    IN NUMBER) IS
    v_cdd_list_id NUMBER;
  BEGIN
    SELECT cc.cdd_list_id
      INTO v_cdd_list_id
      FROM con_contract cc
     WHERE cc.contract_id = p_contract_id;
    UPDATE csh_payment_req_hd h
       SET h.contract_id = p_contract_id, h.cdd_list_id = v_cdd_list_id
     WHERE h.payment_req_id = p_payment_req_id;
    --复制附件
    /*save_cdd_item_doc_ref(p_source_document_table => 'CON_CONTRACT',
    p_source_document_id    => p_contract_id,
    p_to_document_table     => 'CSH_PAYMENT_REQ_HD',
    p_to_document_id        => p_payment_req_id,
    p_user_id               => 1);*/
  END;

  --检验可抵扣金额
  PROCEDURE check_ddct_amount(p_payment_req_id IN NUMBER) IS
    v_cdd_list_id       NUMBER;
    v_payment_req_ln_id NUMBER;
    v_un_due_amount     NUMBER;
    e_ddct_amount_error EXCEPTION;
  BEGIN
    --可以多行
    /*Select l.payment_req_ln_id
      Into v_payment_req_ln_id
      From csh_payment_req_ln l
     Where l.payment_req_id = p_payment_req_id;
    
    Select Sum(dt.un_due_amount)
      Into v_un_due_amount
      From csh_payment_req_ln_ddct_lv dt
     Where dt.payment_req_ln_id = v_payment_req_ln_id;*/
    SELECT SUM(dt.un_due_amount)
      INTO v_un_due_amount
      FROM csh_payment_req_ln_ddct_lv dt
     WHERE dt.payment_req_ln_id IN
           (SELECT l.payment_req_ln_id
              FROM csh_payment_req_ln l
             WHERE l.payment_req_id = p_payment_req_id);
    IF v_un_due_amount < 0 THEN
      RAISE e_ddct_amount_error;
    END IF;
  EXCEPTION
    WHEN e_ddct_amount_error THEN
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '可抵扣金额小于0,请检查该条单据!',
                                                     p_created_by              => 1,
                                                     p_package_name            => 'csh_payment_req_pkg',
                                                     p_procedure_function_name => 'check_ddct_amount');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    WHEN no_data_found THEN
      NULL;
  END;

  PROCEDURE check_contract_payment(p_contract_id    NUMBER,
                                   p_payment_req_id NUMBER,
                                   p_user_id        NUMBER) IS
    v_count NUMBER;
    e_con_not_found_payment_err EXCEPTION;
  BEGIN
    SELECT COUNT(1)
      INTO v_count
      FROM csh_payment_req_ln l
     WHERE l.ref_doc_category = 'CONTRACT'
       AND l.ref_doc_id = p_contract_id
       AND l.payment_req_id = p_payment_req_id
       AND l.payment_status <> 'FULL';
    IF v_count >= 1 THEN
      RAISE e_con_not_found_payment_err;
    END IF;
  EXCEPTION
    WHEN e_con_not_found_payment_err THEN
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '该合同尚未全部完成支付!',
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'csh_payment_req_pkg',
                                                     p_procedure_function_name => 'check_contract_payment');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  END;

  --add by 9188 检验付款申请工作流的所有合同是否全部核销
  PROCEDURE check_sch_payment_con(p_payment_req_id NUMBER,
                                  p_user_id        NUMBER) IS
    v_count NUMBER := 0;
    e_csh_payment_err EXCEPTION;
  BEGIN
    FOR cu IN (SELECT *
                 FROM csh_payment_req_ln t1
                WHERE t1.payment_req_id = p_payment_req_id
                  AND t1.ref_doc_category = 'CONTRACT') LOOP
      IF (cu.payment_status <> 'FULL') THEN
        v_count := v_count + 1;
      END IF;
    END LOOP;
    IF (v_count >= 1) THEN
      RAISE e_csh_payment_err;
    END IF;
  EXCEPTION
    WHEN e_csh_payment_err THEN
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '该付款申请下存在尚未全部完成支付的合同!',
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'csh_payment_req_pkg',
                                                     p_procedure_function_name => 'check_sch_payment_con');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  END check_sch_payment_con;

  --add by Harry 9952 保证金退还-付款申请校验
  PROCEDURE check_status_and_flag(p_cashflow_id NUMBER, p_user_id NUMBER) IS
    v_count_1 NUMBER;
    v_count_2 NUMBER;
    v_count_3 NUMBER;
    e_closed_flag_error EXCEPTION;
    e_approving_error   EXCEPTION;
    e_csh_trx_error     EXCEPTION;
  BEGIN
    SELECT COUNT(*)
      INTO v_count_1
      FROM csh_payment_req_hd h
     WHERE h.payment_req_id IN
           (SELECT ln.payment_req_id
              FROM csh_payment_req_ln ln
             WHERE ln.ref_doc_line_id = p_cashflow_id)
       AND h.closed_flag != 'Y';
    SELECT COUNT(*)
      INTO v_count_2
      FROM csh_payment_req_hd h
     WHERE h.payment_req_id IN
           (SELECT l.payment_req_id
              FROM csh_payment_req_ln l
             WHERE l.ref_doc_line_id = p_cashflow_id)
       AND h.approval_status = 'APPROVING';
    -- 收付抵扣工作流中
    SELECT COUNT(*)
      INTO v_count_3
      FROM hls_common_wfl c, con_contract_cashflow d, csh_write_off_temp t
     WHERE c.status = 'APPROVING'
       AND c.wfl_instance_id = t.wfl_instance_id
       AND t.document_line_id = d.cashflow_id
       AND d.cashflow_id = p_cashflow_id
       AND t.document_category = 'CSH_TRX';
    /* if v_count_1 <> 0 then
      raise e_closed_flag_error;
    end if;*/
    IF v_count_2 <> 0 THEN
      RAISE e_approving_error;
    END IF;
    /*    if v_count_3 <> 0 then
      raise e_csh_trx_error;
    end if;*/
  EXCEPTION
    WHEN e_closed_flag_error THEN
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '该合同客户保证金正在抵扣工作流中,请勿继续抵扣或支付!',
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'csh_payment_req_pkg',
                                                     p_procedure_function_name => 'check_status_and_flag');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    WHEN e_approving_error THEN
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '该合同客户保证金正在付款工作流中,请勿继续抵扣或支付!',
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'csh_payment_req_pkg',
                                                     p_procedure_function_name => 'check_status_and_flag');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    WHEN e_csh_trx_error THEN
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '该合同客户保证金正在抵扣工作流中,请勿继续抵扣或支付!',
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'csh_payment_req_pkg',
                                                     p_procedure_function_name => 'check_status_and_flag');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  END check_status_and_flag;

END csh_payment_req_pkg;
/