--1.处理红字票据
if v_count = 0 /*or v_count2>1*/ then
v_tk_amt := '-b.yamt';
else
v_tk_amt := '-a.out_amt';
end if;
s_sql := 'merge into ticket_detail'||s_termyear||' x using(
select ticket_type,ticket_no,uni_no,name,fcode,startyear,termyear,sum(ticket_amt) ticket_amt,
rfdzfid fid
from (
select b.refticket_type ticket_type,b.refticket_no ticket_no,
a.uni_no,a.name,a.fcode,a.startyear,a.termyear,'||v_tk_amt||' ticket_amt,b.rfdzfid fid,
b.ticket_type refticket_type,b.ticket_no refticket_no,b.operator,
b.type_no rftype_no,b.dzticket_type rfdzticket_type,b.dzticketno rfdzticketno,a.cashtype,
b.rfdzfid
from capital_record'||v_fyear||' a,ticket_refund b
where b.refundord= '||p_ticketno||'
and b.ticket_no = lpad('||QuotedStr(v_ticket_no)||',25)
and a.fid=b.fid
)
group by ticket_type,ticket_no,uni_no,name,fcode,startyear,termyear,rfdzfid
)y
on (x.fid=y.fid and x.uni_no=y.uni_no and x.fcode=y.fcode and x.startyear=y.startyear)
when not matched then
insert (x.ticket_type,x.ticket_no,x.uni_no,x.fcode,x.startyear,x.termyear,x.ticket_amt,x.fid)
values (y.ticket_type,y.ticket_no,y.uni_no,y.fcode,y.startyear,y.termyear,y.ticket_amt,y.fid)';
--dbms_output.put_line(s_sql);
execute immediate s_sql;
s_sql := 'merge into ticket'||s_termyear||' x using(
select ticket_type,ticket_no,uni_no,operator ticket_man, operator print_man,refticket_type,
refticket_no,sum(ticket_amt) ticketamt,rftype_no,rfdzticket_type,rfdzticketno,
cashtype,''Y'' state,rfdzfid,nvl(max(reason),''其他'') reason
from (
select b.refticket_type ticket_type,b.refticket_no ticket_no,
a.uni_no,a.name,a.fcode,a.startyear,a.termyear,'||v_tk_amt||' ticket_amt,b.rfdzfid fid,
b.ticket_type refticket_type,b.ticket_no refticket_no,b.operator,
b.type_no rftype_no,b.dzticket_type rfdzticket_type,b.dzticketno rfdzticketno,a.cashtype,
b.rfdzfid,b.reason
from capital_record'||v_fyear||' a,ticket_refund b
where b.refundord= '||trim(p_ticketno)||'
and b.ticket_no = lpad('||QuotedStr(v_ticket_no)||',25)
and a.fid=b.fid
)
group by ticket_type,ticket_no,uni_no,operator,refticket_type,
refticket_no,rftype_no,rfdzticket_type,rfdzticketno,rfdzfid,cashtype )y
on (x.ticket_type=y.ticket_type and x.ticket_no=y.ticket_no)
when matched then
update set x.state=y.state,x.uni_no=y.uni_no,x.ticketamt=y.ticketamt,x.ticket_man=y.ticket_man,
x.print_man=y.print_man,x.refticket_type=y.refticket_type,x.refticket_no=y.refticket_no,
x.rftype_no=y.rftype_no,x.rfdzticket_type=y.rfdzticket_type,
x.rfdzticketno=y.rfdzticketno,x.rfdzfid=y.rfdzfid,
x.ticket_date=trunc(sysdate),x.cashtype=y.cashtype,
x.reason=y.reason
';
--dbms_output.put_line(s_sql);
execute immediate s_sql;
--原始票据红字锁定/*此处使用历史年度*/
s_sql :='merge into ticket'||v_fyear||' x using(
select b.* from ticket'||v_fyear||' a,(select distinct t.ticket_type,t.ticket_no ,
t.refticket_type,t.refticket_no,t.rftype_no,
t.rfdzticketno,t.rfdzticket_type,t.rfdzfid,fid from ticket_refund t
where t.refundord='||trim(p_ticketno)||'
/* and t.ticket_no = lpad('||QuotedStr(v_ticket_no)||',25)*/) b
where a.ticket_type=b.ticket_type and a.ticket_no=b.ticket_no
and a.refticket_no is null
)y
on (x.ticket_type=y.ticket_type and x.ticket_no=y.ticket_no)
when matched then
update set x.refticket_type=y.refticket_type,x.refticket_no=y.refticket_no,
x.rfdzfid=y.rfdzfid,x.rftype_no=y.rftype_no,x.rfdzticket_type=y.rfdzticket_type,
x.rfdzticketno=y.rfdzticketno';
--dbms_output.put_line(s_sql);
execute immediate s_sql;
--重新开票
s_sql := 'merge into ticket_detail'||s_termyear||' x using(
select ticket_type,ticket_no,uni_no,name,fcode,startyear,termyear,sum(ticket_amt) ticket_amt,
fid
from (
select a.ticket_type,a.ticket_no,
a.uni_no,a.name,a.fcode,a.startyear,a.termyear,a.out_amt ticket_amt,a.fid,
null refticket_type,null refticket_no,b.operator,
null rftype_no,null rfdzticket_type,null rfdzticketno,a.cashtype,
null rfdzfid
from capital_record'||s_termyear||' a,ticket_refund b
where b.refundord= '||trim(p_ticketno)||'
/* and b.ticket_no = lpad('||QuotedStr(v_ticket_no)||',25)*/
and a.fid=b.newfid
)
group by ticket_type,ticket_no,uni_no,name,fcode,startyear,termyear,fid
)y
on (x.fid=y.fid and x.uni_no=y.uni_no and x.fcode=y.fcode and x.startyear=y.startyear)
when not matched then
insert (x.ticket_type,x.ticket_no,x.uni_no,x.fcode,x.startyear,x.termyear,x.ticket_amt,x.fid)
values (y.ticket_type,y.ticket_no,y.uni_no,y.fcode,y.startyear,y.termyear,y.ticket_amt,y.fid)';
--dbms_output.put_line(s_sql);
execute immediate s_sql;
s_sql :='merge into ticket'||s_termyear||' x using(
select ticket_type,ticket_no,uni_no,operator ticket_man, operator print_man,refticket_type,
refticket_no,sum(ticket_amt) ticketamt,rftype_no,rfdzticket_type,rfdzticketno,
cashtype,''Y'' state,rfdzfid
from (
select a.ticket_type,a.ticket_no,
a.uni_no,a.name,a.fcode,a.startyear,a.termyear,a.out_amt ticket_amt,a.fid,
null refticket_type,null refticket_no,b.operator,
null rftype_no,null rfdzticket_type,null rfdzticketno,a.cashtype,
null rfdzfid
from capital_record'||s_termyear||' a,ticket_refund b
where b.refundord= '||trim(p_ticketno)||'
/*and b.ticket_no = lpad('||QuotedStr(v_ticket_no)||',25)*/
and a.fid=b.newfid
)
group by ticket_type,ticket_no,uni_no,operator,refticket_type,
refticket_no,rftype_no,rfdzticket_type,rfdzticketno,rfdzfid,cashtype
)y
on (x.ticket_type=y.ticket_type and x.ticket_no=y.ticket_no)
when matched then
update set x.state=y.state,x.uni_no=y.uni_no,x.ticketamt=y.ticketamt,x.ticket_man=y.ticket_man,
x.print_man=y.print_man,x.refticket_type=y.refticket_type,x.refticket_no=y.refticket_no,
x.rftype_no=y.rftype_no,x.rfdzticket_type=y.rfdzticket_type,
x.rfdzticketno=y.rfdzticketno,x.rfdzfid=y.rfdzfid,
x.ticket_date=trunc(sysdate),x.cashtype=y.cashtype';
--dbms_output.put_line(s_sql);
execute immediate s_sql;
end if;
if v_tkamt1>=v_tkamt then
exit;
end if;
end loop;
/*--修改数据删除多余数据 徐念驰20200509
s_sql:='delete from capital_record'||s_termyear||' t where t.capital_no in (select capital_no from fei_detail'||s_termyear||' where ticket_no =lpad('''||p_ticketno||''',25)) and t.remark is null';
execute immediate s_sql;
s_sql:='update fei_detail2019 t set t.capital_no=null,t.capital_ord=null where t.refundord=lpad('''||p_ticketno||''',25) and remark=''内部结算''';
--dbms_output.put_line(s_sql);
execute immediate s_sql;
--dbms_output.put_line(v_msg);*/
resultstr := 'ok:退费成功';
end;
Execution time: 47 ms
----------------------------------
Timestamp: 17:51:01.413
begin :Result := SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA');end;
Result = 'XSSF1'
Execution time: 0 ms
----------------------------------
Timestamp: 17:51:01.523
begin :Result := SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA');end;
Result = 'XSSF1'
Execution time: 0 ms
----------------------------------
Timestamp: 17:51:01.638
declare
CURSOR Cur IS
SELECT Line,Position,Text
FROM sys.All_Errors
WHERE Name = :Name and Type = :Type
AND OWNER = :Owner
ORDER BY Sequence;
Rec User_Errors%ROWTYPE;
StartPos INTEGER;
EndPos INTEGER;
begin
:Res := '';
for Rec in Cur loop
if Rec.Line <> 0 or Rec.Position <> 0 then
:Res := :Res || RPad(Rec.Line || '/' || Rec.Position, 8);
end if;
StartPos := 1;
EndPos := 1;
while EndPos > 0 and StartPos <= Length(Rec.Text) loop
EndPos := InStr(Rec.Text, Chr(10), StartPos);
if EndPos > 0 then
if EndPos <> StartPos then
if StartPos > 1 then
:Res := :Res || RPad(' ', 10);
end if;
:Res := :Res || LTrim(SubStr(Rec.Text, StartPos, EndPos - StartPos)) || Chr(13);
end if;
else
if StartPos > 1 then
:Res := :Res || RPad(' ', 10);
end if;
:Res := :Res || LTrim(SubStr(Rec.Text, StartPos)) || Chr(13);
end if;
StartPos := EndPos + 1;
end loop;
end loop;
end;
Name = 'DZP_TUIFEI'
TYPE = 'PROCEDURE'
Owner = 'XSSF1'
Res = '118/78 PL/SQL: ORA-00904: "B"."FCODE": 标识符无效
118/3 PL/SQL: SQL Statement ignored
'这里面报标识符无效是什么错误