====== 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>
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."IQacDocumentOutstandingAmount" IS 
{create function 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>