pears.GlobalDocumentSelect

CREATE FUNCTION "pears"."GlobalDocumentSelect"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "pDescription" CHAR(50) DEFAULT NULL,IN "pDocumentTypeID" CHAR(20) DEFAULT NULL,IN "pAgencyID" CHAR(20) DEFAULT NULL,IN "pDivisionID" CHAR(20) DEFAULT NULL,
  IN "pDepartmentID" CHAR(2) DEFAULT NULL,IN "pWebPublish" SMALLINT DEFAULT NULL,IN "pWebUserClassID" CHAR(20) DEFAULT NULL ) 
RETURNS CHAR(20)
BEGIN
  /* Specify pDescription and/or pDocumentTypeID plus any of the other parameters to narrow it down*/
  FOR "docs" AS "curs" no scroll cursor FOR
    SELECT top 1
      "g"."globaldocumentID",
      "isnull"("description",'') AS "xdescrip",
      "isnull"((SELECT FIRST "divisionid" FROM "globaldocumentlink" WHERE "globaldocumentID" = "g"."globaldocumentID" AND "divisionid" = "pDivisionID" ORDER BY 1 ASC),'') AS "xdiv",
      (SELECT "count"() FROM "globaldocumentlink" WHERE "globaldocumentID" = "g"."globaldocumentID" AND "divisionid" IS NOT NULL) AS "xdivcnt",
      "isnull"((SELECT FIRST "departmentid" FROM "globaldocumentlink" WHERE "globaldocumentID" = "g"."globaldocumentID" AND "departmentid" = "pDepartmentID" ORDER BY 1 ASC),'') AS "xdept",
      (SELECT "count"() FROM "globaldocumentlink" WHERE "globaldocumentID" = "g"."globaldocumentID" AND "departmentid" IS NOT NULL) AS "xdeptcnt",
      "isnull"("b"."publishtoweb",0) AS "xpublish",
      "isnull"("agencyid",'') AS "xagency",
      "isnull"("documenttypeid",'') AS "xdoctype",
      "isnull"("iqxnetuserclassid",'') AS "xuserclass",
      "length"("string"("xagency","xdiv","xdept","xdoctype","xuserclass")) AS "xmatch"
      FROM "globaldocument" AS "g" JOIN "blobstore" AS "b" ON "b"."id" = "g"."globaldocumentID" AND "b"."class" = 'G'
      WHERE("isnull"("pDescription","xdescrip") = "xdescrip")
      AND("isnull"("pAgencyID",'') = "xagency" OR "xagency" = '')
      AND("isnull"("pDivisionID",'') = "xdiv" OR "xdivcnt" = 0)
      AND("isnull"("pDepartmentID",'') = "xdept" OR "xdeptcnt" = 0)
      AND("isnull"("pDocumentTypeID","xdoctype") = "xdoctype")
      AND("isnull"("pWebUserClassID",'') = "xuserclass" OR "xuserclass" = '')
      AND("isnull"("pWebPublish","xpublish") = "xpublish")
      AND "isnull"("g"."defunct",0) = 0
      ORDER BY "xmatch" DESC FOR READ ONLY do
    RETURN "globaldocumentID"
  END FOR
END