Order Submittal Notification Assistance Request
-
I originally posted this in the SS2K5 forum for T-SQL assistance requests.
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
-
can you post your tables ddl?
if you have a column in your Order Header table that contains a count of order detail lines, you could use a trigger on the order Details tables that checks to see how many of them have been inserted into the table already.
but I would suggest using a job that goes through your table sending the emails based on the flag.
-
Hi. I'm pasting the ddl for the tables that are required within this script. One thing I should add, which your suggestion reminded me of, is the application is actually storing the pre-orders (we're really talking about pre-orders, not true orders) in temp tables. I could run a check against those to see if all lines finished inserting...
CREATE TABLE [Order_Master] (
[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
[RequesterID] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RequesterFN] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RequesterLN] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RequesterEmail] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RNWID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RefID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrderStatID] [int] NULL CONSTRAINT [DF__Order_Mas__Order__3E1D39E1] DEFAULT (1),
[CustStatID] [int] NOT NULL ,
[OrderDate] [datetime] NOT NULL ,
[ShipLocalOffice] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__Order_Mas__ShipL__3F115E1A] DEFAULT ('Y'),
[ShipMethodID] [int] NOT NULL ,
[Waybill] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IsAutoPushList] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__Order_Mas__IsAut__40058253] DEFAULT ('0'),
[LastChanged] [datetime] NULL CONSTRAINT [DF__Order_Mas__LastC__40F9A68C] DEFAULT (getdate()),
[ArchiveDate] [datetime] NULL ,
[OrigOrderID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmailSent] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__Order_Mas__Email__1F2E9E6D] DEFAULT ('0'),
[CCEmail] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [OrderMaster_PK] PRIMARY KEY CLUSTERED
(
[OrderID]
 
WITH FILLFACTOR = 90 ON [PRIMARY] ,CONSTRAINT [FK_Order_Master_AuthUser] FOREIGN KEY
(
[RequesterID]
 
REFERENCES [AuthUser] ([UserID]
 
) ON [PRIMARY]
GO
---------if standard-----------
CREATE TABLE [StandardOrder_Detail] (
[StdOrdDetID] [int] IDENTITY (1, 1) NOT NULL ,
[OrderID] [int] NOT NULL ,
[RelProdTypID] [int] NOT NULL ,
[Quantity] [int] NOT NULL ,
[ApprovalFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LicenseAgreement] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Reason] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateAdded] [datetime] NOT NULL CONSTRAINT [DF__StandardO__DateA__6A85CC04] DEFAULT (getdate()),
[LastChanged] [datetime] NOT NULL CONSTRAINT [DF_StandardOrder_Detail_LastChanged] DEFAULT (getdate()),
[ArchiveDate] [datetime] NULL ,
CONSTRAINT [StdOrdDet_PK] PRIMARY KEY CLUSTERED
(
[StdOrdDetID]
 
WITH FILLFACTOR = 90 ON [PRIMARY] ,CONSTRAINT [OrderMaster_FK1] FOREIGN KEY
(
[OrderID]
 
REFERENCES [Order_Master] ([OrderID]
 
) ON [PRIMARY]
GO
CREATE TABLE [ReleaseProdType] (
[RelProdTypID] [int] IDENTITY (1, 1) NOT NULL ,
[ProdRelID] [int] NOT NULL ,
[ProdTypeID] [int] NOT NULL ,
[LastChanged] [datetime] NOT NULL CONSTRAINT [DF__ReleasePr__LastC__473C8FC7] DEFAULT (getdate()),
[ArchiveDate] [datetime] NULL ,
CONSTRAINT [PK_ReleaseProdType] PRIMARY KEY CLUSTERED
(
[RelProdTypID]
 
WITH FILLFACTOR = 90 ON [PRIMARY] ,CONSTRAINT [ProdRel_FK1] FOREIGN KEY
(
[ProdRelID]
 
REFERENCES [ProdRelease] ([ProdRelID]
 
,CONSTRAINT [ProdType_FK1] FOREIGN KEY
(
[ProdTypeID]
 
REFERENCES [ProductType_Master] ([ProdTypeID]
 
) ON [PRIMARY]
GO
CREATE TABLE [ProductType_Master] (
[ProdTypeID] [int] IDENTITY (1, 1) NOT NULL ,
[ProductType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ProdTypeDescription] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastChanged] [datetime] NULL CONSTRAINT [DF__ProductTy__LastC__719CDDE7] DEFAULT (getdate()),
[ArchiveDate] [datetime] NULL CONSTRAINT [DF__ProductTy__Archi__72910220] DEFAULT (null),
CONSTRAINT [ProdTypeID_PK] PRIMARY KEY CLUSTERED
(
[ProdTypeID]
 
ON [PRIMARY]) ON [PRIMARY]
GO
CREATE TABLE [ProdRelease] (
[ProdRelID] [int] IDENTITY (1, 1) NOT NULL ,
[ProdVerID] [int] NOT NULL ,
[ReleaseID] [int] NOT NULL ,
[ReleaseTypeID] [int] NOT NULL ,
[ProdRelName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ImportantNotes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ReleaseStatusID] [int] NOT NULL CONSTRAINT [DF__ProdRelea__Relea__2610A626] DEFAULT (1),
[CustomerReleaseID] [int] NULL ,
[ShipDate] [datetime] NULL ,
[Snapshot] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FreezeDate] [datetime] NULL ,
[LastChanged] [datetime] NOT NULL CONSTRAINT [DF_ProdRelease_LastChanged] DEFAULT (getdate()),
[ArchiveDate] [datetime] NULL ,
[ReleaseSort] [int] NOT NULL CONSTRAINT [DF_ProdRelease_ReleaseSort] DEFAULT (0),
[ApprovalReq] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__ProdRelea__Appro__1E3A7A34] DEFAULT ('0'),
[LicAgreeReq] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__prodrelea__LicAg__795DFB40] DEFAULT ('N'),
CONSTRAINT [ProdRelID_PK] PRIMARY KEY CLUSTERED
(
[ProdRelID]
 
WITH FILLFACTOR = 90 ON [PRIMARY] ,CONSTRAINT [ProdVerID_FK1] FOREIGN KEY
(
[ProdVerID]
 
REFERENCES [ProdVersion] ([ProdVerID]
 
,CONSTRAINT [ReleaseID_FK1] FOREIGN KEY
(
[ReleaseID]
 
REFERENCES [Release_Master] ([ReleaseID]
 
,CONSTRAINT [ReleaseTypeID_FK1] FOREIGN KEY
(
[ReleaseTypeID]
 
REFERENCES [ReleaseType_Master] ([ReleaseTypeID]
 
,CONSTRAINT [RelStatID_FK1] FOREIGN KEY
(
[ReleaseStatusID]
 
REFERENCES [ReleaseStatus_Master] ([ReleaseStatusID]
 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [ProdVersion] (
[ProdVerID] [int] IDENTITY (1, 1) NOT NULL ,
[VersionTypeID] [int] NOT NULL ,
[PartNumber] [int] NULL ,
[VersionID] [int] NULL ,
[PublishedDate] [datetime] NULL ,
[qmsDisplay] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__ProdVersi__qmsDi__15DA3E5D] DEFAULT ('1'),
[IsMidCycle] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__ProdVersi__IsMid__16CE6296] DEFAULT ('0'),
[IsDisplay] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__ProdVersi__IsDis__17C286CF] DEFAULT ('1'),
[TrackBackVersions] [int] NOT NULL CONSTRAINT [DF_ProdVersion_TrackBackVersions] DEFAULT (0),
[VersionSort] [int] NOT NULL CONSTRAINT [DF_ProdVersion_VersionSort] DEFAULT (999999999),
[LastChanged] [datetime] NULL CONSTRAINT [DF__ProdVersi__LastC__18B6AB08] DEFAULT (getdate()),
[ArchiveDate] [datetime] NULL ,
[VerDescription] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [ProdVerID_PK] PRIMARY KEY CLUSTERED
(
[ProdVerID]
 
WITH FILLFACTOR = 90 ON [PRIMARY] ,CONSTRAINT [PartNumber_FK] FOREIGN KEY
(
[PartNumber]
 
REFERENCES [Product_Master] ([PartNumber]
 
,CONSTRAINT [VersionID_FK1] FOREIGN KEY
(
[VersionID]
 
REFERENCES [Version_Master] ([VersionID]
 
ON DELETE CASCADE ON UPDATE CASCADE ,CONSTRAINT [VersionTypeID_FK] FOREIGN KEY
(
[VersionTypeID]
 
REFERENCES [VersionType_Master] ([VersionTypeID]
 
) ON [PRIMARY]
GO
CREATE TABLE [Product_Master] (
[PartNumber] [int] IDENTITY (1, 1) NOT NULL ,
[Product] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ProdDescription] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Extra] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastChanged] [datetime] NULL CONSTRAINT [DF__Product_M__LastC__37703C52] DEFAULT (getdate()),
[ArchiveDate] [datetime] NULL ,
CONSTRAINT [PartNumber_PK] PRIMARY KEY CLUSTERED
(
[PartNumber]
 
ON [PRIMARY]) ON [PRIMARY]
GO
-----if custom--------------------------------
CREATE TABLE [CustomOrder_Detail] (
[SpecOrdDetID] [int] IDENTITY (1, 1) NOT NULL ,
[OrderID] [int] NOT NULL ,
[ProdBranTypID] [int] NOT NULL ,
[Quantity] [int] NOT NULL ,
[ApprovalFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Snapshot] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Reason] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateAdded] [datetime] NOT NULL CONSTRAINT [DF__CustomOrd__DateA__6B79F03D] DEFAULT (getdate()),
[LastChanged] [datetime] NOT NULL CONSTRAINT [DF_CustomOrder_Detail_LastChanged] DEFAULT (getdate()),
[ArchiveDate] [datetime] NULL ,
CONSTRAINT [SpecOrdDet_PK] PRIMARY KEY CLUSTERED
(
[SpecOrdDetID]
 
ON [PRIMARY] ,CONSTRAINT [OrderMaster_FK2] FOREIGN KEY
(
[OrderID]
 
REFERENCES [Order_Master] ([OrderID]
 
) ON [PRIMARY]
GO
CREATE TABLE [ProdBranType] (
[ProdBranTypeID] [int] IDENTITY (1, 1) NOT NULL ,
[ProdBranchID] [int] NOT NULL ,
[ProdTypeID] [int] NOT NULL ,
[LastChanged] [datetime] NOT NULL CONSTRAINT [DF__ProdBranT__LastC__61F08603] DEFAULT (getdate()),
[ArchiveDate] [datetime] NULL ,
CONSTRAINT [ProdBranType_PK] PRIMARY KEY CLUSTERED
(
[ProdBranTypeID]
 
ON [PRIMARY] ,CONSTRAINT [ProdBranch_FK1] FOREIGN KEY
(
[ProdBranchID]
 
REFERENCES [ProductBranch] ([ProdBranchID]
 
,CONSTRAINT [ProdType_FK2] FOREIGN KEY
(
[ProdTypeID]
 
REFERENCES [ProductType_Master] ([ProdTypeID]
 
) ON [PRIMARY]
GO
CREATE TABLE [ProductBranch] (
[ProdBranchID] [int] IDENTITY (1, 1) NOT NULL ,
[PartNumber] [int] NOT NULL ,
[BranchID] [int] NOT NULL ,
[ArchiveDate] [datetime] NULL ,
[LastChanged] [datetime] NOT NULL CONSTRAINT [DF__ProductBr__LastC__2EDAF651] DEFAULT (getdate()),
CONSTRAINT [ProdBranchID_PK] PRIMARY KEY CLUSTERED
(
[ProdBranchID]
 
WITH FILLFACTOR = 90 ON [PRIMARY] ,CONSTRAINT [FK_ProductBranch_Product_Master] FOREIGN KEY
(
[PartNumber]
 
REFERENCES [Product_Master] ([PartNumber]
 
,CONSTRAINT [FK_ProductBranch_tBranches] FOREIGN KEY
(
[BranchID]
 
REFERENCES [tBranches] ([BranchID]
 
) ON [PRIMARY]
GO
CREATE TABLE [tBranches] (
[BranchID] [int] IDENTITY (1, 1) NOT NULL ,
[BranchTypeID] [int] NOT NULL ,
[BranchName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DisplayName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BranchDesc] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ArchiveDate] [datetime] NULL ,
[LastChanged] [datetime] NULL CONSTRAINT [DF__tbranches__LastC__5629CD9C] DEFAULT (getdate()),
CONSTRAINT [BranchID_PK] PRIMARY KEY CLUSTERED
(
[BranchID]
 
WITH FILLFACTOR = 90 ON [PRIMARY] ,CONSTRAINT [FK_tBranches_BranchType_Master] FOREIGN KEY
(
[BranchTypeID]
 
REFERENCES [BranchType_Master] ([BranchTypeID]
 
) ON [PRIMARY]
GO
----------if comments added to order----------
CREATE TABLE [OrderComments] (
[CommentID] [int] IDENTITY (1, 1) NOT NULL ,
[OrderID] [int] NOT NULL ,
[Comment] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CommentDate] [datetime] NOT NULL ,
[ArchiveDate] [datetime] NULL ,
[UpdatedBy] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__ordercomm__Updat__7CD98669] DEFAULT (''),
CONSTRAINT [CommentID_PK] PRIMARY KEY CLUSTERED
(
[CommentID]
 
ON [PRIMARY] ,CONSTRAINT [OrderMaster_FK3] FOREIGN KEY
(
[OrderID]
 
REFERENCES [Order_Master] ([OrderID]
 
) ON [PRIMARY]
GO
-
Can anyone help with this? I would appreciate any suggestions you may have. I am at a loss as far as how to get this to work.
Maybe I should set the orderid as a parameter and have the application pass the orderid to the stored procedure?
I know that I didn't set the order id correctly. If I set it to 0 as I did, then my while statement is always true I guess. I thought that within the loop, the orderid would eventually get set to null when there were no longer orders with an emailsent flag of 0, but maybe that is not the case.
All other code within teh loop works. Initially, I set the orderid to the max(orderid) in the order_master table. The notifications were all sent out and sent out accurately. I realized at the end that using the max order id was not going to work. I would need to find all orders that haven't received a notification and then send the notification out. This is where the while loop came from, as well as the orderid initialization.
-
Cathy
,
when
you say that the code
[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.]
are you saying that its giving you an error or it not executing as expected
can you try
select top 1 orderid
from Order_Master
where EmailSent = 0
order by orderid asc
-
Bledu,
I can run the select and get the expected result. Let me explain a little more. I began by trying to send a notification for just the max order id. This won't work if I miss an order that was submitted while the procedure is running. So, I was trying to figure out the best way to identify when the best time would be to send a notification. I can't use a trigger for two reasons:
- The notification can't be sent until all details are inserted into the standardorder_detail table.
- The trigger will produce locks when different orders are submitted simultaneously.
My proposed solution was to find all orders where emailsent = 0, then loop through this set until all emailsent flags were set to 1. This is why I begin with the min orderid. I have 2 test case orders, orderid 7456 and 7457. I expect 2 notifications to be triggered. Instead, the sp runs and doesn't stop. I have to kill it. The output I receive is:
-2147220980
(1 row(s) affected)
Source: CDO.Message.1
Description: At least one recipient is required, but none were found.
~Cathy
-
Bledu,
Thank you, thank you, thank you. It worked. Really...I cannot thank you enough. I have been wracking my brain for almost 2 days. My final code is:
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
SELECT @orderid = NULL
SELECT @orderid = MIN(orderid) FROM Order_Master WHERE EmailSent = 0
while @orderid is not null
begin
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
SET @orderid = null
SELECT @orderid = MIN(orderid) FROM Order_Master WHERE EmailSent = 0
end
-
Thank you, Bledu. I actually did add that column earlier today...based on your earlier suggestion I think. The developer I am working with does not want to populate that column though. I'm in the process of going back and forth with her on this.
~Cathy
-
You could update the column in the order header with the count of line items using a trigger on the detail table. As the trigger is updating a single row with the current count of line items, it should execute very quickly, and then the developer doesn't have to populate the field.
-
What could I use to find out when the line inserts are complete? Would I loop through the detail table and when the orderid changes, the loop ends?
-
First, it looks like you are thinking procedurally. There is no need to loop through anything, nor to actually wait for all the line items to be inserted. Here is the start of how i would write the trigger. It may require some work, but it is a start:
create trigger dbo.trgOrderLineCountIns on dbo.OrderLine
after insert
as
begin
update dbo.OrderHeader set
LineItemCount = count(*)
from
dbo.OrderHeader oh
inner join inserted ins
on (oh.OrderNumber = ins.OrderNumber)
group by
oh.OrderNumber
end
-
Lynn,
Reading my question, I realize I was asking a question that belonged with my other forum question regarding sending notifications. I was trying to figure out a way to know when the insert was complete so I could send a notification out with all of the order details.
As for your trigger suggestion, it totally makes sense. Thank you for the head start.
btw, this question will definitely highlight my ignorance, but I guess that can't be helped. I do think procedurally most, if not all, of the time. If I should not be thinking procedurally, how should I be thinking? This may be the root cause of many of the limitations I think that I have.
~Cathy
-
When working with SQL, you should try thinking in a set-based fashion. Thinking procedurally you will end up with a lot of scripts/stored procedures/et al that probably make a lot of use of cursors. Set-based queries and procedures will almost always work faster than cursor based solutions. This is not to say you should never use cursors, just be careful of when you use them. I have had found that there are occasions that they make sense to use, but I have not used them with much frequency.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply