cus_con_change_req_wfl_pkg.pck 8.17 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
create or replace package cus_con_change_req_wfl_pkg is

  -- Author  : Spencer 3893
  -- Created : 2019/9/25 13:51:00
  -- Purpose : 合同變更工作流相關邏輯

  procedure workflow_start(p_change_req_rec in con_contract_change_req%rowtype,
                           p_change_con_rec in con_contract%rowtype,
                           p_layout_code    in varchar2,
                           p_user_id        in number);

  procedure workflow_approve(p_change_req_id in number,
                             p_user_id       in number);

  procedure workflow_reject(p_change_req_id in number, p_user_id in number);

  procedure workflow_cancel(p_change_req_id in number, p_user_id in number);

end cus_con_change_req_wfl_pkg;
/
create or replace package body cus_con_change_req_wfl_pkg is

  procedure workflow_start(p_change_req_rec in con_contract_change_req%rowtype,
                           p_change_con_rec in con_contract%rowtype,
                           p_layout_code    in varchar2,
                           p_user_id        in number) is
    v_document_info varchar2(2000);
    v_instance_id   number;
    v_bp_name       varchar2(300);
    v_workflow_desc varchar2(2000);
  begin
32 33 34 35 36 37 38 39 40 41 42 43
    --承租人已改变,此时con_contract_bp并没有新的承租人信息,会返回no_data_found例外
    if p_layout_code = 'CON_TENANT_CHANGE_MODIFY' then
      select lv.bp_name
        into v_bp_name
        from con_contract_bp_tenant_lv lv
       where lv.contract_id = p_change_req_rec.contract_id;
    else
      select lv.bp_name
        into v_bp_name
        from con_contract_bp_tenant_lv lv
       where lv.contract_id = p_change_con_rec.contract_id;
    end if;
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 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147
    update con_contract_change_req t
       set t.req_status     = 'APPROVING',
           t.submit_date    = sysdate,
           last_updated_by  = p_user_id,
           last_update_date = sysdate
     where t.change_req_id = p_change_req_rec.change_req_id;
  
    v_instance_id := p_change_req_rec.wfl_instance_id;
    begin
      select t1.workflow_desc
        into v_workflow_desc
        from hls_document_type_v t1
       where t1.enabled_flag = 'Y'
         and t1.document_type = p_change_req_rec.document_type
         and t1.document_category = p_change_req_rec.document_category;
    exception
      when others then
        null;
    end;
    v_document_info := v_workflow_desc || ':' ||
                       p_change_con_rec.contract_number || '-' || v_bp_name;
  
    hls_workflow_pkg.workflow_start(p_instance_id           => v_instance_id,
                                    p_document_id           => p_change_req_rec.change_req_id,
                                    p_document_category     => p_change_req_rec.document_category,
                                    p_document_type         => p_change_req_rec.document_type,
                                    p_business_type         => p_change_req_rec.business_type,
                                    p_business_type_2nd     => p_change_con_rec.business_type,
                                    p_document_category_2nd => p_change_con_rec.document_category,
                                    p_document_type_2nd     => p_change_con_rec.document_type,
                                    p_company_id            => p_change_con_rec.company_id,
                                    p_user_id               => p_user_id,
                                    p_lease_org             => p_change_con_rec.lease_organization, --事业部
                                    p_lease_channel         => p_change_con_rec.lease_channel,
                                    p_division              => p_change_con_rec.division,
                                    p_ccr_reschedule        => p_change_req_rec.reschedule_flag,
                                    p_ccr_et                => p_change_req_rec.et_flag,
                                    p_ccr_tenant_change     => p_change_req_rec.tenant_change_flag,
                                    p_ccr_lease_item_change => p_change_req_rec.lease_item_change_flag,
                                    p_ccr_mortgage_change   => p_change_req_rec.mortgage_change_flag,
                                    p_ccr_guarantor_change  => p_change_req_rec.guarantor_change_flag,
                                    p_ccr_bank_acc_change   => p_change_req_rec.bank_acc_change_flag,
                                    p_ccr_contract_cancel   => p_change_req_rec.contract_cancel_flag,
                                    p_ccr_cashflow_change   => p_change_req_rec.cashflow_change_flag,
                                    -- parameter values
                                    p_parameter_1        => 'NEW_CONTRACT_ID',
                                    p_parameter_1_value  => p_change_req_rec.change_req_id,
                                    p_parameter_2        => 'OLD_CONTRACT_ID',
                                    p_parameter_2_value  => p_change_req_rec.contract_id,
                                    p_parameter_4        => 'OWNER_USER_ID',
                                    p_parameter_4_value  => nvl(p_change_req_rec.owner_user_id,
                                                                p_user_id),
                                    p_parameter_5        => 'CHANGE_REQ_ID',
                                    p_parameter_5_value  => p_change_req_rec.change_req_id,
                                    p_parameter_6        => 'DOCUMENT_INFO',
                                    p_parameter_6_value  => v_document_info,
                                    p_parameter_8        => 'CHANGE_REQ_NUMBER',
                                    p_parameter_8_value  => p_change_req_rec.change_req_number,
                                    p_parameter_9        => 'PROJECT_ID',
                                    p_parameter_9_value  => p_change_con_rec.project_id,
                                    p_parameter_10       => 'LAYOUT_CODE',
                                    p_parameter_10_value => p_layout_code);
    update con_contract_change_req t
       set t.wfl_instance_id = v_instance_id
     where t.change_req_id = p_change_req_rec.change_req_id;
  
  exception
    when no_data_found then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CON_CONTRACT_CHG_REQ_WFL_PKG.NO_DATA_FOUND',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'cus_con_change_req_wfl_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            => 'cus_con_change_req_wfl_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;

  procedure workflow_approve(p_change_req_id in number,
                             p_user_id       in number) is
  begin
    con_contract_change_req_pkg.change_req_confirm(p_change_req_id => p_change_req_id,
                                                   p_user_id       => p_user_id);
  end;

  procedure workflow_reject(p_change_req_id in number, p_user_id in number) is
  begin
    con_contract_change_req_pkg.change_req_reject(p_change_req_id => p_change_req_id,
                                                  p_user_id       => p_user_id);
  end;

  procedure workflow_cancel(p_change_req_id in number, p_user_id in number) is
  begin
    con_contract_change_req_pkg.change_req_cancel(p_change_req_id => p_change_req_id,
                                                  p_user_id       => p_user_id);
  end;
end cus_con_change_req_wfl_pkg;
/