/* Set up default 50/50 payterm stages for all customers that are not already set up for multi stage invoicing
Change the Stage and StageName values as needed
Contact support@rentp.com to modify this query */
DECLARE mycursor CURSOR fast_forward FOR
SELECT c.customer_code
FROM tblcust c
WHERE c.customer_code NOT IN (SELECT customer_code
FROM tblpayterms)
DECLARE @custcode VARCHAR(30)
DECLARE @PayTermID DECIMAL(10, 0)
DECLARE @Stage1 INT
DECLARE @Stage1InvName VARCHAR(20)
DECLARE @Stage2InvName VARCHAR(20)
DECLARE @Stage2 INT
DECLARE @NumStages INT
SET @Stage1 = 50 /* percentage for stage 1 */
SET @Stage1InvName = 'Deposit' /*Name for Stage 1*/
SET @Stage2 = 50 /*percentage for stage 2 */
SET @Stage2InvName = 'Final' /*Name for Stage 2*/
SET @NumStages = 2
OPEN mycursor;
FETCH next FROM mycursor INTO @custcode
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
INSERT INTO tblpayterms
(booking_no,
customer_code,
noofstages)
VALUES ('',
@custcode,
@NumStages)
SET @PayTermID = Scope_identity()
INSERT INTO tbltermstages
(invstagename,
percentage,
stageno,
paytermid)
VALUES (@Stage1InvName,
@Stage1,
1,
@PayTermID)
INSERT INTO tbltermstages
(invstagename,
percentage,
stageno,
paytermid)
VALUES (@Stage2InvName,
@Stage2,
2,
@PayTermID)
FETCH next FROM mycursor INTO @custcode
END
CLOSE mycursor
DEALLOCATE mycursor