USE [ReadWorthyPublication]
GO
/****** Object:  StoredProcedure [dbo].[RW_SP_GET_PUBLICITY_DETAIL]    Script Date: 06/29/2009 15:03:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
 
 
 
CREATE PROC [dbo].[RW_SP_GET_PUBLICITY_DETAIL]
	(
		@vParty_Name VARCHAR(200) = NULL,
		@iCategory_ID INT= NULL,
		@vCity VARCHAR(100) = NULL,
		@iCountry_ID INT = NULL,
		@iSubject_ID INT = NULL,
		@iPublicity_ID INT = NULL,
		@vAddress VARCHAR(200) = NULL,
		@bActive BIT = NULL
 
	)
AS
BEGIN
	BEGIN TRY
		SELECT  PT.Publicity_ID,
				PT.Party_Name,
				LT.Lookup_ID [Publicity_Category_ID],
				LT.Lookup_Item [Publicity_Category_Type],
				CT.Country_ID,
				CT.Country_Name,
				PT.Address,
				PT.City,
				PT.Phone,
				PT.Fax,
				PT.Active,
				PT.Web_Address,
				PT.Email,
				PST.Subject_ID
 
		FROM [dbo].[Publicity_Table] PT WITH (NOLOCK)
			INNER JOIN [dbo].[Country_Table] CT WITH (NOLOCK)
			ON PT.Country_ID = CT.Country_ID
				INNER JOIN [dbo].[Lookup_Table] LT WITH (NOLOCK)
				ON PT.Category_Type_ID = LT.Lookup_ID
					INNER JOIN [dbo].[PublicitySubject_Table] PST WITH (NOLOCK)
					ON PT.Publicity_ID = PST.Publicity_ID
		WHERE ((@vParty_Name IS NULL) OR (PT.Party_Name LIKE '%'+@vParty_Name+'%'))
			AND ((@iCategory_ID IS NULL) OR (PT.Category_Type_ID = @iCategory_ID))
			AND ((@vCity IS NULL) OR (PT.City LIKE '%' + @vCity + '%'))
			AND ((@iCountry_ID IS NULL) OR (PT.Country_ID = @iCountry_ID))
 
			AND ((@iSubject_ID IS NULL) OR (PST.Subject_ID = @iSubject_ID))
			AND ((@iPublicity_ID IS NULL) OR (PT.Publicity_ID = @iPublicity_ID))
			AND ((@vAddress IS NULL) OR (PT.Address LIKE '%' + @vAddress + '%'))
			AND ((@bActive IS NULL) OR (PT.Active = @bActive))
		ORDER BY PT.Publicity_ID
 
 
	END TRY
	BEGIN CATCH
		EXEC dbo.RW_SP_GET_LOG_ERROR_INFO 'Expense_Table'
	END CATCH
END