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;
/