CREATE OR REPLACE PROCEDURE CAPITAL_BUDGET03
(
Department in varchar2, ----部门
Term in varchar2, ----经营期间
Account in varchar2, ----预算科目
Capital_CURSOR OUT REPORT_PACKAGE.REPORT_CURSOR
)
IS
v_dep varchar2(200);
V_depnew varchar2(200);
v_term varchar2(100);
v_termnew varchar2(200);
v_account varchar2(200);
v_accountnew varchar2(200);
v_sql varchar2(4000);
BEGIN
v_dep:=Department;
V_depnew:=v_dep||V_depnew;
v_term:=Term;
v_termnew:=v_term||v_termnew;
v_account:=Account;
v_accountnew:=v_account||v_accountnew;
v_sql:= 'SELECT DISTINCT A.STRUCTUREID,C.TOTALCODE,C.OBJECTCODE,C.OBJECTNAME AS ACCOUNT,D.OBJECTNAME AS DEPARTMENT,E.OBJECTNAME AS TERM,G.OBJECTNAME AS YEAR,NVL(SUM(B.TOTALYEAR),0) AS BUDGETYear,NVL(SUM(J.ACTUAL),0) AS ACTUAL
FROM T_CC_STRUCTURE A
INNER JOIN (SELECT A.STRUCTUREID,A.TOTALYEAR,B.OBJECTNAME,A.YEARID FROM T_CC_BUDGETDATA A
INNER JOIN (SELECT OBJECTID,OBJECTNAME FROM T_CC_OBJECT WHERE OBJECTTYPEID=2) B ON A.YEARID=B.OBJECTID
WHERE A.DATATYPE=0 ) B ON B.StructureID=A.StructureID
INNER JOIN (SELECT OBJECTID,OBJECTNAME,TOTALCODE,OBJECTCODE FROM T_CC_OBJECT WHERE OBJECTTYPEID=0 AND TOTALCODE like ''0002,1011,2%'') C ON C.OBJECTID=A.DIMACCOUNTID
INNER JOIN (SELECT OBJECTID,OBJECTNAME FROM T_CC_OBJECT WHERE OBJECTTYPEID=1) D ON D.OBJECTID=A.DIMSUBJECTID
INNER JOIN T_CC_OBJECT E ON E.OBJECTID=A.DIM01ID
INNER JOIN T_CC_OBJECT G ON G.OBJECTID=B.YEARID
LEFT JOIN (SELECT A.STRUCTUREID,A.NATURALCURRENCY AS ACTUAL FROM T_CC_BILLDETAILDATA A
INNER JOIN T_CC_BILLMAINDATA B ON A.BILLMAINDATAID=B.BILLMAINDATAID
WHERE A.STRUCTUREID IS NOT NULL) J ON J.STRUCTUREID=A.STRUCTUREID
WHERE A.DIM02ID IN (SELECT OBJECTID FROM T_CC_OBJECT WHERE OBJECTNAME=''年初预算'') AND C.OBJECTNAME in (' || v_accountnew || ')
AND D.OBJECTNAME in(' || V_depnew || ') AND E.OBJECTNAME in(' || v_termnew || ')
GROUP BY A.STRUCTUREID,C.TOTALCODE,C.OBJECTCODE,C.OBJECTNAME,D.OBJECTNAME,E.OBJECTNAME,G.OBJECTNAME
ORDER BY C.TOTALCODE ASC' ;
OPEN Capital_CURSOR FOR v_sql;
END CAPITAL_BUDGET03;
编译成功,但是在调用存储过程输入参数时提示ORA-06550 PLS-00103错误,无法得到结果集。麻烦高手指点下。谢谢!