create or replace package cus_con_contract_repo_pkg is -- Author : Spencer 3893 -- Created : 9/10/2019 2:21:13 PM -- Purpose : ��ͬ�ع� function get_contract_repo_number(p_document_type varchar2, p_document_date date, p_company_id number, p_user_id number, p_contract_id number default null) return varchar2; --add by Spencer 3893 20190911 �ع����� procedure create_change_req_repo(p_contract_id in number, p_req_type in varchar2, p_user_id in number, p_simulation_flag in varchar2 default 'N', p_change_req_id out number); /* --�ع��ֽ������� procedure calc_repo_cashflows(p_contract_id in number, p_user_id in number);*/ --ͨ���ع���Լ�ջ�ȡ�ع������Ϣ procedure calc_repo_date_amount(p_contract_id in number, p_repurchase_date date, p_last_rent_due_date out date, p_sum_received_rent_amount out number, p_sum_unreceived_rent_amount out number, p_sum_overdue_amount out number, p_sum_unreceived_principal out number, p_due_amount out number, p_penalty out number, --���ڷ�Ϣ p_fund_possession_cost out number, --�ʽ�ռ�÷� p_user_id in number); --�ύ������ procedure submit_change_req_repo(p_change_req_id in number, p_layout_code in varchar2, p_user_id in number); --�����������д����ֽ��� procedure calc_repo_cashflow(p_contract_id in number, p_calc_session_id IN NUMBER, p_user_id IN NUMBER, p_msg OUT VARCHAR2); --һ���Իع������ֽ��� PROCEDURE calc_one_time_repo_cashflow(p_contract_id IN NUMBER, p_user_id IN NUMBER); --�ع��������������ı�֤���Զ��ֿۺ��� PROCEDURE calc_cashflow_division_tj(p_contract_id IN NUMBER, p_user_id IN NUMBER); --�ܾ����ڵ�����ͨ��ʱ����Ϣ֪ͨ��Ʋ��������ࣩ������Ϲ沿�����Խݣ� PROCEDURE repo_wfl_approved_mail(p_change_req_id number, p_user_id IN NUMBER); --��������ʱ�������ύ�� PROCEDURE repo_wfl_mail_to_submit(p_change_req_id number, p_user_id IN NUMBER, p_wfl_flag_desc varchar2); --�����ͬ��Լ�ع���˰ procedure deal_repur_cash_leaseback(p_contract_id number, p_interest out number); end cus_con_contract_repo_pkg; / create or replace package body cus_con_contract_repo_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_contract_repo_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_contract_repo_number(p_document_type varchar2, p_document_date date, p_company_id number, p_user_id number, p_contract_id number default null) return varchar2 is v_no con_contract.repurchase_contract_number%type; v_agent_bp_code varchar2(30); v_price_ref_v05 varchar2(200); v_product_code varchar2(2000); e_product_code_err exception; e_get_contract_number_err exception; begin begin select hbm.bp_code into v_agent_bp_code from con_contract_bp t, hls_bp_master hbm where t.contract_id = p_contract_id and t.bp_category = 'AGENT' and t.enabled_flag = 'Y' and t.bp_id = hbm.bp_id and rownum = 1; exception when no_data_found then null; end; begin select l.ref_v05 into v_price_ref_v05 from con_contract t, hls_price_list l where t.contract_id = p_contract_id and t.price_list = l.price_list; exception when no_data_found then null; end; v_no := fnd_code_rule_pkg.get_rule_next_auto_num(p_document_category => 'CHANGE_REQUEST', p_document_type => p_document_type, p_company_id => p_company_id, p_operation_unit_id => null, p_operation_date => p_document_date, p_agent_code => v_agent_bp_code, p_price_ref_v05 => v_price_ref_v05, p_product_code => v_product_code, p_created_by => p_user_id); if v_no = fnd_code_rule_pkg.c_error then raise e_get_contract_number_err; end if; return v_no; exception when e_get_contract_number_err then sys_raise_app_error_pkg.raise_sys_others_error(p_message => '��ȡ����ʧ��,����ϵϵͳ����Ա��', p_created_by => p_user_id, p_package_name => 'cus_con_contract_repo_pkg', p_procedure_function_name => 'get_contract_repo_number'); 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_contract_repo_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; --add by Spencer 3893 20190911 �ع����� procedure create_change_req_repo(p_contract_id in number, p_req_type in varchar2, p_user_id in number, p_simulation_flag in varchar2 default 'N', p_change_req_id out number) is v_change_req_id number; v_contract_rec con_contract%rowtype; v_calc_session_id number; r_hls_fin_calculator_hd hls_fin_calculator_hd%rowtype; v_con_change_re_rec con_contract_change_req%rowtype; --�¹ʵ�I-10185 v_count number; begin v_contract_rec := get_contract_rec(p_contract_id, p_user_id); --��ʷ��ͬǨ�ƹ����ޱ��ۣ��ֶ�insert ͷ�� v_calc_session_id := v_contract_rec.calc_session_id; if v_calc_session_id is null then r_hls_fin_calculator_hd := null; v_calc_session_id := hls_fin_calculator_hd_s.nextval; r_hls_fin_calculator_hd.calc_session_id := v_calc_session_id; r_hls_fin_calculator_hd.company_id := v_contract_rec.company_id; r_hls_fin_calculator_hd.price_list := v_contract_rec.price_list; insert into hls_fin_calculator_hd values r_hls_fin_calculator_hd; update con_contract c set c.calc_session_id = v_calc_session_id where c.contract_id = v_contract_rec.contract_id; end if; --�¹ʵ�I-10185 �����Լ�ع�������ڸ�����ֽ��������ܷ����Լ�ع� select count(1) into v_count from csh_payment_req_hd cph, csh_payment_req_ln ln where cph.payment_req_id = ln.payment_req_id and ln.ref_doc_id = p_contract_id and ln.ref_doc_line_id in (select t.cashflow_id from con_contract_cashflow t where t.contract_id = p_contract_id and t.cf_direction = 'OUTFLOW' and t.write_off_flag <> 'FULL' and t.cf_item = '0') and cph.approval_status in('NEW','APPROVING'); if v_count>0 then sys_raise_app_error_pkg.raise_sys_others_error(p_message => '�ú�ͬ�Ѿ������˸�������������ڸ������������У����ܷ����Լ�ع���', p_created_by => p_user_id, p_package_name => 'cus_con_contract_repo_pkg', p_procedure_function_name => 'create_change_req_repo'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end if; if v_contract_rec.contract_status <> 'INCEPT' and p_req_type = 'REPUR' then sys_raise_app_error_pkg.raise_sys_others_error(p_message => 'ֻ������״̬�ĺ�ͬ���ܽ��лع����룡', p_created_by => p_user_id, p_package_name => 'cus_con_contract_repo_pkg', p_procedure_function_name => 'create_change_req_repo'); 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 => sysdate, p_ref_v01 => p_user_id, p_description => null, 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_repo_change_flag => 'Y', p_user_id => p_user_id); /* --���ɻع���� v_contract_rec.repurchase_contract_number := get_contract_repo_number(p_document_type => p_req_type, p_document_date => sysdate, p_company_id => v_contract_rec.company_id, p_user_id => p_user_id);*/ select * into v_con_change_re_rec from con_contract_change_req c where c.change_req_id = v_change_req_id; --���»ع���ŵ���������е��º�ͬ update con_contract c set c.repurchase_contract_number = v_con_change_re_rec.change_req_number, c.last_updated_by = p_user_id, c.last_update_date = sysdate where c.contract_id = v_change_req_id; 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', p_instruments_types => '�����������ͬЭ��', 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_F_NUMBER', p_user_id => p_user_id); 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', p_instruments_types => 'ծȨծ�����Э��', 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_F_NUMBER', p_user_id => p_user_id); 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', p_instruments_types => '������ع�����Э��(���°�)', 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_F_NUMBER', p_user_id => p_user_id); p_change_req_id := v_change_req_id; end; procedure delete_cashflow(p_contract_id number, p_times number) is begin delete from con_contract_cashflow cf where cf.times > p_times and cf.cf_item in (1, 9) and cf.contract_id = p_contract_id; end; -- �����Ѵ��� procedure sc_cashflow_hander(p_contract_id number, p_service_charges number, p_paymengt_deadlinedate date, p_times number, p_user_id in number) is v_cashflow_rec con_contract_cashflow%rowtype; v_outstanding_principal number; --ʣ�౾�� v_principal number; --���� v_interest number; --��Ϣ v_due_date date; begin v_interest := round(p_service_charges * 0.13 / 1.13, 2); v_principal := round(p_service_charges / 1.13, 2); v_outstanding_principal := 0; v_due_date := p_paymengt_deadlinedate; v_cashflow_rec.cashflow_id := con_contract_cashflow_s.nextval; v_cashflow_rec.contract_id := p_contract_id; v_cashflow_rec.cf_item := 304; v_cashflow_rec.cf_type := 3; v_cashflow_rec.cf_direction := 'INFLOW'; v_cashflow_rec.cf_status := 'RELEASE'; v_cashflow_rec.times := p_times; v_cashflow_rec.calc_date := v_due_date; v_cashflow_rec.due_date := v_due_date; v_cashflow_rec.due_amount := p_service_charges; v_cashflow_rec.net_due_amount := round(p_service_charges / 1.13, 2); v_cashflow_rec.vat_due_amount := round(p_service_charges * 0.13 / 1.13, 2); v_cashflow_rec.principal := v_principal; v_cashflow_rec.net_principal := round(v_principal / 1.13, 2); v_cashflow_rec.vat_principal := round(v_principal * 0.13 / 1.13, 2); v_cashflow_rec.interest := v_interest; v_cashflow_rec.net_interest := round(v_interest / 1.13, 2); v_cashflow_rec.vat_interest := round(v_interest * 0.13 / 1.13, 2); v_cashflow_rec.received_amount := 0; v_cashflow_rec.received_principal := 0; v_cashflow_rec.received_interest := 0; v_cashflow_rec.outstanding_prin_tax_incld := v_outstanding_principal; v_cashflow_rec.write_off_flag := 'NOT'; v_cashflow_rec.overdue_status := 'NOT'; v_cashflow_rec.penalty_process_status := 'NORMAL'; v_cashflow_rec.billing_status := 'N'; v_cashflow_rec.generated_source := 'MANUAL'; v_cashflow_rec.created_by := p_user_id; v_cashflow_rec.creation_date := sysdate; v_cashflow_rec.last_updated_by := p_user_id; v_cashflow_rec.last_update_date := sysdate; insert into con_contract_cashflow values v_cashflow_rec; end sc_cashflow_hander; -- Refactored procedure fund_prossession_cost_hander procedure fund_prossession_cost_hander(p_contract_id number, p_fund_possession_cost number, p_paymengt_deadlinedate date, p_times number, p_user_id number) is v_cashflow_rec con_contract_cashflow%rowtype; v_outstanding_principal number; --ʣ�౾�� v_principal number; --���� v_interest number; --��Ϣ v_due_date date; begin v_interest := round(p_fund_possession_cost * 0.13 / 1.13, 2); v_principal := round(p_fund_possession_cost / 1.13, 2); v_outstanding_principal := 0; v_due_date := p_paymengt_deadlinedate; v_cashflow_rec.cashflow_id := con_contract_cashflow_s.nextval; v_cashflow_rec.contract_id := p_contract_id; v_cashflow_rec.cf_item := 14; v_cashflow_rec.cf_type := 14; v_cashflow_rec.cf_direction := 'INFLOW'; v_cashflow_rec.cf_status := 'RELEASE'; v_cashflow_rec.times := p_times; v_cashflow_rec.calc_date := v_due_date; v_cashflow_rec.due_date := v_due_date; v_cashflow_rec.due_amount := p_fund_possession_cost; v_cashflow_rec.net_due_amount := round(p_fund_possession_cost / 1.13, 2); v_cashflow_rec.vat_due_amount := round(p_fund_possession_cost * 0.13 / 1.13, 2); v_cashflow_rec.principal := v_principal; v_cashflow_rec.net_principal := round(v_principal / 1.13, 2); v_cashflow_rec.vat_principal := round(v_principal * 0.13 / 1.13, 2); v_cashflow_rec.interest := v_interest; v_cashflow_rec.net_interest := round(v_interest / 1.13, 2); v_cashflow_rec.vat_interest := round(v_interest * 0.13 / 1.13, 2); v_cashflow_rec.received_amount := 0; v_cashflow_rec.received_principal := 0; v_cashflow_rec.received_interest := 0; v_cashflow_rec.outstanding_prin_tax_incld := v_outstanding_principal; v_cashflow_rec.write_off_flag := 'NOT'; v_cashflow_rec.overdue_status := 'NOT'; v_cashflow_rec.penalty_process_status := 'NORMAL'; v_cashflow_rec.billing_status := 'N'; v_cashflow_rec.generated_source := 'MANUAL'; v_cashflow_rec.created_by := p_user_id; v_cashflow_rec.creation_date := sysdate; v_cashflow_rec.last_updated_by := p_user_id; v_cashflow_rec.last_update_date := sysdate; insert into con_contract_cashflow values v_cashflow_rec; end fund_prossession_cost_hander; /* --�ع��ֽ������� procedure calc_repo_cashflows(p_contract_id in number, p_user_id in number) is v_contract_rec con_contract%rowtype; v_cashflow_rec con_contract_cashflow%rowtype; v_due_amount number; --ÿ����� v_outstanding_principal number; --ʣ�౾�� v_principal number; --���� v_interest number; --��Ϣ v_due_date date; v_calc_due_date date; v_times number; v_calc_days number; e_due_amount_error exception; e_repo_calc_amount_error exception; begin --�ع���ͬ v_contract_rec := get_contract_rec(p_contract_id, p_user_id); select * into v_cashflow_rec from con_contract_cashflow f where f.due_date = v_contract_rec.last_rent_due_date and f.cf_item = 1 and f.cf_type = 1 and f.contract_id = p_contract_id; --����ǰɾ�����α����ʼ�ڼ�֮���ڣ� delete_cashflow(p_contract_id, v_cashflow_rec.times); -- һ���Իع� if v_contract_rec.repurchase_types = 'ONE_TIME_REPURCHASE' then v_times := v_cashflow_rec.times + 1; v_cashflow_rec := null; v_interest := round(v_contract_rec.repurchase_tatal_amount * 0.13 / 1.13, 2); v_principal := round(v_contract_rec.repurchase_tatal_amount / 1.13, 2); v_outstanding_principal := 0; v_due_date := v_contract_rec.paymengt_deadlinedate; v_cashflow_rec.cashflow_id := con_contract_cashflow_s.nextval; v_cashflow_rec.contract_id := v_contract_rec.contract_id; v_cashflow_rec.cf_item := 1; v_cashflow_rec.cf_type := 1; v_cashflow_rec.cf_direction := 'INFLOW'; v_cashflow_rec.cf_status := 'RELEASE'; v_cashflow_rec.times := v_times; v_cashflow_rec.calc_date := v_due_date; v_cashflow_rec.due_date := v_due_date; v_cashflow_rec.due_amount := v_contract_rec.repurchase_tatal_amount; v_cashflow_rec.net_due_amount := round(v_contract_rec.repurchase_tatal_amount / 1.13, 2); v_cashflow_rec.vat_due_amount := round(v_contract_rec.repurchase_tatal_amount * 0.13 / 1.13, 2); v_cashflow_rec.principal := v_principal; v_cashflow_rec.net_principal := round(v_principal / 1.13, 2); v_cashflow_rec.vat_principal := round(v_principal * 0.13 / 1.13, 2); v_cashflow_rec.interest := v_interest; v_cashflow_rec.net_interest := round(v_interest / 1.13, 2); v_cashflow_rec.vat_interest := round(v_interest * 0.13 / 1.13, 2); v_cashflow_rec.received_amount := 0; v_cashflow_rec.received_principal := 0; v_cashflow_rec.received_interest := 0; v_cashflow_rec.outstanding_prin_tax_incld := v_outstanding_principal; v_cashflow_rec.write_off_flag := 'NOT'; v_cashflow_rec.overdue_status := 'NOT'; v_cashflow_rec.penalty_process_status := 'NORMAL'; v_cashflow_rec.billing_status := 'N'; v_cashflow_rec.generated_source := 'MANUAL'; v_cashflow_rec.created_by := p_user_id; v_cashflow_rec.creation_date := sysdate; v_cashflow_rec.last_updated_by := p_user_id; v_cashflow_rec.last_update_date := sysdate; insert into con_contract_cashflow values v_cashflow_rec; --������ if v_contract_rec.repurchase_service_charges is not null and v_contract_rec.repurchase_service_charges > 0 then v_cashflow_rec := null; sc_cashflow_hander(p_contract_id => v_contract_rec.contract_id, p_service_charges => v_contract_rec.repurchase_service_charges, p_paymengt_deadlinedate => v_contract_rec.paymengt_deadlinedate, p_times => v_times, p_user_id => p_user_id); end if; --�ʽ�ռ�÷� if v_contract_rec.fund_possession_cost is not null and v_contract_rec.fund_possession_cost > 0 then v_cashflow_rec := null; fund_prossession_cost_hander(p_contract_id => v_contract_rec.contract_id, p_fund_possession_cost => v_contract_rec.fund_possession_cost, p_paymengt_deadlinedate => v_contract_rec.paymengt_deadlinedate, p_times => v_times, p_user_id => p_user_id); end if; --���ڻع� elsif v_contract_rec.repurchase_types = 'STAGE_REPURCHASE' then v_outstanding_principal := v_contract_rec.repurchase_tatal_amount; --�ع��ۿ��ܶ� if v_outstanding_principal > 0 then -- ������� select round(abs(hls_financial_calculate_pkg.pmt(nvl(v_contract_rec.repurchase_interest_rate, v_contract_rec.int_rate_display) / 12, --����������/֧��Ƶ�� v_contract_rec.payment_period, --�������� v_outstanding_principal, --ʣ�౾�� 0, 0)), 2) into v_due_amount from dual; if nvl(v_due_amount, 0) <= 0 then hls_sys_log_pkg.log('�ع���ͬ��������쳣���������ʣ�' || v_contract_rec.repurchase_interest_rate || ' ,֧��Ƶ�ʣ�' || v_contract_rec.payment_frequency || ' ,�������ޣ�' || v_contract_rec.payment_period || ' ,�ع��ۿ��ܶ' || v_outstanding_principal); raise e_due_amount_error; end if; v_due_date := v_contract_rec.paymengt_deadlinedate; v_times := v_cashflow_rec.times + 1; for cur in 1 .. v_contract_rec.payment_period loop v_cashflow_rec := null; --���һ�� if cur = v_contract_rec.payment_period then v_interest := v_due_amount - v_outstanding_principal; v_principal := v_outstanding_principal; v_outstanding_principal := 0; else if cur >= 2 then v_calc_due_date := add_months(v_due_date, 1); v_calc_days := v_calc_due_date - v_due_date; v_interest := round(v_outstanding_principal * nvl(v_calc_days, 1) * (nvl(v_contract_rec.repurchase_interest_rate, v_contract_rec.int_rate_display) / 360), 2); else --��һ����Ϣ���� v_interest := round(v_outstanding_principal * 1 * (nvl(v_contract_rec.repurchase_interest_rate, v_contract_rec.int_rate_display) / 360), 2); end if; v_principal := v_due_amount - v_interest; v_outstanding_principal := v_outstanding_principal - v_principal; end if; v_cashflow_rec.cashflow_id := con_contract_cashflow_s.nextval; v_cashflow_rec.contract_id := v_contract_rec.contract_id; v_cashflow_rec.cf_item := 1; v_cashflow_rec.cf_type := 1; v_cashflow_rec.cf_direction := 'INFLOW'; v_cashflow_rec.cf_status := 'RELEASE'; v_cashflow_rec.times := v_times; v_cashflow_rec.calc_date := v_due_date; v_cashflow_rec.due_date := v_due_date; v_cashflow_rec.due_amount := v_due_amount; v_cashflow_rec.net_due_amount := round(v_due_amount / 1.13, 2); v_cashflow_rec.vat_due_amount := round(v_due_amount * 0.13 / 1.13, 2); v_cashflow_rec.principal := v_principal; v_cashflow_rec.net_principal := round(v_principal / 1.13, 2); v_cashflow_rec.vat_principal := round(v_principal * 0.13 / 1.13, 2); v_cashflow_rec.interest := v_interest; v_cashflow_rec.net_interest := round(v_interest / 1.13, 2); v_cashflow_rec.vat_interest := round(v_interest * 0.13 / 1.13, 2); v_cashflow_rec.received_amount := 0; v_cashflow_rec.received_principal := 0; v_cashflow_rec.received_interest := 0; v_cashflow_rec.outstanding_prin_tax_incld := v_outstanding_principal; v_cashflow_rec.write_off_flag := 'NOT'; v_cashflow_rec.overdue_status := 'NOT'; v_cashflow_rec.penalty_process_status := 'NORMAL'; v_cashflow_rec.billing_status := 'N'; v_cashflow_rec.generated_source := 'MANUAL'; v_cashflow_rec.created_by := p_user_id; v_cashflow_rec.creation_date := sysdate; v_cashflow_rec.last_updated_by := p_user_id; v_cashflow_rec.last_update_date := sysdate; insert into con_contract_cashflow values v_cashflow_rec; --����ڲ���ҪУ��ʣ�౾���Ƿ�С��0 if cur < 6 then v_due_date := add_months(v_due_date, 1); v_times := v_times + 1; if v_outstanding_principal < 0 then raise e_repo_calc_amount_error; end if; end if; end loop; --������ if v_contract_rec.repurchase_service_charges is not null and v_contract_rec.repurchase_service_charges > 0 then v_cashflow_rec := null; sc_cashflow_hander(p_contract_id => v_contract_rec.contract_id, p_service_charges => v_contract_rec.repurchase_service_charges, p_paymengt_deadlinedate => v_contract_rec.paymengt_deadlinedate, p_times => v_times, p_user_id => p_user_id); end if; --�ʽ�ռ�÷� if v_contract_rec.fund_possession_cost is not null and v_contract_rec.fund_possession_cost > 0 then v_cashflow_rec := null; fund_prossession_cost_hander(p_contract_id => v_contract_rec.contract_id, p_fund_possession_cost => v_contract_rec.fund_possession_cost, p_paymengt_deadlinedate => v_contract_rec.paymengt_deadlinedate, p_times => v_times, p_user_id => p_user_id); end if; elsif v_outstanding_principal = 0 then raise e_repo_calc_amount_error; end if; end if; exception when e_repo_calc_amount_error then sys_raise_app_error_pkg.raise_sys_others_error(p_message => '�ع��ۿ��ܶ�С��0�����飡', p_created_by => -1, p_package_name => 'cus_con_contract_repo_pkg', p_procedure_function_name => 'calc_repo_cashflows'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); when e_due_amount_error then sys_raise_app_error_pkg.raise_sys_others_error(p_message => '�ع���ͬ����������������ϵϵͳ����Ա��', p_created_by => p_user_id, p_package_name => 'cus_con_contract_repo_pkg', p_procedure_function_name => 'calc_repo_cashflows'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end calc_repo_cashflows;*/ --ͨ���ع���Լ�ջ�ȡ�ع������Ϣ procedure calc_repo_date_amount(p_contract_id in number, p_repurchase_date date, p_last_rent_due_date out date, p_sum_received_rent_amount out number, p_sum_unreceived_rent_amount out number, p_sum_overdue_amount out number, p_sum_unreceived_principal out number, p_due_amount out number, p_penalty out number, --���ڷ�Ϣ p_fund_possession_cost out number, --�ʽ�ռ�÷� -- p_sum_received_penalty_amount out number, --����ΥԼ�� p_user_id in number) is v_contract_rec con_contract%rowtype; v_old_contract_rec con_contract%rowtype; v_cashflow_rec con_contract_cashflow%rowtype; v_times number; v_last_rent_due_date date; --�ѻ������ v_sum_received_rent_amount number; --ʣ����� v_sum_unreceived_rent_amount number; --������� v_sum_overdue_amount number; --ʣ�౾�� v_sum_unreceived_principal number; -- ���ڷ�Ϣ v_penalty number; --����ΥԼ�� v_sum_received_penalty_amount number; v_due_amount number; v_overdue_max_days number; v_count number := 0; v_et_date date; e_et_date_err exception; e_write_off_flagg_err exception; e_error exception; v_ccr_start_times number; r_con_contract_change_req con_contract_change_req%ROWTYPE; v_fund_possession_rate number; v_fund_possession_cost number; v_fund_possession_time number; e_defin_error exception; e_err_msg varchar2(2000); v_max_penalty_date date; v_max_error_date date; v_max_due_date date; begin select * into r_con_contract_change_req from con_contract_change_req t where t.change_req_id = p_contract_id; --���ǰ��ͬ select * into v_old_contract_rec from con_contract t where t.contract_id = r_con_contract_change_req.contract_id; select count(1) into v_count from con_contract_cashflow ccc where ccc.contract_id = v_old_contract_rec.contract_id and decode(ccc.cf_item, 1, ccc.due_date, 8, ccc.due_date, 9, (select due_date from con_contract_cashflow where contract_id = v_old_contract_rec.contract_id and cf_item = 1 and times = ccc.times)) >= p_repurchase_date and ccc.cf_item in (1, 8, 9) and ccc.cf_direction = 'INFLOW' and ccc.cf_status = 'RELEASE' and ((nvl(ccc.write_off_flag, 'NOT') <> 'NOT') OR (nvl(ccc.billing_status, 'NOT') <> 'NOT')); if v_count > 0 then raise e_error; end if; v_count := 0; begin select max(decode(ccc.cf_item, 1, ccc.due_date, 8, ccc.due_date, 9, (select due_date from con_contract_cashflow where contract_id = v_old_contract_rec.contract_id and cf_item = 1 and times = ccc.times))) into v_max_error_date from con_contract_cashflow ccc where ccc.contract_id = v_old_contract_rec.contract_id and ccc.cf_item in (1, 8, 9) and ccc.cf_direction = 'INFLOW' and ccc.cf_status = 'RELEASE' and nvl(ccc.write_off_flag, 'NOT') <> 'NOT'; exception when no_data_found then v_max_error_date := null; end; if v_max_error_date is not null and v_max_error_date > p_repurchase_date then e_err_msg := '��Լ�ع����������ڻ�֮������,������,ΥԼ���Ѵ��ڻ���Ϳ�Ʊ��¼�����ȷ��壡'; raise e_defin_error; end if; /* select max(a.due_date) into v_et_date from con_contract_cashflow a where a.cf_item = 1 and a.contract_id = v_old_contract_rec.contract_id; if v_et_date < p_repurchase_date then raise e_et_date_err; end if;*/ select max(a.times) into v_times from con_contract_cashflow a where decode(a.cf_item, 1, a.due_date, 9, (select due_date from con_contract_cashflow where contract_id = v_old_contract_rec.contract_id and cf_item = 1 and times = a.times)) >= p_repurchase_date and a.cf_item in (1, 9) and a.write_off_flag in ('PARTIAL', 'FULL') and a.contract_id = v_old_contract_rec.contract_id; if v_times >= 1 then raise e_write_off_flagg_err; end if; /* v_count := 0; select count(1) into v_count from con_contract_cashflow ccc where ccc.contract_id = v_old_contract_rec.contract_id and decode(ccc.cf_item, 1, ccc.due_date, 9, (select due_date from con_contract_cashflow where contract_id = v_old_contract_rec.contract_id and cf_item = 1 and times = ccc.times)) < trunc(p_repurchase_date) and ccc.cf_item in (1, 9) and ccc.cf_direction = 'INFLOW' and ccc.cf_status = 'RELEASE' and (nvl(ccc.write_off_flag, 'NOT') <> 'FULL') and decode(ccc.cf_item, 1, ccc.due_date, 9, (select due_date from con_contract_cashflow where contract_id = v_old_contract_rec.contract_id and cf_item = 1 and times = ccc.times)) > trunc(sysdate); if v_count > 0 then e_err_msg := '��Լ�ع���֮ǰ�����,��������δ��ȫ������δ���ڵ����!'; raise e_defin_error; end if;*/ v_contract_rec := get_contract_rec(p_contract_id, p_user_id); select min(a.times) into v_ccr_start_times from con_contract_cashflow a where a.due_date >= p_repurchase_date and a.cf_item = 1 and a.cf_direction != 'NONCASH' and a.contract_id = v_old_contract_rec.contract_id; if v_ccr_start_times is null then v_ccr_start_times := v_old_contract_rec.lease_times + 1; end if; --ǰ�����֧��Լ���� --ȡ����ƻ���С�ڡ���Լ�ع��ա��ġ���������ġ����Ӧ���յ����ֵ begin select max(f.due_date) into v_last_rent_due_date from con_contract_cashflow f where f.due_date < p_repurchase_date and f.cf_item = 1 and f.cf_type = 1 -- and nvl(f.due_amount, 0) > 0 and f.contract_id = v_old_contract_rec.contract_id; end; p_last_rent_due_date := v_last_rent_due_date; --�ѻ������ �Ѻ����������֮��+���� select sum(nvl(ccc.received_amount, 0)) + nvl(v_old_contract_rec.down_payment, 0) into v_sum_received_rent_amount from con_contract_cashflow ccc where ccc.contract_id = v_old_contract_rec.contract_id and ccc.cf_status = 'RELEASE' and ccc.cf_item = 1; --����ΥԼ�� select nvl((select sum(nvl(ccc.received_amount, 0)) from con_contract_cashflow ccc where ccc.contract_id = v_old_contract_rec.contract_id and ccc.cf_status = 'RELEASE' and ccc.cf_item = 9), 0) into v_sum_received_penalty_amount from dual; --ʣ����� --�����ʼ�ڣ����������һ�����Ӧ�ս�� select sum(nvl(ccc.due_amount, 0)) into v_sum_unreceived_rent_amount from con_contract_cashflow ccc where ccc.contract_id = v_old_contract_rec.contract_id and ccc.times >= v_ccr_start_times and ccc.cf_status = 'RELEASE' and ccc.cf_item = 1; --�ʽ�ռ������ select trunc(p_repurchase_date) - trunc(v_last_rent_due_date) + 1 into v_fund_possession_time from dual; --�ʽ�ռ�÷��� Ĭ�ϴ�����ͬ���� v_fund_possession_rate := v_old_contract_rec.int_rate_display; --ʣ�౾����ǰ������ʼ�ڣ���ǰ�����������ڣ�������Ԥ��֧���գ���ʼ��Ϊ��һ�ڣ���һ�ڵ�ʣ�౾�� begin select --f.principal - nvl(f.received_principal, 0) f.outstanding_principal into v_sum_unreceived_principal from con_contract_cashflow f where f.due_date = v_last_rent_due_date and f.cf_item = 1 and f.cf_type = 1 and f.contract_id = v_old_contract_rec.contract_id and f.due_amount>0; exception --0����ֽ������������� when no_data_found then select t1.outstanding_principal into v_sum_unreceived_principal from ( select f.cashflow_id, f.outstanding_principal+nvl(f.principal,0) outstanding_principal from con_contract_cashflow f where f.due_date > v_last_rent_due_date and f.cf_item = 1 and f.cf_type = 1 and f.contract_id = v_old_contract_rec.contract_id and f.due_amount>0 order by f.times) t1 where rownum=1; end; --�ʽ�ռ�÷ѣ�ʣ�౾����ʽ�ռ�÷��ʣ�����������ռ���ʽ����� v_fund_possession_cost := v_sum_unreceived_principal * v_fund_possession_time * v_fund_possession_rate / 360; --������𣺽�Լ�ع���ʼ��֮ǰδ�����֮�� select nvl(sum(f.due_amount), 0) - nvl(sum(f.received_amount), 0) into v_sum_overdue_amount from con_contract_cashflow f where f.due_date <= v_last_rent_due_date and f.cf_item = 1 and f.cf_type = 1 and f.contract_id = v_old_contract_rec.contract_id; /* -- ���ڷ�Ϣ����Լ�ع���ʼ��֮ǰδ�շ�Ϣ֮��; select nvl(sum(f.due_amount), 0) - nvl(sum(f.received_amount), 0) into v_penalty from con_contract_cashflow f where f.times < v_ccr_start_times and f.cf_item = 9 and f.contract_id = v_old_contract_rec.contract_id;*/ v_penalty := 0; --��ѡ��Ϣ FOR c_contract_cashflow IN (SELECT a.* FROM con_contract_cashflow a, con_contract_cf_item b WHERE a.contract_id = v_old_contract_rec.contract_id AND a.cf_status = 'RELEASE' AND a.due_date <= v_last_rent_due_date --ֻ�������IJż��㷣Ϣ�� -- AND a.penalty_process_status = 'NORMAL' AND a.cf_direction = 'INFLOW' AND a.contract_id = b.contract_id AND a.cf_item = b.cf_item AND b.calc_penalty = 'Y' ORDER BY times, due_date) LOOP if nvl(c_contract_cashflow.penalty_process_status, 'N') = 'NORMAL' then v_penalty := v_penalty + CON_OVERDUE_PENALTY_PKG.CALC_PENALTY_FOR_WRITE_OFF(p_cashflow_id => c_contract_cashflow.cashflow_id, p_calc_date => p_repurchase_date, p_user_id => p_user_id, p_penalty_flag => 'N'); -- if nvl(c_contract_cashflow.write_off_flag, 'NOT') <> 'FULL' then select v_penalty - nvl((SELECT sum(nvl(a.received_amount, 0)) FROM con_contract_cashflow a WHERE a.contract_id = v_old_contract_rec.contract_id AND a.cf_status = 'RELEASE' AND a.cf_item = 9 AND a.times = c_contract_cashflow.times), 0) into v_penalty from dual; -- end if; end if; if nvl(c_contract_cashflow.penalty_process_status, 'N') <> 'NORMAL' and c_contract_cashflow.cf_item <> 8 then select v_penalty + nvl((SELECT sum(nvl(a.due_amount, 0) - nvl(a.received_amount, 0)) FROM con_contract_cashflow a WHERE a.contract_id = v_old_contract_rec.contract_id AND a.cf_status = 'RELEASE' AND a.cf_item = 9 AND a.times = c_contract_cashflow.times), 0) into v_penalty from dual; end if; end loop; p_sum_overdue_amount := v_sum_overdue_amount; p_due_amount := v_penalty; p_sum_unreceived_principal := v_sum_unreceived_principal; p_last_rent_due_date := v_last_rent_due_date; p_sum_received_rent_amount := v_sum_received_rent_amount; p_sum_unreceived_rent_amount := v_sum_unreceived_rent_amount; p_fund_possession_cost := v_fund_possession_cost; update con_contract_change_req set repurchase_date = p_repurchase_date, ccr_start_times = v_ccr_start_times, last_update_date = sysdate, last_rent_due_date = v_last_rent_due_date, last_updated_by = p_user_id, ref_v07 = 'N' where change_req_id = p_contract_id; update con_contract ct set ct.offset_flag = 'N' where contract_id = p_contract_id; exception when e_defin_error then sys_raise_app_error_pkg.raise_sys_others_error(p_message => e_err_msg, p_created_by => p_user_id, p_package_name => 'cus_con_contract_repo_pkg', p_procedure_function_name => 'calc_repo_date_amount'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); when e_error then sys_raise_app_error_pkg.raise_sys_others_error(p_message => '��Լ�ع����������ڻ�֮������,������,ΥԼ���Ѵ��ڻ���Ϳ�Ʊ��¼�����ȷ��壡', p_created_by => p_user_id, p_package_name => 'cus_con_contract_repo_pkg', p_procedure_function_name => 'calc_repo_date_amount'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); when e_write_off_flagg_err then sys_raise_app_error_pkg.raise_sys_others_error(p_message => '��' || v_times || '�����к�����¼��������ѡ������' || v_times || '֮�����ݷ���ع���', p_created_by => p_user_id, p_package_name => 'cus_con_contract_repo_pkg', p_procedure_function_name => 'calc_repo_date_amount'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); when e_et_date_err then sys_raise_app_error_pkg.raise_sys_others_error(p_message => '��Լ�ع�����ӦС�ں�ͬ�����գ�', p_created_by => p_user_id, p_package_name => 'cus_con_contract_repo_pkg', p_procedure_function_name => 'calc_repo_date_amount'); 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_contract_repo_pkg', p_procedure_function_name => 'calc_repo_date_amount'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end calc_repo_date_amount; --�ύ������ procedure submit_change_req_repo(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; v_calc_hd_rec hls_fin_calculator_hd%rowtype; v_success_flag varchar2(1); e_change_req_status_error exception; e_calc_error exception; e_simulation_error exception; e_calc_session_id_notfound exception; e_is_calc_flag_err exception; e_attachment_error exception; v_mail_list_id Number; v_time Varchar2(1000); v_mail_to Varchar2(1000); v_mail_cc Varchar2(1000); v_subject Varchar2(1000); v_body Long; ---��Լ�ع��ύ������У�� 18083 2022/11/15 v_tax_type_rate Number; v_count number; v_max_times number; e_tax_amount_error 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);*/ select * into r_change_req_rec from con_contract_change_req t where t.change_req_id = p_change_req_id; /*add by Harry 9952 2017/1/19 purpose: verify the atttachment required property when submit the document */ --Ӧderonҵ����Ҫȥ��У�� /* select count(*) into v_request_num from prj_cdd_item_doc_ref dr, prj_cdd_item_check ic, prj_cdd_item_list_grp_tab gt where dr.document_table = 'PRJ_PROJECT' and dr.document_id = (select c.project_id from con_contract c where c.contract_id = r_change_req_rec.contract_id) and dr.check_id = ic.check_id and ic.cdd_item_id = gt.cdd_item_id and gt.important_flag = 'Y' and not exists (select 1 from fnd_atm_attachment_multi fam where fam.table_name = 'PRJ_CDD_ITEM_CHECK' and fam.table_pk_value = ic.check_id); if v_request_num > 0 then raise e_attachment_error; end if;*/ /* r_change_con_rec := con_contract_pkg.get_contract_rec(p_contract_id => p_change_req_id, p_user_id => p_user_id);*/ select * into r_change_con_rec from con_contract t where t.contract_id = p_change_req_id; if r_change_req_rec.req_status not in ('NEW', 'REJECT') then raise e_change_req_status_error; end if; --��Լ�ع������ύǰУ���Լ�ع����һ�ڵ���˰���Ƿ����ֽ���˰��һ�� 18083 2022/11/15 if r_change_con_rec.business_type='LEASE' then v_tax_type_rate :=0.13; else v_tax_type_rate :=0.06; end if; select max(ccc.times) into v_max_times from con_contract_cashflow ccc where ccc.contract_id = p_change_req_id and ccc.cf_item = 250; select count(*) into v_count from con_contract_cashflow cc where cc.contract_id = p_change_req_id and cc.cf_item = 250 and abs(cc.vat_due_amount - round(cc.due_amount / (1 + v_tax_type_rate) * v_tax_type_rate, 2)) > 1 and cc.times=v_max_times; if v_count>0 then null; --raise e_tax_amount_error; dev������˰����㲻�ԣ���������ʽ�����д����в��죬��ʱע�ʹ˴�У�� end if; /* if r_change_con_rec.calc_session_id is null then raise e_calc_session_id_notfound; else select calc_successful into v_success_flag from hls_fin_calculator_hd where calc_session_id = r_change_con_rec.calc_session_id; if nvl(v_success_flag, 'N') <> 'Y' then raise e_calc_error; 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); /* CON_CONTRACT_CHANGE_REQ_PKG.CHANGE_REQ_CONFIRM(p_change_req_id, p_user_id => p_user_id);*/ --�ع��ύ���̷����ʼ����������ϼ� SELECT replace(wm_concat(ee.email), ',', ';') into v_mail_to FROM con_contract cc, con_contract_bp ccb, hls_bp_master hbm, exp_org_position eop, exp_emp_assign_e_v eea, exp_employees ee where cc.contract_id = ccb.contract_id and hbm.bp_code = ccb.bp_code and ccb.bp_category = 'AGENT' and eea.employee_id = ee.employee_id and eop.unit_id = hbm.unit_id and eea.position_id = eop.parent_position_id and cc.CONTRACT_id = r_change_req_rec.change_req_id; v_time := to_char(Sysdate, 'yyyy"��"mm"��"dd"��"'); v_subject := '�ع���������������֪ͨ'; v_body := '���쵼�����ã�' || chr(13) || chr(10) || chr(13) || chr(10) || chr(32) || chr(32) || chr(32) || chr(32) || chr(32) || chr(32) || '�ع����빤����-�ع���ţ�' || r_change_req_rec.change_req_number || '��ͬ��ţ�' || r_change_con_rec.contract_number || '�Ѿ��������������ڵ㣬����ǰ������������ http://10.203.0.125:8082/hlcm_leasing/login' || chr(13) || chr(10) || chr(13) || chr(10) || ' ʮ�ָ�л��'; Select zj_sys_mailing_list_s.nextval Into v_mail_list_id From dual; ---�������ʼ���¼���� zj_sys_mail_pkg.insert_mailing_list(p_mail_list_id => v_mail_list_id, p_mail_to => v_mail_to, p_mail_cc => v_mail_cc, p_subject => v_subject, p_body => v_body, p_user_id => p_user_id, p_mail_source => Null, p_mail_source_id => Null, p_content_type => 'text/plain'); exception when e_attachment_error then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'PRJ_PROJECT_WORKFLOW_PKG.ATTACHMENT_ERROR', p_created_by => p_user_id, p_package_name => 'cus_con_contract_repo_pkg', p_procedure_function_name => 'submit_change_req_repo'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); 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_contract_repo_pkg', p_procedure_function_name => 'submit_change_req_repo'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); WHEN e_tax_amount_error THEN sys_raise_app_error_pkg.raise_sys_others_error(p_message => '�ع��ֽ���˰���ȷ�������¼��㣡', p_created_by => -1, p_package_name => 'cus_con_contract_repo_pkg', p_procedure_function_name => 'submit_change_req_repo'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); when e_calc_error then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_CHANGE_REQ_PKG.CALCULATOR_STATUS_ERROR', p_created_by => p_user_id, p_package_name => 'cus_con_contract_repo_pkg', p_procedure_function_name => 'submit_change_req_repo'); 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_contract_repo_pkg', p_procedure_function_name => 'submit_change_req_repo'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); when e_calc_session_id_notfound then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_CHANGE_REQ_PKG.CALC_SESSION_ID_NOTFOUND', p_created_by => p_user_id, p_package_name => 'cus_con_contract_repo_pkg', p_procedure_function_name => 'submit_change_req_repo'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); when e_simulation_error then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_CHANGE_REQ_PKG.SIMULATION_FLAG_ERROR', p_created_by => p_user_id, p_package_name => 'cus_con_contract_repo_pkg', p_procedure_function_name => 'submit_change_req_repo'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end submit_change_req_repo; --�ع����봴���ֽ��� procedure insert_rental_cashflow(p_calculator_ln_rec hls_fin_calculator_ln%rowtype, p_price_list varchar2, p_contract_id number, p_company_id number, p_user_id number, p_max_calc_time number) is v_contract_cashflow_rec con_contract_cashflow%rowtype; v_cf_item hls_cashflow_item.cf_item%type; v_cf_type hls_cashflow_item.cf_type%type; v_cf_direction hls_cashflow_item.cf_direction%type; v_cf_status con_contract_cashflow.cf_status%type; v_con_contract_cashflow_id number; v_times number; begin --�ع�������⸳ֵ v_cf_item := 250; v_cf_type := 1; v_cf_direction := 'INFLOW'; v_cf_status := 'RELEASE'; v_times := p_calculator_ln_rec.times + p_max_calc_time; select con_contract_cashflow_s.nextval into v_con_contract_cashflow_id from dual; v_contract_cashflow_rec.cashflow_id := v_con_contract_cashflow_id; v_contract_cashflow_rec.contract_id := p_contract_id; --��ǰ��ͬid v_contract_cashflow_rec.cf_item := v_cf_item; --�������б����У������õ��ֽ�����Ŀ v_contract_cashflow_rec.cf_type := v_cf_type; -- �����ֽ�����Ŀȡ���ֽ������� v_contract_cashflow_rec.cf_direction := v_cf_direction; v_contract_cashflow_rec.cf_status := v_cf_status; v_contract_cashflow_rec.times := v_times; --���� v_contract_cashflow_rec.calc_date := to_date(p_calculator_ln_rec.calc_date, 'yyyy-mm-dd'); --������ v_contract_cashflow_rec.due_date := to_date(p_calculator_ln_rec.due_date, 'yyyy-mm-dd'); --������ v_contract_cashflow_rec.fin_income_date := to_date(p_calculator_ln_rec.fin_income_date, 'yyyy-mm-dd'); --��������� v_contract_cashflow_rec.due_amount := p_calculator_ln_rec.rental; --��� v_contract_cashflow_rec.principal := p_calculator_ln_rec.principal; --���� v_contract_cashflow_rec.interest := p_calculator_ln_rec.interest; --��Ϣ v_contract_cashflow_rec.outstanding_rental := p_calculator_ln_rec.outstanding_rental; --ʣ����� v_contract_cashflow_rec.outstanding_principal := p_calculator_ln_rec.outstanding_principal; --ʣ�౾�� v_contract_cashflow_rec.outstanding_interest := p_calculator_ln_rec.outstanding_interest; --ʣ����Ϣ v_contract_cashflow_rec.interest_accrual_balance := p_calculator_ln_rec.interest_accrual_balance; --��Ϣ��� v_contract_cashflow_rec.principal_implicit_rate := p_calculator_ln_rec.principal_implicit_rate; --ʵ�����ʷ����� v_contract_cashflow_rec.interest_implicit_rate := p_calculator_ln_rec.interest_implicit_rate; --ʵ�����ʷ���Ϣ v_contract_cashflow_rec.accumulated_unpaid_interest := p_calculator_ln_rec.accumulated_unpaid_interest; --�ۼ�δ������Ϣ v_contract_cashflow_rec.interest_period_days := p_calculator_ln_rec.interest_period_days; --��Ϣ��������Ϣ�������� v_contract_cashflow_rec.discounting_days := p_calculator_ln_rec.discounting_days; --�������� v_contract_cashflow_rec.vat_due_amount := p_calculator_ln_rec.vat_rental; v_contract_cashflow_rec.vat_principal := p_calculator_ln_rec.vat_principal; v_contract_cashflow_rec.vat_interest := p_calculator_ln_rec.vat_interest; v_contract_cashflow_rec.vat_principal_implicit := p_calculator_ln_rec.vat_principal_implicit; --ʵ�����ʷ�������ֵ˰�� v_contract_cashflow_rec.vat_interest_implicit := p_calculator_ln_rec.vat_interest_implicit; --ʵ�����ʷ���Ϣ��ֵ˰�� v_contract_cashflow_rec.net_due_amount := p_calculator_ln_rec.net_rental; v_contract_cashflow_rec.net_principal := p_calculator_ln_rec.net_principal; v_contract_cashflow_rec.net_interest := p_calculator_ln_rec.net_interest; v_contract_cashflow_rec.net_principal_implicit := p_calculator_ln_rec.net_principal_implicit; --ʵ�����ʷ�˰�� v_contract_cashflow_rec.net_interest_implicit := p_calculator_ln_rec.net_interest_implicit; --ʵ�����ʷ�˰����Ϣ v_contract_cashflow_rec.fix_principal_flag := p_calculator_ln_rec.fix_principal_flag; --�̶������־ v_contract_cashflow_rec.fix_rental_flag := p_calculator_ln_rec.fix_rental_flag; --�̶�����־ v_contract_cashflow_rec.interest_only_flag := p_calculator_ln_rec.interest_only_flag; --��֧����Ϣ v_contract_cashflow_rec.equal_flag := p_calculator_ln_rec.equal_flag; v_contract_cashflow_rec.manual_flag := p_calculator_ln_rec.manual_flag; v_contract_cashflow_rec.beginning_of_lease_year := p_calculator_ln_rec.beginning_of_lease_year; --ÿ��������ȵĿ�ʼ v_contract_cashflow_rec.salestax := p_calculator_ln_rec.salestax; --Ӫҵ˰ v_contract_cashflow_rec.generated_source := 'CALCULATOR'; --�ֽ����ɼ��������� v_contract_cashflow_rec.calc_line_id := p_calculator_ln_rec.calc_line_id; v_contract_cashflow_rec.overdue_status := 'N'; v_contract_cashflow_rec.overdue_book_date := null; v_contract_cashflow_rec.overdue_amount := null; v_contract_cashflow_rec.overdue_principal := null; v_contract_cashflow_rec.overdue_interest := null; v_contract_cashflow_rec.overdue_remark := null; v_contract_cashflow_rec.received_amount := null; v_contract_cashflow_rec.received_principal := null; v_contract_cashflow_rec.received_interest := null; v_contract_cashflow_rec.write_off_flag := 'NOT'; v_contract_cashflow_rec.last_received_date := null; v_contract_cashflow_rec.full_write_off_date := null; v_contract_cashflow_rec.penalty_process_status := 'NORMAL'; v_contract_cashflow_rec.billing_status := 'NOT'; --�û��ֶ�����1 if p_calculator_ln_rec.ln_user_col_d01 is not null then v_contract_cashflow_rec.ln_user_col_d01 := to_date(p_calculator_ln_rec.ln_user_col_d01, 'yyyy-mm-dd'); else v_contract_cashflow_rec.ln_user_col_d01 := null; end if; if p_calculator_ln_rec.ln_user_col_d02 is not null then v_contract_cashflow_rec.ln_user_col_d02 := to_date(p_calculator_ln_rec.ln_user_col_d02, 'yyyy-mm-dd'); else v_contract_cashflow_rec.ln_user_col_d02 := null; end if; if p_calculator_ln_rec.ln_user_col_d03 is not null then v_contract_cashflow_rec.ln_user_col_d03 := to_date(p_calculator_ln_rec.ln_user_col_d03, 'yyyy-mm-dd'); else v_contract_cashflow_rec.ln_user_col_d03 := null; end if; if p_calculator_ln_rec.ln_user_col_d04 is not null then v_contract_cashflow_rec.ln_user_col_d04 := to_date(p_calculator_ln_rec.ln_user_col_d04, 'yyyy-mm-dd'); else v_contract_cashflow_rec.ln_user_col_d04 := null; end if; if p_calculator_ln_rec.ln_user_col_d05 is not null then v_contract_cashflow_rec.ln_user_col_d05 := to_date(p_calculator_ln_rec.ln_user_col_d05, 'yyyy-mm-dd'); else v_contract_cashflow_rec.ln_user_col_d05 := null; end if; v_contract_cashflow_rec.ln_user_col_v01 := p_calculator_ln_rec.ln_user_col_v01; --�û��ֶ��ַ�1 v_contract_cashflow_rec.ln_user_col_v02 := p_calculator_ln_rec.ln_user_col_v02; v_contract_cashflow_rec.ln_user_col_v03 := 'REPUR'; v_contract_cashflow_rec.ln_user_col_v04 := p_calculator_ln_rec.ln_user_col_v04; v_contract_cashflow_rec.ln_user_col_v05 := p_calculator_ln_rec.ln_user_col_v05; v_contract_cashflow_rec.ln_user_col_n01 := p_calculator_ln_rec.ln_user_col_n01; --�û��ֶ�����1 v_contract_cashflow_rec.ln_user_col_n02 := p_calculator_ln_rec.ln_user_col_n02; v_contract_cashflow_rec.ln_user_col_n03 := p_calculator_ln_rec.ln_user_col_n03; v_contract_cashflow_rec.ln_user_col_n04 := p_calculator_ln_rec.ln_user_col_n04; v_contract_cashflow_rec.ln_user_col_n05 := p_calculator_ln_rec.ln_user_col_n05; v_contract_cashflow_rec.ln_user_col_n06 := p_calculator_ln_rec.ln_user_col_n06; v_contract_cashflow_rec.ln_user_col_n07 := p_calculator_ln_rec.ln_user_col_n07; v_contract_cashflow_rec.ln_user_col_n08 := p_calculator_ln_rec.ln_user_col_n08; v_contract_cashflow_rec.ln_user_col_n09 := p_calculator_ln_rec.ln_user_col_n09; v_contract_cashflow_rec.ln_user_col_n10 := p_calculator_ln_rec.ln_user_col_n10; v_contract_cashflow_rec.ln_user_col_n11 := p_calculator_ln_rec.ln_user_col_n11; v_contract_cashflow_rec.ln_user_col_n12 := p_calculator_ln_rec.ln_user_col_n12; v_contract_cashflow_rec.ln_user_col_n13 := p_calculator_ln_rec.ln_user_col_n13; v_contract_cashflow_rec.ln_user_col_n14 := p_calculator_ln_rec.ln_user_col_n14; v_contract_cashflow_rec.ln_user_col_n15 := p_calculator_ln_rec.ln_user_col_n15; v_contract_cashflow_rec.ln_user_col_n16 := p_calculator_ln_rec.ln_user_col_n16; v_contract_cashflow_rec.ln_user_col_n17 := p_calculator_ln_rec.ln_user_col_n17; v_contract_cashflow_rec.ln_user_col_n18 := p_calculator_ln_rec.ln_user_col_n18; v_contract_cashflow_rec.ln_user_col_n19 := p_calculator_ln_rec.ln_user_col_n19; v_contract_cashflow_rec.ln_user_col_n20 := p_calculator_ln_rec.ln_user_col_n20; v_contract_cashflow_rec.rental_eq_pymt_raw := p_calculator_ln_rec.rental_eq_pymt_raw; --��ʼ��𣨵ȶ v_contract_cashflow_rec.rental_eq_pymt_adj := p_calculator_ln_rec.rental_eq_pymt_adj; --������𣨵ȶ v_contract_cashflow_rec.interest_eq_pymt_raw := p_calculator_ln_rec.interest_eq_pymt_raw; --��ʼ��Ϣ���ȶ v_contract_cashflow_rec.interest_eq_pymt_adj := p_calculator_ln_rec.interest_eq_pymt_adj; --������Ϣ���ȶ v_contract_cashflow_rec.principal_eq_pymt_raw := p_calculator_ln_rec.principal_eq_pymt_raw; --��ʼ���𣨵ȶ v_contract_cashflow_rec.principal_eq_pymt_adj := p_calculator_ln_rec.principal_eq_pymt_adj; --�������𣨵ȶ v_contract_cashflow_rec.rental_eq_prin_raw := p_calculator_ln_rec.rental_eq_prin_raw; --��ʼ��𣨵ȱ��� v_contract_cashflow_rec.rental_eq_prin_adj := p_calculator_ln_rec.rental_eq_prin_adj; --������𣨵ȱ��� v_contract_cashflow_rec.interest_eq_prin_raw := p_calculator_ln_rec.interest_eq_prin_raw; --��ʼ��Ϣ���ȱ��� v_contract_cashflow_rec.interest_eq_prin_adj := p_calculator_ln_rec.interest_eq_prin_adj; --������Ϣ���ȱ��� v_contract_cashflow_rec.principal_eq_prin_raw := p_calculator_ln_rec.principal_eq_prin_raw; --��ʼ���𣨵ȱ��� v_contract_cashflow_rec.principal_eq_prin_adj := p_calculator_ln_rec.principal_eq_prin_adj; --�������𣨵ȱ��� v_contract_cashflow_rec.outstanding_rental_tax_incld := p_calculator_ln_rec.outstanding_rental_tax_incld; v_contract_cashflow_rec.outstanding_prin_tax_incld := p_calculator_ln_rec.outstanding_prin_tax_incld; v_contract_cashflow_rec.outstanding_int_tax_incld := p_calculator_ln_rec.outstanding_int_tax_incld; v_contract_cashflow_rec.interest_accrual_bal_tax_incl := p_calculator_ln_rec.interest_accrual_bal_tax_incl; v_contract_cashflow_rec.accumulated_unpd_int_tax_incl := p_calculator_ln_rec.accumulated_unpd_int_tax_incl; v_contract_cashflow_rec.exchange_rate_type := p_calculator_ln_rec.exchange_rate_type; v_contract_cashflow_rec.exchange_rate_quotation := p_calculator_ln_rec.exchange_rate_quotation; v_contract_cashflow_rec.exchange_rate := p_calculator_ln_rec.exchange_rate; v_contract_cashflow_rec.due_amount_cny := p_calculator_ln_rec.due_amount_cny; v_contract_cashflow_rec.main_business_income := p_calculator_ln_rec.main_business_income; v_contract_cashflow_rec.main_business_cost := p_calculator_ln_rec.main_business_cost; v_contract_cashflow_rec.financing_cost := p_calculator_ln_rec.financing_cost; v_contract_cashflow_rec.created_by := p_user_id; --��ǰ�û� v_contract_cashflow_rec.creation_date := sysdate; --ϵͳʱ�� v_contract_cashflow_rec.last_updated_by := p_user_id; --��ǰ�û� v_contract_cashflow_rec.last_update_date := sysdate; --ϵͳʱ�� -- liukang v_contract_cashflow_rec.payment_deduction_flag := 'Y'; v_contract_cashflow_rec.payment_deduction_amount := v_contract_cashflow_rec.due_amount; --add by chenlingfeng 2019��4��9�� v_contract_cashflow_rec.tax_type_id := p_calculator_ln_rec.tax_type_id; v_contract_cashflow_rec.tax_type_rate := p_calculator_ln_rec.tax_type_rate; insert into con_contract_cashflow values v_contract_cashflow_rec; end; procedure save_contract_cashflow(p_contract_id number, p_fin_calculator_hd_rec hls_fin_calculator_hd%rowtype, p_user_id number, p_max_calc_time number) is begin for c_hls_fin_calculator_ln in (select * from hls_fin_calculator_ln where calc_session_id = p_fin_calculator_hd_rec.calc_session_id and to_number(times) > 0 order by to_number(times)) loop insert_rental_cashflow(p_calculator_ln_rec => c_hls_fin_calculator_ln, p_price_list => p_fin_calculator_hd_rec.price_list, p_contract_id => p_contract_id, p_company_id => p_fin_calculator_hd_rec.company_id, p_user_id => p_user_id, p_max_calc_time => p_max_calc_time); end loop; end; procedure restore_cashflow_from_con(p_change_req_id number, p_contract_id number, p_user_id number) is doc_pk_list hls_document_transfer_pkg.t_doc_pk_list; i number; v_new_id number; begin --CON_CONTRACT_CASHFLOW delete from con_contract_cashflow where contract_id = p_change_req_id; doc_pk_list.delete; delete from hls_document_history_ref r where r.document_history_id=p_change_req_id and r.table_name='CON_CONTRACT_CASHFLOW'; i := 0; for c_cf in (select cashflow_id, calc_line_id, times from con_contract_cashflow where contract_id = p_contract_id order by cashflow_id) loop i := i + 1; select con_contract_cashflow_s.nextval into v_new_id from dual; doc_pk_list(i).from_doc_pk := c_cf.cashflow_id; doc_pk_list(i).to_doc_pk := v_new_id; hls_document_history_pkg.create_document_history_ref(p_document_history_id => p_change_req_id, p_table_name => 'CON_CONTRACT_CASHFLOW', p_from_pk_id => c_cf.cashflow_id, p_to_pk_id => v_new_id, p_user_id => p_user_id); prj_cdd_item_pkg.prj_cdd_item_doc_ref_copy(p_from_document_table => 'CON_CONTRACT_CASHFLOW', p_from_document_id => c_cf.cashflow_id, p_to_document_table => 'CON_CONTRACT_CASHFLOW', p_to_document_id => v_new_id, p_user_id => p_user_id); end loop; hls_document_transfer_pkg.doc_to_doc(p_from_doc_table => 'CON_CONTRACT_CASHFLOW', p_to_doc_table => 'CON_CONTRACT_CASHFLOW', p_doc_pk_list => doc_pk_list, p_copy_method => 'DOC_TO_HISTORY', p_to_doc_column_1 => 'contract_id', p_to_doc_column_1_value => p_change_req_id, p_user_id => p_user_id); --CON_UNEARNED_FINANCE_INCOME delete from con_unearned_finance_income where contract_id = p_change_req_id; doc_pk_list.delete; delete from hls_document_history_ref r where r.document_history_id = p_change_req_id and r.table_name = 'CON_UNEARNED_FINANCE_INCOME'; i := 0; for c_cf in (select record_id from con_unearned_finance_income where contract_id = p_contract_id) loop i := i + 1; select con_unearned_finance_income_s.nextval into v_new_id from dual; doc_pk_list(i).from_doc_pk := c_cf.record_id; doc_pk_list(i).to_doc_pk := v_new_id; hls_document_history_pkg.create_document_history_ref(p_document_history_id => p_change_req_id, p_table_name => 'CON_UNEARNED_FINANCE_INCOME', p_from_pk_id => c_cf.record_id, p_to_pk_id => v_new_id, p_user_id => p_user_id); end loop; hls_document_transfer_pkg.doc_to_doc(p_from_doc_table => 'CON_UNEARNED_FINANCE_INCOME', p_to_doc_table => 'CON_UNEARNED_FINANCE_INCOME', p_doc_pk_list => doc_pk_list, p_copy_method => 'DOC_TO_HISTORY', p_to_doc_column_1 => 'contract_id', p_to_doc_column_1_value => p_change_req_id, p_user_id => p_user_id); update con_unearned_finance_income a set a.cashflow_id = (select r.from_pk_id from hls_document_history_ref r where r.document_history_id = p_change_req_id and r.table_name = 'CON_CONTRACT_CASHFLOW' and r.to_pk_id = a.cashflow_id) where a.contract_id = p_change_req_id and a.cashflow_id is not null; end; --�¹ʵ�I-10652 �����Լ�ع���Ϣ��������� procedure deal_repur_cash_leaseback(p_contract_id in number, p_interest out number) is v_contract_rec con_contract%rowtype; r_con_contract_change_req con_contract_change_req%ROWTYPE; v_old_contract_rec con_contract%rowtype; v_vat_principal number; v_due_residual number; v_overdue_interest number; v_vat_sum_principal number; v_vat_fund_possession_cost number; v_vat_service_charges number; v_penalty number; v_tax_type_rate number; begin select * into r_con_contract_change_req from con_contract_change_req t where t.change_req_id = p_contract_id; --���ǰ��ͬ select * into v_old_contract_rec from con_contract t where t.contract_id = r_con_contract_change_req.contract_id; --����к�ͬ select * into v_contract_rec from con_contract t where t.contract_id = p_contract_id; /* select ft.tax_type_rate into v_tax_type_rate from fnd_tax_type_codes ft where ft.tax_type_id = v_contract_rec.tax_type_id;*/ --�в�������������ع����е���Ϣ֮��=������������������Ϣδ�ղ��֣���Ӫҵʵ�աݵ�����Ϣ��Ϊ0��+��������е���Ϣ����+������+���ɽ𣨻ع�����еģ� --��������������ع����е���Ϣ֮��=��������е���Ϣ����+������+���ɽ𣨻ع�����еģ� --��Լ�ع����𣺽�Լ�ع����-��Լ�ع���Ϣ --���������� --δ���� -> ȡinterest --���ֺ��� �ֶ���ʽ���� for c_over_due_amount in (select * from con_contract_cashflow f where f.due_date <= r_con_contract_change_req.last_rent_due_date and f.cf_item = 1 and f.cf_type = 1 and f.contract_id = v_old_contract_rec.contract_id and f.write_off_flag <> 'FULL') loop if c_over_due_amount.write_off_flag = 'PARTIAL' then --����Ӫҵʵ�ա��ܡ�����Ӧ�ձ��𡱵�������Ϣ��ȫ��Ҫ�㵽�ع������� if c_over_due_amount.received_amount<=c_over_due_amount.principal then v_overdue_interest :=nvl(v_overdue_interest, 0)+nvl(c_over_due_amount.interest,0); else --������Ӫҵʵ�ա���������Ӧ�ձ��𡱵�����Ӧ��-����Ӫҵʵ�ա��IJ���Ҫ���ڻع������� v_overdue_interest := nvl(v_overdue_interest, 0) + (c_over_due_amount.due_amount - nvl(c_over_due_amount.received_amount, 0)); end if; elsif c_over_due_amount.write_off_flag = 'NOT' then v_overdue_interest := nvl(v_overdue_interest, 0) + c_over_due_amount.interest; end if; end loop; --������ begin select ccw.due_amount into v_due_residual from con_contract_cashflow ccw where ccw.contract_id = v_contract_rec.contract_id and cf_item = 8; exception when others then v_due_residual := 0; end; --���ɽ� v_penalty :=v_contract_rec.ccr_due_amount; p_interest :=nvl(v_overdue_interest,0)+nvl(v_due_residual,0)+nvl(v_penalty,0); end; procedure deal_repur_cash(p_contract_id in number, p_vat_principal out number) is v_contract_rec con_contract%rowtype; r_con_contract_change_req con_contract_change_req%ROWTYPE; v_old_contract_rec con_contract%rowtype; v_vat_principal number; v_vat_residual number; v_overdue_vat_due_amount number; v_vat_sum_principal number; v_vat_fund_possession_cost number; v_vat_service_charges number; v_penalty number; v_tax_type_rate number; begin select * into r_con_contract_change_req from con_contract_change_req t where t.change_req_id = p_contract_id; --���ǰ��ͬ select * into v_old_contract_rec from con_contract t where t.contract_id = r_con_contract_change_req.contract_id; --����к�ͬ select * into v_contract_rec from con_contract t where t.contract_id = p_contract_id; select ft.tax_type_rate into v_tax_type_rate from fnd_tax_type_codes ft where ft.tax_type_id = v_contract_rec.tax_type_id; --vat_due_amount�ֶΣ��ع�����~���1��/�������+������+����������+���ڷ�Ϣ���+�ʽ�ռ�÷�+�ع������� --ע���������������ڷ�Ϣ�в��ֺ��������ʱ����δ������������ȡVat_due_amount�� --�Բ��ֺ�����������ֻ���Լ����㣬�ȼ���vat�������net --�ع�����~���1��/������� begin select sum(ccw.vat_principal) into v_vat_principal from con_contract_cashflow ccw where ccw.contract_id = v_contract_rec.contract_id and ccw.times >= r_con_contract_change_req.ccr_start_times and ccw.times <= v_old_contract_rec.lease_times and cf_item = 1; exception when others then v_vat_principal := 0; end; --������ begin select ccw.vat_due_amount into v_vat_residual from con_contract_cashflow ccw where ccw.contract_id = v_contract_rec.contract_id and cf_item = 8; exception when others then v_vat_residual := 0; end; --���������� --δ���� -> ȡvat_due_amount --���ֺ��� �ֶ���ʽ���� for c_over_due_amount in (select * from con_contract_cashflow f where f.due_date <= r_con_contract_change_req.last_rent_due_date and f.cf_item = 1 and f.cf_type = 1 and f.contract_id = v_old_contract_rec.contract_id and f.write_off_flag <> 'FULL') loop if c_over_due_amount.write_off_flag = 'PARTIAL' then v_overdue_vat_due_amount := nvl(v_overdue_vat_due_amount, 0) + (c_over_due_amount.due_amount - nvl(c_over_due_amount.received_amount, 0)) / (1 + v_tax_type_rate) * v_tax_type_rate; elsif c_over_due_amount.write_off_flag = 'NOT' then v_overdue_vat_due_amount := nvl(v_overdue_vat_due_amount, 0) + c_over_due_amount.vat_due_amount; end if; end loop; --���ڷ�Ϣ��� --�жϵ�����Ϣ�Ƿ�����ڷ�Ϣ�ֶ�һ�� --δ���� -> ȡvat_due_amount --���ֺ��� �ֶ���ʽ���� if nvl(v_contract_rec.ccr_due_amount, 0) = nvl(v_contract_rec.due_amount, 0) then for c_overdue_penalty in (select * from con_contract_cashflow f where f.times < r_con_contract_change_req.ccr_start_times and f.cf_item = 9 and f.contract_id = v_old_contract_rec.contract_id and f.write_off_flag <> 'FULL') loop if c_overdue_penalty.write_off_flag = 'PARTIAL' then v_penalty := nvl(v_penalty, 0) + (c_overdue_penalty.due_amount - nvl(c_overdue_penalty.received_amount, 0)) / (1 + v_tax_type_rate) * v_tax_type_rate; elsif c_overdue_penalty.write_off_flag = 'NOT' then v_penalty := nvl(v_penalty, 0) + c_overdue_penalty.due_amount/ (1 + v_tax_type_rate) * v_tax_type_rate; end if; end loop; else v_penalty := round(nvl(v_contract_rec.ccr_due_amount, 0) / (1 + v_tax_type_rate) * v_tax_type_rate, 2); end if; v_vat_fund_possession_cost := round(nvl(v_contract_rec.fund_possession_cost, 0) / (1 + v_tax_type_rate) * v_tax_type_rate, 2); v_vat_service_charges := round(nvl(v_contract_rec.repurchase_service_charges, 0) / (1 + v_tax_type_rate) * v_tax_type_rate, 2); p_vat_principal := nvl(v_vat_principal, 0) + nvl(v_vat_residual, 0) + nvl(round(v_overdue_vat_due_amount, 2), 0) + nvl(round(v_penalty, 2), 0) + v_vat_fund_possession_cost + v_vat_service_charges; end; ---����ҵ��֤���Զ����� --������Ȳ�����ʱ�� --�ֽ�������ֶ����и���change_req,ģ����ǰ����ɺ������� --��ʱ��normal���������ִ�и���normal�ֽ������������� procedure calc_deposit_auto_split(p_change_req_id number, p_user_id number) is r_con_contract_rec con_contract%rowtype; v_deposit_deduct number; v_write_off_interest number; v_write_off_princpal number; v_write_off_flag varchar2(30); begin select cc.* into r_con_contract_rec from con_contract cc where cc.contract_id=p_change_req_id; v_deposit_deduct:=nvl(r_con_contract_rec.deposit_deduct,0); if v_deposit_deduct>0 and r_con_contract_rec.division='70' then delete from csh_write_off_interface c where c.contract_id= p_change_req_id and c.trx_interface_id is null; for cashflow_tj in (select * from con_contract_cashflow c where c.contract_id=p_change_req_id and c.write_off_flag <>'FULL' and c.cf_item in ('1','9') and c.cf_status!='CANCEL' order by c.times,decode(c.cf_item,'9',1,2)) LOOP v_write_off_interest:=null; v_write_off_princpal:=null; --�ų��պõ���0������������Ϊ0���� if v_deposit_deduct=0 then exit; end if; if (cashflow_tj.due_amount-nvl(cashflow_tj.received_amount,0))>v_deposit_deduct then v_write_off_flag:='PARTIAL'; if cashflow_tj.cf_item=1 then if (cashflow_tj.principal-nvl(cashflow_tj.received_principal,0))>=v_deposit_deduct then v_write_off_interest:=0; v_write_off_princpal:=v_deposit_deduct; else v_write_off_interest:=v_deposit_deduct-(cashflow_tj.principal-nvl(cashflow_tj.received_principal,0)); v_write_off_princpal:=cashflow_tj.principal-nvl(cashflow_tj.received_principal,0); end if; end if; update con_contract_cashflow c set c.received_amount = v_deposit_deduct + nvl(cashflow_tj.received_amount, 0), c.received_principal = nvl(cashflow_tj.received_principal, 0) + nvl(v_write_off_princpal, 0), c.received_interest = nvl(cashflow_tj.received_interest, 0) + nvl(v_write_off_interest, 0), c.write_off_flag=v_write_off_flag where c.cashflow_id = cashflow_tj.cashflow_id; insert into csh_write_off_interface(write_off_interface_id, record_type, batch_id, trx_interface_id, trx_number, csh_bank_slip_num, company_id, contract_number, contract_id, write_off_cashflow_id, write_off_type, write_off_date, write_off_period_name, write_off_period_num, write_off_times, write_off_cf_item, write_off_amount, write_off_principal, write_off_interest, write_off_description, write_off_bp_id, check_flag, post_flag, write_off_id, error_message, creation_date, created_by, last_update_date, last_updated_by, write_off_classification, agent_id, assigns_contract_id, journal_date, conds_account_num, conds_account_name) values (csh_write_off_interface_s.nextval, 'MANUAL', null, null, null, null, 1, r_con_contract_rec.contract_number, cashflow_tj.contract_id, cashflow_tj.cashflow_id, 'DEPOSIT_CREDIT', sysdate, --�������� to_char(sysdate,'yyyy-mm'), --�����������ڼ� to_number(to_char(sysdate,'yyyymm')), --�����������·� cashflow_tj.times, cashflow_tj.cf_item, v_deposit_deduct, --������� v_write_off_princpal, --�������� v_write_off_interest, --������Ϣ null, r_con_contract_rec.bp_id_tenant, 'Y', null, null, null, sysdate, 1, sysdate, 1, null, null, cashflow_tj.contract_id, sysdate, null, null); exit; else v_write_off_flag:='FULL'; if cashflow_tj.cf_item=1 then v_write_off_interest:=(cashflow_tj.interest-nvl(cashflow_tj.received_interest,0)); v_write_off_princpal:=(cashflow_tj.principal-nvl(cashflow_tj.received_principal,0)); end if; update con_contract_cashflow c set c.received_amount = c.due_amount, c.received_principal = c.principal, c.received_interest = c.interest, c.write_off_flag=v_write_off_flag where c.cashflow_id = cashflow_tj.cashflow_id; insert into csh_write_off_interface(write_off_interface_id, record_type, batch_id, trx_interface_id, trx_number, csh_bank_slip_num, company_id, contract_number, contract_id, write_off_cashflow_id, write_off_type, write_off_date, write_off_period_name, write_off_period_num, write_off_times, write_off_cf_item, write_off_amount, write_off_principal, write_off_interest, write_off_description, write_off_bp_id, check_flag, post_flag, write_off_id, error_message, creation_date, created_by, last_update_date, last_updated_by, write_off_classification, agent_id, assigns_contract_id, journal_date, conds_account_num, conds_account_name) values (csh_write_off_interface_s.nextval, 'MANUAL', null, null, null, null, 1, r_con_contract_rec.contract_number, cashflow_tj.contract_id, cashflow_tj.cashflow_id, 'DEPOSIT_CREDIT', sysdate, --�������� to_char(sysdate,'yyyy-mm'), --�����������ڼ� to_number(to_char(sysdate,'yyyymm')), --�����������·� cashflow_tj.times, cashflow_tj.cf_item, cashflow_tj.due_amount, --������� v_write_off_princpal, --�������� v_write_off_interest, --������Ϣ null, r_con_contract_rec.bp_id_tenant, 'Y', null, null, null, sysdate, 1, sysdate, 1, null, null, cashflow_tj.contract_id, sysdate, null, null); v_deposit_deduct:=v_deposit_deduct-cashflow_tj.due_amount+nvl(cashflow_tj.received_amount,0); end if; END LOOP; end if; end; procedure calc_repo_cashflow(p_contract_id in number, p_calc_session_id IN NUMBER, p_user_id IN NUMBER, p_msg OUT VARCHAR2) is r_con_contract_rec con_contract%rowtype; v_max_calc_time NUMBER; r_con_contract_change_req con_contract_change_req%ROWTYPE; r_calculator_hd_rec hls_fin_calculator_hd%rowtype; v_new_calc_session_id number; v_min_overdue_times number; v_start_change_times number; v_vat_principal number; v_deposit_deduct number; v_division varchar2(30); --�¹ʵ�I-10652 v_cashflow_rec con_contract_cashflow%ROWTYPE; v_interest NUMBER; --��Ϣ v_principal NUMBER; --���� v_outstanding_principal NUMBER; --ʣ�౾�� v_due_date DATE; v_interest_back number; --�����ͬ��Ϣ begin --step 1 ��������� aux_single_variable_calc_pkg.calc_main(p_calc_session_id => p_calc_session_id, p_user_id => p_user_id, p_msg => p_msg); /* --���Ƽ�����4�ű������� hls_fin_calculator_itfc_pkg.copy_calculator(p_from_calc_session_id => p_calc_session_id, p_to_calc_session_id => v_new_calc_session_id, p_user_id => p_user_id);*/ select * into r_con_contract_rec from con_contract t where t.contract_id = p_contract_id; SELECT * INTO r_con_contract_change_req FROM con_contract_change_req t WHERE t.change_req_id = p_contract_id; /* select * into r_calculator_hd_rec from hls_fin_calculator_hd h where h.source_doc_category = 'CHANGE_CONTRACT' and h.source_doc_id = r_con_contract_change_req.contract_id;*/ select * into r_calculator_hd_rec from hls_fin_calculator_hd h where h.calc_session_id = p_calc_session_id; --ɾ��ǰһ�α����������Լ�ع������� delete from con_contract_cashflow t where t.contract_id = p_contract_id and t.cf_item in (250); v_deposit_deduct:=nvl(r_con_contract_rec.deposit_deduct,0); v_division:=nvl(r_con_contract_rec.division,0); --��ԭǰһ�α�����㶳����� /*update con_contract_cashflow set cf_direction = 'INFLOW', cf_status = 'RELEASE' where contract_id = p_contract_id and cf_direction = 'NONCASH' and cf_status = 'BLOCK' and ln_user_col_v03 = 'REPUR';*/ restore_cashflow_from_con(p_change_req_id =>p_contract_id, p_contract_id =>r_con_contract_change_req.contract_id, p_user_id =>p_user_id); if v_deposit_deduct=0 then --����ķ�Ϣ�ֽ������� FOR c_contract_cashflow IN (SELECT a.* FROM con_contract_cashflow a, con_contract_cf_item b WHERE a.contract_id = p_contract_id AND a.cf_status = 'RELEASE' AND a.due_date <= r_con_contract_change_req.last_rent_due_date --ֻ�������IJż��㷣Ϣ�� AND a.penalty_process_status = 'NORMAL' AND a.cf_direction = 'INFLOW' AND a.contract_id = b.contract_id AND a.cf_item = b.cf_item AND b.calc_penalty = 'Y' ORDER BY times, due_date) LOOP CON_OVERDUE_PENALTY_PKG.overdue_dayend(p_contract_cashflow_rec => c_contract_cashflow, p_contract_rec => r_con_contract_rec, p_calc_date => r_con_contract_change_req.repurchase_date, p_user_id => p_user_id); END LOOP; end if; --����ҵ��֤���Զ����� calc_deposit_auto_split(p_change_req_id =>p_contract_id,p_user_id=>p_user_id); --step 3 ƴ�ӱ����ʼ��֮ǰ���ֽ����ͱ����ʼ��֮����ֽ��� ����µ�cashflow --��ȡ�������֮ǰ�����һ�� ��ǰ��֧��Ԥ������������ select cw.times into v_max_calc_time from con_contract_cashflow cw where cw.contract_id = r_con_contract_change_req.contract_id and cw.due_date = r_con_contract_rec.last_rent_due_date and cw.cf_item = 1; --��ȡ������Сһ������ ���� select min(cw.times) into v_min_overdue_times from con_contract_cashflow cw where cw.contract_id = r_con_contract_change_req.contract_id and cw.times <= v_max_calc_time and cw.write_off_flag <> 'FULL' and cw.cf_item in (1, 8) and nvl(cw.due_amount, 0) <> 0; if v_min_overdue_times is null then v_min_overdue_times := r_con_contract_change_req.ccr_start_times; end if; select max(cw.times) into v_start_change_times from con_contract_cashflow cw where cw.contract_id = r_con_contract_change_req.contract_id and cw.times <= v_max_calc_time and cw.write_off_flag <> 'FULL' and cw.cf_item in (1, 8) and nvl(cw.due_amount, 0) <> 0; --��������������ֽ��� update con_contract_cashflow caw set caw.cf_status = 'BLOCK', caw.cf_direction = 'NONCASH', caw.ln_user_col_v03 = 'REPUR' where caw.contract_id = r_con_contract_rec.contract_id and ((caw.times >= v_min_overdue_times) or nvl(caw.due_amount, 0) = 0); --ƴ�ӱ����ı��� save_contract_cashflow(p_contract_id => p_contract_id, p_fin_calculator_hd_rec => r_calculator_hd_rec, p_user_id => p_user_id, p_max_calc_time => r_con_contract_change_req.ccr_start_times - 1); --���±��α��ۼ���ΪY update con_contract ct set ct.offset_flag = 'Y' where ct.contract_id = p_contract_id; --���ڻع����һ�ڲ�˰����ֱ��ͻ��� --if r_con_contract_rec.business_type='LEASE' then --�������һ�ڽ�Լ�ع�������Ϣ��˰ deal_repur_cash(p_contract_id => p_contract_id, p_vat_principal => v_vat_principal); hls_sys_log_pkg.log('���ڻع���ͬid' || p_contract_id || '˰��' ||v_vat_principal||'����'||r_con_contract_change_req.ccr_start_times||'��������'||r_calculator_hd_rec.lease_times); --���ڻع����� select v_vat_principal - sum(vat_principal) into v_vat_principal from con_contract_cashflow where contract_id = p_contract_id and cf_item = 250 and times > 0 and times < r_con_contract_change_req.ccr_start_times + to_number(r_calculator_hd_rec.lease_times) - 1; update con_contract_cashflow caw set caw.vat_principal = v_vat_principal, caw.net_principal = caw.principal - v_vat_principal, caw.vat_due_amount = v_vat_principal + caw.vat_interest, caw.net_due_amount = caw.due_amount - caw.vat_due_amount where caw.contract_id = r_con_contract_rec.contract_id and caw.cf_item = 250 and caw.times = r_con_contract_change_req.ccr_start_times + to_number(r_calculator_hd_rec.lease_times) - 1; /* elsif r_con_contract_rec.business_type='LEASEBACK' THEN deal_repur_cash_leaseback(p_contract_id =>p_contract_id, p_interest =>v_interest_back); hls_sys_log_pkg.log('��ͬid' || p_contract_id || '˰��' ||v_interest_back); update con_contract_cashflow caw set caw.interest=v_interest_back, caw.vat_interest=round(v_interest_back*0.06/1.06,2), caw.net_interest=v_interest_back-round(v_interest_back*0.06/1.06,2), caw.principal=caw.due_amount-v_interest_back, caw.vat_principal = 0, caw.net_principal = caw.due_amount-v_interest_back, caw.vat_due_amount = round(caw.due_amount*0.06/1.06,2), caw.net_due_amount = caw.due_amount - round(caw.due_amount*0.06/1.06,2) where caw.contract_id = r_con_contract_rec.contract_id and cf_item = 250 and caw.times = r_con_contract_change_req.ccr_start_times + to_number(r_calculator_hd_rec.lease_times) - 1; end if;*/ ----�¹ʵ�I-10652 �����ͬ������һ�ڿ�Ʊ�ֽ��� if r_con_contract_rec.business_type='LEASEBACK' then ---����һ���ۺ������ڻع����� v_cashflow_rec := NULL; v_interest := 0; v_principal := round(r_con_contract_rec.repurchase_tatal_amount, 2); v_outstanding_principal := 0; v_due_date := r_con_contract_rec.paymengt_deadlinedate; v_cashflow_rec.cashflow_id := con_contract_cashflow_s.nextval; v_cashflow_rec.contract_id := r_con_contract_rec.contract_id; v_cashflow_rec.cf_item := 251; v_cashflow_rec.cf_type := 1; v_cashflow_rec.cf_direction := 'INFLOW'; v_cashflow_rec.cf_status := 'RELEASE'; v_cashflow_rec.times := r_con_contract_change_req.ccr_start_times; v_cashflow_rec.calc_date := v_due_date; v_cashflow_rec.due_date := v_due_date; v_cashflow_rec.due_amount := r_con_contract_rec.repurchase_tatal_amount; v_cashflow_rec.vat_due_amount := round(r_con_contract_rec.repurchase_tatal_amount * 0.06 / 1.06, 2); v_cashflow_rec.net_due_amount := v_cashflow_rec.due_amount - v_cashflow_rec.vat_due_amount; v_cashflow_rec.principal := r_con_contract_rec.repurchase_tatal_amount; v_cashflow_rec.vat_principal := round(v_principal * 0.06 / 1.06, 2); v_cashflow_rec.net_principal := v_cashflow_rec.principal - v_cashflow_rec.vat_principal; v_cashflow_rec.interest := 0; v_cashflow_rec.net_interest := 0; v_cashflow_rec.vat_interest := 0; v_cashflow_rec.received_amount := 0; v_cashflow_rec.received_principal := 0; v_cashflow_rec.received_interest := 0; v_cashflow_rec.outstanding_prin_tax_incld := v_outstanding_principal; v_cashflow_rec.write_off_flag := 'NOT'; v_cashflow_rec.overdue_status := 'N'; v_cashflow_rec.penalty_process_status := 'NORMAL'; v_cashflow_rec.billing_status := 'NOT'; v_cashflow_rec.ln_user_col_v03 := 'REPUR'; v_cashflow_rec.generated_source := 'MANUAL'; v_cashflow_rec.created_by := p_user_id; v_cashflow_rec.creation_date := SYSDATE; v_cashflow_rec.last_updated_by := p_user_id; v_cashflow_rec.last_update_date := SYSDATE; INSERT INTO con_contract_cashflow VALUES v_cashflow_rec; ---�����ͬ��˰ cus_con_contract_repo_pkg.deal_repur_cash_leaseback(p_contract_id =>p_contract_id, p_interest =>v_interest_back); update con_contract_cashflow caw set caw.interest=v_interest_back, caw.vat_interest=round(v_interest_back*0.06/1.06,2), caw.net_interest=v_interest_back-round(v_interest_back*0.06/1.06,2), caw.principal=caw.due_amount-v_interest_back, caw.vat_principal = 0, caw.net_principal = caw.due_amount-v_interest_back, caw.vat_due_amount = round(caw.due_amount*0.06/1.06,2), caw.net_due_amount = caw.due_amount - round(caw.due_amount*0.06/1.06,2), --�����ͬ����һ�����ֽ�����������Ʊ����������;���˴�������տ��� caw.received_interest=v_interest_back, caw.received_principal=caw.due_amount-v_interest_back where caw.contract_id = r_con_contract_rec.contract_id and cf_item = 251; end if; exception 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_contract_repo_pkg', p_procedure_function_name => 'calc_repo_cashflow'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end calc_repo_cashflow; PROCEDURE calc_one_time_repo_cashflow(p_contract_id IN NUMBER, p_user_id IN NUMBER) IS r_con_contract_rec con_contract%ROWTYPE; v_cashflow_rec con_contract_cashflow%ROWTYPE; r_con_contract_change_req con_contract_change_req%ROWTYPE; v_max_calc_time NUMBER; v_min_overdue_times number; v_start_change_times number; v_due_amount NUMBER; --ÿ����� v_outstanding_principal NUMBER; --ʣ�౾�� v_principal NUMBER; --���� v_interest NUMBER; --��Ϣ v_due_date DATE; v_calc_due_date DATE; v_times NUMBER; v_calc_days NUMBER; e_due_amount_error EXCEPTION; e_repo_calc_amount_error EXCEPTION; v_vat_principal number; v_deposit_deduct number; v_division varchar2(10); --�¹ʵ�I-10652 v_interest_back number; --�����ͬ��Ϣ --�¹ʵ�I-11074 e_last_rent_due_amount_error exception; BEGIN --�ع���ͬ select * into r_con_contract_rec from con_contract t where t.contract_id = p_contract_id; SELECT * INTO r_con_contract_change_req FROM con_contract_change_req t WHERE t.change_req_id = p_contract_id; --�¹ʵ�I-11074 �Ļع���ֱ���˻ص��µ�ǰ�����֧���ղ�һ������� if r_con_contract_rec.last_rent_due_date<>r_con_contract_change_req.last_rent_due_date then raise e_last_rent_due_amount_error; end if; restore_cashflow_from_con(p_change_req_id =>p_contract_id, p_contract_id =>r_con_contract_change_req.contract_id, p_user_id =>p_user_id); /*--ɾ��ǰһ�α����������Լ�ع������� delete from con_contract_cashflow t where t.contract_id = p_contract_id and t.cf_item in (250); --��ԭǰһ�α�����㶳����� update con_contract_cashflow set cf_direction = 'INFLOW', cf_status = 'RELEASE' where contract_id = p_contract_id and cf_direction = 'NONCASH' and cf_status = 'BLOCK' and ln_user_col_v03 = 'REPUR'; --ɾ����Լ�ع���֮��ķ�Ϣ delete from con_contract_cashflow t where t.contract_id = p_contract_id and t.cf_item = 9 and t.times > r_con_contract_change_req.ccr_start_times and t.write_off_flag = 'NOT';*/ --�ع�У�� con_change_single_calc_pkg.check_deposit_deduct(p_contract_id,p_user_id); --����ķ�Ϣ�ֽ������� FOR c_contract_cashflow IN (SELECT a.* FROM con_contract_cashflow a, con_contract_cf_item b WHERE a.contract_id = p_contract_id AND a.cf_status = 'RELEASE' AND a.due_date <= r_con_contract_change_req.last_rent_due_date --ֻ�������IJż��㷣Ϣ�� AND a.penalty_process_status = 'NORMAL' AND a.cf_direction = 'INFLOW' AND a.contract_id = b.contract_id AND a.cf_item = b.cf_item AND b.calc_penalty = 'Y' ORDER BY times, due_date) LOOP CON_OVERDUE_PENALTY_PKG.overdue_dayend(p_contract_cashflow_rec => c_contract_cashflow, p_contract_rec => r_con_contract_rec, p_calc_date => r_con_contract_change_req.repurchase_date, p_user_id => p_user_id); END LOOP; --����ҵ��֤���Զ����� calc_deposit_auto_split(p_change_req_id =>p_contract_id,p_user_id=>p_user_id); --step 3 ƴ�ӱ����ʼ��֮ǰ���ֽ����ͱ����ʼ��֮����ֽ��� ����µ�cashflow --��ȡ�������֮ǰ�����һ�� ��ǰ��֧��Ԥ������������ select cw.times into v_max_calc_time from con_contract_cashflow cw where cw.contract_id = r_con_contract_change_req.contract_id and cw.due_date = r_con_contract_rec.last_rent_due_date and cw.cf_item = 1; --��ȡ������Сһ������ ���� select min(cw.times) into v_min_overdue_times from con_contract_cashflow cw where cw.contract_id = r_con_contract_change_req.contract_id and cw.times <= v_max_calc_time and cw.write_off_flag <> 'FULL' and cw.cf_item in (1, 8) and nvl(cw.due_amount, 0) <> 0; if v_min_overdue_times is null then v_min_overdue_times := r_con_contract_change_req.ccr_start_times; end if; select max(cw.times) into v_start_change_times from con_contract_cashflow cw where cw.contract_id = r_con_contract_change_req.contract_id and cw.times <= v_max_calc_time and cw.write_off_flag <> 'FULL' and cw.cf_item in (1, 8) and nvl(cw.due_amount, 0) <> 0; --��������������ֽ��� update con_contract_cashflow caw set caw.cf_status = 'BLOCK', caw.cf_direction = 'NONCASH', caw.ln_user_col_v03 = 'REPUR' where caw.contract_id = r_con_contract_rec.contract_id and ((caw.times >= v_min_overdue_times) or nvl(caw.due_amount, 0) = 0); -- ����һ��һ���Իع����� v_cashflow_rec := NULL; v_interest := 0; v_principal := round(r_con_contract_rec.repurchase_tatal_amount, 2); v_outstanding_principal := 0; v_due_date := r_con_contract_rec.paymengt_deadlinedate; v_cashflow_rec.cashflow_id := con_contract_cashflow_s.nextval; v_cashflow_rec.contract_id := r_con_contract_rec.contract_id; v_cashflow_rec.cf_item := 250; v_cashflow_rec.cf_type := 1; v_cashflow_rec.cf_direction := 'INFLOW'; v_cashflow_rec.cf_status := 'RELEASE'; v_cashflow_rec.times := r_con_contract_change_req.ccr_start_times; v_cashflow_rec.calc_date := v_due_date; v_cashflow_rec.due_date := v_due_date; v_cashflow_rec.due_amount := r_con_contract_rec.repurchase_tatal_amount; v_cashflow_rec.vat_due_amount := round(r_con_contract_rec.repurchase_tatal_amount * 0.13 / 1.13, 2); v_cashflow_rec.net_due_amount := v_cashflow_rec.due_amount - v_cashflow_rec.vat_due_amount; v_cashflow_rec.principal := r_con_contract_rec.repurchase_tatal_amount; v_cashflow_rec.vat_principal := round(v_principal * 0.13 / 1.13, 2); v_cashflow_rec.net_principal := v_cashflow_rec.principal - v_cashflow_rec.vat_principal; v_cashflow_rec.interest := 0; v_cashflow_rec.net_interest := 0; v_cashflow_rec.vat_interest := 0; v_cashflow_rec.received_amount := 0; v_cashflow_rec.received_principal := 0; v_cashflow_rec.received_interest := 0; v_cashflow_rec.outstanding_prin_tax_incld := v_outstanding_principal; v_cashflow_rec.write_off_flag := 'NOT'; v_cashflow_rec.overdue_status := 'N'; v_cashflow_rec.penalty_process_status := 'NORMAL'; v_cashflow_rec.billing_status := 'NOT'; v_cashflow_rec.ln_user_col_v03 := 'REPUR'; v_cashflow_rec.generated_source := 'MANUAL'; v_cashflow_rec.created_by := p_user_id; v_cashflow_rec.creation_date := SYSDATE; v_cashflow_rec.last_updated_by := p_user_id; v_cashflow_rec.last_update_date := SYSDATE; INSERT INTO con_contract_cashflow VALUES v_cashflow_rec; --���±��α��ۼ���ΪY update con_contract ct set ct.offset_flag = 'Y' where ct.contract_id = p_contract_id; --�¹ʵ�I-10652 �����ͬ��Լ�ع��������� if r_con_contract_rec.business_type='LEASE' then --�������һ�ڽ�Լ�ع�������Ϣ��˰ deal_repur_cash(p_contract_id => p_contract_id, p_vat_principal => v_vat_principal); hls_sys_log_pkg.log('��ͬid' || p_contract_id || '˰��' ||v_vat_principal); update con_contract_cashflow caw set caw.vat_principal = v_vat_principal, caw.net_principal = caw.principal - v_vat_principal, caw.vat_due_amount = v_vat_principal, caw.net_due_amount = caw.due_amount - v_vat_principal where caw.contract_id = r_con_contract_rec.contract_id and cf_item = 250; elsif r_con_contract_rec.business_type='LEASEBACK' THEN deal_repur_cash_leaseback(p_contract_id =>p_contract_id, p_interest =>v_interest_back); hls_sys_log_pkg.log('��ͬid' || p_contract_id || '˰��' ||v_interest_back); update con_contract_cashflow caw set caw.interest=v_interest_back, caw.vat_interest=round(v_interest_back*0.06/1.06,2), caw.net_interest=v_interest_back-round(v_interest_back*0.06/1.06,2), caw.principal=caw.due_amount-v_interest_back, caw.vat_principal = 0, caw.net_principal = caw.due_amount-v_interest_back, caw.vat_due_amount = round(caw.due_amount*0.06/1.06,2), caw.net_due_amount = caw.due_amount - round(caw.due_amount*0.06/1.06,2) where caw.contract_id = r_con_contract_rec.contract_id and cf_item = 250; end if; EXCEPTION WHEN e_repo_calc_amount_error THEN sys_raise_app_error_pkg.raise_sys_others_error(p_message => '�ع��ۿ��ܶ�С��0�����飡', p_created_by => -1, p_package_name => 'cus_con_contract_repo_pkg', p_procedure_function_name => 'calc_one_time_repo_cashflow'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); WHEN e_due_amount_error THEN sys_raise_app_error_pkg.raise_sys_others_error(p_message => '�ع���ͬ����������������ϵϵͳ����Ա��', p_created_by => p_user_id, p_package_name => 'cus_con_contract_repo_pkg', p_procedure_function_name => 'calc_one_time_repo_cashflow'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); WHEN e_last_rent_due_amount_error THEN sys_raise_app_error_pkg.raise_sys_others_error(p_message => 'ǰ�����֧���շ����˱������ȡ�����α���������·�������', p_created_by => p_user_id, p_package_name => 'cus_con_contract_repo_pkg', p_procedure_function_name => 'calc_one_time_repo_cashflow'); 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_contract_repo_pkg', p_procedure_function_name => 'calc_one_time_repo_cashflow'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END calc_one_time_repo_cashflow; PROCEDURE calc_cashflow_division_tj(p_contract_id IN NUMBER, p_user_id IN NUMBER) is r_con_contract_rec con_contract%ROWTYPE; v_deposit_deduct number; v_division varchar2(10); begin --�ع���ͬ select * into r_con_contract_rec from con_contract t where t.contract_id = p_contract_id; --����ҵ��֤���Զ����� select nvl(req.deposit_deduct,0) into v_deposit_deduct from con_contract req Where req.contract_id = r_con_contract_rec.contract_id; select req.division into v_division from con_contract req Where req.contract_id = r_con_contract_rec.contract_id; if v_deposit_deduct>0 and v_division='70' and r_con_contract_rec.contract_id<>null then delete from csh_write_off_interface c where c.contract_id= r_con_contract_rec.contract_id and c.trx_interface_id is null; for cashflow_tj in (select * from con_contract_cashflow c where c.contract_id=r_con_contract_rec.contract_id and c.write_off_flag <>'FULL' and c.cf_item in ('1','9') order by c.times,decode(c.cf_item,'9',1,2)) LOOP if (cashflow_tj.due_amount-nvl(cashflow_tj.received_amount,0))>=v_deposit_deduct then update con_contract_cashflow c set c.received_amount=v_deposit_deduct+nvl(cashflow_tj.received_amount,0) where c.cashflow_id=cashflow_tj.cashflow_id; insert into csh_write_off_interface values (csh_write_off_interface_s.nextval, 'MANUAL', null, null, null, null, 1, r_con_contract_rec.contract_number, cashflow_tj.contract_id, cashflow_tj.cashflow_id, 'DEPOSIT_CREDIT', sysdate, '2018-11', 201811, cashflow_tj.times, cashflow_tj.cf_item, v_deposit_deduct, v_deposit_deduct, null, null, r_con_contract_rec.bp_id_tenant, 'Y', null, null, null, sysdate, 1, sysdate, 1, null, null, cashflow_tj.contract_id, sysdate, null, null); exit; else update con_contract_cashflow c set c.received_amount=cashflow_tj.due_amount where c.cashflow_id=cashflow_tj.cashflow_id; insert into csh_write_off_interface values (csh_write_off_interface_s.nextval, 'MANUAL', null, null, null, null, 1, r_con_contract_rec.contract_number, cashflow_tj.contract_id, cashflow_tj.cashflow_id, 'DEPOSIT_CREDIT', sysdate, '2018-11', 201811, cashflow_tj.times, cashflow_tj.cf_item, cashflow_tj.due_amount, cashflow_tj.due_amount, null, null, r_con_contract_rec.bp_id_tenant, 'Y', null, null, null, sysdate, 1, sysdate, 1, null, null, cashflow_tj.contract_id, sysdate, null, null); v_deposit_deduct:=v_deposit_deduct-cashflow_tj.due_amount+nvl(cashflow_tj.received_amount,0); end if; END LOOP; end if; END calc_cashflow_division_tj; PROCEDURE repo_wfl_approved_mail(p_change_req_id number, p_user_id IN NUMBER) is r_change_con_rec con_contract%rowtype; v_bp_name varchar2(1000); v_repurchase_type_n varchar2(1000); v_sumbit_user_id number; v_mail_list_id Number; v_contract_number varchar2(1000); v_time Varchar2(1000); v_mail_to Varchar2(1000); v_mail_cc Varchar2(1000); v_subject Varchar2(1000); v_body Long; v_repurchase_date DATE; begin --�ʼ��ı������ֶ� select t.contract_number, hbm.bp_name, (SELECT v.code_value_name FROM sys_code_values_v v WHERE v.code = 'REPURCHASE_TYPES' AND v.code_value = t.REPURCHASE_TYPES) AS REPURCHASE_TYPES_n, t.repurchase_date into v_contract_number, v_bp_name, v_repurchase_type_n, v_repurchase_date from con_contract t, hls_bp_master hbm where t.contract_id = p_change_req_id and t.bp_id_tenant = hbm.bp_id; --mail_to ���� -- 013 GAOYAN 015 ZHAOJIE /*select replace(wm_concat(email), ',', ';') into v_mail_to from exp_employees where employee_code in ('GAOYAN', 'ZHAOJIE');*/ -- @Description: @Date: @Modified:Liyuan.Chen v_mail_to := sys_parameter_pkg.email_by_position('013') || sys_parameter_pkg.email_by_position('015'); Select zj_sys_mailing_list_s.nextval Into v_mail_list_id From dual; v_time := to_char(v_repurchase_date, 'yyyy"��"mm"��"dd"��"'); v_subject := '�ع���������������֪ͨ'; v_body := '���쵼������:' || chr(13) || chr(10) || chr(13) || chr(10) || chr(32) || chr(32) || chr(32) || chr(32) || chr(32) || chr(32) || '��ͬ��ţ�' || v_contract_number || ',������:' || v_bp_name || ',ͨ����' || v_repurchase_type_n || '����,��Լ�ع���:' || v_time || chr(13) || chr(10) || chr(13) || chr(10) || '��֪Ϥ,лл��'; Select zj_sys_mailing_list_s.nextval Into v_mail_list_id From dual; ---�������ʼ���¼���� zj_sys_mail_pkg.insert_mailing_list(p_mail_list_id => v_mail_list_id, p_mail_to => v_mail_to, p_mail_cc => v_mail_cc, p_subject => v_subject, p_body => v_body, p_user_id => p_user_id, p_mail_source => Null, p_mail_source_id => Null, p_content_type => 'text/plain'); --�����ύ�� repo_wfl_mail_to_submit(p_change_req_id => p_change_req_id, p_user_id => p_user_id, p_wfl_flag_desc => 'ͨ��'); end repo_wfl_approved_mail; PROCEDURE repo_wfl_mail_to_submit(p_change_req_id number, p_user_id IN NUMBER, p_wfl_flag_desc varchar2) is r_change_req_rec con_contract_change_req%rowtype; r_change_con_rec con_contract%rowtype; v_mail_list_id Number; v_contract_number varchar2(1000); v_change_req_number varchar2(1000); v_time Varchar2(1000); v_mail_to Varchar2(1000); v_mail_cc Varchar2(1000); v_subject Varchar2(1000); v_body Long; begin ----�ʼ��ı������ֶ�,mail_to select t.change_req_number, ee.email into v_change_req_number, v_mail_to from con_contract_change_req t, sys_user su, exp_employees ee where t.change_req_id = p_change_req_id and t.created_by = su.user_id and su.employee_id = ee.employee_id; --��ͬ��Ϣ select * into r_change_con_rec from con_contract t where t.contract_id = p_change_req_id; v_time := to_char(Sysdate, 'yyyy"��"mm"��"dd"��"'); v_subject := '�ع���������������֪ͨ'; v_body := '����:' || chr(13) || chr(10) || chr(13) || chr(10) || chr(32) || chr(32) || chr(32) || chr(32) || chr(32) || chr(32) || '�ع����빤����-�ع���ţ�' || v_change_req_number || '��ͬ��ţ�' || r_change_con_rec.contract_number || ',������������' || p_wfl_flag_desc || '��' || ' http://10.203.0.125:8082/hlcm_leasing/login' || chr(13) || chr(10) || chr(13) || chr(10) || '��֪Ϥ,лл��'; Select zj_sys_mailing_list_s.nextval Into v_mail_list_id From dual; ---�������ʼ���¼���� zj_sys_mail_pkg.insert_mailing_list(p_mail_list_id => v_mail_list_id, p_mail_to => v_mail_to, p_mail_cc => v_mail_cc, p_subject => v_subject, p_body => v_body, p_user_id => p_user_id, p_mail_source => Null, p_mail_source_id => Null, p_content_type => 'text/plain'); end repo_wfl_mail_to_submit; end cus_con_contract_repo_pkg; /