pears.GlobalDocumentSelect

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."GlobalDocumentSelect" IS 
{CREATE FUNCTION 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),'') 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),'') 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 g JOIN blobstore 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
}