ORACLE中的function 、package、package bodies、procedure的区别和相同：
- function有返回值，有参数；
- procedure无返回值，有参数；
- package、package body是同时存在的，就像.h和.cpp文件，如果要外部调用的，就在package里声明一下，包内调用的，只要在body里写就行了。
创建package
create or replace package cus_csh_payment_wfl_pkg is
-- Author : gaoyang
-- Created : 2016-4-4 9:14:48
-- Purpose :
procedure auto_create_csh_payment(p_contract_id number, p_user_id number);//调用需要在这里声明
procedure calc_due_date_after_print(p_contract_id number,
p_user_id number);//调用需要在这里声明
end cus_csh_payment_wfl_pkg;
创建package bodies
create or replace package body cus_csh_payment_wfl_pkg is
e_lock_error exception;
pragma exception_init(e_lock_error, -54);
procedure lock_con_contract(p_contract_id number,
p_user_id number,
p_con_contract out con_contract%rowtype) is
begin
select *
into p_con_contract
from con_contract t
where t.contract_id = p_contract_id
for update nowait;
exception
when e_lock_error then
sys_raise_app_error_pkg.raise_sys_others_error(p_message => '该合同资源正忙，请稍后再试',
p_created_by => p_user_id,
p_package_name => 'cus_csh_payment_wfl_pkg',
p_procedure_function_name => 'lock_con_contract');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
end;
procedure lock_csh_payment_req_hd(p_payment_req_id in number,
p_user_id in number,
p_csh_payment_req_hd_rec out csh_payment_req_hd%rowtype) is
begin
select *
into p_csh_payment_req_hd_rec
from csh_payment_req_hd t
where t.payment_req_id = p_payment_req_id
for update nowait;
exception
when e_lock_error then
sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CSH_TRANSACTION_PKG.CSH510_LOCK',
p_created_by => p_user_id,
p_package_name => 'cus_csh_payment_wfl_pkg',
p_procedure_function_name => 'lock_csh_payment_req_hd');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
end;
procedure ins_csh_payment_req_ln_ddct(p_payment_req_ln_id number,
p_ref_doc_category varchar2,
p_ref_doc_id number,
p_ref_doc_line_id number,
p_amount number,
p_principal number,
p_interest number,
p_user_id number) is
begin
insert into csh_payment_req_ln_ddct
(payment_req_ln_ddct_id,
payment_req_ln_id,
ref_doc_category,
ref_doc_id,
ref_doc_line_id,
amount,
principal,
interest,
deduction_flag,
creation_date,
created_by,
last_update_date,
last_updated_by)
values
(csh_payment_req_ln_ddct_s.nextval,
p_payment_req_ln_id,
p_ref_doc_category,
p_ref_doc_id,
p_ref_doc_line_id,
p_amount,
p_principal,
p_interest,
'N',
sysdate,
p_user_id,
sysdate,
p_user_id);
end;
procedure delete_csh_payment_req_ln_tmp(p_session_id in number,
p_type in varchar2 default null) is
begin
delete from csh_payment_req_ln_tmp
where session_id = p_session_id
and type = nvl(p_type, type);
end;
procedure insert_csh_payment_req_ln_tmp(p_session_id in number,
p_id in number,
p_type in varchar2,
p_amt in number default null) is
begin
insert into csh_payment_req_ln_tmp
(session_id, type, id, amt)
values
(p_session_id, p_type, p_id, p_amt);
end;
--自动分配收付抵扣
procedure auto_allocation_ddct(p_session_id in number,
p_payment_req_id in number,
p_user_id in number) is
v_req_amt number;
v_trx_amt number;
v_trx_amt_p number;
v_trx_amt_i number;
v_amt number;
v_amt_p number;
v_amt_i number;
r_payment_req_rec csh_payment_req_hd%rowtype;
begin
select *
into r_payment_req_rec
from csh_payment_req_hd
where payment_req_id = p_payment_req_id;
--清除原分配数据
delete from csh_payment_req_ln_ddct p
where p.payment_req_ln_id in
(select t.id
from csh_payment_req_ln_tmp t
where t.session_id = p_session_id
and t.type = 'PAYMENT_REQ_LN');
for c_payment_req in (select l.payment_req_ln_id,
l.bp_id,
l.amount -
nvl((select sum(d.write_off_amt)
from csh_payment_req_ln_prepay d
where d.payment_req_ln_id =
l.payment_req_ln_id
and nvl(d.write_off_flag, 'N') = 'N'),
0) as amount,
l.ref_doc_id as contract_id,
l.ref_doc_line_id as cashflow_id,
c.pay_type
from csh_payment_req_ln l,
csh_payment_req_ln_tmp t,
con_contract c,
con_contract_cashflow ccc
where t.session_id = p_session_id
and t.type = 'PAYMENT_REQ_LN'
and l.payment_req_ln_id = t.id
and l.payment_req_ln_type = 'DEBT'
and l.ref_doc_id = c.contract_id
and l.ref_doc_category = 'CONTRACT'
and l.ref_doc_line_id = ccc.cashflow_id
and ccc.cf_item = 5
order by c.contract_id,
ccc.cf_item,
l.payment_req_ln_id) loop
v_req_amt := c_payment_req.amount;
for c_cashflow in (select ccc.contract_id,
ccc.cashflow_id,
ccc.cf_item,
ccc.due_amount - nvl(ccc.received_amount, 0) as due_amount,
ccc.principal -
nvl(ccc.received_principal, 0) as principal,
ccc.interest - nvl(ccc.received_interest, 0) as interest
from con_contract_cashflow ccc
where ccc.contract_id = c_payment_req.contract_id
and ccc.cf_direction = 'INFLOW'
and ccc.cf_status = 'RELEASE'
and ccc.write_off_flag <> 'FULL'
-- and ccc.times = 0 modify by xuls 2016-11-18 不限制0期间
and (ccc.cf_type in (2, 3, 5/*,8*/,6)
or (ccc.cf_type in ('908','12') and ccc.times=0)
)
order by ccc.cf_item) loop
select c_cashflow.due_amount - nvl(sum(d.amount), 0),
c_cashflow.principal - nvl(sum(d.principal), 0),
c_cashflow.interest - nvl(sum(d.interest), 0)
into v_trx_amt, v_trx_amt_p, v_trx_amt_i
from csh_payment_req_ln_ddct d
where d.payment_req_ln_id = c_payment_req.payment_req_ln_id
and d.ref_doc_category = 'CONTRACT'
and d.ref_doc_line_id = c_cashflow.cashflow_id
and nvl(d.deduction_flag, 'N') = 'N';
--金额为0时，退出循环
if v_trx_amt > 0 then
if v_req_amt > v_trx_amt then
v_amt := v_trx_amt;
else
v_amt := v_req_amt;
end if;
--分配本息
if c_cashflow.cf_item = 1 then
if v_trx_amt_i >= v_amt then
v_amt_i := v_amt;
v_amt_p := 0;
else
v_amt_i := v_trx_amt_i;
v_amt_p := v_amt - v_amt_i;
end if;
else
v_amt_p := null;
v_amt_i := null;
end if;
ins_csh_payment_req_ln_ddct(p_payment_req_ln_id => c_payment_req.payment_req_ln_id,
p_ref_doc_category => 'CONTRACT',
p_ref_doc_id => c_cashflow.contract_id,
p_ref_doc_line_id => c_cashflow.cashflow_id,
p_amount => v_amt,
p_principal => v_amt_p,
p_interest => v_amt_i,
p_user_id => p_user_id);
v_req_amt := v_req_amt - v_amt;
exit when v_req_amt <= 0;
end if;
end loop;
end loop;
delete from csh_payment_req_ln_tmp
where session_id = p_session_id
and type in ('PREPAYMENT_TRX', 'PAYMENT_REQ_LN');
end;
procedure csh_payment_req_amount_check(p_cashflow_id number,
p_amount number,
p_company_id number,
p_user_id number) is
e_req_amount_check exception;
e_req_amount_zero_check exception;
v_due_amount number;
v_amount number;
v_amount_paid number;
v_amount_approving number;
begin
select nvl(f.due_amount, 0)
into v_due_amount
from con_contract_cashflow f
where f.cashflow_id = p_cashflow_id;
select sum(l.amount)
into v_amount
from csh_payment_req_ln l, csh_payment_req_hd h
where l.ref_doc_line_id = p_cashflow_id
and l.payment_req_id = h.payment_req_id
and h.submitted_flag = 'Y'
and h.approval_status = 'APPROVED'
and nvl(h.closed_flag, 'N') <> 'Y';
select sum(nvl(l.amount_paid, 0))
into v_amount_paid
from csh_payment_req_ln l, csh_payment_req_hd h
where l.ref_doc_line_id = p_cashflow_id
and l.payment_req_id = h.payment_req_id
and h.submitted_flag = 'Y'
and h.approval_status = 'APPROVED'
and nvl(h.closed_flag, 'N') = 'Y';
select sum(l.amount)
into v_amount_approving
from csh_payment_req_ln l, csh_payment_req_hd h
where l.ref_doc_line_id = p_cashflow_id
and l.payment_req_id = h.payment_req_id
and h.submitted_flag = 'Y'
and h.approval_status = 'APPROVING';
if p_amount > (v_due_amount - nvl(v_amount, 0) - nvl(v_amount_paid, 0) -
nvl(v_amount_approving, 0)) then
raise e_req_amount_check;
elsif p_amount = 0 or p_amount is null then
raise e_req_amount_zero_check;
end if;
exception
when e_req_amount_check then
sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CSH_PAYMENT_REQ_PKG.CSH501_REQ_AMOUNT_CHECK',
p_created_by => p_user_id,
p_package_name => 'cus_csh_payment_wfl_pkg',
p_procedure_function_name => 'csh_payment_req_amount_check');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
when e_req_amount_zero_check then
sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CSH_PAYMENT_REQ_PKG.CSH501_REQ_AMOUNT_ZERO_CHECK',
p_created_by => p_user_id,
p_package_name => 'cus_csh_payment_wfl_pkg',
p_procedure_function_name => 'csh_payment_req_amount_check');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
end;
procedure auto_create_csh_payment(p_contract_id number, p_user_id number) is
r_con_contract con_contract%rowtype;
e_con_status_error exception;
v_payment_req_id number;
v_anget_bp_id number;
v_payment_req_ln_id number;
v_5_payment_req_ln_id number;
v_instance_id number;
v_session_id number;
v_document_type con_contract.document_type%type;
-- v_cashflow_rec con_contract_cashflow%rowtype;
v_bp_bank_account_rec hls_bp_master_bank_account%rowtype;
v_csh_payment_req_hd_rec csh_payment_req_hd%rowtype;
v_sum_due_amount number;
e_agent_bank_account_error exception;
begin
lock_con_contract(p_contract_id => p_contract_id,
p_user_id => p_user_id,
p_con_contract => r_con_contract);
select d.wfl_instance_id
into v_instance_id
from prj_project d
where d.project_id = r_con_contract.project_id;
if r_con_contract.contract_status <> 'SIGN' then
raise e_con_status_error;
end if;
select cc.document_type
into v_document_type
from con_contract cc
where cc.contract_id=p_contract_id;
if v_document_type = 'CARCON' then
select t.bp_id
into v_anget_bp_id
from con_contract_bp t
where t.bp_category = 'AGENT'
and t.enabled_flag = 'Y'
and t.contract_id = r_con_contract.contract_id
and rownum = 1;
elsif v_document_type = 'PRJCON' then
v_anget_bp_id := r_con_contract.unit_id;
else
null;
end if;
begin
select *
into v_bp_bank_account_rec
from hls_bp_master_bank_account t
where t.bp_id = v_anget_bp_id
and t.enabled_flag = 'Y'
and rownum=1;
exception
when no_data_found then
raise e_agent_bank_account_error;
end;
select sum(cfl.due_amount)
into v_sum_due_amount
from con_contract_cashflow cfl
where cfl.contract_id = r_con_contract.contract_id
and cfl.times = 0
-- and cfl.cf_item = 5
and cfl.cf_item != 81 --GPS费用单独发起付款申请 add by Spencer 3893 20160819
and cfl.cf_direction = 'OUTFLOW'
and cfl.cf_status = 'RELEASE';
csh_payment_req_pkg.ins_csh_payment_req_hd(p_payment_req_id => v_payment_req_id,
p_company_id => r_con_contract.company_id,
p_document_type => 'STD_PAYMENT_REQ',
p_payment_req_number => null,
p_req_date => sysdate,
p_apply_pay_date => sysdate,
p_transaction_category => 'BUSINESS',
p_distribution_set_id => '',
p_payment_method_id => null,
p_bp_id => v_anget_bp_id,
p_bp_category => 'AGENT',
-- p_amount => v_cashflow_rec.due_amount,
p_amount => v_sum_due_amount,
p_currency_code => r_con_contract.currency,
p_description => null,
p_submitted_flag => 'N',
p_printed_flag => 'N',
p_printed_times => 0,
p_last_print_date => null,
p_closed_flag => 'N',
p_closed_date => sysdate,
p_closed_note => null,
p_approval_status => null,
p_approval_date => null,
p_approval_note => null,
p_bp_bank_account_id => v_bp_bank_account_rec.bank_account_id,
p_bp_bank_account_num => v_bp_bank_account_rec.bank_account_num,
p_bp_bank_account_name => v_bp_bank_account_rec.bank_account_name,
p_business_type => 'PAYMENT',
p_branch_id => v_bp_bank_account_rec.branch_id,
p_city_id => v_bp_bank_account_rec.city_id,
p_user_id => p_user_id);
for v_cashflow_rec in (select *
from con_contract_cashflow cfl
where cfl.contract_id =
r_con_contract.contract_id
and cfl.times = 0
-- and cfl.cf_item = 5
and cfl.cf_item != 81 --GPS费用单独发起付款申请 add by Spencer 3893 20160819
and cfl.cf_direction = 'OUTFLOW'
and cfl.cf_status = 'RELEASE') loop
v_payment_req_ln_id := '';
csh_payment_req_pkg.ins_csh_payment_req_ln(p_payment_req_ln_id => v_payment_req_ln_id,
p_payment_req_id => v_payment_req_id,
p_payment_req_ln_type => 'DEBT',
p_ref_doc_category => 'CONTRACT',
p_ref_doc_id => r_con_contract.contract_id,
p_ref_doc_line_id => v_cashflow_rec.cashflow_id,
p_bp_id => v_anget_bp_id,
p_currency_code => r_con_contract.currency,
p_amount => v_cashflow_rec.due_amount,
p_amount_paid => 0,
p_description => null,
p_payment_status => 'NOT',
p_payment_completed_date => null,
p_apply_pay_date => sysdate,
p_payment_method_id => null,
p_bank_account_id => null,
p_bank_account_num => null,
p_bank_account_name => null,
p_bank_branch_name => null,
p_bp_bank_account_id => v_bp_bank_account_rec.bank_account_id,
p_bp_bank_account_num => v_bp_bank_account_rec.bank_account_num,
p_bp_bank_account_name => v_bp_bank_account_rec.bank_account_name,
p_user_id => p_user_id,
p_company_id => r_con_contract.company_id);
if v_cashflow_rec.cf_item = 5 then
v_5_payment_req_ln_id := v_payment_req_ln_id;
end if;
end loop;
lock_csh_payment_req_hd(p_payment_req_id => v_payment_req_id,
p_user_id => p_user_id,
p_csh_payment_req_hd_rec => v_csh_payment_req_hd_rec);
for csh_payment_req_ln_rec in (select *
from csh_payment_req_ln l
where l.payment_req_id =
v_payment_req_id) loop
csh_payment_req_amount_check(p_cashflow_id => csh_payment_req_ln_rec.ref_doc_line_id,
p_amount => csh_payment_req_ln_rec.amount,
p_company_id => r_con_contract.company_id,
p_user_id => p_user_id);
end loop;
update csh_payment_req_hd
set approval_status = 'APPROVING',
wfl_instance_id = v_instance_id,
last_updated_by = p_user_id,
last_update_date = sysdate
where payment_req_id = v_payment_req_id;
update csh_payment_req_hd t
set t.submitted_flag = 'Y'
where t.payment_req_id = v_payment_req_id;
--设置工作流参数
zj_wfl_core_pkg.set_parameter_value(p_instance_id => v_instance_id,
p_parameter_name => 'PAYMENT_REQ_ID',
p_parameter_value => v_payment_req_id,
p_user_id => p_user_id);
v_session_id := v_payment_req_id * -1;
--默认收付抵扣首付款和保证金,贴息
--if v_document_type = 'CARCON' then
for r_cashflow in (select cfl.due_amount
from con_contract_cashflow cfl
where cfl.contract_id = r_con_contract.contract_id
and cfl.cf_status = 'RELEASE'
and (cfl.cf_item in (2, 3, 51,/*8,*/61)
or (cfl.cf_item in('913','912','12') and times=0)
)
--and cfl.times = 0 modify by xuls 2016-11-18不限制 0期
AND cfl.write_off_flag <> 'FULL'--add by Spencer 3893 20160819
) loop
insert_csh_payment_req_ln_tmp(p_session_id => v_session_id,
p_id => v_5_payment_req_ln_id,
p_type => 'PAYMENT_REQ_LN',
p_amt => r_cashflow.due_amount);
end loop;
--end if;
auto_allocation_ddct(p_session_id => v_session_id,
p_payment_req_id => v_payment_req_id,
p_user_id => p_user_id);
exception
when e_con_status_error then
sys_raise_app_error_pkg.raise_sys_others_error(p_message => '只有签约状态的合同才允许此操作！',
p_created_by => p_user_id,
p_package_name => 'cus_csh_payment_wfl_pkg',
p_procedure_function_name => 'auto_create_csh_payment');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
when e_agent_bank_account_error then
sys_raise_app_error_pkg.raise_sys_others_error(p_message => '该合同代理商银行账号未维护',
p_created_by => p_user_id,
p_package_name => 'csh_payment_workflow_pkg',
p_procedure_function_name => 'auto_create_csh_payment');
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 => 'csh_payment_workflow_pkg',
p_procedure_function_name => 'auto_create_csh_payment');
raise_application_error(sys_raise_app_error_pkg.c_error_number,
sys_raise_app_error_pkg.g_err_line_id);
end;
procedure calc_due_date_after_print(p_contract_id number,
p_user_id number) is
r_con con_contract%rowtype;
v_contract_status varchar2(30);
begin
NULL;
/* select cc.contract_status
into v_contract_status
from con_contract cc
where cc.contract_id = p_contract_id;
if v_contract_status = 'NEW' or v_contract_status = 'SIGN' then
lock_con_contract(p_contract_id => p_contract_id,
p_user_id => p_user_id,
p_con_contract => r_con);
con_contract_pkg.create_cf_date(p_contract_rec => r_con,
p_inception_of_lease => trunc(sysdate));
update con_contract_cashflow cfl
set cfl.due_date = sysdate,
cfl.calc_date = sysdate,
cfl.fin_income_date = sysdate
where cfl.contract_id = p_contract_id
--and cfl.cf_item = 5
--and cfl.cf_direction = 'OUTFLOW'modify by 20160818 Spencer 3893 INFLOW也需要更新
and cfl.cf_status = 'RELEASE'
and cfl.times = 0;
end if;*/
end;
end cus_csh_payment_wfl_pkg;
使用package bodies
begin
cus_csh_payment_wfl_pkg.calc_due_date_after_print(
p_contract_id =>${@contract_id},
p_user_id =>${/session/@user_id});
end;
扫一扫
1669
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
