July 23, 2007 at 11:50 am
Hi! I'm trying to write a script that will send a notification when an order is placed. I can't use a trigger because the order details must be inserted as well. Also, I can't rely on the maximum order ID because there could be concurrency issues there and I may skip over an order. The script I have now does not deal with ensuring all details are inserted, so that is a big question mark. The other problem I am having is it is no longer working now that I have the while loop in there. If I set the order ID = max order ID in the order_master table, this script works. However, if I try to find the lowest order id that does not have it's emailsent flag set, it bombs. Any help would be appreciated.
~ Cathy
declare @body1 varchar (200)
declare @body2 varchar (1000)
declare @body3 varchar(4000)
declare @fullbody varchar(8000)
declare @subject1 varchar (500)
declare @orderid int
declare @siteid varchar(20)
declare @reqemail varchar (50)
declare @countCC int
declare @ccemail varchar (500)
declare @recipients varchar (700)
declare @iscustom int
declare @ApprReq int
declare @Avail int
while exists (select min(orderid) from Order_Master where EmailSent = 0)
begin
set @orderid = (select min(orderid) from Order_Master where EmailSent = 0)
SET @siteid = (select RNWID from Order_Master where OrderID = @orderid)
set @iscustom = case
when (select count (*) from CustomOrder_Detail where OrderID = @orderid) >= 1 then 1
else 0 end
set @ApprReq = case
when @iscustom = 1 then 1
when (select count (*) from StandardOrder_Detail where OrderID = @orderid and ApprovalFlag = 0) >= 1 then 1
else 0 end
set @Avail = (select sum(case when c.shipdate = null or shipdate >= convert (varchar(11), getdate(), 101)
then 1 else 0 end)
from StandardOrder_Detail a, ReleaseProdType b, ProdRelease c
where a.OrderID = @orderid
and a.RelProdTypID = b.RelProdTypID
and b.ProdRelID = c.ProdRelID)
SET @Subject1 = 'TEST, PLEASE IGNORE...Order ID: ' + cast(@orderid as varchar(10))+ ' has been received'
set @body1 = 'Dear ' + (select FirstName from AuthUser a, Order_Master b
where a.UserID=b.RequesterID
and b.orderid = @orderid) + ', <br><br>'
if @iscustom = 0
begin
if @ApprReq = 0 and @Avail = 1
begin
set @body2 = 'Your request,' + cast(@orderid as varchar(10))+ ', for Site ID '+ @siteid + ', has been received.
Please review the order below and contact Release Management as soon as possible if there are
any issues with the order submittal.<br><br>Your order will be forwarded to shipping within one business day of receipt.
<br><br>Thank you, <br><br> Release Management<br><br>'
set @body3 = '<table border = "2" cellspacing="2" cellpadding="2">
<TR><th>Quantity</th><th>Release</th><th>Media Type</th><th>Order Date</th><th>Comments</th><td colspan="2"
style="color: #A0522D; font-family: Verdana; font-size: 10;"
align=center
</TD></TR>'
select @body3 = @body3 +
'<TR><TD>' + cast (a.Quantity as varchar(40))+'<TD>'+c.ProdRelName +'<TD>'+
d.ProductType +'<TD>' + cast(e.OrderDate as varchar (11)) + '<TD>' +
f.Comment+'</TD></TR>'
from StandardOrder_Detail a, ReleaseProdType b, ProdRelease c, ProductType_Master d, Order_Master e, OrderComments f
where a.OrderId = @orderid
and a.RelProdTypID = b.RelProdTypID
and b.ProdRelID = c.ProdRelID
and b.ProdTypeID = d.ProdTypeID
and e.OrderID = a.OrderID
and e.OrderID = f.OrderID
end
if @ApprReq = 0 and @Avail = 0
begin
set @body2 = 'Your request,' + cast(@orderid as varchar(10))+ ', for Site ID '+ @siteid + ', has been received.
One or more items in your request are not available for shipment at this time.
The order will not be forwarded to shipping until the unavailable items become available.
Please review the order below and make sure it is accurate. If you have any problems
with the order, please contact Release Management immediately.<br><br>As soon as all items are available and have shipped, you will receive a notification
indicating the date of shipment and shipping information.
<br><br>Thank you, <br><br> Release Management<br><br>'
set @body3 = '<table border = "2" cellspacing="2" cellpadding="2">
<TR><th>Quantity</th><th>Release</th><th>Media Type</th><th>Order Date</th><thAnticipated Ship Date</th><th>Comments</th><td colspan="2"
style="color: #A0522D; font-family: Verdana; font-size: 10;"
align=center
</TD></TR>'
select @body3 = @body3 +
'<TR><TD>' + cast (a.Quantity as varchar(40))+'<TD>'+c.ProdRelName +'<TD>'+
d.ProductType +'<TD>' + cast(e.OrderDate as varchar (11)) + '<TD>' +
case when c.ShipDate = null then 'TBD' else cast (c.ShipDate as varchar(11)) end
+ '<TD>' + f.Comment+'</TD></TR>'
from StandardOrder_Detail a, ReleaseProdType b, ProdRelease c, ProductType_Master d, Order_Master e, OrderComments f
where a.OrderId = @orderid
and a.RelProdTypID = b.RelProdTypID
and b.ProdRelID = c.ProdRelID
and b.ProdTypeID = d.ProdTypeID
and e.OrderID = a.OrderID
and e.OrderID = f.OrderID
end
if @ApprReq = 1 and @Avail = 1
begin
set @body2 = 'Your request,' + cast(@orderid as varchar(10))+ ', for Site ID '+ @siteid + ', has been received.
One or more items in your request require approval. The order will not be forwarded to
shipping until all items requiring approval have been reviewed and a determination has been made.
<br><br>Please review the order below and make sure it is accurate. If you have any problems with the order, please contact Release Management immediately.
<br><br>As soon as all items have been reviewed, you will receive a notification indicating the status of each item.
<br><br>Thank you, <br><br> Release Management<br><br>'
set @body3 = '<table border = "2" cellspacing="2" cellpadding="2">
<TR><th>Quantity</th><th>Release</th><th>Media Type</th><th>Order Date</th><th>Comments</th><th>Approval Required</th><td colspan="2"
style="color: #A0522D; font-family: Verdana; font-size: 10;"
align=center
</TD></TR>'
select @body3 = @body3 +
'<TR><TD>' + cast (a.Quantity as varchar(40))+'<TD>'+c.ProdRelName +'<TD>'+
d.ProductType +'<TD>' + cast(e.OrderDate as varchar (11)) + '<TD>' +
f.Comment + '<TD>'+ case when ApprovalFlag = 1 then 'Yes' else 'No' end + '</TD></TR>'
from StandardOrder_Detail a, ReleaseProdType b, ProdRelease c, ProductType_Master d, Order_Master e, OrderComments f
where a.OrderId = @orderid
and a.RelProdTypID = b.RelProdTypID
and b.ProdRelID = c.ProdRelID
and b.ProdTypeID = d.ProdTypeID
and e.OrderID = a.OrderID
and e.OrderID = f.OrderID
end
if @ApprReq = 1 and @Avail = 0
begin
set @body2 = 'Your request,' + cast(@orderid as varchar(10))+ ', for Site ID '+ @siteid + ', has been received.
One or more items in your request require approval and one or more items in your
request are not available for shipment at this time. The order will
not be forwarded to shipping until the unavailable items become available
and until all items requiring approval have been reviewed. <br><br>Please
review the order below and make sure it is accurate. If you have any
problems with the order, please contact Release Management immediately.
<br><br>As soon as all items have been reviewed, you will receive a
notification indicating the status of each item.
<br><br>Thank you, <br><br> Release Management<br><br>'
set @body3 = '<table border = "2" cellspacing="2" cellpadding="2">
<TR><th>Quantity</th><th>Release</th><th>Media Type</th><th>Order Date</th><th>Comments</th><th>Approval Required</th><td colspan="2"
style="color: #A0522D; font-family: Verdana; font-size: 10;"
align=center
</TD></TR>'
select @body3 = @body3 +
'<TR><TD>' + cast (a.Quantity as varchar(40))+'<TD>'+c.ProdRelName +'<TD>'+
d.ProductType +'<TD>' + cast(e.OrderDate as varchar (11)) + '<TD>' +
f.Comment + '<TD>'+ case when ApprovalFlag = 1 then 'Yes' else 'No' end + '</TD></TR>'
from StandardOrder_Detail a, ReleaseProdType b, ProdRelease c, ProductType_Master d, Order_Master e, OrderComments f
where a.OrderId = @orderid
and a.RelProdTypID = b.RelProdTypID
and b.ProdRelID = c.ProdRelID
and b.ProdTypeID = d.ProdTypeID
and e.OrderID = a.OrderID
and e.OrderID = f.OrderID
end
if @iscustom = 1
begin
set @body2 = 'Your request,' + cast(@orderid as varchar(10))+ ', for Site ID '+ @siteid + ', has been received.
All custom requests require approval. Once the request has been approved, it will be
forwarded to shipping.<br><br>Please review the order below and make sure
it is accurate. If you have any problems with the order, please contact
Release Management immediately.
<br><br>As soon as all items have been reviewed, you will receive a
notification indicating the status of each item.
<br><br>Thank you, <br><br> Release Management<br><br>'
set @body3 = '<table border = "2" cellspacing="2" cellpadding="2">
<TR><th>Quantity</th><th>Release</th><th>Media Type</th><th>Order Date</th><th>Comments</th><th>Approval Required</th><td colspan="2"
style="color: #A0522D; font-family: Verdana; font-size: 10;"
align=center
</TD></TR>'
select @body3 = @body3 +
'<TR><TD>' + cast (a.Quantity as varchar(40))+'<TD>'+g.Product + ' ' + h.DisplayName +'<TD>'+
d.ProductType +'<TD>' + cast(e.OrderDate as varchar (11)) + '<TD>' +
f.Comment + '<TD>'+ case when ApprovalFlag = 1 then 'Yes' else 'No' end + '</TD></TR>'
from CustomOrder_Detail a, ProdBranType b, ProductBranch c, ProductType_Master d,
Order_Master e, OrderComments f, Product_Master g, tBranches h
where a.OrderID = @orderid
and a.ProdBranTypID = b.ProdBranTypeID
and b.ProdBranchID = c.ProdBranchID
and b.ProdTypeID = d.ProdTypeID
and e.OrderID = a.OrderID
and e.OrderID = f.OrderID
and c.PartNumber = g.PartNumber
and c.BranchID = h.BranchID
END
END
set @ReqEmail = (select RequesterEmail from Order_Master
where orderid = @orderid)
set @countCC = (select count (CCEmail) from Order_Master
where orderid = @orderid and CCEmail is not null and CCEmail <> '')
if @countCC <> 0
begin
set @ccemail = (select CCEmail from Order_Master
where orderid = @orderid )
end
set @recipients = @ReqEmail + ';' + 'catherine.depaolo@3ds.com'
set @fullbody = @body1 + @body2 + @body3
exec sp_send_cdosysmail_OrderNotifications
@To=@recipients,
@cc=@ccemail,
@subject = @subject1,
@body = @fullbody
update Order_Master
set EmailSent = 1
where orderid = @orderid and EmailSent = 0
end
July 23, 2007 at 12:02 pm
One more thing...I posted this in the wrong area. I'm working with SS2K, not SS2K5. From a syntax perspective, this won't matter, but it probably will if we are talking about features. I'll post this in the appropriate forum as well.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply