====== pears.IQacDocumentOutstandingAmount ======
<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"."IQacDocumentOutstandingAmount"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in "DocID" char(8),in "CutoffDate" date ) 
returns decimal(12,2)
begin
  declare "rv" decimal(12,2);
  declare "amt" decimal(12,2);
  declare "fuldate" date;
  set "amt" = 0.0;
  set "rv" = 0.0;
  select first "amount","fullyallocated" into "amt","fuldate" from "iqacdocument" where "documentid" = "DocID";
  if "cutoffdate" is null then // We want the current state
    if "fuldate" is not null then // The document is fully allocated
      return 0.0
    end if;
    select "sum"("AllocatedAmount") into "rv" from "iqacallocation" where "allocateddocumentid" = "DocID";
    set "rv" = "isnull"("amt",0.0)-"isnull"("rv",0.0)
  else
    if "fuldate" is not null then // The document is now fully allocated
      if "fuldate" <= "cutoffdate" then // The document was fully allocated at the cut off date
        return 0.0
      end if end if;
    select "sum"("AllocatedAmount") into "rv" from "iqacallocation" where "allocateddocumentid" = "DocID" and "allocationdate" <= "cutoffdate";
    set "rv" = "isnull"("amt",0.0)-"isnull"("rv",0.0)
  end if;
  return "rv"
end
</code>