HI All,
I hope that someone can help me with what should have been a simple problem to solve, but I have been pulling out my hair now for a week.

BACKGROUND:
At each end of month, I need to insert some records into my billing table. This is based on username, planamount and plan type. Plan type is only there because it needs to exclude a certain type (bulk plan).

I had this working perfectly however, the amount that it inserted into the billing table was a static amount. Further, I had to copy, paste and alter the script for each and every plan that we have AND then when the prices chaged, update them all.

So, I tried to modify the script to be able to put in the variable into the billing table that came from the planamount column. This works only so far as it dumps the WHOLE amount into each user. What I need to do, is to isolate each planamount for each user.

MICROSOFT ACCESS.
In MS Access, I get it working everytime using this query:

INSERT INTO billtest ( username, invamnt, plan, invno, paiddate, paidamnt, [month], [year] )
SELECT userbase.username, userbase.planamount, userbase.plan, Right(Rnd([planamount]),6) AS invoiceno, Format(Date()) AS paiddate, (0) AS paidamnt, Format(Now(),"mmmm") AS [month], Format(Now(),"yyyy") AS [year]
FROM userbase
WHERE (((userbase.planamount) Is Not Null) AND ((userbase.plan)<>"bulk"));


MYSQL:
This is my current code (Which does not work and which I need help on)

#!/bin/sh
#
# End Of Month Billing Script.
#
# This Script performs end of month billing procedures.
#Obtain a list of billable users that are not BULK access plan customers from the MySQL DATABASE. and output to a temp file for processing.
mysql -s -e "SELECT * FROM userbase WHERE plan <> 'bulk'" internet|awk '{print $1}' > /tmp/.sql.user
#echo "itgroup" > /tmp/.sql.user
# Perform billing functions on each user listed in the above processing file.
for i in `cat /tmp/.sql.user` ; do
#Now we have this information, we set some variables to add into the billing table for the customers.
#This gives us a unique invoice number
INV=`/itg-scripts/generate-inv-no`
MONTH=`date +%B`
DATE=`date +%d-%m-%y`
YEAR=`date +%Y`
AMOUNT=`mysql -s -e "SELECT userbase.planamount FROM userbase WHERE username IN ('$1')" internet`
PLAN=`mysql -s -e "SELECT userbase.plan FROM userbase WHERE username IN ('$1')" internet`
#Execute SQL Syntax to update billing database
mysql -s -e "INSERT INTO billtest (username,invno,invamnt,paidamnt,month,paiddate,pl an,year) VALUES ('$i','$INV','$AMOUNT','0','$MONTH','$DATE','$PLAN ','$YEAR');" internet
done
##Clean up
rm /tmp/.sql.user


Any ideas would be greatly appreciated
thanks
Steve