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