pears.IQacDocumentOutstandingAmount

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
}