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