Find Component Interfaces with NO Menu Attached

Event Mapping it shaping up to be a pretty cool feature for customizing without touching delivered objects. The one area of concern is that when you add event mapping PeopleCode there are instances where the code may not trigger when the component is updated via a Component Interfaces.

The component interface definition must have a Menu assigned to the CI in order for the Event Mapping code to trigger. It turns out there seems to be a ton of CI’s delivered that do not have that menu since it is optional on the CI definition.

I wrote some quick SQL to find CI’s that are tied to unlocked users that do NOT have the menu attached. The idea here is to filter out any CI’s that may be in the database but no one is using. Therefore, I added the security lookup on unlocked accounts.

SELECT A.BCNAME AS CI_NAME,
  BCPGNAME      AS COMPONENT_NAME,
  A.MARKET,
  a.MENUNAME,
  A.LASTUPDDTTM,
  A.LASTUPDOPRID
FROM PSBCDEFN A
WHERE A.MENUNAME = ' '
AND EXISTS
  (SELECT B.CLASSID
  FROM PS_PSBCDEFN_VW B,
    PSOPRCLS C,
    PSOPRDEFN O
  WHERE B.BCNAME = A.BCNAME
  AND C.OPRCLASS = B.CLASSID
  AND O.OPRID    = C.OPRID
  AND O.ACCTLOCK = 0
  )

ORDER BY A.BCNAME