Utility PLSQL Function for Retrieving MEANING from FND_LOOKUP_VALUES

 Java function from the utility class

    public static String getFndLookupValue(Date effectiveDate,
                                            String lookupType,
                                            String lookupCode,
                                            OADBTransaction oaDBTransaction) {

        CallableStatement callableStatement = null;
        String s = "BEGIN ? := xxhls_util_pkg.get_fnd_meaning_fn(?,?,?); END;";

        try {

            callableStatement = oaDBTransaction.createCallableStatement(s, -1);
            callableStatement.registerOutParameter(1, OracleTypes.VARCHAR);
            callableStatement.setDate(2, effectiveDate);
            callableStatement.setString(3, lookupType);
            callableStatement.setString(4, lookupCode);
            callableStatement.execute();
           
            return callableStatement.getString(1);
          
        } catch (Exception sqlexception) {

            throw new OAException(sqlexception.getMessage());

        } finally {

            try {
                if (callableStatement != null)
                    callableStatement.close();               
            } catch (SQLException sqlexception1) {
                throw new OAException(sqlexception1.getMessage());
            }

        }

    }

Java function inside the Application Module (pretty short =w=;; )

    public String getFndLookupValue(String lookupType, String lookupCode) {
       
        Date date = new Date(new java.util.Date().getTime());
       
        return XxhlsServerUtil.getFndLookupValue(date, lookupType, lookupCode, this.getOADBTransaction());
       
    }

PLSQL code

FUNCTION get_fnd_meaning_fn (i_effective_date IN DATE,
                             i_lookup_type IN FND_LOOKUP_VALUES.LOOKUP_TYPE%TYPE,
                             i_lookup_code IN FND_LOOKUP_VALUES.LOOKUP_CODE%TYPE)
                            
    RETURN FND_LOOKUP_VALUES.MEANING%TYPE

    IS
   
        l_meaning FND_LOOKUP_VALUES.MEANING%TYPE;
   
    BEGIN
   
        SELECT  MEANING
        INTO    l_meaning
        FROM    FND_LOOKUP_VALUES
        WHERE   LOOKUP_TYPE = i_lookup_type
                AND LOOKUP_CODE = i_lookup_code
                AND i_effective_date BETWEEN START_DATE_ACTIVE AND END_DATE_ACTIVE;
               
        RETURN  l_meaning;
               
        EXCEPTION
       
            WHEN NO_DATA_FOUND THEN
           
            SELECT  MEANING
            INTO    l_meaning
            FROM    FND_LOOKUP_VALUES
            WHERE   LOOKUP_TYPE = i_lookup_type
                    AND LOOKUP_CODE = i_lookup_code
                    AND END_DATE_ACTIVE IS NULL;
           
            RETURN  l_meaning;
   
    END get_fnd_meaning_fn;