CREATE FUNCTION proc_lookup_exportSQL 
    ( 
     CHAR(5), 
     INTEGER, 
     INTEGER, 
     SMALLINT, 
     SMALLINT 
    ) 
    RETURNS 
     resultRec
 AS ' 
 DECLARE 
  rec                  resultRec;            

  p_code               ALIAS FOR $1; 
  p_serviceCoRecNo     ALIAS FOR $2; 
  p_recNo              ALIAS FOR $3; 
  p_onFileIsErr        ALIAS FOR $4; 
  p_notOnFileIsErr     ALIAS FOR $5; 
  l_recNo              exportSQL.recNo%TYPE; 
  l_code               exportSQL.code%TYPE; 
  l_description        exportSQL.description%TYPE; 

 BEGIN 
    rec.sqlStatus       := 0; 
    rec.isamStatus      := 0; 

    rec.recNo           := NULL; 
    rec.code            := NULL; 
    rec.description     := NULL; 
 
    IF p_code IS NULL AND p_recNo IS NULL THEN 
        rec.l_string := ''No Export SQL Code Provided''; 
        RAISE EXCEPTION TRIM(l_string); 
    END IF; 
 
    IF p_code IS NOT NULL THEN 
        SELECT  recNo, code, description 
        INTO rec.recNo, rec.code, rec.description 
        FROM exportSQL 
        WHERE serviceCoRecNo   = p_serviceCoRecNo 
        AND   code             = p_code; 
    ELSE 
        SELECT  recNo, code, description 
        INTO rec.recNo, rec.code, rec.description 
        FROM exportSQL 
        WHERE recNo   = p_recNo; 
    END IF; 
 
    rec.string := ''EXPORT SQL (''||TRIM(p_code)||'')''; 
 
    IF NOT FOUND OR rec.recNo IS NULL THEN 
        IF p_notOnFileIsErr = 1 THEN 
            rec.string := TRIM(rec.string)||'' Not On File''; 
            RAISE EXCEPTION TRIM(rec.string); 
        END IF; 
    ELSE 
        IF p_onFileIsErr = 1  THEN 
            rec.string := TRIM(rec.string)||'' Already On File''; 
            RAISE EXCEPTION TRIM(rec.string); 
        END IF; 
    END IF; 
 
    RETURN rec;
 
 END; 
 ' LANGUAGE 'plpgsql';