USE [LegacyShieldStagingDB_New]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_Get_Client_List_By_ID]
    -- Add the parameters for the stored procedure here
    @User_ID nvarchar(MAX) = NULL
AS



BEGIN
    SELECT   [tbl_Advisor_User_Master].[User_ID]
        ,[tbl_Advisor_User_Master].[Email]
        ,[tbl_Advisor_User_Master].[FName]
        ,[tbl_Advisor_User_Master].[MName]
        ,[tbl_Advisor_User_Master].[LName]
        ,[tbl_Advisor_User_Master].[AdvisorUserName]
        ,[tbl_Advisor_User_Master].[AlternateEmail]
        ,[tbl_Advisor_User_Master].[Address1]
        ,[tbl_Advisor_User_Master].[Address2]
        ,[tbl_Advisor_User_Master].[City]
        ,[tbl_Advisor_User_Master].[StateID]
        ,[tbl_Advisor_User_Master].[CountryID]
        ,[tbl_Advisor_User_Master].[Zip]
        ,[tbl_Advisor_User_Master].[Phone_Home]
        ,[tbl_Advisor_User_Master].[Phone_Work]
        ,[tbl_Advisor_User_Master].[Mobile]
        ,[tbl_Advisor_User_Master].[Security_Question_ID]
        ,[tbl_Advisor_User_Master].[Security_Question_Ans]
        ,[tbl_Advisor_User_Master].[IsSubscriptionAgreement]
        ,[tbl_Advisor_User_Master].[IsSponsorsMailReceived]
        ,[tbl_Advisor_User_Master].[IsMailReceived]
        ,[tbl_Advisor_User_Master].[PhotoName]
        ,[tbl_Advisor_User_Master].[PhotoPath]
        ,[tbl_Advisor_User_Master].[Coupon_Code_ID]
        ,[tbl_Advisor_Account_Type_Map].[AccountName]
        ,[tbl_Advisor_User_Master].[Created_Date]
        ,[tbl_Advisor_User_Master].[SellCA]
        ,[tbl_Advisor_User_Master].[SellUS]
        ,[tbl_Advisor_User_Master].[Business_Address1]
        ,[tbl_Advisor_User_Master].[Business_Address2]
        ,[tbl_Advisor_User_Master].[Business_City]
        ,[tbl_Advisor_User_Master].[Business_State]
        ,[tbl_Advisor_User_Master].[Business_Country]
        ,[tbl_Advisor_User_Master].[Business_Zip]
        ,[tbl_Advisor_User_Master].[Phone_Work]
        ,[tbl_WFG_Advisor_Details].[WFG_Producer_Number]
        ,[tbl_Advisor_Codes].[PromoCode]

FROM     tbl_Advisor_User_Master
        LEFT OUTER JOIN tbl_WFG_Advisor_Details
ON       tbl_WFG_Advisor_Details.AdvisorID=tbl_Advisor_User_Master.AdvisorUser_ID
        LEFT OUTER JOIN
         tbl_Advisor_Codes
ON
        tbl_Advisor_Codes.Id=tbl_Advisor_User_Master.Coupon_Code_ID
        LEFT OUTER JOIN
        tbl_Advisor_Account_Type_Map
ON
        tbl_Advisor_Account_Type_Map.AccountID=tbl_Advisor_User_Master.AccountType

WHERE

        [tbl_Advisor_User_Master].[AdvisorUser_ID] = @User_ID


ORDER BY
        [tbl_Advisor_User_Master].[Created_Date] ASC

END