pears.IQacDocumentOutstandingAmount

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