cus_con_change_req_pkg.pck 19.9 KB
Newer Older
1 2 3 4 5 6
create or replace package cus_con_change_req_pkg is

  -- Author  : Spencer 3893
  -- Created : 9/23/2019 4:25:37 PM
  -- Purpose : 合同變更申請

7 8 9 10 11 12 13 14 15 16 17 18
  --创建时维护默认的用印信息
  procedure insert_hls_document_use_seal(p_change_req_id       number,
                                         p_document_table      varchar2,
                                         p_instruments_types   varchar2,
                                         p_common_seal         varchar2,
                                         p_corporate_visa_seal varchar2,
                                         p_legal_seal          varchar2,
                                         p_con_special_seal    varchar2,
                                         p_financial_seal      varchar2,
                                         p_supervisio_seal     varchar2,
                                         p_seal_number         varchar2,
                                         p_user_id             number);
19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42
  procedure create_change_req(p_contract_id     number,
                              p_req_date        date,
                              p_req_type        varchar2,
                              p_description     varchar2,
                              p_user_id         number,
                              p_simulation_flag varchar2 default 'N',
                              p_ref_v01         number,
                              p_change_req_id   out number);
  --報價計算                             
  procedure calc_change_req(p_change_req_id         in number,
                            p_ccr_start_times       in number,
                            p_ccr_outstanding_times in number,
                            p_calc_session_id       in out number,
                            p_calc_successful       out varchar2,
                            p_warning_message       out varchar2,
                            p_user_id               in number);
  --承租人變更日計算應繳金額相關信息                           
  procedure calc_tenant_change_req(p_change_req_id       in number,
                                   p_ccr_lease_date      in date,
                                   p_ccr_lease_rental    out number, --应交租金
                                   p_ccr_lease_principal out number, --应交本金
                                   p_ccr_lease_interest  out number, --应交利息
                                   p_ccr_lease_penalty   out number, --应交罚息
                                   p_user_id             in number);
43 44 45 46
  --提交工作流                                
  procedure submit_change_req(p_change_req_id in number,
                              p_layout_code   in varchar2,
                              p_user_id       in number);
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
end cus_con_change_req_pkg;
/
create or replace package body cus_con_change_req_pkg is

  e_lock_table exception;
  pragma exception_init(e_lock_table, -54);

  function get_contract_rec(p_contract_id number, p_user_id number)
    return con_contract%rowtype is
    v_con_contract_rec con_contract%rowtype;
  begin
    select *
      into v_con_contract_rec
      from con_contract t
     where t.contract_id = p_contract_id
       for update nowait;
    return v_con_contract_rec;
  exception
    when e_lock_table then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CON_CONTRACT_PKG.CONTRACT_LOCK_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'cus_con_change_req_pkg',
                                                      p_procedure_function_name => 'get_contract_rec');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  end;
  function get_con_change_req_rec(p_change_req_id con_contract_change_req.change_req_id%type,
                                  p_user_id       con_contract_change_req.created_by%type)
    return con_contract_change_req%rowtype is
    v_change_req_rec con_contract_change_req%rowtype;
  begin
    select *
      into v_change_req_rec
      from con_contract_change_req t
     where t.change_req_id = p_change_req_id
       for update nowait;
    return v_change_req_rec;
  exception
    when e_lock_table then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CON_CONTRACT_CHANGE_REQ_PKG.CON_CHANGE_REQ_LOCK',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'cus_con_change_req_pkg',
                                                      p_procedure_function_name => 'get_con_change_req_rec');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  end;
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
  --创建时维护默认的用印信息
  procedure insert_hls_document_use_seal(p_change_req_id       number,
                                         p_document_table      varchar2,
                                         p_instruments_types   varchar2,
                                         p_common_seal         varchar2,
                                         p_corporate_visa_seal varchar2,
                                         p_legal_seal          varchar2,
                                         p_con_special_seal    varchar2,
                                         p_financial_seal      varchar2,
                                         p_supervisio_seal     varchar2,
                                         p_seal_number         varchar2,
                                         p_user_id             number) is
  begin
    insert into hls_document_use_seal
      (document_use_seal_id,
       document_table,
       document_id,
       instruments_types,
       common_seal,
       corporate_visa_seal,
       legal_seal,
       con_special_seal,
       financial_seal,
       supervisio_seal,
       seal_number,
       created_by,
       creation_date,
       last_updated_by,
       last_update_date)
    values
      (hls_document_use_seal_s.nextval,
       p_document_table,
       p_change_req_id,
       p_instruments_types,
       p_common_seal,
       p_corporate_visa_seal,
       p_legal_seal,
       p_con_special_seal,
       p_financial_seal,
       p_supervisio_seal,
       p_seal_number,
       p_user_id,
       sysdate,
       p_user_id,
       sysdate);
  end insert_hls_document_use_seal;
139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174
  procedure create_change_req(p_contract_id     number,
                              p_req_date        date,
                              p_req_type        varchar2,
                              p_description     varchar2,
                              p_user_id         number,
                              p_simulation_flag varchar2 default 'N',
                              p_ref_v01         number,
                              p_change_req_id   out number) is
    v_change_req_id number;
  
    v_contract_rec con_contract%rowtype;
  begin
    v_contract_rec := get_contract_rec(p_contract_id, p_user_id);
  
    if v_contract_rec.contract_status <> 'INCEPT' and
       p_req_type in ('CUT_CHAG', 'LEASE_CHAG', 'ACC_CHAG') then
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '只有起租状态的合同才能进行合同变更申请!',
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'cus_con_change_req_pkg',
                                                     p_procedure_function_name => 'create_change_req');
    
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    end if;
    con_contract_history_pkg.create_change_req(p_contract_id          => p_contract_id,
                                               p_change_req_id        => v_change_req_id,
                                               p_req_date             => p_req_date,
                                               p_ref_v01              => p_ref_v01,
                                               p_description          => p_description,
                                               p_change_quotation     => null,
                                               p_change_lease_item    => null,
                                               p_change_bp            => null,
                                               p_req_doc_type         => p_req_type,
                                               p_simulation_flag      => p_simulation_flag,
                                               p_cashflow_change_flag => 'Y',
                                               p_user_id              => p_user_id);
175 176 177
    if p_req_type = 'LEASE_CHAG' then
      cus_con_change_req_pkg.insert_hls_document_use_seal(p_change_req_id       => v_change_req_id,
                                                          p_document_table      => 'CON_CONTRACT_CHANGE_REQ',
178
                                                          p_instruments_types   => '权利义务转让文本',
179 180 181 182 183 184 185 186 187
                                                          p_common_seal         => 'Y',
                                                          p_corporate_visa_seal => 'Y',
                                                          p_legal_seal          => 'N',
                                                          p_con_special_seal    => 'N',
                                                          p_financial_seal      => 'N',
                                                          p_supervisio_seal     => 'N',
                                                          p_seal_number         => 'SEAL_O_TW_NUMBER',
                                                          p_user_id             => p_user_id);
    end if;
188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332
    p_change_req_id := v_change_req_id;
  end create_change_req;

  --報價計算                             
  procedure calc_change_req(p_change_req_id         in number,
                            p_ccr_start_times       in number,
                            p_ccr_outstanding_times in number,
                            p_calc_session_id       in out number,
                            p_calc_successful       out varchar2,
                            p_warning_message       out varchar2,
                            p_user_id               in number) is
    r_change_req            con_contract_change_req%rowtype;
    r_contract_rec          con_contract%rowtype;
    v_ccr_outstanding_incld number;
    v_exists                number;
    v_count                 number;
  begin
    r_change_req   := get_con_change_req_rec(p_change_req_id, p_user_id);
    r_contract_rec := get_contract_rec(p_change_req_id, p_user_id);
  
    --根据开始期数计算未到期本金
    select nvl(sum(nvl(ccc.principal, 0) - nvl(ccc.received_principal, 0)),
               0) sum_ccr_outstanding_incld
      into v_ccr_outstanding_incld
      from con_contract_cashflow ccc
     where ccc.contract_id = r_change_req.contract_id
       and ccc.write_off_flag <> 'FULL'
       and ccc.cf_item = 1
       and ccc.cf_direction = 'INFLOW'
       and ccc.cf_status = 'RELEASE'
       and ccc.times >= p_ccr_start_times;
  
    --核销的不能进行更改,要从没核销的期数开始修改
    select count(1)
      into v_count
      from con_contract_cashflow ccc
     where ccc.contract_id = r_change_req.contract_id
       and ccc.times >= p_ccr_start_times
       and ccc.cf_item <> 304
       and ccc.cf_direction = 'INFLOW'
       and ccc.cf_status = 'RELEASE'
       and ccc.write_off_flag <> 'NOT';
    if v_count > 0 then
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '延期开始期数之后的期数存在核销,请选择正确的日期或者进行核销反冲!',
                                                     p_created_by              => -1,
                                                     p_package_name            => 'cus_con_change_req_pkg',
                                                     p_procedure_function_name => 'calc_change_req');
    
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    end if;
  
    if r_change_req.ccr_start_times is not null then
      begin
        select 1
          into v_exists
          from dual
         where exists (select 1
                  from con_contract_cashflow a
                 where contract_id = p_change_req_id
                   and cf_type = 1
                   and times >= r_change_req.ccr_start_times
                   and nvl(a.received_amount, 0) <> 0);
        sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CON_CHANGE_REQ_CALC_ITFC_PKG.CCR_START_TIMES_ERROR',
                                                        p_created_by              => p_user_id,
                                                        p_package_name            => 'cus_con_change_req_pkg',
                                                        p_procedure_function_name => 'calc_change_req');
        raise_application_error(sys_raise_app_error_pkg.c_error_number,
                                sys_raise_app_error_pkg.g_err_line_id);
      exception
        when no_data_found then
          null;
      end;
    end if;
  
    select hls_fin_calculator_hd_s.nextval
      into r_contract_rec.calc_session_id
      from dual;
  
    hls_fin_calculator_ccr_pkg.create_calculate(p_change_req_id      => p_change_req_id,
                                                p_calc_session_id    => r_contract_rec.calc_session_id,
                                                p_recreate_h_formula => 'Y', --modify 2017年12月18日16:40:01
                                                p_recreate_l_std     => 'Y',
                                                p_recreate_l_formula => 'Y',
                                                p_calc_successful    => p_calc_successful,
                                                p_warning_message    => p_warning_message,
                                                p_user_id            => p_user_id);
  
    update con_contract_change_req
       set calc_session_id     = r_contract_rec.calc_session_id,
           ccr_calc_session_id = r_contract_rec.calc_session_id,
           is_calc_flag        = 'Y'
     where change_req_id = p_change_req_id;
  
    update con_contract
       set calc_session_id = r_contract_rec.calc_session_id
     where contract_id = p_change_req_id;
  
    p_calc_session_id := r_contract_rec.calc_session_id;
  
  end calc_change_req;
  --承租人變更日計算應繳金額相關信息                           
  procedure calc_tenant_change_req(p_change_req_id       in number,
                                   p_ccr_lease_date      in date,
                                   p_ccr_lease_rental    out number, --应交租金
                                   p_ccr_lease_principal out number, --应交本金
                                   p_ccr_lease_interest  out number, --应交利息
                                   p_ccr_lease_penalty   out number, --应交罚息
                                   p_user_id             in number) is
    v_ccr_lease_rental    number; --应交租金
    v_ccr_lease_principal number; --应交本金
    v_ccr_lease_interest  number; --应交利息
    v_ccr_lease_penalty   number; --应交罚息
  begin
    select nvl(sum(f.due_amount), 0),
           nvl(sum(f.principal), 0),
           nvl(sum(f.interest), 0)
      into v_ccr_lease_rental, v_ccr_lease_principal, v_ccr_lease_interest
      from con_contract_cashflow f
     where f.due_date <= p_ccr_lease_date
       and f.write_off_flag = 'NOT'
       and f.cf_item = 1
       and f.cf_type = 1
       and f.cf_direction = 'INFLOW'
       and f.contract_id = p_change_req_id;
  
    select nvl(sum(f.due_amount), 0)
      into v_ccr_lease_penalty
      from con_contract_cashflow f
     where f.due_date <= p_ccr_lease_date
       and f.write_off_flag = 'NOT'
       and f.cf_item = 9
       and f.cf_type = 9
       and f.cf_direction = 'INFLOW'
       and f.contract_id = p_change_req_id;
  
    p_ccr_lease_rental    := v_ccr_lease_rental;
    p_ccr_lease_principal := v_ccr_lease_principal;
    p_ccr_lease_interest  := v_ccr_lease_interest;
    p_ccr_lease_penalty   := v_ccr_lease_penalty;
  
  exception
    when others then
      null;
  end calc_tenant_change_req;
333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391
  --提交工作流                                
  procedure submit_change_req(p_change_req_id in number,
                              p_layout_code   in varchar2,
                              p_user_id       in number) is
    r_change_req_rec con_contract_change_req%rowtype;
    r_change_con_rec con_contract%rowtype;
  
    v_approval_method hls_document_type.approval_method%type;
  
    e_change_req_status_error exception;
    e_is_calc_flag_err        exception;
  begin
    hls_document_compare_pkg.con_contract_compare(p_table_name    => 'CON_CONTRACT',
                                                  p_change_req_id => p_change_req_id,
                                                  p_from_table_pk => '',
                                                  p_to_table_pk   => p_change_req_id,
                                                  p_user_id       => p_user_id);
  
    r_change_req_rec := get_con_change_req_rec(p_change_req_id => p_change_req_id,
                                               p_user_id       => p_user_id);
    r_change_con_rec := con_contract_pkg.get_contract_rec(p_contract_id => p_change_req_id,
                                                          p_user_id     => p_user_id);
  
    if r_change_req_rec.req_status not in ('NEW', 'REJECT') then
      raise e_change_req_status_error;
    end if;
    --租金計劃變更需要校驗是否計算報價
    if p_layout_code = 'CON_CHANGE_MODIFY' then
      if nvl(r_change_req_rec.is_calc_flag, 'N') = 'N' then
        raise e_is_calc_flag_err;
      end if;
    end if;
    con_change_req_custom_pkg.before_submit_change_req(p_change_req_id => p_change_req_id,
                                                       p_user_id       => p_user_id);
  
    con_contract_history_pkg.calc_change_history(p_change_req_id => p_change_req_id,
                                                 p_user_id       => p_user_id);
  
    cus_con_change_req_wfl_pkg.workflow_start(p_change_req_rec => r_change_req_rec,
                                              p_change_con_rec => r_change_con_rec,
                                              p_layout_code    => p_layout_code,
                                              p_user_id        => p_user_id);
  
  exception
    when e_change_req_status_error then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CON_CONTRACT_CHANGE_REQ_PKG.CHANGE_REQ_STATUS_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'cus_con_change_req_pkg',
                                                      p_procedure_function_name => 'submit_change_req');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    when e_is_calc_flag_err then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CON_CONTRACT_CHANGE_REQ_PKG.IS_CALC_FLAG_ERR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'cus_con_change_req_pkg',
                                                      p_procedure_function_name => 'submit_change_req');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  end submit_change_req;
392 393
end cus_con_change_req_pkg;
/