Hi all,
i'm using the following procedure(REF Cursor) and functions,it's compiling well(without error in oralce),but when i call that procedure in Crystal report the following error is coming,
" STATEMENT HANDLE NOT EXECUTED",
error number:ORA 24338,
this is the package:
-----------------------
CREATE OR REPLACE package pack_corpus
as type TEST_TYPE IS REF CURSOR;
END PACK_corpus;
/


this is the procedure:
----------------------------
PROCEDURE PCG_MOVEMENT
(
T_CURSOR OUT PACK_CORPUS.TEST_TYPE,
P_REPORT_TYPE VARCHAR2,
P_ASONDATE DATE,
P_PREV_DATE DATE
)
AS
BEGIN
IF P_REPORT_TYPE = 'U' THEN
OPEN T_CURSOR FOR

SELECT SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_ASONDATE))), 1, 60) PRIORITY_CUST_1,
SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_PREV_DATE))), 1, 60) PRIORITY_CUST_2,
A.CUST_ID,
A.FIRST_NAME,
A.MIDDLE_NAME,
A.LAST_NAME,
GETCUSTUNITS(P_ASONDATE, A.CUST_ID) UNITS
FROM CUSTOMER_MASTER A
WHERE (UPPER(LTRIM(RTRIM(SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_ASONDATE))), 1, 60)))) = 'PLATINUM' AND (UPPER(LTRIM(RTRIM(SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_PREV_DATE))), 1, 60)))) IN
('GOLD', 'SILVER') OR UPPER(LTRIM(RTRIM(SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_PREV_DATE))), 1, 60)))) IS NULL))
OR (UPPER(LTRIM(RTRIM(SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_ASONDATE))), 1, 60)))) = 'GOLD' AND (UPPER(LTRIM(RTRIM(SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_PREV_DATE))), 1, 60)))) = 'SILVER'
OR UPPER(LTRIM(RTRIM(SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_PREV_DATE))), 1, 60)))) IS NULL))
OR (UPPER(LTRIM(RTRIM(SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_ASONDATE))), 1, 60)))) = 'SILVER' AND UPPER(LTRIM(RTRIM(SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_PREV_DATE))), 1, 60)))) IS NULL);
END IF;
IF P_REPORT_TYPE = 'D' THEN
OPEN T_CURSOR FOR
SELECT SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_ASONDATE))), 1, 60) PRIORITY_CUST_1,
SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_PREV_DATE))), 1, 60) PRIORITY_CUST_2,
A.CUST_ID,
A.FIRST_NAME,
A.MIDDLE_NAME,
A.LAST_NAME,
GETCUSTUNITS(P_ASONDATE, A.CUST_ID) UNITS
FROM CUSTOMER_MASTER A
WHERE (UPPER(LTRIM(RTRIM(SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_PREV_DATE))), 1, 60)))) IN ('PLATINUM', 'GOLD', 'SILVER')
AND UPPER(LTRIM(RTRIM(SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_ASONDATE))), 1, 60)))) IS NULL)
OR (UPPER(LTRIM(RTRIM(SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_PREV_DATE))), 1, 60)))) = 'PLATINUM'
AND UPPER(LTRIM(RTRIM(SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_ASONDATE))), 1, 60)))) IN ('GOLD', 'SILVER'))
OR (UPPER(LTRIM(RTRIM(SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_PREV_DATE))), 1, 60)))) = 'GOLD'
AND UPPER(LTRIM(RTRIM(SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_ASONDATE))), 1, 60)))) = 'SILVER');
END IF;
IF P_REPORT_TYPE = 'S' THEN
OPEN T_CURSOR FOR
SELECT SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_ASONDATE))), 1, 60) PRIORITY_CUST_1,
SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_PREV_DATE))), 1, 60) PRIORITY_CUST_2,
A.CUST_ID,
A.FIRST_NAME,
A.MIDDLE_NAME,
A.LAST_NAME,
GETCUSTUNITS(P_ASONDATE, A.CUST_ID) UNITS
FROM CUSTOMER_MASTER A
WHERE UPPER(LTRIM(RTRIM(SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_ASONDATE))), 1, 60)))) = UPPER(LTRIM(RTRIM(SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_PREV_DATE))), 1, 60))));
END IF;
END;


these are the functions:
---------------------------
1)
FUNCTION GETAUMACNO
(
P_ASONDATE DATE,
P_ACCOUNT_NUMBER VARCHAR2,
P_FUND_cATEGORY VARCHAR2
) RETURN NUMBER AS
M_CATEG_AUM NUMBER(20,2) := 0;
M_NAV_VALUE NUMBER(20,2) := 0;
M_DATE DATE;
M_PUR NUMBER(20,4) := 0;
M_RED NUMBER(20,4) := 0;
M_FUND_OPTION FUND_OPTION_MASTER.FUND_OPTION%TYPE := NULL;
BEGIN
BEGIN
SELECT SUM(UNITS_ALLOTTED) INTO M_PUR
FROM PURCHASE_LOG A, V_FUND_OPTION B
WHERE B.FUND_OPTION = A.FUND_OPTION
AND A.ACCOUNT_NUMBER= P_ACCOUNT_NUMBER
AND B.FUND_CATEGORY LIKE NVL(P_FUND_CATEGORY, '%')
AND A.PURCHASE_ALLOTTED_DATE BETWEEN to_date('01-JAN-1900','DD-MON-YYYY') AND P_ASONDATE;
EXCEPTION WHEN OTHERS THEN
NULL;
END;
BEGIN
SELECT SUM(UNITS_REDEEMED) INTO M_RED
FROM REDEMPTION_LOG A, V_FUND_OPTION B
WHERE B.FUND_OPTION = A.FUND_OPTION
AND A.ACCOUNT_NUMBER= P_ACCOUNT_NUMBER
AND B.FUND_CATEGORY LIKE NVL(P_FUND_CATEGORY, '%')
AND A.REDEEM_EFFECTIVE_DATE BETWEEN to_date('01-JAN-1900','DD-MON-YYYY') AND P_ASONDATE;
EXCEPTION WHEN OTHERS THEN
NULL;
END;

BEGIN
SELECT FUND_OPTION INTO M_FUND_OPTION
FROM ACCOUNT_MASTER
WHERE ACCOUNT_NUMBER = P_ACCOUNT_NUMBER;
EXCEPTION WHEN OTHERS THEN
NULL;
END;

BEGIN
SELECT MAX(EFFECTIVE_DATE), NAV_VALUE INTO M_DATE, M_NAV_VALUE FROM NAV
WHERE FUND_OPTION = M_FUND_OPTION
AND EFFECTIVE_DATE BETWEEN to_date('01-JAN-1900','DD-MON-YYYY') AND P_ASONDATE
GROUP BY NAV_VALUE;
EXCEPTION WHEN OTHERS THEN
NULL;
END;
M_CATEG_AUM := (M_PUR - M_RED) * M_NAV_VALUE;
RETURN NVL(M_CATEG_AUM,0);
END;

2)
FUNCTION GETCUSTUNITS
(
P_ASONDATE DATE,
P_CUST_ID VARCHAR2
) RETURN NUMBER AS

CURSOR C1 IS
SELECT ACCOUNT_NUMBER FROM ACC_CUS_LINK
WHERE CUST_ID = P_CUST_ID;

M_UNITS NUMBER(20,2) := 0;
M_PUR NUMBER(20,4) := 0;
M_RED NUMBER(20,4) := 0;
M_TOT_PUR NUMBER(20,4) := 0;
M_TOT_RED NUMBER(20,4) := 0;
M_ASONDATE DATE := NULL;

BEGIN
IF P_ASONDATE IS NULL THEN
BEGIN
SELECT MAX(BUSINESS_DATE) INTO M_ASONDATE
FROM DATE_MASTER;
EXCEPTION WHEN OTHERS THEN
NULL;
END;
ELSE
M_ASONDATE := P_ASONDATE;
END IF;

FOR C1REC IN C1 LOOP
M_PUR := 0;
M_RED := 0;
BEGIN
SELECT SUM(UNITS_ALLOTTED) INTO M_PUR
FROM PURCHASE_LOG
WHERE ACCOUNT_NUMBER = C1REC.ACCOUNT_NUMBER
AND PURCHASE_ALLOTTED_DATE BETWEEN to_date('01-JAN-1900','DD-MON-YYYY') AND M_ASONDATE;
EXCEPTION WHEN OTHERS THEN
NULL;
END;
BEGIN
SELECT SUM(UNITS_REDEEMED) INTO M_RED
FROM REDEMPTION_LOG
WHERE ACCOUNT_NUMBER = C1REC.ACCOUNT_NUMBER
AND REDEEM_EFFECTIVE_DATE BETWEEN to_date('01-JAN-1900','DD-MON-YYYY') AND M_ASONDATE;
EXCEPTION WHEN OTHERS THEN
NULL;
END;
M_TOT_PUR := M_TOT_PUR + M_PUR;
M_TOT_RED := M_TOT_RED + M_RED;
END LOOP;

M_UNITS := (M_TOT_PUR - M_TOT_RED);
RETURN NVL(M_UNITS,0);
END;

3)
UNCTION GETPRIORITYCUST
(
P_CUST_ID VARCHAR2,
P_ASONDATE DATE
)
RETURN VARCHAR2 IS

CURSOR C1 IS
SELECT CODE, VALUE
FROM PARAMETER
WHERE UPPER(CODE) IN ('EQUITY','INCOME','CASH')
AND UPPER(GROUP_ID)='PRIORITYWT';

CURSOR C2 IS
SELECT CODE,
SUBSTR(VALUE, 1, (INSTR(VALUE, '-') - 1)) FROMVAL,
SUBSTR(VALUE, (INSTR(VALUE, '-') + 1),
(LENGTH(LTRIM(RTRIM(VALUE))) -
LENGTH(LTRIM(RTRIM(SUBSTR(VALUE, 1, (INSTR(VALUE, '-') - 1))))))) TOVAL
FROM PARAMETER WHERE UPPER(GROUP_ID) = 'PRIORITYCUST';

CURSOR C3 IS
SELECT ACCOUNT_NUMBER
FROM ACC_CUS_LINK
WHERE CUST_ID = P_CUST_ID
AND HOLDING_STATUS = 'F';

M_EQ_WT NUMBER :=0;
M_IN_WT NUMBER :=0;
M_CA_WT NUMBER :=0;
M_PL_FROMVAL NUMBER :=0;
M_PL_TOVAL NUMBER :=0;
M_GL_FROMVAL NUMBER :=0;
M_GL_TOVAL NUMBER :=0;
M_CL_FROMVAL NUMBER :=0;
M_CL_TOVAL NUMBER :=0;
M_WT_EQ_AUM NUMBER :=0;
M_WT_IN_AUM NUMBER :=0;
M_WT_CA_AUM NUMBER :=0;
M_EQ_AUM NUMBER :=0;
M_IN_AUM NUMBER :=0;
M_CA_AUM NUMBER :=0;
M_TOT_EQ_AUM NUMBER :=0;
M_TOT_IN_AUM NUMBER :=0;
M_TOT_CA_AUM NUMBER :=0;
M_PRIORITY_CUST VARCHAR2(30) := NULL;
M_CURR_DATE DATE := NULL;
M_ASONDATE DATE := NULL;

BEGIN
BEGIN
SELECT MAX(BUSINESS_DATE) INTO M_CURR_DATE
FROM DATE_MASTER;
EXCEPTION WHEN OTHERS THEN
NULL;
END;

IF P_ASONDATE IS NULL THEN
M_ASONDATE := M_CURR_DATE;
ELSE
M_ASONDATE := P_ASONDATE;
END IF;

FOR I IN C1 LOOP
IF UPPER(I.CODE)='EQUITY' then
M_EQ_WT :=TO_NUMBER(I.VALUE);
ELSIF UPPER(I.CODE)='INCOME' then
M_IN_WT :=TO_NUMBER(I.VALUE);
ELSIF UPPER(I.CODE)='CASH' then
M_CA_WT :=TO_NUMBER(I.VALUE);
END IF;
END LOOP;

FOR I IN C2 LOOP
IF UPPER(I.CODE)='PLATINUM' THEN
M_PL_FROMVAL := I.FROMVAL;
M_PL_TOVAL := I.TOVAL;
ELSIF UPPER(I.CODE)='GOLD' THEN
M_GL_FROMVAL := I.FROMVAL;
M_GL_TOVAL := I.TOVAL;
ELSIF UPPER(I.CODE)='SILVER' THEN
M_CL_FROMVAL := I.FROMVAL;
M_CL_TOVAL := I.TOVAL;
END IF;
END LOOP;

FOR I IN C3 LOOP
M_EQ_AUM := 0;
M_IN_AUM := 0;
M_CA_AUM := 0;

M_EQ_AUM := GETAUMACNO(M_ASONDATE, I.ACCOUNT_NUMBER, 'EQUITY');
M_IN_AUM := GETAUMACNO(M_ASONDATE, I.ACCOUNT_NUMBER, 'INCOME');
M_CA_AUM := GETAUMACNO(M_ASONDATE, I.ACCOUNT_NUMBER, 'CASH');

M_TOT_EQ_AUM := M_TOT_EQ_AUM + M_EQ_AUM;
M_TOT_IN_AUM := M_TOT_IN_AUM + M_IN_AUM;
M_TOT_CA_AUM := M_TOT_CA_AUM + M_CA_AUM;
END LOOP;

IF M_EQ_WT > 0 THEN
M_WT_EQ_AUM := (M_TOT_EQ_AUM * M_EQ_WT) / 10;
END IF;

IF M_IN_WT > 0 THEN
M_WT_IN_AUM := (M_TOT_IN_AUM * M_IN_WT) / 10;
END IF;

IF M_CA_WT > 0 THEN
M_WT_CA_AUM := (M_TOT_CA_AUM * M_CA_WT) / 10;
END IF;

IF (M_WT_EQ_AUM + M_WT_IN_AUM + M_WT_CA_AUM) >= M_PL_FROMVAL AND
(M_WT_EQ_AUM + M_WT_IN_AUM + M_WT_CA_AUM) <= M_PL_TOVAL THEN
M_PRIORITY_CUST := 'PLATINUM';
END IF;

IF (M_WT_EQ_AUM + M_WT_IN_AUM + M_WT_CA_AUM) >= M_GL_FROMVAL AND
(M_WT_EQ_AUM + M_WT_IN_AUM + M_WT_CA_AUM) <= M_GL_TOVAL THEN
M_PRIORITY_CUST := 'GOLD';
END IF;

IF (M_WT_EQ_AUM + M_WT_IN_AUM + M_WT_CA_AUM) >= M_CL_FROMVAL AND
(M_WT_EQ_AUM + M_WT_IN_AUM + M_WT_CA_AUM) <= M_CL_TOVAL THEN
M_PRIORITY_CUST := 'SILVER';
END IF;

RETURN M_PRIORITY_CUST;
END;

kindly give me the solution.