Hi all,
please help me to understand why do I get PLS-00323 error when my function does exist in both header and body of package.
Please find source code below.
Maybe I should post more details here, please tell me what else should I show to get help.
I tried to compile my schema
EXEC DBMS_UTILITY.compile_schema(schema => 'ZVIT');
but still
select * from user_errors
returns
NAME TYPE SEQUENCE LINE POSITION
------------------------------ ------------ ---------- ---------- ----------
TEXT
--------------------------------------------------------------------------------
ATTRIBUTE MESSAGE_NUMBER
--------- --------------
PKG_PK8842_ACCNOTMOVE PACKAGE BODY 2 8 12
PLS-00323: subprogram or cursor 'ACCNOTMOVEFIZ' is declared in a package specifi
cation and must be defined in the package body
ERROR 323
PKG_PK8842_ACCNOTMOVE PACKAGE BODY 1 3 12
PLS-00323: subprogram or cursor 'ACCNOTMOVEJUR' is declared in a package specifi
cation and must be defined in the package body
ERROR 323
2 rows selected.
source code:
CREATE OR REPLACE PACKAGE ZVIT.PKG_PK8842_AccNotMove
AS
FUNCTION AccNotMoveJUR(
p_contragentId dwh_CR_contragent.ID%TYPE,
p_date Date)
RETURN Number;
FUNCTION AccNotMoveFIZ(
p_contragentId dwh_CR_contragent.ID%TYPE,
p_date Date)
RETURN Number;
FUNCTION DtLastMove(
p_Id B2_OLAP.DIMAACCOUNT_ALL.ID%TYPE)
RETURN Date;
END;
/
CREATE OR REPLACE PACKAGE BODY ZVIT.PKG_PK8842_AccNotMove
AS
FUNCTION AccNotMoveJUR(
p_contragentId zvit.dwh_CR_contragent.ID%TYPE,
p_date Date)
RETURN Number
IS
l_Result Number(1);
l_contragentId zvit.dwh_CR_contragent.ID%TYPE;
l_dateopen Date;
l_dtMove1 Date;
l_dtMove2 Date;
BEGIN
l_Result:=0;
BEGIN
Select Distinct a.contragentid
Into l_contragentId
FROM B2_OLAP.DIMAACCOUNT_ALL a
where a.contragentid=p_contragentId
and a.BACCOUNTID in (2600,2650)
and a.ACCOUNTSTATEID not in (1,2);
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_contragentId:=NULL;
END;
IF l_contragentId IS NOT NULL THEN
l_Result:=0;
ELSE
BEGIN
Select MAX(a.dateopen)
Into l_DateOpen
FROM B2_OLAP.DIMAACCOUNT_ALL a
where a.contragentid=p_contragentId
and a.BACCOUNTID in (2600,2650)
and a.ACCOUNTSTATEID<>2
and a.DateOpen>p_date;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_DateOpen:=NULL;
END;
IF l_DateOpen IS NOT NULL THEN
l_Result:=0;
ELSE
BEGIN
SELECT MAX(d.arcdate)
Into l_dtMove1
FROM B2_OLAP.DIMAACCOUNT_ALL a,
B2_OLAP.AR_DOCUMENT d,
B2_OLAP.DIMDOCUMENTTYPE dt
WHERE a.contragentid=p_contragentId
and a.BACCOUNTID in (2600,2650)
and d.ACCOUNTBID=a.Id
and d.arcdate>=p_date
and d.DOCUMENTTYPEID=dt.id
and dt.OPERATIONTYPEBYSUMMAID not in (2,3)
and Substr(d.accountano,1,4) not in ('2608','2658');
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_dtMove1:=NULL;
END;
IF l_dtMove1 IS NULL THEN
BEGIN
SELECT MAX(d.arcdate)
Into l_dtMove1
FROM B2_OLAP.DIMAACCOUNT_ALL a,
B2_OLAP.AR_DOCUMENT d,
B2_OLAP.DIMDOCUMENTTYPE dt
WHERE a.contragentid=p_contragentId
and a.BACCOUNTID in (2600,2650)
and d.ACCOUNTBID=a.Id
and d.arcdate>=p_date
and d.DOCUMENTTYPEID=dt.id
and dt.OPERATIONTYPEBYSUMMAID not in (2,3)
and Substr(d.accountano,1,4) not in ('2608','2658');
--and d.DOCUMENTSTATEID=2
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_dtMove1:=NULL;
END;
END IF;
IF l_dtMove1 IS NULL THEN
BEGIN
SELECT MAX(d.arcdate)
Into l_dtMove1
FROM B2_OLAP.DIMAACCOUNT_ALL a,
B2_OLAP.AR_DOCUMENT d,
B2_OLAP.DIMDOCUMENTTYPE dt
WHERE a.contragentid=p_contragentId
and a.BACCOUNTID in (2600,2650)
and d.ACCOUNTAID=a.Id
and d.arcdate>=p_date
and d.DOCUMENTTYPEID=dt.id
and dt.OPERATIONTYPEBYSUMMAID not in (2,3);
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_dtMove1:=NULL;
END;
END IF;
IF l_dtMove1 IS NULL THEN
BEGIN
SELECT MAX(d.arcdate)
Into l_dtMove1
FROM B2_OLAP.DIMAACCOUNT_ALL a,
B2_OLAP.AR_DOCUMENT d,
B2_OLAP.DIMDOCUMENTTYPE dt
WHERE a.contragentid=p_contragentId
and a.BACCOUNTID in (2600,2650)
and d.arcdate>=p_date
and d.ACCOUNTAID=a.Id
and d.DOCUMENTTYPEID=dt.id
and dt.OPERATIONTYPEBYSUMMAID not in (2,3);
--and d.DOCUMENTSTATEID=2
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_dtMove1:=NULL;
END;
END IF;
IF l_dtMove1 IS NULL THEN
l_Result:=1;
Else
l_Result:=0;
End If;
End IF;
END IF;
RETURN l_Result;
END AccNotMoveJUR;
--------------------------------------------------------------------------------
FUNCTION AccNotMoveFIZ(
p_contragentId zvit.dwh_CR_contragent.ID%TYPE,
p_date Date)
RETURN Number
IS
l_Result Number(1);
l_contragentId zvit.dwh_CR_contragent.ID%TYPE;
l_dateopen Date;
l_dtMove1 Date;
BEGIN
l_Result:=0;
BEGIN
Select Distinct a.contragentid
Into l_contragentId
FROM B2_OLAP.DIMAACCOUNT_ALL a
where a.contragentid=p_contragentId
and a.BACCOUNTID in (2620,2625)
and a.ACCOUNTSTATEID not in (1,2);
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_contragentId:=NULL;
END;
IF l_contragentId IS NOT NULL THEN
l_Result:=0;
ELSE
BEGIN
Select MAX(a.dateopen)
Into l_DateOpen
FROM B2_OLAP.DIMAACCOUNT_ALL a
where a.contragentid=p_contragentId
and a.BACCOUNTID in (2620,2625)
and a.ACCOUNTSTATEID<>2
and a.DateOpen>p_date;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_DateOpen:=NULL;
END;
IF l_DateOpen IS NOT NULL THEN
l_Result:=0;
ELSE
BEGIN
SELECT MAX(d.arcdate)
Into l_dtMove1
FROM B2_OLAP.DIMAACCOUNT_ALL a,
B2_OLAP.AR_DOCUMENT d,
B2_OLAP.DIMDOCUMENTTYPE dt
WHERE a.contragentid=p_contragentId
and a.BACCOUNTID in (2620,2625)
and d.ACCOUNTBID=a.Id
and d.arcdate>=p_date
and d.DOCUMENTTYPEID=dt.id
and dt.OPERATIONTYPEBYSUMMAID not in (2,3)
and Substr(d.accountano,1,4)<>'2628';
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_dtMove1:=NULL;
END;
IF l_dtMove1 IS NULL THEN
BEGIN
SELECT MAX(d.arcdate)
Into l_dtMove1
FROM B2_OLAP.DIMAACCOUNT_ALL a,
B2_OLAP.AR_DOCUMENT d,
B2_OLAP.DIMDOCUMENTTYPE dt
WHERE a.contragentid=p_contragentId
and a.BACCOUNTID in (2620,2625)
and d.arcdate>=p_date
and d.ACCOUNTBID=a.Id
and d.DOCUMENTTYPEID=dt.id
and dt.OPERATIONTYPEBYSUMMAID not in (2,3)
and Substr(d.accountano,1,4)<>'2628';
--and d.DOCUMENTSTATEID=2
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_dtMove1:=NULL;
END;
END IF;
IF l_dtMove1 IS NULL THEN
BEGIN
SELECT MAX(d.arcdate)
Into l_dtMove1
FROM B2_OLAP.DIMAACCOUNT_ALL a,
B2_OLAP.AR_DOCUMENT d,
B2_OLAP.DIMDOCUMENTTYPE dt
WHERE a.contragentid=p_contragentId
and a.BACCOUNTID in (2620,2625)
and d.ACCOUNTAID=a.Id
and d.arcdate>=p_date
and d.DOCUMENTTYPEID=dt.id
and dt.OPERATIONTYPEBYSUMMAID not in (2,3);
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_dtMove1:=NULL;
END;
END IF;
IF l_dtMove1 IS NULL THEN
BEGIN
SELECT MAX(d.arcdate)
Into l_dtMove1
FROM B2_OLAP.DIMAACCOUNT_ALL a,
B2_OLAP.AR_DOCUMENT d,
B2_OLAP.DIMDOCUMENTTYPE dt
WHERE a.contragentid=p_contragentId
and a.BACCOUNTID in (2620,2625)
and d.arcdate>=p_date
and d.ACCOUNTAID=a.Id
and d.DOCUMENTTYPEID=dt.id
and dt.OPERATIONTYPEBYSUMMAID not in (2,3);
--and d.DOCUMENTSTATEID=2
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_dtMove1:=NULL;
END;
END IF;
IF l_dtMove1 IS NULL THEN
l_Result:=1;
Else
l_Result:=0;
End If;
End IF;
END IF;
RETURN l_Result;
END AccNotMoveFIZ;
--------------------------------------------------------------------------------
FUNCTION DtLastMove(
p_Id B2_OLAP.DIMAACCOUNT_ALL.ID%TYPE)
RETURN Date
IS
l_Result Date;
l_DtLast1 Date;
l_DtLast2 Date;
l_DtLast3 Date;
l_DtLast4 Date;
BEGIN
l_Result:=NULL;
BEGIN
SELECT MAX(d.arcdate)
Into l_DtLast1
FROM B2_OLAP.AR_DOCUMENT d,
B2_OLAP.DIMDOCUMENTTYPE dt
WHERE d.ACCOUNTBID=p_Id
-- and d.arcdate>=p_date
and d.DOCUMENTTYPEID=dt.id
and dt.OPERATIONTYPEBYSUMMAID not in (2,3)
and Substr(d.accountano,1,4) not in ('2608','2658','2628');
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_DtLast1:=NULL;
END;
BEGIN
SELECT MAX(d.arcdate)
Into l_DtLast2
FROM B2_OLAP.AR_DOCUMENT d,
B2_OLAP.DIMDOCUMENTTYPE dt
WHERE d.ACCOUNTBID=p_Id
and d.DOCUMENTTYPEID=dt.id
and dt.OPERATIONTYPEBYSUMMAID not in (2,3)
and Substr(d.accountano,1,4) not in ('2608','2658','2628');
--and d.DOCUMENTSTATEID=2
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_DtLast2:=NULL;
END;
BEGIN
SELECT MAX(d.arcdate)
Into l_DtLast3
FROM B2_OLAP.AR_DOCUMENT d,
B2_OLAP.DIMDOCUMENTTYPE dt
WHERE d.ACCOUNTAID=p_Id
and d.DOCUMENTTYPEID=dt.id
and dt.OPERATIONTYPEBYSUMMAID not in (2,3);
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_DtLast3:=NULL;
END;
BEGIN
SELECT MAX(d.arcdate)
Into l_DtLast4
FROM B2_OLAP.AR_DOCUMENT d,
B2_OLAP.DIMDOCUMENTTYPE dt
WHERE d.ACCOUNTAID=p_Id
and d.DOCUMENTTYPEID=dt.id
and dt.OPERATIONTYPEBYSUMMAID not in (2,3);
--and d.DOCUMENTSTATEID=2
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_DtLast4:=NULL;
END;
l_DtLast1:=NVL(l_DtLast1,TO_DATE('01.01.1900','dd.mm.yyyy'));
l_DtLast2:=NVL(l_DtLast2,TO_DATE('01.01.1900','dd.mm.yyyy'));
l_DtLast3:=NVL(l_DtLast3,TO_DATE('01.01.1900','dd.mm.yyyy'));
l_DtLast4:=NVL(l_DtLast4,TO_DATE('01.01.1900','dd.mm.yyyy'));
l_Result:=GREATEST(l_DtLast1,l_DtLast2,l_DtLast3,l_DtLast4);
IF l_Result=TO_DATE('01.01.1900','dd.mm.yyyy') THEN
l_Result:=NULL;
END IF;
RETURN l_Result;
END DtLastMove;
---////////////////////////////////////////////////////
END;
/