Dear Sandra:
Instead of variables, you may wish to create objects using decodes as in
the following actual example that works OK in our Oracle AcctsReceivable
system:
Object Name: Amt 1-30 Days Past Due
decode(round((sysdate-AR_PAYMENT_SCHEDULES.DUE_DATE)/30 + .5
),1,AR_PAYMENT_SCHEDULES.AMOUNT_DUE_REMAINING,0 )
Object Name: Amt 31-60 Days Past Due
decode(round((sysdate-AR_PAYMENT_SCHEDULES.DUE_DATE)/30 + .5
),2,AR_PAYMENT_SCHEDULES.AMOUNT_DUE_REMAINING,0 )
Object Name: Amt 2-3 Years Past Due
decode(round((sysdate-AR_PAYMENT_SCHEDULES.DUE_DATE)/360 + .5
),3,AR_PAYMENT_SCHEDULES.AMOUNT_DUE_REMAINING,0 )
Object Name: Amt Over 3 Years Past Due
AR_PAYMENT_SCHEDULES.AMOUNT_DUE_REMAINING
decode(round((sysdate-AR_PAYMENT_SCHEDULES.DUE_DATE)/1080 + .5
),1,AR_PAYMENT_SCHEDULES.AMOUNT_DUE_REMAINING,0)
Sincerely, Tom Slone
Lawrence Livermore National Lab
Livermore, California
Sandra wrote:
I have a basic accounting table which tracks payments made and payments due
for an account with a specific due date. There are several entries for each
account, and I am wanting to sum the balance due/paid where the
days delinquent (today - date due) falls within several ‘buckets’. Days
delinquent is a measure that I created.Example Results
Account 1 $25.00 10 days 1-30 30-60 60-90
Account 1 $25.00 20 days Account 1 $50.00 $25.00 $25.00
Account 1 $25.00 35 days
Account 1 $25.00 65 days
Listserv Archives (BOB member since 2002-06-25)