pears.GlobalDocumentSelect [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.GlobalDocumentSelect ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
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
</code>