acr_invoice_claim_pkg.pck 4.4 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104
create or replace package body ACR_INVOICE_CLAIM_PKG is

  g_status_unclaimed Constant Varchar2(30) := 'UNCLAIMED';
  g_status_claiming  Constant Varchar2(30) := 'CLAIMING';
  g_status_confirmed Constant Varchar2(30) := 'CONFIRMED';
  g_status_return    Constant Varchar2(30) := 'RETURN';

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

  Procedure apply_invoice(p_invoice_hd_id Number, p_user_id Number) Is
    r_acr_invoice_hd_rec acr_invoice_hd%Rowtype;
    e_invoice_claim_status_err Exception;
  begin
    --锁表
    r_acr_invoice_hd_rec := lock_acr_invoice(p_invoice_hd_id => p_invoice_hd_id);
    --状态检查
    If  r_acr_invoice_hd_rec.claim_status <> g_status_return and r_acr_invoice_hd_rec.claim_status <>g_status_unclaimed Then
      Raise e_invoice_claim_status_err;
    End If;

    --更新状态
    Update acr_invoice_hd hd
       Set claim_status        = g_status_claiming,
           hd.last_update_date = sysdate,
           hd.last_updated_by  = p_user_id
     Where invoice_hd_id = p_invoice_hd_id;

  Exception
    When e_invoice_claim_status_err Then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'ACR516_INVOICE_CLAIM_STATUS_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'ACR_INVOICE_CLAIM_PKG',
                                                      p_procedure_function_name => 'apply_invoice');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  end;

  Procedure apply_invoice_confirm(p_invoice_hd_id Number, p_user_id Number) Is
    r_acr_invoice_hd_rec acr_invoice_hd%Rowtype;
    e_invoice_claim_status_err Exception;
  begin
    --锁表
    r_acr_invoice_hd_rec := lock_acr_invoice(p_invoice_hd_id => p_invoice_hd_id);
    --状态检查
    If r_acr_invoice_hd_rec.claim_status <> g_status_claiming Then
      Raise e_invoice_claim_status_err;
    End If;

    --更新状态
    Update acr_invoice_hd hd
       Set claim_status        = g_status_confirmed,
           hd.last_update_date = sysdate,
           hd.last_updated_by  = p_user_id
     Where invoice_hd_id = p_invoice_hd_id;

  Exception
    When e_invoice_claim_status_err Then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'ACR516_INVOICE_CLAIM_STATUS_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'ACR_INVOICE_CLAIM_PKG',
                                                      p_procedure_function_name => 'apply_invoice_confirm');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  end;

  Procedure apply_invoice_return(p_invoice_hd_id Number, p_user_id Number) Is
    r_acr_invoice_hd_rec acr_invoice_hd%Rowtype;
    e_invoice_claim_status_err Exception;
  begin
    --锁表
    r_acr_invoice_hd_rec := lock_acr_invoice(p_invoice_hd_id => p_invoice_hd_id);
    --状态检查
    If r_acr_invoice_hd_rec.claim_status <> g_status_claiming Then
      Raise e_invoice_claim_status_err;
    End If;

    --更新状态
    Update acr_invoice_hd hd
       Set claim_status        = g_status_return,
           hd.last_update_date = sysdate,
           hd.last_updated_by  = p_user_id
     Where invoice_hd_id = p_invoice_hd_id;

  Exception
    When e_invoice_claim_status_err Then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'ACR516_INVOICE_CLAIM_STATUS_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'ACR_INVOICE_CLAIM_PKG',
                                                      p_procedure_function_name => 'apply_invoice_return');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  end;

end ACR_INVOICE_CLAIM_PKG;