Edit D:\app\Administrator\product\11.2.0\dbhome_1\owb\reposasst\TranslationUtilityImpl.pkb

CREATE OR REPLACE PACKAGE BODY TranslationUtilityImpl AS

TYPE TTYPE is REF CURSOR;
---some global variable

--sochen: in tokyo, cwmseq increment_by changed from 100 to 10
default_cwmseq_inc integer:=10;
cwmseq_increment INTEGER := 0;
nextSequenceNumber INTEGER:=1;
maximumSequenceNumber INTEGER:=0;
--used for namespace check mode
INSERT_MODE CONSTANT NUMBER(1) := 0;
UPDATE_MODE CONSTANT NUMBER(1) := 1;
NAMESPACEPARENT CONSTANT NUMBER(1) := 0;
AGGREGATEPARENT CONSTANT NUMBER(1) := 1;
FCONamespaceOK BOOLEAN := TRUE;

--used to save the fatal error message...
fatal_error varchar2(300) := 'SUCCESS';

--used to automatically correct the name space vialaion by suffix a name with this number
next_suffix_number INTEGER  := 1;

-- following methods are intended to debug only
PROCEDURE MyDebug(debugMessage VARCHAR2)
IS
BEGIN
	DBMS_OUTPUT.PUT_LINE('DEBUG : '|| debugMessage);
END MyDebug;

function findBaseElementID(baseUOID in CMPElement_v.UOID%TYPE)
return CMPElement_v.ElementId%TYPE
IS
  baseElementId CMPElement_v.ElementId%TYPE := null;
BEGIN
  begin
     select elementId into baseElementId from CMPElement_v
     where  CMPElement_v.UOID = baseUOID;
     return baseElementId;	
  exception 
     when NO_DATA_FOUND then
	  --MyDebug('Sophai:ERROR: can not find base object on uoid!');	
          return null;	
     when others then
	  return null;	
  end;

END findBaseElementID;

Procedure findBaseInfo(baseUOID in CMPElement_v.UOID%TYPE,
			mlsTranslatable out CMPTranslation_v.mlsTranslatable%TYPE,
			baseClassName out CMPElement_v.classname%TYPE)
IS

BEGIN
  begin
     select elementId,className into mlsTranslatable,baseClassName from CMPElement_v
     where  CMPElement_v.UOID = baseUOID;

exception 
     when NO_DATA_FOUND then
     --MyDebug('Sophai:ERROR: can not find base object on uoid!');	
     	mlsTranslatable := null;
     	baseClassName := null;	
	
     when others then
	 mlsTranslatable := null;
    	 baseClassName := null;

end;

END findBaseInfo;

function findFCOID(elemId in CMPElement_v.ElementId%TYPE)
return CMPTranslation_v.firstclassobject%TYPE
IS
  fco CMPTranslation_v.firstclassobject%TYPE := null;
BEGIN
  begin
     select firstclassobject into fco from secondclassobject_v
     where  secondclassobject_v.elementid = elemId;
     return fco;	
  exception 
     when NO_DATA_FOUND then
	-- maybe the base object is not a SCO
	fco := elemId;	
          return fco;	
     when others then
	  return null;	
  end;	
END findFCOID;

/* no need since there is no foreign key between translation and mlstranslatable
function findLanguageElementID(tempISOID  cmpsupportedlanguage_v.ISOID%TYPE)
return CMPElement_v.ElementId%TYPE
IS
  elemId CMPElement_v.ElementId%TYPE := null;
	
BEGIN
  begin
     select elementId into elemId from cmpsupportedlanguage_v
     where  ISOID = tempISOID;
     return elemId;	
  exception 
     when NO_DATA_FOUND then
          return null;	
     when others then
	  return null;	
  end;

END findLanguageElementID;
*/
--sochen: return the old logicalname if exists
function checkTranslationExist(theMlsTranslatable CMPTranslation_v.MLSTranslatable%TYPE,
			      theSupportedLanguage CMPTranslation_v.ISOID%TYPE)
return CMPElement_v.logicalName%TYPE

IS
	
	oldLogicalName CMPElement_v.logicalName%type := null;

BEGIN
	begin
		select logicalName into oldLogicalName from CMPTranslation_v 
		where mlstranslatable = theMlsTranslatable and
			ISOID =  theSupportedLanguage;
		return oldLogicalName;
	exception 
		when NO_DATA_FOUND then
		return NULL;		
	end;	
END checkTranslationExist;

function  getNextElementId
return CMPElement_v.elementId%TYPE
IS

BEGIN	
	
		IF (cwmseq_increment = 0) THEN	
		   begin
 		   select increment_by into cwmseq_increment from user_sequences where sequence_name = 'CWMSEQ';
		   exception
	     		when NO_DATA_FOUND then
			  cwmseq_increment :=default_cwmseq_inc;--100;
	     		when others then
		          cwmseq_increment :=default_cwmseq_inc;--100;
		   end;

END IF;
	
                IF (nextSequenceNumber >= maximumSequenceNumber) THEN
			select cwmseq.nextval into nextSequenceNumber from dual;
			maximumSequenceNumber := nextSequenceNumber + cwmseq_increment;
			nextSequenceNumber :=nextSequenceNumber-1; --minus 1 since later nextSequenceNumber+1
		END IF;

nextSequenceNumber := nextSequenceNumber+1;
		return nextSequenceNumber;

END getNextElementId;

PROCEDURE updateTranslation(theMlsTranslatable CMPTranslation_v.MLSTranslatable%TYPE,
			theSupportedLanguage CMPTranslation_v.ISOID%TYPE,
			theFirstClassObjectId CMPTranslation_v.firstclassobject%TYPE,
			theLogicalName CMPTranslation_v.logicalName%TYPE,
			theDescription CMPTranslation_v.description%TYPE)
IS

BEGIN
	update CMPTranslation_v set updateTimestamp =sysdate,
		logicalName = theLogicalName,description = theDescription 
        where mlstranslatable = theMlsTranslatable and 
	      ISOID = theSupportedLanguage;
END updateTranslation;

PROCEDURE insertTranslation(theMlsTranslatable CMPTranslation_v.MLSTranslatable%TYPE,
			theSupportedLanguage CMPTranslation_v.ISOID%TYPE,
			theFirstClassObjectId CMPTranslation_v.firstclassobject%TYPE,
			theLogicalName CMPTranslation_v.logicalName%TYPE,
			theDescription CMPTranslation_v.description%TYPE,
				elemId CMPElement_v.elementId%TYPE)
IS
	--elemId CMPElement_v.elementId%TYPE;
	-- theUOID CMPElement_v.UOID%TYPE;	
BEGIN
	--begin
	--select cwmseq.nextval into elemId from dual;
	  --elemId := getNextElementId();	
	 
	--select increment_by into elemId from user_sequences where sequence_name = 'CWMSEQ';
		--MyDebug('sophia:eleid:'||elemId||',fco:'||theFirstClassObjectId||',thesupportedLang:'||theSupportedLanguage||',mlsable:'||theMlsTranslatable);
	insert into CMPTranslation_v(className,creationTimestamp,updateTimestamp,elementId,
	name,LogicalName,description,firstclassobject,ISOID,mlstranslatable,
	EDITABLE,CUSTOMERDELETABLE,CUSTOMERRENAMABLE,
	IMPORTED,PERSISTENT,SEEDED)values('CMPTranslation',sysdate,sysdate,elemId,'n_'||elemId,theLogicalName,theDescription,theFirstClassObjectId,theSupportedLanguage,theMlsTranslatable,'1','1','1',
	'0','1','0');
	
	--exception
	 --when others then	
	 --MyDebug('error when insert:'||SQLERRM);
	--end;

END insertTranslation;

function getOldLogicalName(
 		theMlsTranslatable CMPTranslation_v.MLSTranslatable%TYPE,
       		theSupportedLanguage CMPTranslation_v.ISOID%TYPE,
		newLogicalName CMPTranslation_v.logicalName%TYPE)
return CMPElement_v.logicalName%TYPE
IS
   oldLogicalName CMPTranslation_v.logicalName%TYPE;
BEGIN	
	begin
		select logicalName into oldLogicalName 
		from CMPTranslation_v where mlstranslatable=theMlsTranslatable
		and ISOID =  theSupportedLanguage;

return oldLogicalName;

exception
		when NO_DATA_FOUND then
		return null; -- wont happen
		when others then
		return null; -- wont happen
					
	end;

END getOldLogicalName;

function findBaseClassName(baseUOID in CMPElement_v.UOID%TYPE)
return CMPElement_v.classname%TYPE
IS
  baseClassName CMPElement_v.className%TYPE := null;
BEGIN
  begin
     select classname into baseClassName from CMPElement_v
     where  CMPElement_v.UOID = baseUOID;
	--MyDebug('after query,baseclassname:'||baseClassName);
     return baseClassName;	
  exception 
     when NO_DATA_FOUND then
	 	--MyDebug('Sophai:ERROR: can not find base classname on uoid:'||baseUOID);	
          return null;	
     when others then
		 --MyDebug('when others,Sophai:ERROR: can not find base classname on uoid:'||baseUOID);	
	  return null;	
  end;	
	return baseClassName;
END findBaseClassName;

FUNCTION IsFCO(theMlsTranslatable IN CMPTranslation_v.mlsTranslatable%TYPE)
	RETURN BOOLEAN IS

isFCO BOOLEAN := FALSE;
	owningFolder FirstClassObject_v.owningFolder%TYPE := null;
	firstClassObject SecondClassObject_v.firstClassObject%TYPE := null;
	className cmpelement_v.classname%TYPE := null;
BEGIN	
	begin
        select owningFolder into owningFolder from FirstClassObject_v
        where elementId = theMlsTranslatable;
        isFCO := TRUE;
	return isFCO;	
    exception
        when NO_DATA_FOUND then
            isFCO := FALSE;
        when others then
          -- should not happen
            isFCO := FALSE;
    end;
/*	
    begin
        select firstClassObject into firstClassObject from SecondClassObject_v
        where UOID = theUOID;
        isFCO := FALSE;
    exception
        when NO_DATA_FOUND then
	   -- IF(isFCO = FALSE) THEN
            isFCO := TRUE;
        when others then
            -- should not happen
            isFCO := TRUE;
    end;
*/	
    begin	
	select className into className from CMPElement_v where elementId =
		 theMlsTranslatable and className in  ('CMPUniqueKey', 'CMPForeignKey',
		'CMPWBCheckConstraint','CMPIndex');
	isFCO := TRUE;
	return isFCO;
	exception
        when NO_DATA_FOUND then
            isFCO := FALSE;
	    return isFCO;
        when others then
            -- should not happen
            isFCO := FALSE;
	    return isFCO;
    end;

END IsFCO;

function getInstalledModule(id in CMPElement_v.elementID%TYPE)
return CMPElement_v.elementID%TYPE
is
    tempElemID CMPElement_v.elementID%TYPE := null;
    moduleID CMPElement_v.elementID%TYPE := null;
    tcursor TTYPE;
begin
    open tcursor for
        select elementid from FirstClassObject_v start with elementid = id
        connect by prior owningFolder = elementid;
    loop
        begin
            fetch tcursor into tempElemID;
            exit when tcursor%NOTFOUND;
            begin
                select elementid into moduleID from CMPInstalledModule_v
                where elementid = tempElemID;
            exception
                when others then
                    null;
            end;
            if moduleID is not null then
                exit;
            end if;
        exception
            when others then
                null;
        end;
    end loop;
    close tcursor;
    return moduleID;
end getInstalledModule;

function getWorkspaceNSParentID return number
is
ownerid number;
begin 
	select elementid into ownerid from cmpwbuser_v where IsWorkspaceOwner = '1';

return ownerid;

EXCEPTION
	WHEN NO_DATA_FOUND THEN
	return null;
	WHEN OTHERS THEN
	return null;
end;

function getParentID(elemId in CMPElement_v.ElementId%TYPE,
		     whichParent in NUMBER)
return CMPElement_v.ElementId%TYPE
is
    parentID Reservation_Store.parentID%TYPE := null;
    className CMPElement_v.className%TYPE := null;
    isFCO BOOLEAN := FALSE;
    isSCO BOOLEAN := FALSE;
    owningFolder FirstClassObject_v.owningFolder%TYPE := null;
    firstClassObject SecondClassObject_v.firstClassObject%TYPE := null;
    logicalObject CMPPhysicalObject_v.logicalObject%TYPE := null;
    tempElemID CMPElement_v.elementID%TYPE := null;
begin
    select className into className from CMPElement_v where elementId = elemId;

if className is null then
        -- should not happen. each element should have a classname.
        return null;
    end if;

begin
        select owningFolder into owningFolder from FirstClassObject_v
        where  elementId = elemId;
        isFCO := TRUE;
    exception
        when NO_DATA_FOUND then
            isFCO := FALSE;
        when others then
          -- should not happen
            isFCO := FALSE;
    end;

begin
        select firstClassObject into firstClassObject from SecondClassObject_v
        where ElementId = elemId;
        isSCO := TRUE;
    exception
        when NO_DATA_FOUND then
            isSCO := FALSE;
        when others then
            -- should not happen
            isSCO := FALSE;
    end;

if className = 'CMPFunction' then
        select nvl(functionCategory, owningFolder)
        into tempElemID from CMPFunction_v
        where elementId = elemId;
        return tempElemID;

elsif className = 'CMPCatalog' then
        select nvl(owningproject,owningFolder)
        into tempElemID
        from CMPCatalog_v where elementId = elemId;
        return tempElemID;
    elsif className = 'CMPMap' then
        select nvl(nvl(owningFolder, installedModule),owningFolder)
        into tempElemID
        from CMPMap_v where elementId = elemId;
        return tempElemID;
    elsif className = 'CMPFunctionCategory' then
        select nvl(installedModule,owningFolder)
        into tempElemID from CMPFunctionCategory_v
        where elementId = elemId;
        return tempElemID;
  -- now proj has parent, which is workspace, treat it as normal FCO	
  --    elsif className = 'CMPWBProject' then
  --      return 0; 
    elsif classname = 'CMPWorkspace' then

return getWorkspaceNSParentID(); 
    elsif className = 'CMPPhysicalObject' then
        select logicalObject into logicalObject from CMPPhysicalObject_v
        where elementId = elemId;
        return logicalObject;
    elsif className = 'CMPPropertyValue'
          or className = 'CMPReferencePropertyValue'
          or className = 'CMPCLOBPropertyValue'
          or className = 'CMPStringPropertyValue'
          or className = 'CMPValuelessProperty' then
        select nvl(propertyOwner, firstClassObject)
        into tempElemID
        from CMPPropertyValue_v
        where elementId = elemId;
        return tempElemID;
    elsif className = 'CMPWBItemSetUsage' then
        select nvl(itemSet, firstClassObject) into tempElemID
        from CMPWBItemSetUsage_v where elementId = elemId;
        return tempElemID;
    elsif className = 'CMPFunctionImplementation' then
        select nvl(function, firstClassObject) into tempElemID
        from CMPFunctionImplementation_v where elementId = elemId;
        return tempElemID;
    elsif className = 'CMPForeignKey' then
	if(whichParent = AGGREGATEPARENT) then

select nvl(owningRelation, firstClassObject) into tempElemID
       		 from CMPForeignKey_v where elementId = elemId;
		return tempElemID;  
	else
	        tempElemID := getInstalledModule(firstClassObject);
        	return tempElemID;
	end if;
/*
    elsif className = 'CMPRedefines' then
        select nvl(owningRelation, firstClassObject) into tempElemID
        from CMPRedefines_v where elementId = elemId;
        return tempElemID;
*/
    elsif className = 'CMPWBNamedItemSet' then
        select nvl(owningRelation, firstClassObject) into tempElemID
        from CMPWBNamedItemSet_v where elementId = elemId;
        return tempElemID;
    elsif className = 'CMPUniqueKey' then
        if(whichParent = AGGREGATEPARENT) then
	   select nvl(owningRelation, firstClassObject) into tempElemID
           from CMPUniqueKey_v where elementId = elemId;
	   return tempElemID;
	else	
           tempElemID := getInstalledModule(firstClassObject);
           return tempElemID;
	end if;
    elsif className = 'CMPIndex' then
        if(whichParent = AGGREGATEPARENT) then
	   select nvl(owningRelation, firstClassObject) into tempElemID
           from CMPUniqueKey_v where elementId = elemId;
	   return tempElemID;
	else	
           tempElemID := getInstalledModule(firstClassObject);
           return tempElemID;
	end if;
    elsif className = 'CMPWBCheckConstraint' then
	if(whichParent = AGGREGATEPARENT) then
        	select nvl(owningRelation, firstClassObject) into tempElemID
        	from CMPWBCheckConstraint_v where elementId = elemId;
		return tempElemID;
	else

tempElemID := getInstalledModule(firstClassObject);
        	return tempElemID;
	end if;
    elsif className = 'CMPFunctionArgument' then
        select nvl(owningRelation,firstClassObject)
        into tempElemID
        from CMPFunctionArgument_v where elementId = elemId;
        return tempElemID;
    elsif className = 'CMPWBField' then
        select nvl(owningRelation,firstClassObject)
        into tempElemID
        from CMPWBField_v where elementId = elemId;
        return tempElemID;
    elsif className = 'CMPRelationalDBItem' then
        select nvl(owningRelation,firstClassObject)
        into tempElemID
        from CMPRelationalDBItem_v where elementId = elemId;
        return tempElemID;
    elsif className = 'CMPRecord' then
        select nvl(owningFile,firstClassObject)
        into tempElemID
        from CMPRecord_v where elementId = elemId;
        return tempElemID;
   elsif isFCO then
        return owningFolder;
    elsif isSCO then
        return firstClassObject;
    else
        return null;
    end if;

exception
 
    when others then
        return null;
end getParentID;
--return classification

FUNCTION getClassification(
		givenClassName IN VARCHAR2)
	RETURN VARCHAR2 IS
	retValue Classification_Store.domainName%TYPE;
BEGIN
	SELECT domainName INTO retValue FROM Classification_Store WHERE className = givenClassName;
	return retValue;
EXCEPTION
	WHEN NO_DATA_FOUND THEN
	return givenClassName;
	WHEN OTHERS THEN
	return givenClassName;
	
END getClassification;

--seems to me only this fn need to be committed anyway
PROCEDURE logErrorMessage(errCode NUMBER, errorMessage VARCHAR2, information VARCHAR2)
IS
v_ErrorTime Owb_Error_Log_Table.WhenHappend%TYPE;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
	SELECT to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') INTO v_ErrorTime FROM dual;
	INSERT INTO Owb_Error_Log_Table(errcode, errtext, info, whenhappend)
		VALUES(errCode, errorMessage,
		information ,
		v_ErrorTime);
	COMMIT;
EXCEPTION
	-- even error might happen when inserting into error log
	WHEN OTHERS THEN
		ROLLBACK;
		RETURN;

END logErrorMessage;

-- for the mapping object, we can not check them since we do not have
-- enough information in database: e.g. cmpmappingparameter, it can
-- have a mapping as nsParent, and also can have
-- cmpmappingparametergroup as nsParent
-- HENCE, here we just return SUCCESS for each check...
FUNCTION checkMappingObjectNamespace(
				theUOID IN CMPElement_v.UOID%TYPE,
				theISOID IN CMPSupportedLanguage_v.ISOID%TYPE,
				theLogicalName IN CMPElement_v.logicalname%TYPE,
				baseClassName IN CMPElement_v.classname%TYPE				
				 ) 
	RETURN Varchar2 IS		
	error varchar2(200) := 'SUCCESS';
	parentId CMPElement_v.elementId%TYPE;
	
BEGIN

return error;	
/*			
	IF(baseClassName ='CMPMappingParameterGroup') THEN
		parentId = getParentId(theUOID);
		open tcursor for
		select logicalname from cmptranslation_v,cmpsupportedlanguage_v,cmpmappingparameterGroup_v 
		where mlstranslatable =cmpmappingparameterGroup_v.elementid 	
		and cmpmappingparametergroup_v.UOID = theUOID
		and  cmptranslation_v.supportedlanguage =cmpsupportedlanguage_v.elementid
		and cmpsupportedlanguage_v.ISOID = theISOID and 
		cmpmappingparametergroup_v.stageComponent = parentId;
	ELSIF(baseClassName ='CMPMappingParameter') THEN
		parentId = getParentId(theUOID);
		open tcursor for
		select logicalname from cmptranslation_v,cmpsupportedlanguage_v,cmpmappingparameter_v 
		where mlstranslatable =cmpmappingparameter_v.elementid 	
		and cmpmappingparametergroup_v.UOID = theUOID
		and  cmptranslation_v.supportedlanguage =cmpsupportedlanguage_v.elementid
		and cmpsupportedlanguage_v.ISOID = theISOID and 
		cmpmappingparametergroup_v.parameterGroup = parentId;

ELSIF(baseClassName ='CMPMappingDisplaySet') THEN
		return checkMappingObjectNamespace(baseClassName);
	END IF;
*/

END checkMappingObjectNamespace;

FUNCTION checkNamespaceForSCO( theMlsTranslatable IN CMPTranslation_v.MLSTranslatable%TYPE,
				theISOID IN CMPSupportedLanguage_v.ISOID%TYPE,
				theLogicalName IN CMPElement_v.logicalname%TYPE,
				baseClassName IN CMPElement_v.classname%TYPE
				)
	RETURN Varchar2 IS	
	parentId CMPElement_v.elementId%TYPE;
	result varchar2(200) := null;
	oneLogicalName CMPElement_v.logicalName%TYPE;
	tcursor TTYPE;

BEGIN
	result := theLogicalName;

parentId := getParentId(theMlsTranslatable,NAMESPACEPARENT);
		open tcursor for 
	   	select cmptranslation_v.logicalName from cmptranslation_v,cmpelement_v   
           	where firstclassobject = parentId and
           	cmptranslation_v.ISOID = theISOID and
	      --   cmpsupportedlanguage_v.ISOID = theISOID and 
	   	cmpelement_v.elementid =cmptranslation_v.mlstranslatable and 
	        cmpelement_v.classname = baseClassName;
		loop 
	  	begin
			fetch tcursor into oneLogicalName;
			exit when tcursor%NOTFOUND;
			if(oneLogicalName = theLogicalName ) then
				result :=theLogicalName||'_'||next_suffix_number;
				next_suffix_number := next_suffix_number+1;

--MyDebug('--returned value:'||result);
				return result;

end if;		
	  	end;
		end loop;
		close tcursor;

return result; --should be the name passed in

END checkNamespaceForSCO;

FUNCTION checkNamespaceForFCO( theMlsTranslatable IN CMPTranslation_v.MLSTranslatable%TYPE,
				theUOID IN CMPElement_v.UOID%TYPE,
			       theISOID IN CMPSupportedLanguage_v.ISOID%TYPE,
			       theLogicalName IN CMPElement_v.logicalname%TYPE,
			       operationMode IN NUMBER,
			       oldLogicalName IN CMPElement_v.logicalname%TYPE,
			       baseClassName IN CMPElement_v.classname%TYPE,
				newElementId IN CMPElement_v.elementid%TYPE)	
	RETURN Varchar2 IS

nsParentId CMPElement_v.elementId%TYPE;
	theDomain CMPALLFOLDERFCONAMES.domain%TYPE;
	result Varchar2(200):= null;
	className CMPElement_v.className%TYPE;
	elemId CMPElement_v.elementId%TYPE;
	aggregParentId CMPElement_v.elementId%TYPE;

v_ErrorText Owb_Error_Log_Table.errText%TYPE;
	v_ErrorCode Owb_Error_Log_Table.errCode%TYPE;
BEGIN
	if(FCONamespaceOK) then	
	   result := theLogicalName; --initialize to passed_in one
 	else
	   result :=theLogicalName||'_'||next_suffix_number;		
	   next_suffix_number := next_suffix_number +1;
	end if;

--MyDebug('sophia:check ns for fco');	
	--select className,elementId into className,elemId from cmpelement_v where UOID =theUOID;	
	nsParentId := getParentId(theMlsTranslatable,NAMESPACEPARENT);
	aggregParentId := nsParentId;
	if(baseClassName = 'CMPUniqueKey' OR
	   baseClassName = 'CMPForeignKey' OR	
	   baseClassName = 'CMPWBCheckConstraint' OR 
	   baseClassName = 'CMPIndex') then
		aggregParentId := getParentId(theMlsTranslatable,AGGREGATEPARENT);
	end if;
	theDomain  := getClassification(baseClassName);	
	
	IF(nsParentId IS NULL) THEN
	  fatal_error := 'Can not find the name space parent Id for the give UOID:'||theUOID;
	  return result;
	ELSE
	   begin
		IF(operationMode = INSERT_MODE) THEN
		   --MyDebug('insert into allnames:'||theLogicalName||',domain:'||theDomain||',pid:'||nsParentId||',isoid:'||theISOID);
         	   INSERT INTO CMPALLFOLDERFCONAMES(parentID, domain, name, isPhys,elementId,aggregateparent,languageISOID)
		VALUES(nsParentId, theDomain, result, 2,newElementId,aggregParentId,theISOID);
		 FCONamespaceOK := TRUE;
		 return result;
		 
		ELSE
		 --MyDebug('UPDATE into allnames:'||theLogicalName||',domain:'||theDomain||',pid:'||nsParentId||',isoid:'||theISOID||',oldname:'||oldLogicalName);
		   UPDATE CMPALLFOLDERFCONAMES set name =result 
		   where parentId = nsParentId and domain = theDomain and
		   isPhys = 2 and languageISOID = theISOID and name = oldLogicalName;
		--MyDebug('what is returned :'||result);
	        FCONamespaceOK := TRUE;
		return result;
		END IF;
	   exception
              	WHEN DUP_VAL_ON_INDEX THEN
		--has name space conflict,record ns warning...
		FCONamespaceOK := FALSE;

WHEN OTHERS THEN
		--MyDebug('error:when ns check for fco,when others happened');
		v_ErrorCode := SQLCODE;
		v_ErrorText := SUBSTR(SQLERRM, 1, 500);
		logErrorMessage(v_ErrorCode, v_ErrorText,
				'when check name space for:'||theUOID||' in language:'||theISOID||' with translation name:'||theLogicalName);
	     	fatal_error := 'fatal error happened when check name space for:'||theUOID
			||' in language:'||theISOID||' with translation name:' 
			||theLogicalName||',the error message is:'||SUBSTR(SQLERRM, 1, 200);
		return result;
	    
	   end;
	END IF;
	return result;
END checkNamespaceForFCO;

function checkNamespace(mlsTranslatable CMPTranslation_v.mlsTranslatable%TYPE ,
			theUOID CMPElement_v.UOID%TYPE,
			ISOID cmpsupportedlanguage_v.ISOID%TYPE,
			logicalName CMPElement_v.logicalName%TYPE,
			operation_mode NUMBER, 
			oldLogicalName CMPElement_v.logicalName%TYPE,
			baseClassName CMPElement_v.ClassName%TYPE,
			newElementId CMPElement_v.elementId%TYPE)
return varchar2
IS
	result varchar2(200);
BEGIN
      IF(IsFCO(mlsTranslatable)) THEN	
	LOOP
	 result := checkNamespaceForFCO(mlsTranslatable,theUOID,ISOID,logicalName,operation_mode,oldLogicalName,baseClassName,newElementId);
	 exit when (FCONamespaceOK = TRUE);
	END LOOP;
	return result;
      ELSE
	 return checkNamespaceForSCO(mlsTranslatable,ISOID,logicalName,baseClassName);
      END IF;

END checkNamespace;

-- populate translation info for the base object
PROCEDURE populateTranslationInfo(outcome out VARCHAR2) 
  IS
  	--PRAGMA AUTONOMOUS_TRANSACTION;--the connection is only on changeofthis

tempBaseObjectUOID TranslationTemp.baseObjectUOID%TYPE;
	tempISOID TranslationTemp.supportedLanguageISOID%TYPE;
	tempLogicalName TranslationTemp.logicalName%TYPE;
	tempDescription TranslationTemp.description%TYPE;
	mlsTranslatable CMPTranslation_v.mlsTranslatable%TYPE;
	firstClassObjectId CMPTranslation_v.firstClassObject%TYPE;
	--supportedLanguage CMPTranslation_v.ISOID%TYPE;
	lineNumber INTEGER;
	tcursor TTYPE;

newElementId CMPElement_v.elementId%TYPE;

oldLogicalName CMPElement_v.logicalName%TYPE;
	baseClassName CMPElement_v.classname%TYPE;
	result Varchar2(200);

--PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN

--initialize suffix number to 1
	next_suffix_number := 1;
	lineNumber := 1;
	open tcursor for
	   select TranslationTemp.baseObjectUOID,TranslationTemp.supportedLanguageISOID,
                  TranslationTemp.logicalName,TranslationTemp.description
	   from TranslationTemp;
	loop
          begin
		fetch tcursor into tempBaseObjectUOID,tempISOID, tempLogicalName,tempDescription;
		exit when tcursor%NOTFOUND;
		
		--sochen:mlsTranslatable and baseClassname can be in
                --same procedure

findBaseInfo(tempBaseObjectUOID,mlsTranslatable,baseClassName);

--mlsTranslatable :=findBaseElementID(tempBaseObjectUOID);
		--firstClassObjectId :=findFCOID(tempBaseObjectUOID);
		firstClassObjectId :=findFCOID(mlsTranslatable);

--no need since there is no longer asso btwn trans and
	        -- mlstranslatable
		--supportedLanguage := findLanguageElementID(tempISOID);
		--baseClassName :=findBaseClassName(tempBaseObjectUOID);
		--errorMsg := 'mlsTranslatable is '||mlsTranslatable;
		
		IF(mlsTranslatable IS null) THEN
		  
                   outcome := 'At row:'||lineNumber||' has ERROR:can not find base object in database for the given UOID:'||tempBaseObjectUOID;
		
		  --MyDebug(outcome);	
		  ROLLBACK;
		  return;	
	        END IF;

IF(firstClassObjectId IS null) THEN

outcome := 'At row:'||lineNumber||' has ERROR:can not find first class object in database for the given UOID:'||tempBaseObjectUOID;
		  --MyDebug(outcome);	
		  ROLLBACK;
		  return;	
	        END IF;
/*
		IF(supportedLanguage IS null) THEN

outcome := 'At row:'||lineNumber||' has ERROR:can not find the supported language  in database for the given ISOID:'||tempBaseObjectUOID;
		  --MyDebug(outcome);	
		   ROLLBACK;
	 	  return;	
	        END IF;
*/
		--sochen: let this checking return the old logicalname 
		--oldLogicalName := checkTranslationExist(mlsTranslatable,supportedLanguage);
		oldLogicalName := checkTranslationExist(mlsTranslatable,tempISOID);

IF(oldLogicalName IS NULL) THEN

--MyDebug('sophia:go to insert');	
			newElementId := getNextElementId();--here we need it before checking namespace

IF(baseClassName != 'CMPFunction')THEN
				--MyDebug('sophia:checkNS when insert');

-- result := checkNamespace(tempBaseObjectUOID,tempISOID,tempLogicalName,INSERT_MODE,oldLogicalName,baseClassName);	
				 result := checkNamespace(mlsTranslatable,tempBaseObjectUOID,tempISOID,tempLogicalName,INSERT_MODE,oldLogicalName,baseClassName,newElementId);	
				IF(fatal_error != 'SUCCESS' ) then
			    		outcome := fatal_error;
			    		ROLLBACK;
			    		return;

END IF;	
			END IF;
			--MyDebug('Sophia:go to insert');
			insertTranslation(mlsTranslatable,tempISOID,firstClassObjectId,result,tempDescription,newElementId);

ELSE

--MyDebug('oldname:'||oldLogicalName);
			--MyDebug('newname:'||tempLogicalName);
			IF(tempLogicalName != oldLogicalName) THEN
				--MyDebug('sophia:baseclassname:'||baseClassName);

IF(baseClassName != 'CMPFunction')THEN
				--MyDebug('sophia:checkNS when update');
				 	-- result := checkNamespace(tempBaseObjectUOID,tempISOID,tempLogicalName,UPDATE_MODE,oldLogicalName,baseClassName);	
					 result := checkNamespace(mlsTranslatable,tempBaseObjectUOID,tempISOID,tempLogicalName,UPDATE_MODE,oldLogicalName,baseClassName,newElementId);	
					IF(fatal_error != 'SUCCESS' ) then
			    			outcome := fatal_error;
			    			ROLLBACK;
			    			return;

END IF;	
				END IF;
				--MyDebug('sophia:go to update');
				updateTranslation(mlsTranslatable,tempISOID,firstClassObjectId,result,tempDescription);
	
			END IF;

END IF;
	  lineNumber := lineNumber+1;
	  end;
	
	end loop;
	close tcursor;
	-- everything is fine, commit it
	--COMMIT; --leave this to caller

outcome := 'SUCCESS';
	
	--MyDebug(outcome);	
 	exception
		when others then		
	 	outcome := 'At Row:'||lineNumber||',has error:'||SUBSTR(SQLERRM,1, 200);
	        -- some err happened, rollback anyway
		--MyDebug(outcome);	
		ROLLBACK;
  		return;
 	
  END populateTranslationInfo;

END TranslationUtilityImpl;

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de