pears.FuzeLookUpPhone

CREATE PROCEDURE "pears"."FuzeLookUpPhone"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN @NUMBER CHAR(100) ) 
RESULT( 
  "RecordType" CHAR(1),
  "RecordTypeDescription" CHAR(20),
  "RecordID" CHAR(20),
  "NumberType" CHAR(25),
  "RecordName" VARCHAR(93),
  "AddressLine1" CHAR(40),
  "Town" CHAR(30),
  "EmailAddress" long VARCHAR,
  "SortName" CHAR(100),
  "CompanyID" CHAR(20),
  "Position" CHAR(50),
  "TotalRows" INTEGER ) 
BEGIN
  SELECT "RecordType","RecordTypeDescription","RecordID","NumberType","RecordName","AddressLine1","Town","EmailAddress","SortName","CompanyID","Position","count"() OVER() AS "TotalRows"
    FROM(SELECT 'C' AS "RecordType",
        (SELECT "CompanyName" FROM "Params") AS "RecordTypeDescription",
        "Phone"."WhoID" AS "RecordID",
        "isnull"("DisplayName","PhoneType"."Name") AS "NumberType",
        "Company"."Name" AS "RecordName",
        "Company"."Addr1" AS "AddressLine1",
        "Company"."Town" AS "Town",
        NULL AS "EmailAddress",
        "Company"."KeyName" AS "SortName",
        NULL AS "CompanyID",
        NULL AS "Position"
        FROM "Phone"
          KEY JOIN "PhoneType"
          JOIN "Company" ON "Company"."CompanyID" = "Phone"."WhoID"
          JOIN "DivisionsAllowed" ON "Company"."DivisionID" = "DivisionsAllowed"."DivisionID"
        WHERE "length"("Phone"."NumberDigits") > 10
        AND "Phone"."Who" = 'C'
        AND "locate"("PhoneType"."Capabilities",'D') > 0
        AND "phone"."NumberDialable" = @NUMBER UNION ALL
      SELECT 'P' AS "RecordType",
        (SELECT "ApplicantName" FROM "Params") AS "RecordTypeDescription",
        "PersonID" AS "RecordID",
        "isnull"("PhoneType"."DisplayName","PhoneType"."Name") AS "NumberType",
        "Person"."Name" AS "RecordName",
        "Person"."Addr1" AS "AddressLine1",
        "Person"."Town" AS "Town",
        (SELECT "nullif"("list"("ep"."Number"),'') FROM "Phone" AS "ep" KEY JOIN "PhoneType" AS "ept" WHERE "ep"."Who" = 'P' AND "ep"."WhoID" = "RecordID" AND "ept"."Capabilities" LIKE '%E%') AS "EmailAddress",
        "Person"."KeyName" AS "SortName",
        NULL AS "CompanyID",
        (SELECT "Status"."Name" FROM "Status" WHERE "Status"."Type" = 'P' AND "Status"."Status" = "Person"."Status") AS "Position"
        FROM "Phone"
          KEY JOIN "PhoneType"
          JOIN "Person" ON "Person"."PersonID" = "Phone"."WhoID"
          JOIN "DivisionsAllowed" ON "Person"."DivisionID" = "DivisionsAllowed"."DivisionID"
        WHERE "length"("Phone"."NumberDigits") > 10
        AND "Phone"."Who" = 'P'
        AND "locate"("PhoneType"."Capabilities",'D') > 0
        AND "Phone"."NumberDialable" = @NUMBER UNION ALL
      SELECT 'E' AS "RecordType",
        (SELECT "CoPersonName" FROM "Params") AS "RecordTypeDescription",
        "Employment"."EmploymentID" AS "RecordID",
        "isnull"("PhoneType"."DisplayName","PhoneType"."Name") AS "NumberType",
        "string"("Company"."Name",' - ',"Person"."Name") AS "RecordName",
        "Company"."Addr1" AS "AddressLine1",
        "Company"."Town" AS "Town",
        (SELECT "nullif"("list"("ep"."Number"),'') FROM "Phone" AS "ep" KEY JOIN "PhoneType" AS "ept" WHERE "ep"."Who" = 'CP' AND "ep"."WhoID" = "RecordID" AND "ept"."Capabilities" LIKE '%E%') AS "EmailAddress",
        "Company"."KeyName" AS "SortName",
        "Company"."CompanyID",
        "Employment"."Position" AS "Position"
        FROM "Phone"
          KEY JOIN "PhoneType"
          JOIN "Employment" ON "Phone"."WhoID" = "Employment"."EmploymentID"
          KEY JOIN("Person","Company")
          JOIN "DivisionsAllowed" ON "Company"."DivisionID" = "DivisionsAllowed"."DivisionID"
        WHERE "length"("Phone"."NumberDigits") > 10
        AND "Phone"."Who" = 'CP'
        AND "locate"("PhoneType"."Capabilities",'D') > 0
        AND "Phone"."NumberDialable" = @NUMBER UNION ALL
      SELECT 'S' AS "RecordType",
        'Staff' AS "RecordTypeDescription",
        "Staff"."StaffID" AS "RecordID",
        'Mobile / Direct' AS "NumberType",
        "Staff"."Name" AS "RecordName",
        NULL AS "AddressLine1",
        NULL AS "Town",
        "Staff"."Email" AS "EmailAddress",
        "Staff"."KeyName" AS "SortName",
        NULL AS "CompanyID",
        "Staff"."Title" AS "Position"
        FROM "Staff"
        WHERE("Staff"."Mobile" = @NUMBER
        OR "Staff"."ExtensionNumber" = @NUMBER)) AS "Q"
    ORDER BY "Q"."SortName" ASC
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."FuzeLookUpPhone" IS 
{CREATE PROCEDURE FuzeLookUpPhone 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
(IN @NUMBER CHAR(100))
RESULT 
	( 
		RecordType CHAR(1),
		RecordTypeDescription CHAR(20),
		RecordID CHAR(20),
		NumberType CHAR(25),
		RecordName VARCHAR(93),
		AddressLine1 CHAR(40),
		Town CHAR(30),
		EmailAddress long VARCHAR, 
		SortName CHAR(100), 
		CompanyID CHAR(20), 
		POSITION CHAR(50), 
		TotalRows INT
	)
BEGIN
		SELECT 
			 RecordType, RecordTypeDescription, RecordID, NumberType, RecordName, AddressLine1, Town, EmailAddress, SortName, CompanyID, POSITION, COUNT(*) OVER () AS TotalRows FROM 
				(
					SELECT  
						'C' AS RecordType, 
						(SELECT CompanyName FROM Params) AS RecordTypeDescription, 
						Phone.WhoID AS RecordID, 
						isnull(DisplayName,PhoneType.[Name]) AS NumberType,
						Company.[Name] AS RecordName, 
						Company.Addr1 AS AddressLine1, 
						Company.Town AS Town, 
						NULL AS EmailAddress, 
						Company.KeyName AS SortName, 
						NULL AS CompanyID, 
						NULL AS POSITION
					FROM 
						Phone KEY JOIN 
						PhoneType JOIN 
						Company ON Company.CompanyID = Phone.WhoID JOIN 
						DivisionsAllowed ON Company.DivisionID = DivisionsAllowed.DivisionID
					WHERE 
						LENGTH(Phone.NumberDigits) > 10 AND 
						Phone.Who='C' AND 
						locate(PhoneType.Capabilities,'D') > 0 AND 
						phone.NumberDialable = @NUMBER
				UNION ALL
					SELECT  
						'P' AS RecordType,   
						(SELECT ApplicantName FROM Params) AS RecordTypeDescription, 
						PersonID AS RecordID, 
						isnull(PhoneType.DisplayName,PhoneType.[Name]) AS NumberType, 
						Person.[Name] AS RecordName,  
						Person.Addr1 AS AddressLine1, 
						Person.Town AS Town, 
						(SELECT NULLIF(list(ep.Number),'') FROM Phone ep KEY JOIN PhoneType ept WHERE ep.Who = 'P' AND ep.WhoID = RecordID AND ept.Capabilities LIKE '%E%' ) AS EmailAddress, 
						Person.KeyName AS SortName, 
						NULL AS CompanyID, 
						(SELECT STATUS.[Name] FROM STATUS WHERE STATUS.[TYPE] = 'P' AND STATUS.Status = Person.Status) AS POSITION   
					FROM 
						Phone KEY JOIN 
						PhoneType JOIN 
						Person ON Person.PersonID = Phone.WhoID JOIN 
						DivisionsAllowed  ON Person.DivisionID = DivisionsAllowed.DivisionID 
					WHERE 
						LENGTH(Phone.NumberDigits) > 10 AND 
						Phone.Who='P' AND 
						locate(PhoneType.Capabilities,'D') > 0 AND 
						Phone.NumberDialable = @NUMBER
				UNION ALL
					SELECT  
						'E' AS RecordType, 
						(SELECT CoPersonName FROM Params) AS RecordTypeDescription, 
						Employment.EmploymentID AS RecordID, 
						isnull(PhoneType.DisplayName,PhoneType.[Name]) AS NumberType, 
						string(Company.[Name],' - ',Person.[Name]) AS RecordName, 
						Company.Addr1 AS AddressLine1, 
						Company.Town AS Town,
						(SELECT NULLIF(list(ep.Number),'') FROM Phone ep KEY JOIN PhoneType ept WHERE ep.Who = 'CP' AND ep.WhoID = RecordID AND ept.Capabilities LIKE '%E%' ) AS EmailAddress, 
						Company.KeyName AS SortName, 
						Company.CompanyID, 
						Employment.Position AS POSITION
					FROM 
						Phone KEY JOIN 
						PhoneType JOIN 
						Employment ON Phone.WhoID = Employment.EmploymentID KEY JOIN 
						(Person,Company) JOIN 
							DivisionsAllowed ON Company.DivisionID = DivisionsAllowed.DivisionID
					WHERE 
						LENGTH(Phone.NumberDigits)>10 AND 
						Phone.Who='CP' AND 
						locate(PhoneType.Capabilities,'D') > 0 AND 
						Phone.NumberDialable = @NUMBER
				UNION ALL
					SELECT 
						'S' AS RecordType,
						'Staff' AS RecordTypeDescription, 
						Staff.StaffID AS RecordID, 
						'Mobile / Direct' AS NumberType, 
						Staff.[Name] AS RecordName,
						NULL AS AddressLine1,
						NULL AS Town,
						Staff.Email AS EmailAddress, 
						Staff.KeyName AS SortName,
						NULL AS CompanyID,
						Staff.Title AS POSITION
					FROM 
						Staff 
					WHERE 
						(Staff.Mobile = @NUMBER 
						OR
						Staff.ExtensionNumber = @NUMBER)
		) Q 
	ORDER BY Q.SortName;
END
}