Hi Gurus ,
Your help is greatly needed. I am trying to compile this function and getting the below error.
Trying to fix the code myself ,could you please suggets here .
PL/SQL: Statement ignored
PLS-00225: subprogram or cursor 'CUR_FEATURE' reference is out of scope
FUNCTION GET_TERMINAL_FEATURE_TYPE RETURN PROD.FEATURE_CUSTOM_LIST_TAB
IS
vTerminal_Id PROD.TERMINAL.TERMINAL_ID%TYPE;
vCFTSELECT PROD.CUSTOM_FEATURE_TYPE.FEATURE_TYPE_SELECT%TYPE;
nPRECEDENCE PROD.CUSTOM_FEATURE_TYPE.PRECEDENCE%TYPE;
nWIRE_COMM_SEQ_ID PROD.WIRELESS_COMM_INFO.WIRELESS_COMM_SEQ_ID%TYPE;
vDESC PROD.WIRELESS_COMM_INFO.DESCRIPTION%TYPE;
nFEATURE_TYPE_ID PROD.FEATURE_TYPE.FEATURE_TYPE_ID%TYPE;
TYPE CFT_SEL_REF_CUR IS REF CURSOR;
CFT_SEL_CUR CFT_SEL_REF_CUR;
CUSTOM_FEATURE_TYPE_LIST PROD.FEATURE_CUSTOM_LIST_TAB := PROD.FEATURE_CUSTOM_LIST_TAB();
CURSOR CUR_FEATURE (cvTERMINAL_ID PROD.TERMINAL.TERMINAL_ID%TYPE) IS
SELECT TSF.TERMINAL_FEATURE_ID ,TF.FEATURE_TYPE_ID, ROWNUM FEATURE_SEQUENCE_ID
FROM PROD.TERMINAL_FEATURE TF, PROD.TERMINAL_SPEC_FEATURE TSF
WHERE TF.TERMINAL_FEATURE_ID = TSF.TERMINAL_FEATURE_ID
AND TSF.TERMINAL_ID=cvTERMINAL_ID
ORDER BY TSF.TERMINAL_FEATURE_ID ;
CURSOR CUR_FEATURE_TYPE(cvFETAURE_TYPE_ID PROD.FEATURE_TYPE.FEATURE_TYPE_ID%TYPE) IS
SELECT FEATURE_TYPE_ID, FEATURE_SEQUENCE_ID, DISPLAY_VALUE, DATA_VALUE
FROM FEATURE_TYPE
WHERE FEATURE_TYPE_ID = cvFETAURE_TYPE_ID
ORDER BY FEATURE_SEQUENCE_ID;
BEGIN
DBMS_OUTPUT.PUT_LINE('Inside GET_TERMINAL_FEATURE_TYPE Function');
vTerminal_id := PROD.TMS_GLOBAL_VARIABLES.gv_ref_Terminal_id;
IF CUR_FEATURE.FEATURE_TYPE_ID>=1000 THEN
-- Get feature_type_id and feature_id for the Terminal
FOR CUR_FEATURE_REC IN CUR_FEATURE(vTerminal_id)
LOOP
-- fetch the select query based on feature_type_id
SELECT FEATURE_TYPE_SELECT INTO vCFTSELECT
FROM PROD.CUSTOM_FEATURE_TYPE
WHERE FEATURE_TYPE_ID=CUR_FEATURE_REC.FEATURE_TYPE_ID;
OPEN CFT_SEL_CUR FOR vCFTSELECT USING vTerminal_id;
LOOP
FETCH CFT_SEL_CUR INTO nWIRE_COMM_SEQ_ID, vDESC;
EXIT WHEN CFT_SEL_CUR%NOTFOUND;
CUSTOM_FEATURE_TYPE_LIST.EXTEND;
CUSTOM_FEATURE_TYPE_LIST(CUSTOM_FEATURE_TYPE_LIST.COUNT)
:= FEATURE_CUSTOM_LIST_OBJ( vTerminal_id,
CUR_FEATURE_REC.TERMINAL_FEATURE_ID,
CUR_FEATURE_REC.FEATURE_SEQUENCE_ID,
vDESC,
nWIRE_COMM_SEQ_ID
);
END LOOP;
CLOSE CFT_SEL_CUR;
END LOOP;
ELSE
FOR CUR_FEATURE_TYPE_REC IN CUR_FEATURE_TYPE(cvFETAURE_TYPE_ID)
LOOP
CUSTOM_FEATURE_TYPE_LIST.EXTEND;
CUSTOM_FEATURE_TYPE_LIST(CUSTOM_FEATURE_TYPE_LIST.COUNT)
:= FEATURE_CUSTOM_LIST_OBJ( vTerminal_id,
CUR_FEATURE_TYPE_REC.FEATURE_TYPE_ID,
CUR_FEATURE_TYPE_REC.FEATURE_SEQUENCE_ID,
CUR_FEATURE_TYPE_REC.DISPLAY_VALUE,
CUR_FEATURE_TYPE_REC.DATA_VALUE
);
END LOOP;
END IF;
RETURN CUSTOM_FEATURE_TYPE_LIST;
EXCEPTION WHEN OTHERS THEN
Raise;
END GET_TERMINAL_FEATURE_TYPE;