[RBASE-L] - Re: AGING COMMAND

[jan johansen]

David,

While not answering your question, you may want to try using a temporary 
table.
Here is one I use.

Jan


SET ERROR MESSAGE 2038 OFF
DROP TABLE tSalesAging
SET ERROR MESSAGE 2038 ON
CREATE TEMPORARY TABLE `tSalesAging` +
(`CustomerID` INTEGER , +
`InvoiceID` INTEGER , +
`InvoiceDate` DATE , +
`ProjectedDue` DATE , +
`InvoiceStatus` TEXT (1) , +
`InvoicePO` TEXT (30) , +
`InvoiceTotal` CURRENCY , +
`AmountPaid` CURRENCY , +
`DatePaid` DATE , +
`Days`=(.#DATE-InvoiceDate) INTEGER,
`LessThanThirty`= +
(IFLT((.#DATE - InvoiceDate),31,InvoiceTotal,NULL)) CURRENCY , +
`Thirty`= +
(IFGT((.#DATE - InvoiceDate),30,(IFLT((.#DATE - 
InvoiceDate),61,InvoiceTotal,NULL)),NULL)) CURRENCY, +
`Sixty`= +
(IFGT((.#DATE - InvoiceDate),60,(IFLT((.#DATE - 
InvoiceDate),91,InvoiceTotal,NULL)),NULL)) CURRENCY, +
`Ninety`= +
(IFGT((.#DATE - InvoiceDate),90,(IFLT((.#DATE - 
InvoiceDate),121,InvoiceTotal,NULL)),NULL)) CURRENCY, +
`OneTwenty`= +
(IFGT((.#DATE - InvoiceDate),120,(IFLT((.#DATE - 
InvoiceDate),151,InvoiceTotal,NULL)),NULL)) CURRENCY, +
`MoreThanOneFifty`= +
(IFGT((.#DATE - InvoiceDate),150,InvoiceTotal,NULL)) CURRENCY )
COMMENT ON TABLE `tSalesAging` IS 'Temporary Sales Aging Report'

APPEND InvoiceHead TO tSalesAging WHERE InvoiceStatus = 'O' +
ORDER BY CustomerID, InvoiceID





 


-----Original Message-----
From: t...@aol.com
To: rbase-l@rbase.com (RBASE-L Mailing List)
Date: Sat, 18 Sep 2010 09:00:59 -0400
Subject: [RBASE-L] - AGING COMMAND


THE FOLLOWING COMMAND USED TO WORK BUT I BELIEVE SOMEONE HAS CHANGED IT.   
COULD SOMEONE LOOK AT IT AND HELP ME FIGURE OUT WHAT IS WRONG.  I AM USING 
VER 7.1 AND WHEN THIS IS RUN IT BRINGS UP AN HOUR GLASS AND LOCKS UP THE 
PROGRAM.  
 
THANK YOU,
 
DAVID
 

*(-----------------------------------AGE.CMD
         AGES ACCOUNTS RECEIVABLE BALANCES
         BY INCREMENTALLY SUBTRACTING CURRENT
         PAYMENT FROM EXISTING BALANCE.)
*(---------------- SET UP VARIABLES.)
Set ANSI OFF
Set QUOTES = "
Set SINGLE = ?
Set MANY = *
Set AND OFF
SET VARIABLE THISPAY CURRENCY
SET VARIABLE REMAIN CURRENCY
SET VARIABLE MORE INTEGER
SET VARIABLE NEXTBAL INTEGER
SET VARIABLE BAL4 CURRENCY
SET VARIABLE BAL3 CURRENCY
SET VARIABLE BAL2 CURRENCY
SET VARIABLE BAL1 CURRENCY
SET VARIABLE BAL5 CURRENCY
*(-------------DISPLAY OPENING MESSAGES)
NEWPAGE
WRITE "AGING THE BALANCE..." AT 1,1
*(-----SET UP POINTER FOR LOOP THROUGH TABLE)
SET ERROR MESSAGE 705 OFF
DROP CURSOR cc1
SET ERROR MESSAGE 705 ON
DECLARE CC1 CURSOR FOR SELECT CUSTNUM FROM CUSTOMER
OPEN cc1
FETCH cc1 INTO passval INDI iv1, compare INDI iv2
WHILE SQLCODE <> 100 THEN
*(--0 IS "FALSE", 1 IS "TRUE" BELOW)
   SET VARIABLE MORE TO 0
   SET VARIABLE NEXTBAL TO 1
   SET VARIABLE REMAIN TO 0
*(---------- 90+ DAY BALANCE)
   SET VARIABLE THISPAY TO CURRPAY IN CUSTOMER
   SET VARIABLE BAL4 TO BAL120 IN CUSTOMER
   IF BAL4 > 0 THEN
      SET VARIABLE REMAIN TO (.THISPAY - .BAL4)
      IF REMAIN >= 0 THEN
         UPDATE CUSTOMER SET BAL120 TO 0
         SET VARIABLE MORE TO 1
      ELSE
         UPDATE CUSTOMER SET BAL120 TO (.REMAIN * -1)
         SET VARIABLE NEXTBAL TO 0
      ENDIF
   ENDIF
*(--------- 90 DAY BALANCE)
   SET VARIABLE BAL3 TO BAL90 IN CUSTOMER
   IF NEXTBAL = 1 AND BAL3 > 0 THEN
      IF MORE = 1 THEN
         SET VARIABLE REMAIN TO (.REMAIN - .BAL3)
      ELSE
         SET VARIABLE REMAIN TO (.THISPAY - .BAL3)
      ENDIF
         IF REMAIN >= 0 THEN
            UPDATE CUSTOMER SET BAL90 TO 0
            SET VARIABLE MORE TO 1
         ELSE
            UPDATE CUSTOMER SET BAL90 TO (.REMAIN * -1)
            SET VARIABLE NEXTBAL TO 0
         ENDIF
       ENDIF
*(--------- 60 DAY BALANCE)
     SET VARIABLE BAL2 TO BAL60 IN CUSTOMER
     IF NEXTBAL = 1 AND BAL2 > 0 THEN
        IF MORE = 1 THEN
           SET VARIABLE REMAIN TO (.REMAIN - .BAL2)
        ELSE
           SET VARIABLE REMAIN TO (.THISPAY - .BAL2)
        ENDIF
        IF REMAIN >= 0 THEN
           UPDATE CUSTOMER SET BAL60 TO 0
           SET VARIABLE MORE TO 1
        ELSE
        UPDATE CUSTOMER SET BAL60 TO (.REMAIN * -1)
          SET VARIABLE NEXTBAL TO 0
        ENDIF
     ENDIF
*(--------- 30 DAY BALANCE)
     SET VARIABLE BAL1 TO BAL30 IN CUSTOMER
     IF NEXTBAL = 1 AND BAL1 > 0 THEN
        IF MORE = 1 THEN
           SET VARIABLE REMAIN TO (.REMAIN - .BAL1)
        ELSE
           SET VARIABLE REMAIN TO (.THISPAY - .BAL1)
        ENDIF
        IF REMAIN >= 0 THEN
        UPDATE CUSTOMER SET BAL30 TO 0
           SET VARIABLE MORE TO 1
        ELSE
        UPDATE CUSTOMER SET BAL30 TO (.REMAIN * -1)
           SET VARIABLE NEXTBAL TO 0
        ENDIF
     ENDIF
*(--------- 0 DAY BALANCE)
     SET VARIABLE BAL5 TO CURRCHG IN CUSTOMER
     IF NEXTBAL = 1 AND BAL5 > 0 THEN
        IF MORE = 1 THEN
           SET VARIABLE REMAIN TO (.REMAIN - .BAL5)
        ELSE
           SET VARIABLE REMAIN TO (.THISPAY - .BAL5)
        ENDIF
        IF REMAIN >= 0 THEN
           UPDATE CUSTOMER SET CURRCHG TO 0
           SET VARIABLE MORE TO 1
        ELSE
           UPDATE CUSTOMER SET CURRCHG TO (.REMAIN * -1)
           SET VARIABLE NEXTBAL TO 0
        ENDIF
     ENDIF
ENDWHILE
  NEWPAGE
  DROP CURSOR cc1
  WRITE "BILL CYCLE COMPLETED..."
  RETURN