试试下面的方法：
procedure CALC_AGE_COMMISSION(acontractid integer, --合同编号
abegin_date date, --起始日期
aend_date date, --截止日期
acalc_type integer, --计算方式 0 一般计算，1 增量重算
ainv_date date --发票日期
)is
laffid integer; --商户ID
lgroup_comm integer; --集团结算方式
lgroup_id integer; --集团号
lcomm_type integer; --明细计算方式
ltotal_amount number; --总金额
ltotal_commission number; --总佣金
ltotal_tax number; --总税金
-- ldetail_amount number; --明细交易额
-- ldetail_commission number; --明细佣金
-- ldetail_tax number; --明细税率
tmp integer; --一个临时变量
tmpamount number; --一个临时变量
lbegin_date date; --开始日期
lend_date date; --结束日期+1
lnext_date date; --下一次结束日期
linterval integer; --佣金计算时间间隔
ltime integer; --每月佣金计算次数
linvoice_id integer; --发票号
lcode_fee integer; --contract_feeaff中的费用类型
ltax_ratio number; --税率
ltitle varchar2(100); --佣金流水帐的标题
lcity_amount number; --各城市消费数量
lcity_code integer; --城市编号
lb_range number; --新定义的三个变量
le_range number; --为了计算区间百分比
lamount number;
laff_name varchar2(50);
llinesort integer;
lcarry integer; --进位方式 1:元 0:分
LINVOICE_DATE DATE; --结算日期,仅当ltime为5.指定日期时有效, add by lihd 2006-5-25
Lcode_client integer; --商户所对应的客户号，用于象可颂坊这样的客户，既是我们的客户，又是我们的商户，
--用于存放这样的关系
--如果是负数，表示是sodex客户,如果是正数,表示是loyaty客户 add by king om 20061110
lselfcomm number;
LAllot_aff integer;
LAllot_per Number;
LAllot_type integer;
LCODE_PAYOUT integer;
LCODE_TAKEIN integer;
CURSOR CURSOR_COM_ALLOT(AGENT_CONTRACT_ID integer)
IS
select CODE_ALLOT_AFFILIATE, ALLOT_PERCENT, ALLOT_TYPE
from com_allot where code_contractaff=AGENT_CONTRACT_ID;
--月付费类型
CURSOR CURSOR_MONTHLY_FEE(AGENT_CONTRACT_ID integer,CUR_BEGIN_DATE date)
IS
select CODE_PAYOUT, CODE_TAKEIN, AMOUNT,code_fee from contract_feeage
where monthly = 1
and (invoice_date is NULL or
to_char(invoice_date, 'mm') <> to_char(CUR_BEGIN_DATE, 'mm'))
and code_contractaff = AGENT_CONTRACT_ID;
-- 年付费类型
CURSOR CURSOR_YEARLY_FEE(AGENT_CONTRACT_ID integer,CUR_BEGIN_DATE date)
IS
select CODE_PAYOUT, CODE_TAKEIN, AMOUNT,code_fee from contract_feeage
where yearly = 1
and (invoice_date is NULL or
to_char(invoice_date, 'yyyy') <> to_char(CUR_BEGIN_DATE, 'yyyy'))
and code_contractaff = AGENT_CONTRACT_ID;
-- 固定的一次性费用
CURSOR CURSOR_FIX_FEE(AGENT_CONTRACT_ID integer)
IS
select CODE_PAYOUT, CODE_TAKEIN, AMOUNT,code_fee from contract_feeage
where yearly = 0
and monthly = 0
and code_invaff = 0
and code_contractaff = AGENT_CONTRACT_ID;
begin
select ca.code_affiliate, --商户号
ca.commission, --佣金计算方式
ca.gr_comm, --集团商户合计方式1 先加后算, 2先算后加
ca.group_aff, --集团号(不是集团的为0)
ca.INVOICE_INTERVAL, --时间间隔
ca.INVOICE_TIME, --每月计算次数
ca.TAX_COM / 100, --税率
CA.INVOICE_DATE, --指定的结算日期
ca.code_affdef --精确度 add by king on 20050720
into laffid,
lcomm_type,
lgroup_comm,
lgroup_id,
linterval,
ltime,
ltax_ratio,
LINVOICE_DATE, --指定的结算日期,add by lihd 20060525
lcarry --add by king on 20050720
from contract_aff ca
where ca.code_contractaff = acontractid
and ca.status = 1
and rownum = 1;
llinesort := 1;
ltotal_amount := 0;
ltotal_commission := 0;
ltotal_tax := 0; --先把总的佣金,金额清掉
lbegin_date := trunc(abegin_date);
lend_date := trunc(aend_date + 1); --把日期圆整了
--modify by king on 20060104
-- tmpamount := to_char(lend_date, 'dd') / linterval; --计算出本月已经计算了几次了
-- 只有把该值取整后，才能计算次数
IF LTIME <> 5 THEN
TMPAMOUNT := ROUND(TO_CHAR(LEND_DATE, 'dd') / LINTERVAL); --计算出本月已经计算了几次了
END IF;
--end
--包含本次计算,
if ltime = 1 then
--如果是一月一次的话，lend_date应当是月底，
--则加一后再取月底，也就是下月月底了,就得到了下次结算截止日期
lnext_date := last_day(lend_date);
ELSIF LTIME = 5 THEN
-- 如果是指定日期 ,add by lihd 20060525
-- 下一次的截止日为: 如果已经是月底了, 就取到下一个月的指定号
-- 如果不是月底, 就指向月底
IF TO_CHAR(LEND_DATE, 'dd') = '01' THEN
LNEXT_DATE := TO_DATE(TO_CHAR(LEND_DATE, 'YYYYMM') ||
TO_CHAR(LINVOICE_DATE, 'DD'),
'YYYYMMDD');
ELSE
LNEXT_DATE := LAST_DAY(LEND_DATE);
END IF;
--add by king on 20070225
--跨月计算佣金
elsif ltime=6 then
LNEXT_DATE :=add_months( TO_DATE(TO_CHAR(LEND_DATE, 'YYYYMM') ||
TO_CHAR(LINVOICE_DATE, 'DD'),
'YYYYMMDD'),1);
elsif ltime - tmpamount = 1 then
--如果本月还有一次要结算，则直接取下一次的日期是月底日期
--算完本次正好还有一次要算,就直接取到月底,不管还有几天了
lnext_date := last_day(lend_date - 1);
else
--否则，加上时间间隔，作为下次结算结束日期
lnext_date := lend_date - 1 + linterval;
end if;
if lcomm_type=1 then -- 预收费
update card set card_number='1' where card_number='1';
else
update card set card_number='1' where card_number='1';
end if;
-- 准备工作完成
select sum(tc.amount), sum(tc.undscnt_amount)
into ltotal_amount,ltotal_commission
from trans_consume tc
inner join v_all_card card on tc.card_number=card.card_number
where 1=1
and (tc.code_invaff = 0 or tc.code_invaff IS NULL)
and tc.card_trans_datetime >= lbegin_date
and tc.card_trans_datetime < lend_date
and card.code_category not in (2, 5)
and tc.code_affiliate =laffid;
-- 这里把turnover和commission计算出来
update /*+index(TRANS_CONSUME IDX_TC_CARD_TRANS_DT)*/trans_consume
set code_invaff = linvoice_id
where code_affiliate = laffid
and (code_invaff = 0 or code_invaff IS NULL) --没记发票号的
and card_trans_datetime >= lbegin_date
and card_trans_datetime < lend_date
and Exists (select *
from v_all_card card
where trans_consume.card_number = card.card_number
and card.code_category not in (2, 5));
-- 下边根据allot的情况分配
select code_invcli.Nextval into linvoice_id from dual;
-- 生成一个发票号
insert into invoice_agent
(code_invaff,
code_affiliate,
code_contractaff,
total_invoice,
total_turnover,
print_flag,
begin_date,
end_date,
date_invoice,
title)
values
(linvoice_id,
laffid,
acontractid,
0,
0,
0,
trunc(abegin_date),
trunc(aend_date),
ainv_date,
ltitle);
-- 只需要生成一条交易额的记录就可以了
insert into lineinv_age_balance
(code_balance,
code_invaff,
code_affiliate,
turnover,
commission,
code_city)
values
(CODE_AFFINV_BALANCE.Nextval,
linvoice_id,
laffid,
ltotal_amount,
0,
0);
OPEN CURSOR_COM_ALLOT(laffid); --打开游标
LOOP
-- CODE_ALLOT_AFFILIATE, ALLOT_PERCENT, ALLOT_TYPE
-- LAllot_aff integer;
-- LAllot_per Number;
-- LAllot_type integer;
FETCH CURSOR_COM_ALLOT
INTO LAllot_aff, LAllot_per, LAllot_type;
EXIT WHEN CURSOR_COM_ALLOT%NOTFOUND;
-- ltotal_amount := ldetail_amount + ltotal_amount; --一条条相加
if LAllot_aff=0 then
laff_name := 'Sodexhopass';
else
select decode(trim(invoice_title), null, name_chi, invoice_title)
into laff_name
from affiliate
where code_affiliate = LAllot_aff;
end if;
-- 根据分配，找出分配商户对应的名称
insert into lineinv_agent
(code_lineaff,
code_invaff,
code_affiliate,
code_type,
linesort,
title,
qty,
unit_price,
total,
groupby,
code_payout,
code_takein)
values
(code_lineaff.NextVal,
linvoice_id,
laffid,
0,
llinesort,
-- 'Turnover(sub) for ' || laff_name,
'Allot for'||laff_name,
0,
ltotal_commission*LAllot_per/100, --根据比例,分配佣金
0,
1,
-1, -- 对于分摊的佣金,使用-1作为付费方
LAllot_aff);
llinesort := llinesort + 1;
end loop;
-- 计算佣金完成
-- 下边计算固定费用
--下边开始计算固定费用
-- 固定费用全部用游标来处理才行
if acalc_type = 0 then
--这里，如果是正常结算的话，会计算固定费用的，但对
--附加重算的情况，这些费用肯定不用考虑的
--是月结,且或结清日期为空,或月份和开始日期的月份不同
OPEN CURSOR_MONTHLY_FEE(laffid,abegin_date); --打开游标
LOOP
-- CODE_ALLOT_AFFILIATE, ALLOT_PERCENT, ALLOT_TYPE
-- LAllot_aff integer;
-- LAllot_per Number;
-- LAllot_type integer;
FETCH CURSOR_MONTHLY_FEE
INTO LCODE_PAYOUT, LCODE_TAKEIN, tmp, lcode_fee;
EXIT WHEN CURSOR_MONTHLY_FEE%NOTFOUND;
-- 取出付费收费方，费用额及费用类型
select Concat(name_chi, name_en)
into ltitle
from typefee
where code_fee = lcode_fee
and rownum = 1; --取出费用类型的中文说明
insert into lineinv_agent
(code_lineaff,
code_invaff,
code_affiliate,
code_type,
linesort,
title,
qty,
unit_price,
total,
groupby,
code_payout,
code_takein)
values
(code_lineaff.NextVal,
linvoice_id,
laffid,
lcode_fee,
llinesort,
ltitle,
1,
tmp,
tmp,
2,
LCODE_PAYOUT,
LCODE_TAKEIN);
llinesort := llinesort + 1;
end loop;
--不管上边有没有记录，都进行一下更新，不过这里放到上边
-- 把CODE_CONFEEAFF取出来更新也是可以的
update contract_feeage
set code_invaff = linvoice_id, invoice_date = lbegin_date
where monthly = 1
and (invoice_date is NULL or
to_char(invoice_date, 'mm') <> to_char(lbegin_date, 'mm'))
and code_contractaff = acontractid;
-- 年收费类型
OPEN CURSOR_YEARLY_FEE(laffid,abegin_date); --打开游标
LOOP
-- CODE_ALLOT_AFFILIATE, ALLOT_PERCENT, ALLOT_TYPE
-- LAllot_aff integer;
-- LAllot_per Number;
-- LAllot_type integer;
FETCH CURSOR_YEARLY_FEE
INTO LCODE_PAYOUT, LCODE_TAKEIN, tmp, lcode_fee;
EXIT WHEN CURSOR_YEARLY_FEE%NOTFOUND;
-- 取出付费收费方，费用额及费用类型
select Concat(name_chi, name_en)
into ltitle
from typefee
where code_fee = lcode_fee
and rownum = 1; --取出费用类型的中文说明
insert into lineinv_agent
(code_lineaff,
code_invaff,
code_affiliate,
code_type,
linesort,
title,
qty,
unit_price,
total,
groupby,
code_payout,
code_takein)
values
(code_lineaff.NextVal,
linvoice_id,
laffid,
lcode_fee,
llinesort,
ltitle,
1,
tmp,
tmp,
2,
LCODE_PAYOUT,
LCODE_TAKEIN);
llinesort := llinesort + 1;
end loop;
--不管上边有没有记录，都进行一下更新，不过这里放到上边
-- 把CODE_CONFEEAFF取出来更新也是可以的
update contract_feeaff
set code_invaff = linvoice_id, invoice_date = lbegin_date
where yearly = 1
and (invoice_date is NULL or to_char(invoice_date, 'yyyy') <>
to_char(lbegin_date, 'yyyy'))
and code_contractaff = acontractid;
--是年结,且 或结清日期为空,或年份和开始日期的年份不同
--一次性费用??
OPEN CURSOR_FIX_FEE(laffid); --打开游标
LOOP
-- CODE_ALLOT_AFFILIATE, ALLOT_PERCENT, ALLOT_TYPE
-- LAllot_aff integer;
-- LAllot_per Number;
-- LAllot_type integer;
FETCH CURSOR_FIX_FEE
INTO LCODE_PAYOUT, LCODE_TAKEIN, tmp, lcode_fee;
EXIT WHEN CURSOR_FIX_FEE%NOTFOUND;
-- 取出付费收费方，费用额及费用类型
select Concat(name_chi, name_en)
into ltitle
from typefee
where code_fee = lcode_fee
and rownum = 1; --取出费用类型的中文说明
insert into lineinv_agent
(code_lineaff,
code_invaff,
code_affiliate,
code_type,
linesort,
title,
qty,
unit_price,
total,
groupby,
code_payout,
code_takein)
values
(code_lineaff.NextVal,
linvoice_id,
laffid,
lcode_fee,
llinesort,
ltitle,
1,
tmp,
tmp,
2,
LCODE_PAYOUT,
LCODE_TAKEIN);
llinesort := llinesort + 1;
end loop;
--不管上边有没有记录，都进行一下更新，不过这里放到上边
-- 把CODE_CONFEEAFF取出来更新也是可以的
update contract_feeage
set code_invaff = linvoice_id, invoice_date = lbegin_date
where yearly = 0
and monthly = 0
and code_invaff = 0
and code_contractaff = acontractid;
--对于集团方式的rollorder intervation 计算还是有点问题
if lgroup_id = 0 then
select count(total)
into tmp
from rollorder
where invoice_num = 0
and client_affiliate = 2
and DATE_ORDER >= abegin_date
and DATE_ORDER < aend_date
and code_link = laffid;
--nd date_invoice >= lbegin_date
--and date_invoice < lend_date;
if tmp > 0 then
select ltotal_commission + sum(nvl(total,0)), sum(nvl(total,0))
into ltotal_commission, tmpamount
from rollorder
where invoice_num = 0
and client_affiliate = 2
and DATE_ORDER >= abegin_date
and DATE_ORDER < aend_date
and code_link = laffid;
--and date_invoice >= lbegin_date
--and date_invoice < lend_date;
insert into lineinv_agent
(code_lineaff,
code_invaff,
code_affiliate,
code_type,
linesort,
title,
qty,
unit_price,
total,
groupby,
code_payout,
code_takein)
values
(code_lineaff.NextVal,
linvoice_id,
laffid,
-1,
llinesort,
'rollorder',
1,
tmpamount,
tmpamount,
3,
laffid,0);
llinesort := llinesort + 1;
update rollorder
set invoice_num = linvoice_id,
date_invoice = ainv_date
where invoice_num = 0
and client_affiliate = 2
and DATE_ORDER >= abegin_date
and DATE_ORDER < aend_date
and code_link = laffid;
--and date_invoice >= lbegin_date
--and date_invoice < lend_date;
elsif tmp > 1 then
RAISE_APPLICATION_ERROR(-20201,
'卷纸费用多于一条' ||
tmp);
--rollback;
--return;
end if;
--纸张的费用
select count(fee)
into tmp
from intervention
where code_invaff = 0
and code_affiliate = laffid
and intervention_date >= lbegin_date
and intervention_date < lend_date; --取到settle的合计
if tmp = 1 then
select ltotal_commission + sum(nvl(fee,0)), count(fee), sum(nvl(fee,0))
into ltotal_commission, tmp, tmpamount
from intervention
where code_invaff = 0
and code_affiliate = laffid
and intervention_date >= lbegin_date
and intervention_date < lend_date; --取到settle的合计
--这个不太清楚是什么费用,但要注意是不是用的intervention_date来记录结算日期???
insert into lineinv_agent
(code_lineaff,
code_invaff,
code_affiliate,
code_type,
linesort,
title,
qty,
unit_price,
total,
groupby,
code_payout,
code_takein)
values
(code_lineaff.NextVal,
linvoice_id,
laffid,
-1,
llinesort,
'intervention',
1,
tmpamount,
tmpamount,
4,
laffid,0);
llinesort := llinesort + 1;
update intervention
set code_invaff = linvoice_id
where code_invaff = 0
and code_affiliate = laffid
and intervention_date >= lbegin_date
and intervention_date < lend_date; --取到settle的合计
elsif tmp > 1 then
RAISE_APPLICATION_ERROR(-20201,
'intervation多于一条' ||
tmp);
end if;
else
--集团方式
select count(total)
into tmp
from rollorder
where invoice_num = 0
and client_affiliate = 2
and DATE_ORDER >= abegin_date
and DATE_ORDER < aend_date
and (code_link = laffid
or code_link
in (select code_affiliate
from affiliate
where code_groupaff = laffid));
if tmp > 0 then
select ltotal_commission + sum(nvl(total,0)), sum(nvl(total,0))
into ltotal_commission, tmpamount
from rollorder
where invoice_num = 0
and client_affiliate = 2
and DATE_ORDER >= abegin_date
and DATE_ORDER < aend_date
and (code_link in (select code_affiliate
from affiliate
where code_groupaff = laffid)
or code_link =laffid);
insert into lineinv_agent
(code_lineaff,
code_invaff,
code_affiliate,
code_type,
linesort,
title,
qty,
unit_price,
total,
groupby,
code_payout,
code_takein)
values
(code_lineaff.NextVal,
linvoice_id,
laffid,
-1,
llinesort,
'rollorder',
1,
tmpamount,
tmpamount,
3,
laffid,
0);
llinesort := llinesort + 1;
update rollorder
set invoice_num = linvoice_id,
date_invoice= ainv_date
where invoice_num = 0
and client_affiliate = 2
and DATE_ORDER >= abegin_date
and DATE_ORDER < aend_date
and (code_link in (select code_affiliate
from affiliate
where code_groupaff = laffid)
or code_link = laffid);
--and date_invoice >= lbegin_date
--and date_invoice < lend_date;
elsif tmp > 1 then
RAISE_APPLICATION_ERROR(-20201,
'集团卷纸费用多于一条' ||
tmp);
rollback;
--return;
end if;
--纸张的费用
select count(fee)
into tmp
from intervention
where code_invaff = 0
and code_affiliate in
(select code_affiliate
from affiliate
where code_groupaff = laffid)
and intervention_date >= lbegin_date
and intervention_date < lend_date; --取到settle的合计
if tmp >0 then
select ltotal_commission + sum(nvl(fee,0)), count(fee), sum(nvl(fee,0))
into ltotal_commission, tmp, tmpamount
from intervention
where code_invaff = 0
and code_affiliate in
(select code_affiliate
from affiliate
where code_groupaff = laffid)
and intervention_date >= lbegin_date
and intervention_date < lend_date; --取到settle的合计
--这个不太清楚是什么费用,但要注意是不是用的intervention_date来记录结算日期???
insert into lineinv_agent
(code_lineaff,
code_invaff,
code_affiliate,
code_type,
linesort,
title,
qty,
unit_price,
total,
groupby,
code_payout,
code_takein)
values
(code_lineaff.NextVal,
linvoice_id,
laffid,
-1,
llinesort,
'intervention',
1,
tmpamount,
tmpamount,
4,
laffid,
0);
llinesort := llinesort + 1;
update intervention
set code_invaff = linvoice_id
where code_invaff = 0
and code_affiliate in
(select code_affiliate
from affiliate
where code_groupaff = laffid)
and intervention_date >= lbegin_date
and intervention_date < lend_date; --取到settle的合计
elsif tmp > 1 then
RAISE_APPLICATION_ERROR(-20201,
'集团intervation费用多于一条' ||
tmp);
--rollback;
--return;
end if;
end if;
end if;
--固定费用计算完成
end CALC_AGE_COMMISSION;
1, :2)？