con_repo_change_req_wfl_pkg.pck 8.18 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 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
create or replace package con_repo_change_req_wfl_pkg is

  -- Author  : Spencer 3893
  -- Created : 9/18/2019 4:40:41 PM
  -- 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 con_repo_change_req_wfl_pkg;
/
create or replace package body con_repo_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
    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;
  
    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       => 'CHANGE_REQ_ID',
                                    p_parameter_1_value => p_change_req_rec.change_req_id,
                                    p_parameter_2       => 'CHANGE_REQ_NUMBER',
                                    p_parameter_2_value => p_change_req_rec.change_req_number,
                                    p_parameter_3       => 'CONTRACT_ID',
                                    p_parameter_3_value => p_change_req_rec.contract_id,
                                    p_parameter_4       => 'CONTRACT_NUMBER',
                                    p_parameter_4_value => p_change_con_rec.contract_number,
                                    p_parameter_5       => 'OWNER_USER_ID',
                                    p_parameter_5_value => nvl(p_change_req_rec.owner_user_id,
                                                               p_user_id),
                                    p_parameter_6       => 'DOCUMENT_INFO',
                                    p_parameter_6_value => v_document_info,
                                    p_parameter_7       => 'OLD_CONTRACT_ID',
                                    p_parameter_7_value => p_change_req_rec.contract_id,
                                    p_parameter_8       => 'NEW_CONTRACT_ID',
                                    p_parameter_8_value => p_change_req_rec.change_req_id,
                                    --add by Harry 9952 2017/1/13
                                    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            => 'con_repo_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            => 'con_repo_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 con_repo_change_req_wfl_pkg;
/