SQL Statement to Identify Plugins for Cleanup

I came accross the following SQL statement posted by Timmus Agersea from Customer Systems, Inc in the SalesLogix Business Partner newsgroups to identify plugins for cleanup.

This SQL statement will return a list of plugins that meet the following criteria:

  1. They are not released
  2. The are not the released version
  3. Another plugin with the same type, family, & name is released

SELECT
  Distinct P.PluginID,
CASE P.Type
    WHEN 0 THEN 'Processes, Contact'
    WHEN 1 THEN 'Processes, Sales'
    WHEN 2 THEN 'Scripts, Legacy Basic'
    WHEN 3 THEN 'Forms, Legacy'
    WHEN 4 THEN 'Report Profiles'
    WHEN 5 THEN 'Scripts, SQL'
    WHEN 8 THEN 'Groups, ACO'
    WHEN 13 THEN 'Macros'
    WHEN 14 THEN 'Menus'
    WHEN 15 THEN 'Toolbars'
    WHEN 18 THEN 'Images, Bitmap'
    WHEN 19 THEN 'Reports'
    WHEN 22 THEN 'Forms, Legacy List View'
    WHEN 23 THEN 'Groups'
    WHEN 24 THEN 'Forms, Legacy Support'
    WHEN 25 THEN 'Templates, Mail Merge'
    WHEN 26 THEN 'XML Schema'
    WHEN 27 THEN 'Scripts, VBscript'
    WHEN 28 THEN 'Forms'
    WHEN 30 THEN 'Main View'
    WHEN 31 THEN 'Global Script'
    ELSE 'Unknown'
End Type,
  P.Family, P.Name
FROM
  Plugin P
  LEFT JOIN Plugin Child
    ON Child.BasedOn = P.PluginID
  INNER JOIN
  (
  SELECT
    Parent.Type, Parent.Family, Parent.Name
  FROM
    Plugin Parent
    INNER JOIN Plugin Child
      ON Child.BasedOn = Parent.PluginID
  ) Released
    ON Released.Type = P.Type
    AND Released.Family = P.Family
    AND Released.Name = P.Name
WHERE
  P.BasedOn IS NULL
  AND Child.PluginID IS NULL
ORDER BY
  P.Type, P.Family, P.Name

This is awesome. Thanks Timmus!

ABOUT THE AUTHOR

Ryan Farley

Ryan Farley is the Director of Development for Customer FX and creator of slxdeveloper.com. He's been blogging regularly about SalesLogix, now Infor CRM, since 2001 and believes in sharing with the community. His new passion for CRM is Creatio, formerly bpm'online. He loves C#, Javascript, web development, open source, and Linux. He also loves his hobby as an amateur filmmaker.

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) and Creatio (bpm'online) news and product updates!

You have Successfully Subscribed!