pears.DBCleanupOrphanPhone

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.

Original SQL

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."DBCleanupOrphanPhone" IS 
{CREATE FUNCTION DBCleanupOrphanPhone 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
(IN countordo CHAR(1))
RETURNS INTEGER
BEGIN
  DECLARE @RC INTEGER;
  SET @RC = 0;
  IF countordo = 'C' THEN
    SELECT COUNT(*) INTO @RC FROM phone WHERE 
     (
     ((who = 'P') AND NOT EXISTS(SELECT * FROM person WHERE personid = phone.whoid)) OR 
     ((who = 'C') AND NOT EXISTS(SELECT * FROM company WHERE companyid = phone.whoid)) OR 
     ((who = 'CP') AND NOT EXISTS(SELECT * FROM employment WHERE employmentid = phone.whoid)) 
     );
    RETURN @RC
  END IF;
  DELETE FROM phone WHERE (who = 'P') AND
    NOT EXISTS(SELECT * FROM person WHERE personid = phone.whoid);
  SET @RC = @RC + (SELECT @@rowcount);
  DELETE FROM phone WHERE  who ='CP' AND
  NOT EXISTS(SELECT * FROM employment WHERE employmentid = phone.whoid);
  SET @RC = @RC + (SELECT @@rowcount);
  DELETE FROM phone WHERE(who = 'C') AND
    NOT EXISTS(SELECT * FROM company WHERE companyid = phone.whoid);
  SET @RC = @RC + (SELECT @@rowcount);
  CALL AuditLog('CLEANUP',NULL,string(@RC,' Unattached Phone Records deleted '),NULL,NULL);
  RETURN @RC;
END
}