CREATE OR REPLACE  FUNCTION "SPF361REPS"."GETSPFDATA"  (
   inOBID    IN   VARCHAR2,
   inAttr    IN   VARCHAR2,
   inType    IN   VARCHAR2,
   inEffDate IN   VARCHAR2 DEFAULT '', 
   inConfig  IN   VARCHAR2 DEFAULT 'N3-FLZEJRZQZICU-KOWT1JWF' 
)
   RETURN VARCHAR2
IS
   retValue   VARCHAR2(1000);
   mTemp      VARCHAR2(1);
BEGIN
  
  BEGIN
    IF inEffDate IS NULL THEN
      SELECT sa.n1strvalue INTO retValue 
      FROM n3objattr oa, n2spcattr sa, n2clsattr ca 
      WHERE ca.n1attrclassname = inAttr 
        AND oa.rightrel = sa.obid 
        AND sa.n1attrclassobid = ca.obid 
        AND oa.leftrel = inOBID 
        AND (oa.n1terminationdate IS NULL) 
        AND oa.n1config LIKE inConfig || '%' 
      ; 

    ELSE
      SELECT sa.n1strvalue INTO retValue 
      FROM n3objattr oa, n2spcattr sa, n2clsattr ca 
      WHERE ca.n1attrclassname = inAttr 
        AND oa.rightrel = sa.obid 
        AND sa.n1attrclassobid = ca.obid 
        AND oa.leftrel = inOBID 
        AND oa.n1creationdate <= inEffDate 
        AND ((oa.n1terminationdate IS NULL) OR oa.n1terminationdate > inEffDate) 
        AND oa.n1config LIKE inConfig || '%' 
      ; 
    END IF;


  EXCEPTION
    WHEN TOO_MANY_ROWS THEN
      retValue := '****>1 Row****';
    WHEN OTHERS THEN
      retValue := '';
  END;

  IF inType = 'ATTRNUM' THEN
    IF retValue IS NULL THEN
      retValue := '0';
    ELSE 
      BEGIN
        retValue := TO_NCHAR(TO_NUMBER(retValue));
      EXCEPTION
        WHEN OTHERS THEN
          retValue := '0'; 
      END; 
    END IF;
  END IF;

  IF inType = 'ATTRYNL' THEN
    IF (retValue IS NOT NULL) THEN
      mTemp := SUBSTR(retValue, 1, 1); 
      retValue := ''; 
      IF mTemp = '1' THEN retValue := 'Y'; END IF; 
      IF mTemp = 'Y' THEN retValue := 'Y'; END IF; 
      IF mTemp = 'y' THEN retValue := 'Y'; END IF; 
      IF mTemp = '+' THEN retValue := 'Y'; END IF; 
    END IF;
  END IF;


  RETURN retValue;
END getspfdata;
--Steve McNamee 2005/09/01 -  Added support for Config and Effectivity Date parameters to be passed.
--Steve McNamee 2005/09/12 -  Added option to pass ATTRNUM as inType which will force a numeric value 
--                            to be returned. For nulls and any non-numeric data return '0'
--Steve McNamee 2005/10/14 -  Added option to pass ATTRYNL as inType which will force either 
--                            'Y' or NULL to be returned depending on the boolean equivalent of the 
--                            first character in the attribute value.
--;