Hi,
i can not use generators in this case.Because i have different types
of billtypes and for each bill type the billnumber field is there in
the database.

BILLTYPEMASTER

BILLTYPE(pk)
BILLTYPENAME
NEXTBILLNUMBER

So in my billing table also, billnumber and billtype is a primary key.
If the customer wants some new billtype also, they can create.At the time
of new billtype,
the number will start from 1.At the end of the financial year, all the
numbers will be reset to 1.
And in the billnumber field in BILLMASTER Table i am concatenating FINYEAR
(0203) also
at the time of creating the new number.
(Eg:billnumber is 0203/000001).

While doing billing,

First we are saving the temporary billnumber in the database and at
the time of saving the bill,
we inserting a new record with the new number ,
updating the details table with the new number and deleting the temp
billnumber.
Actually stored procedure is doing all these things.




SELECT OUTLETCODE, BILLREMARKS, DOCTORCODE, COSTCENTRE, DIVISION,
IPNUMBER, IPSERIALNUMBER,
OPNUMBER, PATIENTNAME, USERNAME FROM BILLMASTER WHERE BILLNUMBER =
:TEMPNO AND
BILLTYPE = :BILLTYPE INTO :OUTLETCODE, :BILLREMARKS, :DOCTORCODE,
:COSTCENTRE,
:DIVISION, :IPNUMBER, :IPSERIALNUMBER, :OPNUMBER, :PATIENTNAME, :USERNAME;
EXECUTE PROCEDURE DBPROCCREATEBILLNUMBER (BILLTYPE, MACHINEID, USERNAME,
PAYMENTCODE,
COMPANY, IDNUMBER, ACCOUNTHEAD) RETURNING_VALUES (BILLNUMBER, ACCOUNTHEAD);
INSERT INTO BILLMASTER (BILLNUMBER, BILLTYPE, AMOUNTCOLLECTED, BILLAMOUNT,
BILLBALANCE,
BILLDATE, BILLDISCOUNT, OUTLETCODE, BILLREMARKS, BILLTIME, CASHBILL,
DOCTORCODE,
COSTCENTRE, DIVISION, IPNUMBER, IPSERIALNUMBER, OPNUMBER, PATIENTNAME,
USERNAME,
MACHINEID, REBATE, CLERK) VALUES (:BILLNUMBER, :BILLTYPE, :AMOUNTCOLLECTED,
:BILLAMOUNT, :BILLBALANCE, :BILLDATE, :BILLDISCOUNT, :OUTLETCODE,
:BILLREMARKS,
:BILLTIME, :CASHBILL, :DOCTORCODE, :COSTCENTRE, :DIVISION, :IPNUMBER,
:IPSERIALNUMBER, :OPNUMBER, :PATIENTNAME, :USERNAME, :MACHINEID, :REBATE,
:CLERK);
UPDATE BILLITEMDETAILS SET BILLNUMBER = :BILLNUMBER WHERE BILLNUMBER = :TEMPNO
AND BILLTYPE = :BILLTYPE;


pROCEDURE FOR CREATING THE NEW BILL NUMBER:(DBPROCCREATENWEBILLNUMBER)

(billnumber field is a character field)

FINYEAR:='0203';
SELECT NEXTBILLNUMBER FROM BILLTYPES WHERE BILLTYPE = :BILLTYPE INTO
:NEXTBILL;
IF ((CAST(:NEXTBILL AS INTEGER) + 1) < 100000) THEN
UPDATE BILLTYPES SET NEXTBILLNUMBER = lpad((CAST(:NEXTBILL AS INTEGER)
+ 1),'0',6)
WHERE BILLTYPE = :BILLTYPE;
ELSE
UPDATE BILLTYPES SET NEXTBILLNUMBER = CAST(:NEXTBILL AS INTEGER) + 1
WHERE BILLTYPE = :BILLTYPE;
NEXTBILL = FINYEAR||'/'||NEXTBILL;
BILLNUMBER = NEXTBILL;



I am sending the details to you with this mail.
This is the very big problem we are facing.

Sivaraman