EDIT

Run script to create common names from facts: #6938

There are some issues with common name import for E+M:

• Common names are generally stored as facts with category 12 in BM, but also in the emCommonName extension
• Common names are imported from the extension as it is better atomized and also for historical reasons

This creates problems:

• emCommonName extension data are not moved to other taxa when a taxon is send to synonymy
• new common names created as facts are not automatically created but the according script needs to be run
• facts being deleted are not deleted as emCommonName extension

Other problems:

• MisappliedName Common Names (see logfile) #7447
• TBC

                 SELECT  factTaxon.PTNameFk as factNameFk,  cn.PTNameFk commonNameFk,cn.NameInSourceFk nameInSource,
                    cn.CommonName, f.fact, cn.RefFk AS commonNameRefId, cn.Status commonNameStatus, synName.FullNameCache synonymName, tax.PTRefFk synonymRefFk, synStatus.StatusAbbrev synonymStatus,              
                    accName.FullNameCache acceptedName, acc.PTRefFk acceptedRefFk, factName.FullNameCache factName, factTaxon.PTRefFk factRefFk, factTaxonStatus.StatusAbbrev factTaxonStatus, f.FactId fact,  cn.CommonNameId,             
                    rel.RelPTaxonId, rel.RelQualifierFk, acc.RIdentifier accTaxonId, factTaxon.RIdentifier factTaxonId, accName.NameId accNameId, factTaxon.PTNameFk as factNameId,                 
                    f.FactId,  cn.CommonNameId, cn.CommonName, tax.RIdentifier AS taxonId, cn.PTNameFk, cn.RefFk AS refId, cn.Status, cn.RegionFks, cn.MisNameRefFk,                
                    cn.NameInSourceFk, cn.Created_When, cn.Updated_When, cn.Created_Who, cn.Updated_Who, cn.Note AS Notes, languageCommonName.Language,             
                    languageCommonName.LanguageOriginal, languageCommonName.ISO639_1, languageCommonName.ISO639_2,              
                    emLangRef.RefFk AS languageRefRefFk, emLangRef.ReferenceShort, emLangRef.ReferenceLong,             
                    emLangRef.LanguageFk, languageReferenceLanguage.Language AS refLanguage, languageReferenceLanguage.ISO639_2 AS refLanguageIso639_2,             
                    misappliedTaxon.RIdentifier AS misappliedTaxonId                

                 FROM   PTaxon AS misappliedTaxon RIGHT OUTER JOIN  
                                  emLanguage AS languageReferenceLanguage RIGHT OUTER JOIN  
                                  emLanguageReference AS emLangRef ON languageReferenceLanguage.LanguageId = emLangRef.LanguageFk RIGHT OUTER JOIN  
                                  emCommonName AS cn INNER JOIN  
                                  PTaxon AS tax ON cn.PTNameFk = tax.PTNameFk AND cn.PTRefFk = tax.PTRefFk ON   
                                  emLangRef.ReferenceId = cn.LanguageRefFk LEFT OUTER JOIN  
                                 emLanguage AS languageCommonName ON cn.LanguageFk = languageCommonName.LanguageId ON misappliedTaxon.PTNameFk = cn.NameInSourceFk AND   
                                 misappliedTaxon.PTRefFk = cn.MisNameRefFk  

                                LEFT OUTER JOIN Fact f ON cn.CommonNameId = f.ExtensionFk
                                LEFT OUTER JOIN PTaxon factTaxon ON factTaxon.PTNameFk = f.PTNameFk AND factTaxon.PTRefFk = f.PTRefFk
                                LEFT OUTER JOIN Name factName ON factTaxon.PTNameFk = factName.NameId
                                LEFT OUTER JOIN Status factTaxonStatus ON factTaxonStatus.StatusId = factTaxon.StatusFk
                                LEFT OUTER JOIN RelPTaxon rel ON rel.PTNameFk1 = tax.PTNameFk AND rel.PTRefFk1 = tax.PTRefFk AND rel.RelQualifierFk IN (2,6,7)
                                LEFT OUTER JOIN PTaxon acc ON rel.PTNameFk2 = acc.PTNameFk AND rel.PTRefFk2 = acc.PTRefFk 
                                LEFT OUTER JOIN Name accName ON accName.NameId = acc.PTNameFk
                                LEFT OUTER JOIN Name synName ON synName.NameId = tax.PTNameFk
                                LEFT OUTER JOIN Status synStatus ON synStatus.StatusId = tax.StatusFk
                WHERE commonNameId IN ( SELECT commonNameId FROM v_cdm_exp_commonNamesAll )
AND ( 1=1
-- acc.RIdentifier <> factTaxon.RIdentifier 
   -- acc.RIdentifier IS NULL AND factTaxon.RIdentifier <>  tax.RIdentifier
   --   AND synName.NameCache = factName.NameCache
  AND factTaxon.StatusFk = 6
 )
 ORDER BY factTaxon.PTNameFk, accTaxonId, RelPTaxonId, factId

Find commonNames or facts attached to orphaned taxa:

SELECT n.fullNameCache, pt.PTNameFk, pt.PTRefFk, count(*) as n  -- , f.* , pt.*
FROM Fact/emCommonName f INNER JOIN PTaxon pt ON pt.PTNameFk = f.PTNameFk AND pt.PTRefFk = f.PTRefFk
INNER JOIN Name n ON n.nameID =  pt.PTNameFk
WHERE pt.StatusFk = 6 AND FactCategoryFk = 12 //replace category for emCommonName
GROUP BY n.fullNameCache, pt.PTNameFk, pt.PTRefFk
ORDER BY n DESC