USE [PP]
GO
/****** Object: StoredProcedure [dbo].[AgentFormList_Get] Script Date: 12/31/2012 09:32:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[AgentFormList_Get]
@AssetID int,
@AssetSID int
AS
/*
////////////////////////////////////////////////////////////////////////////////////////////////////////////
Name: AgentFormList_Get
Date:
Purpose: Returns list of active forms for specified asset
Requested by: Madhav
Author: Paul Peterson
History
Date Modified by Requested by Modification/Description
--------------------------------------------------------------------------------------------------------------
11-30-2010 Paul Kade Don't return Reimbursement Policy for Saxon & Litton
3/3/2011 Bethb Madhav A part of the Document and Image Archive Process, changed proc to look at vDocument
////////////////////////////////////////////////////////////////////////////////////////////////////////////
*/
DECLARE @Colist bit,
@ExcludeValue int,
@DocExpReim int,
@Abbr varchar(10)
SELECT @Colist = dbo.ColistRequired(@AssetID, @AssetSID)
SELECT @ExcludeValue = CASE WHEN @Colist = 1 THEN 2 ELSE 1 END
SELECT @DocExpReim = dbo.GetLookupID('DocExpReim')
SELECT @Abbr = RTRIM(Merchant.Abbr)
FROM Asset
INNER JOIN Pool ON Asset.PoolID = Pool.PoolID AND Asset.PoolSID = Pool.PoolSID
INNER JOIN Merchant on Pool.MerchantID = Merchant.MerchantID AND Pool.MerchantSID = Merchant.MerchantSID
WHERE Asset.AssetID = @AssetID AND Asset.AssetSID = @AssetSID
DECLARE @tblForms table (
AgentFormListID int,
AgentFormListSID int,
FormTypeID int,
Sequence int,
FormName varchar(50),
AcceptanceReqd bit,
DuplicatesAllowed bit,
ExternalLink varchar(50),
CreateDt datetime,
ActionDate datetime,
AcceptDecline int,
AcceptedBy varchar(50),
DocumentID int,
DocumentSID int,
AssetTaskID int,
AssetTaskSID int,
AgentFormID int,
AgentFormSID int,
TaskCode varchar(50),
Active bit,
ShowForm bit
)
INSERT INTO @tblForms
SELECT AgentFormList.AgentFormListID, AgentFormList.AgentFormListSID,
FormTypeID, Sequence, FormName, AcceptanceReqd, DuplicatesAllowed, ExternalLink,
CreateDt, ActionDate, AcceptDecline, dbo.getUserName(AgentForm.AcceptUserID,AgentForm.AcceptUserSID) AS AcceptedBy, Document.DocumentID, Document.DocumentSID, AssetTaskID, AssetTaskSID,
AgentForm.AgentFormID, AgentForm.AgentFormSID, TaskCode, AgentForm.Active, afo.ShowForm
FROM AgentFormList
LEFT JOIN AgentForm on
AgentFormList.AgentFormListID = AgentForm.AgentFormListID
AND AgentFormList.AgentFormListSID = AgentForm.AgentFormListSID
AND AgentForm.AssetID = @AssetID AND AgentForm.AssetSID = @AssetSID
LEFT JOIN AgentFormOverride afo on
AgentFormList.AgentFormListID = afo.AgentFormListID
LEFT JOIN vDocument Document ON
AgentForm.DocumentID = Document.DocumentID
AND AgentForm.DocumentSID = Document.DocumentSID
WHERE ColistFlag <> @ExcludeValue
AND (FormTypeID <> @DocExpReim OR (FormTypeID = @DocExpReim AND @Abbr NOT IN ('LIT','SXN')))
INSERT INTO @tblForms
SELECT
AgentFormListID,
AgentFormListSID,
FormTypeID,
Sequence,
FormName,
AcceptanceReqd,
DuplicatesAllowed,
ExternalLink,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
TaskCode,
1,
ShowForm
FROM @tblForms t
WHERE
t.ShowForm = 1 and
t.DuplicatesAllowed = 1 AND NOT EXISTS
(SELECT * FROM @tblForms WHERE Sequence = t.Sequence AND DocumentID IS NULL)
SELECT * FROM @tblForms
order by sequence
USE [PP]
GO
/****** Object: StoredProcedure [dbo].[AgentFormList_Get] Script Date: 12/31/2012 12:54:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[AgentFormList_Get]
@AssetID int,
@AssetSID int
AS
/*
////////////////////////////////////////////////////////////////////////////////////////////////////////////
Name: AgentFormList_Get
Date:
Purpose: Returns list of active forms for specified asset
Requested by: Madhav
Author: Paul Peterson
History
Date Modified by Requested by Modification/Description
--------------------------------------------------------------------------------------------------------------
11-30-2010 Paul Kade Don't return Reimbursement Policy for Saxon & Litton
3/3/2011 Bethb Madhav A part of the Document and Image Archive Process, changed proc to look at vDocument
////////////////////////////////////////////////////////////////////////////////////////////////////////////
*/
DECLARE @Colist bit,
@ExcludeValue int,
@DocExpReim int,
@Abbr varchar(10)
SELECT @Colist = dbo.ColistRequired(@AssetID, @AssetSID)
SELECT @ExcludeValue = CASE WHEN @Colist = 1 THEN 2 ELSE 1 END
SELECT @DocExpReim = dbo.GetLookupID('DocExpReim')
SELECT @Abbr = RTRIM(Merchant.Abbr)
FROM Asset
INNER JOIN Pool ON Asset.PoolID = Pool.PoolID AND Asset.PoolSID = Pool.PoolSID
INNER JOIN Merchant on Pool.MerchantID = Merchant.MerchantID AND Pool.MerchantSID = Merchant.MerchantSID
WHERE Asset.AssetID = @AssetID AND Asset.AssetSID = @AssetSID
DECLARE @tblForms table (
AgentFormListID int,
AgentFormListSID int,
FormTypeID int,
Sequence int,
FormName varchar(50),
AcceptanceReqd bit,
DuplicatesAllowed bit,
ExternalLink varchar(50),
CreateDt datetime,
ActionDate datetime,
AcceptDecline int,
AcceptedBy varchar(50),
DocumentID int,
DocumentSID int,
AssetTaskID int,
AssetTaskSID int,
AgentFormID int,
AgentFormSID int,
TaskCode varchar(50),
Active bit,
ShowForm bit
)
INSERT INTO @tblForms y
SELECT AgentFormList.AgentFormListID, AgentFormList.AgentFormListSID,
FormTypeID, Sequence, FormName, AcceptanceReqd, DuplicatesAllowed, ExternalLink,
CreateDt, ActionDate, AcceptDecline, dbo.getUserName(AgentForm.AcceptUserID,AgentForm.AcceptUserSID) AS AcceptedBy, Document.DocumentID, Document.DocumentSID, AssetTaskID, AssetTaskSID,
AgentForm.AgentFormID, AgentForm.AgentFormSID, TaskCode, AgentForm.Active, afo.ShowForm
FROM AgentFormList
LEFT JOIN AgentForm on
AgentFormList.AgentFormListID = AgentForm.AgentFormListID
AND AgentFormList.AgentFormListSID = AgentForm.AgentFormListSID
AND AgentForm.AssetID = @AssetID AND AgentForm.AssetSID = @AssetSID
LEFT JOIN AgentFormOverride afo on
AgentFormList.AgentFormListID = afo.AgentFormListID
LEFT JOIN vDocument Document ON
AgentForm.DocumentID = Document.DocumentID
AND AgentForm.DocumentSID = Document.DocumentSID
WHERE ColistFlag <> @ExcludeValue
AND (FormTypeID <> @DocExpReim OR (FormTypeID = @DocExpReim AND @Abbr NOT IN ('LIT','SXN')))
AND y.ShowForm <> 0
INSERT INTO @tblForms
SELECT
AgentFormListID,
AgentFormListSID,
FormTypeID,
Sequence,
FormName,
AcceptanceReqd,
DuplicatesAllowed,
ExternalLink,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
TaskCode,
1,
ShowForm
FROM @tblForms t
WHERE
t.DuplicatesAllowed = 1 AND NOT EXISTS
(SELECT * FROM @tblForms WHERE Sequence = t.Sequence AND DocumentID IS NULL)
SELECT * FROM @tblForms
order by sequence
USE [PP]
GO
/****** Object: StoredProcedure [dbo].[AgentFormList_Get] Script Date: 12/31/2012 12:54:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[AgentFormList_Get]
@AssetID int,
@AssetSID int
AS
/*
////////////////////////////////////////////////////////////////////////////////////////////////////////////
Name: AgentFormList_Get
Date:
Purpose: Returns list of active forms for specified asset
Requested by: Madhav
Author: Paul Peterson
History
Date Modified by Requested by Modification/Description
--------------------------------------------------------------------------------------------------------------
11-30-2010 Paul Kade Don't return Reimbursement Policy for Saxon & Litton
3/3/2011 Bethb Madhav A part of the Document and Image Archive Process, changed proc to look at vDocument
////////////////////////////////////////////////////////////////////////////////////////////////////////////
*/
DECLARE @Colist bit,
@ExcludeValue int,
@DocExpReim int,
@Abbr varchar(10)
SELECT @Colist = dbo.ColistRequired(@AssetID, @AssetSID)
SELECT @ExcludeValue = CASE WHEN @Colist = 1 THEN 2 ELSE 1 END
SELECT @DocExpReim = dbo.GetLookupID('DocExpReim')
SELECT @Abbr = RTRIM(Merchant.Abbr)
FROM Asset
INNER JOIN Pool ON Asset.PoolID = Pool.PoolID AND Asset.PoolSID = Pool.PoolSID
INNER JOIN Merchant on Pool.MerchantID = Merchant.MerchantID AND Pool.MerchantSID = Merchant.MerchantSID
WHERE Asset.AssetID = @AssetID AND Asset.AssetSID = @AssetSID
DECLARE @tblForms table (
AgentFormListID int,
AgentFormListSID int,
FormTypeID int,
Sequence int,
FormName varchar(50),
AcceptanceReqd bit,
DuplicatesAllowed bit,
ExternalLink varchar(50),
CreateDt datetime,
ActionDate datetime,
AcceptDecline int,
AcceptedBy varchar(50),
DocumentID int,
DocumentSID int,
AssetTaskID int,
AssetTaskSID int,
AgentFormID int,
AgentFormSID int,
TaskCode varchar(50),
Active bit,
ShowForm bit
)
INSERT INTO @tblForms y
SELECT AgentFormList.AgentFormListID, AgentFormList.AgentFormListSID,
FormTypeID, Sequence, FormName, AcceptanceReqd, DuplicatesAllowed, ExternalLink,
CreateDt, ActionDate, AcceptDecline, dbo.getUserName(AgentForm.AcceptUserID,AgentForm.AcceptUserSID) AS AcceptedBy, Document.DocumentID, Document.DocumentSID, AssetTaskID, AssetTaskSID,
AgentForm.AgentFormID, AgentForm.AgentFormSID, TaskCode, AgentForm.Active, afo.ShowForm
FROM AgentFormList
LEFT JOIN AgentForm on
AgentFormList.AgentFormListID = AgentForm.AgentFormListID
AND AgentFormList.AgentFormListSID = AgentForm.AgentFormListSID
AND AgentForm.AssetID = @AssetID AND AgentForm.AssetSID = @AssetSID
LEFT JOIN AgentFormOverride afo on
AgentFormList.AgentFormListID = afo.AgentFormListID
LEFT JOIN vDocument Document ON
AgentForm.DocumentID = Document.DocumentID
AND AgentForm.DocumentSID = Document.DocumentSID
WHERE ColistFlag <> @ExcludeValue
AND (FormTypeID <> @DocExpReim OR (FormTypeID = @DocExpReim AND @Abbr NOT IN ('LIT','SXN')))
INSERT INTO @tblForms
SELECT
AgentFormListID,
AgentFormListSID,
FormTypeID,
Sequence,
FormName,
AcceptanceReqd,
DuplicatesAllowed,
ExternalLink,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
TaskCode,
1,
ShowForm
FROM @tblForms t
WHERE
t.DuplicatesAllowed = 1 AND NOT EXISTS
(SELECT * FROM @tblForms WHERE Sequence = t.Sequence AND DocumentID IS NULL)
AND t.ShowForm <> 0
SELECT * FROM @tblForms
order by sequence
USE [PP]
GO
/****** Object: StoredProcedure [dbo].[AgentFormList_Get] Script Date: 12/31/2012 12:54:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[AgentFormList_Get]
@AssetID int,
@AssetSID int
AS
/*
////////////////////////////////////////////////////////////////////////////////////////////////////////////
Name: AgentFormList_Get
Date:
Purpose: Returns list of active forms for specified asset
Requested by: Madhav
Author: Paul Peterson
History
Date Modified by Requested by Modification/Description
--------------------------------------------------------------------------------------------------------------
11-30-2010 Paul Kade Don't return Reimbursement Policy for Saxon & Litton
3/3/2011 Bethb Madhav A part of the Document and Image Archive Process, changed proc to look at vDocument
////////////////////////////////////////////////////////////////////////////////////////////////////////////
*/
DECLARE @Colist bit,
@ExcludeValue int,
@DocExpReim int,
@Abbr varchar(10)
SELECT @Colist = dbo.ColistRequired(@AssetID, @AssetSID)
SELECT @ExcludeValue = CASE WHEN @Colist = 1 THEN 2 ELSE 1 END
SELECT @DocExpReim = dbo.GetLookupID('DocExpReim')
SELECT @Abbr = RTRIM(Merchant.Abbr)
FROM Asset
INNER JOIN Pool ON Asset.PoolID = Pool.PoolID AND Asset.PoolSID = Pool.PoolSID
INNER JOIN Merchant on Pool.MerchantID = Merchant.MerchantID AND Pool.MerchantSID = Merchant.MerchantSID
WHERE Asset.AssetID = @AssetID AND Asset.AssetSID = @AssetSID
DECLARE @tblForms table (
AgentFormListID int,
AgentFormListSID int,
FormTypeID int,
Sequence int,
FormName varchar(50),
AcceptanceReqd bit,
DuplicatesAllowed bit,
ExternalLink varchar(50),
CreateDt datetime,
ActionDate datetime,
AcceptDecline int,
AcceptedBy varchar(50),
DocumentID int,
DocumentSID int,
AssetTaskID int,
AssetTaskSID int,
AgentFormID int,
AgentFormSID int,
TaskCode varchar(50),
Active bit,
ShowForm bit
)
INSERT INTO @tblForms
SELECT AgentFormList.AgentFormListID, AgentFormList.AgentFormListSID,
FormTypeID, Sequence, FormName, AcceptanceReqd, DuplicatesAllowed, ExternalLink,
CreateDt, ActionDate, AcceptDecline, dbo.getUserName(AgentForm.AcceptUserID,AgentForm.AcceptUserSID) AS AcceptedBy, Document.DocumentID, Document.DocumentSID, AssetTaskID, AssetTaskSID,
AgentForm.AgentFormID, AgentForm.AgentFormSID, TaskCode, AgentForm.Active, afo.ShowForm
FROM AgentFormList
LEFT JOIN AgentForm on
AgentFormList.AgentFormListID = AgentForm.AgentFormListID
AND AgentFormList.AgentFormListSID = AgentForm.AgentFormListSID
AND AgentForm.AssetID = @AssetID AND AgentForm.AssetSID = @AssetSID
LEFT JOIN AgentFormOverride afo on
AgentFormList.AgentFormListID = afo.AgentFormListID
LEFT JOIN vDocument Document ON
AgentForm.DocumentID = Document.DocumentID
AND AgentForm.DocumentSID = Document.DocumentSID
WHERE ColistFlag <> @ExcludeValue
AND (FormTypeID <> @DocExpReim OR (FormTypeID = @DocExpReim AND @Abbr NOT IN ('LIT','SXN')))
AND afo.ShowForm <> 0
SELECT * FROM @tblForms
order by sequence
INSERT INTO @tblForms
SELECT
AgentFormListID,
AgentFormListSID,
FormTypeID,
Sequence,
FormName,
AcceptanceReqd,
DuplicatesAllowed,
ExternalLink,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
TaskCode,
1,
ShowForm
FROM @tblForms t
WHERE
t.DuplicatesAllowed = 1 AND NOT EXISTS
(SELECT * FROM @tblForms WHERE Sequence = t.Sequence AND DocumentID IS NULL)
SELECT * FROM @tblForms
order by sequence
USE [PP]
GO
/****** Object: StoredProcedure [dbo].[TomTest] Script Date: 01/02/2013 11:14:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[TomTest]
@AssetID int,
@AssetSID int
AS
/*
////////////////////////////////////////////////////////////////////////////////////////////////////////////
Name: AgentFormList_Get
Date:
Purpose: Returns list of active forms for specified asset
Requested by: Madhav
Author: Paul Peterson
History
Date Modified by Requested by Modification/Description
--------------------------------------------------------------------------------------------------------------
11-30-2010 Paul Kade Don't return Reimbursement Policy for Saxon & Litton
3/3/2011 Bethb Madhav A part of the Document and Image Archive Process, changed proc to look at vDocument
////////////////////////////////////////////////////////////////////////////////////////////////////////////
*/
DECLARE @Colist bit,
@ExcludeValue int,
@DocExpReim int,
@Abbr varchar(10)
SELECT @Colist = dbo.ColistRequired(@AssetID, @AssetSID)
SELECT @ExcludeValue = CASE WHEN @Colist = 1 THEN 2 ELSE 1 END
SELECT @DocExpReim = dbo.GetLookupID('DocExpReim')
SELECT @Abbr = RTRIM(Merchant.Abbr)
FROM Asset
INNER JOIN Pool ON Asset.PoolID = Pool.PoolID AND Asset.PoolSID = Pool.PoolSID
INNER JOIN Merchant on Pool.MerchantID = Merchant.MerchantID AND Pool.MerchantSID = Merchant.MerchantSID
WHERE Asset.AssetID = @AssetID AND Asset.AssetSID = @AssetSID
DECLARE @tblForms table (
AgentFormListID int,
AgentFormListSID int,
FormTypeID int,
Sequence int,
FormName varchar(50),
AcceptanceReqd bit,
DuplicatesAllowed bit,
ExternalLink varchar(50),
CreateDt datetime,
ActionDate datetime,
AcceptDecline int,
AcceptedBy varchar(50),
DocumentID int,
DocumentSID int,
AssetTaskID int,
AssetTaskSID int,
AgentFormID int,
AgentFormSID int,
TaskCode varchar(50),
Active bit,
ShowForm bit
)
INSERT INTO @tblForms
SELECT AgentFormList.AgentFormListID, AgentFormList.AgentFormListSID,
FormTypeID, Sequence, FormName, AcceptanceReqd, DuplicatesAllowed, ExternalLink,
CreateDt, ActionDate, AcceptDecline, dbo.getUserName(AgentForm.AcceptUserID,AgentForm.AcceptUserSID) AS AcceptedBy, Document.DocumentID, Document.DocumentSID, AssetTaskID, AssetTaskSID,
AgentForm.AgentFormID, AgentForm.AgentFormSID, TaskCode, AgentForm.Active, afo.ShowForm
FROM AgentFormList
LEFT JOIN AgentForm on
AgentFormList.AgentFormListID = AgentForm.AgentFormListID
AND AgentFormList.AgentFormListSID = AgentForm.AgentFormListSID
AND AgentForm.AssetID = @AssetID AND AgentForm.AssetSID = @AssetSID
LEFT JOIN AgentFormOverride afo on
AgentFormList.AgentFormListID = afo.AgentFormListID
LEFT JOIN vDocument Document ON
AgentForm.DocumentID = Document.DocumentID
AND AgentForm.DocumentSID = Document.DocumentSID
WHERE ColistFlag <> @ExcludeValue
AND (FormTypeID <> @DocExpReim OR (FormTypeID = @DocExpReim AND @Abbr NOT IN ('LIT','SXN')))
SELECT * FROM @tblForms
order by sequence
INSERT INTO @tblForms
SELECT
AgentFormListID,
AgentFormListSID,
FormTypeID,
Sequence,
FormName,
AcceptanceReqd,
DuplicatesAllowed,
ExternalLink,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
TaskCode,
1,
ShowForm
FROM @tblForms t
WHERE
t.DuplicatesAllowed = 1 AND NOT EXISTS
(SELECT * FROM @tblForms WHERE Sequence = t.Sequence AND DocumentID IS NULL)
SELECT * FROM @tblForms
order by sequence