text: -- -*-Sql-*- mode (to keep my emacs happy)
  --
  -- API Package Body for Dbxref_Qualifier_Assoc.
  --
  -- Scaffold auto-generated by gen-api.pl. gen-api.pl is
  -- Copyright 2002-2003 Genomics Institute of the Novartis Research Foundation
  -- Copyright 2002-2008 Hilmar Lapp
  -- 
  --  This file is part of BioSQL.
  --
  --  BioSQL is free software: you can redistribute it and/or modify it
  --  under the terms of the GNU Lesser General Public License as
  --  published by the Free Software Foundation, either version 3 of the
  --  License, or (at your option) any later version.
  --
  --  BioSQL is distributed in the hope that it will be useful,
  --  but WITHOUT ANY WARRANTY; without even the implied warranty of
  --  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  --  GNU Lesser General Public License for more details.
  --
  --  You should have received a copy of the GNU Lesser General Public License
  --  along with BioSQL. If not, see <http://www.gnu.org/licenses/>.
  --
  
  CREATE OR REPLACE
  PACKAGE BODY DbxTrmA IS
  
  DbxTrmA_cached	SG_DBXREF_QUALIFIER_ASSOC.DBX_OID%TYPE DEFAULT NULL;
  cache_key		VARCHAR2(128) DEFAULT NULL;
  
  CURSOR DbxTrmA_c (DbxTrmA_DBX_OID	IN SG_DBXREF_QUALIFIER_ASSOC.DBX_OID%TYPE,
  		  DbxTrmA_TRM_OID	IN SG_DBXREF_QUALIFIER_ASSOC.TRM_OID%TYPE,
  		  DbxTrmA_RANK	IN SG_DBXREF_QUALIFIER_ASSOC.RANK%TYPE)
  RETURN SG_DBXREF_QUALIFIER_ASSOC%ROWTYPE IS
  	SELECT t.* FROM SG_DBXREF_QUALIFIER_ASSOC t
  	WHERE
  		t.DBX_Oid = DbxTrmA_DBX_OID
  	AND	t.Trm_Oid = DbxTrmA_TRM_OID
  	AND	t.Rank	  = DbxTrmA_RANK
  	;
  
  FUNCTION get_oid(
  		DBX_OID	IN SG_DBXREF_QUALIFIER_ASSOC.DBX_OID%TYPE DEFAULT NULL,
  		TRM_OID	IN SG_DBXREF_QUALIFIER_ASSOC.TRM_OID%TYPE DEFAULT NULL,
  		DbxTrmA_RANK	IN SG_DBXREF_QUALIFIER_ASSOC.RANK%TYPE DEFAULT NULL,
  		DbxTrmA_VALUE	IN SG_DBXREF_QUALIFIER_ASSOC.VALUE%TYPE DEFAULT NULL,
  		Dbx_ACCESSION	IN SG_DBXREF.ACCESSION%TYPE DEFAULT NULL,
  		Dbx_DBNAME	IN SG_DBXREF.DBNAME%TYPE DEFAULT NULL,
  		Dbx_VERSION	IN SG_DBXREF.VERSION%TYPE DEFAULT NULL,
  		Trm_NAME	IN SG_TERM.NAME%TYPE DEFAULT NULL,
  		ONT_OID	IN SG_TERM.ONT_OID%TYPE DEFAULT NULL,
  		ONT_NAME	IN SG_ONTOLOGY.NAME%TYPE DEFAULT NULL,
  		Trm_IDENTIFIER	IN SG_TERM.IDENTIFIER%TYPE DEFAULT NULL,
  		do_DML		IN NUMBER DEFAULT BSStd.DML_NO)
  RETURN SG_DBXREF_QUALIFIER_ASSOC.DBX_OID%TYPE
  IS
  	pk	SG_DBXREF_QUALIFIER_ASSOC.DBX_OID%TYPE DEFAULT NULL;
  	DbxTrmA_row DbxTrmA_c%ROWTYPE;
  	DBX_OID_	SG_DBXREF.OID%TYPE DEFAULT DBX_OID;
  	TRM_OID_	SG_TERM.OID%TYPE DEFAULT TRM_OID;
  	key_str	VARCHAR2(128) DEFAULT DBX_OID || '|' || TRM_OID || '|' || DbxTrmA_Rank || '|' || Dbx_ACCESSION || '|' || Dbx_DBNAME || '|' || Dbx_VERSION || '|' || Trm_NAME || '|' || ONT_OID || '|' || ONT_NAME || '|' || Trm_IDENTIFIER;
  BEGIN
  	-- initialize
  	-- look up
  	IF pk IS NULL THEN
  		IF (key_str = cache_key) THEN
  			pk := DbxTrmA_cached;
  		ELSE
  			-- reset cache
  			cache_key := NULL;
  			DbxTrmA_cached := NULL;
                  	-- look up SG_DBXREF
                  	IF (DBX_OID_ IS NULL) THEN
                  		DBX_OID_ := Dbx.get_oid(
                  			Dbx_ACCESSION => Dbx_ACCESSION,
                  			Dbx_DBNAME => Dbx_DBNAME,
                  			Dbx_VERSION => Dbx_VERSION);
                  	END IF;
                  	-- look up SG_TERM
                  	IF (TRM_OID_ IS NULL) THEN
                  		TRM_OID_ := Trm.get_oid(
                  			Trm_NAME => Trm_NAME,
                  			ONT_OID => ONT_OID,
                  			ONT_NAME => ONT_NAME,
                  			Trm_IDENTIFIER => Trm_IDENTIFIER);
                  	END IF;
  			-- do the look up
  			FOR DbxTrmA_row IN DbxTrmA_c (DBX_OID_, TRM_OID_, DbxTrmA_Rank) LOOP
  		        	pk := DbxTrmA_row.DBX_OID;
  				-- cache result
  			    	cache_key := key_str;
  			    	DbxTrmA_cached := pk;
  			END LOOP;
  		END IF;
  	END IF;
  	-- insert/update if requested
  	IF (pk IS NULL) AND 
  	   ((do_DML = BSStd.DML_I) OR (do_DML = BSStd.DML_UI)) THEN
  	    	-- look up foreign keys if not provided:
  		-- look up SG_DBXREF successful?
  		IF (DBX_OID_ IS NULL) THEN
  			raise_application_error(-20101,
  				'failed to look up Dbx <' || Dbx_ACCESSION || '|' || Dbx_DBNAME || '|' || Dbx_VERSION || '>');
  		END IF;
  		-- look up SG_TERM successful?
  		IF (TRM_OID_ IS NULL) THEN
  			raise_application_error(-20101,
  				'failed to look up Trm <' || Trm_NAME || '|' || ONT_OID || '|' || ONT_NAME || '|' || Trm_IDENTIFIER || '>');
  		END IF;
  	    	-- insert the record and obtain the primary key
  	    	pk := do_insert(
  		        DBX_OID => DBX_OID_,
  		        TRM_OID => TRM_OID_,
  			RANK => DbxTrmA_RANK,
  			VALUE => DbxTrmA_VALUE);
  	END IF; -- no update here
  	-- return the foreign key
  	RETURN DBX_OID_;
  END;
  
  FUNCTION do_insert(
  		DBX_OID	IN SG_DBXREF_QUALIFIER_ASSOC.DBX_OID%TYPE,
  		TRM_OID	IN SG_DBXREF_QUALIFIER_ASSOC.TRM_OID%TYPE,
  		RANK	IN SG_DBXREF_QUALIFIER_ASSOC.RANK%TYPE,
  		VALUE	IN SG_DBXREF_QUALIFIER_ASSOC.VALUE%TYPE)
  RETURN SG_DBXREF_QUALIFIER_ASSOC.DBX_OID%TYPE 
  IS
  BEGIN
  	-- insert the record
  	INSERT INTO SG_DBXREF_QUALIFIER_ASSOC (
  		DBX_OID,
  		TRM_OID,
  		RANK,
  		VALUE)
  	VALUES (DBX_OID,
  		TRM_OID,
  		RANK,
  		VALUE)
  	;
  	-- return the foreign key value
  	RETURN DBX_OID;
  END;
  
  END DbxTrmA;
  /
  
  