Oracle Applications

Oracle for Beginners

Posts Tagged ‘Key Flexfield Value Set’

Fetching Flexfield Description

Posted by shameemakhtar on October 19, 2009


CREATE OR REPLACE FUNCTION APPS.GET_FLEX_VS_DESC (P_VALUE_SET_NAME VARCHAR2, P_FLEX_VALUE VARCHAR2, P_PARENT_FLEX_VALUE VARCHAR2)
RETURN VARCHAR2 IS
DESCRIP VARCHAR2(50);
BEGIN
IF P_PARENT_FLEX_VALUE IS NULL
THEN
/*** In case the value set is independent ***/
SELECT DISTINCT DESCRIPTION
INTO DESCRIP
FROM FND_FLEX_VALUES_VL
WHERE FLEX_VALUE_SET_ID =
(
SELECT FLEX_VALUE_SET_ID
FROM FND_FLEX_VALUE_SETS
WHERE FLEX_VALUE_SET_NAME = P_VALUE_SET_NAME
AND FLEX_VALUE = P_FLEX_VALUE
--AND PARENT_FLEX_VALUE_LOW = P_PARENT_FLEX_VALUE
);
ELSE
/*** If the value set is dependent on another value set ***/
SELECT DISTINCT DESCRIPTION
INTO DESCRIP
FROM FND_FLEX_VALUES_VL
WHERE FLEX_VALUE_SET_ID =
(
SELECT FLEX_VALUE_SET_ID
FROM FND_FLEX_VALUE_SETS
WHERE FLEX_VALUE_SET_NAME = P_VALUE_SET_NAME
AND FLEX_VALUE = P_FLEX_VALUE
AND PARENT_FLEX_VALUE_LOW = P_PARENT_FLEX_VALUE
);
END IF;
RETURN NVL(DESCRIP, 'ERROR');
END;
/

Shameem Bauccha

19 October 2009

Advertisements

Posted in Oracle Apps, Scripts, System Administration, System Administrator, Technical | Tagged: , , , | Leave a Comment »