USE [prince]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[Orginal].[View_AllSaffronFactorsSmallest_Lottery]'))
DROP VIEW [Orginal].[View_AllSaffronFactorsSmallest_Lottery]
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create VIEW [Orginal].[View_AllSaffronFactorsSmallest_Lottery]
 
AS
	SELECT  SFC_ID, 
	        SFC_No, 
	        SFC_Type, 
	        SFC_Comment, 
	        SFC_Status, 
	        SFC_IsDraft,
	        SFC_IsBusy,
            SFC_Date,
            SFC_Date_Int,
            SFC_Time,
            SFC_Time_Int,
			SFC_DateTime_Int,
            SFC_IsGuest, 
            SFC_Style, 
            SFC_IsChanged, 
            SFC_CustomerCount,
            SFC_Hostess,
            SFC_TempAddress,
            SFC_TempPhone,
            CPN_Description,
            SFC_Total AS FactorSum,
            CPN_Code,
            V_CPN_ID AS CPN_ID,
            Address1,
            Address2,
            Phone1,
            Phone2,
            CPN_Type,
            SFC_LKP_Section,
            CPN_Is_Credit,
            CPN_MaxCredit ,
            Mobile_NO,
            SFC_TransferCost,
            (SELECT [LKP_Caption] FROM [Common].[Lookup] WHERE LKP_ID = CPN_LKP_Title) as CPN_Title, CPN_Name,
		    (SELECT (ISNULL((SELECT LKP_Caption FROM [Common].[Lookup] 
	                                            WHERE LKP_ID = [CPN_LKP_Title]) , '') + ' ' + [CPN_FirstName]  + ' ' + [CPN_LastName]) 
	                                            FROM [Common].[CompanyPerson] WHERE [CPN_ID] = [SFC_Hostess]) AS [SFC_HostessName],
		    SCP_ID, 
		    SCP_Name, 
		    [FSA_Value] AS SCP_Cost,
			Fax_NO,
			PostalCode,
			CPN_IS_Subscriber,
			CPN_SubscriberType,
			SFC_AutoCredit,
			SFC_RowDiscountCost,
			SFC_DepositCost,
			SFC_SRE_ID, 			
			SFC_UserID,		
			SFC_InsertedBy,
			SFC_TakeAwayAppDesc
        ,(SELECT SPT_CPN_ID FROM [Orginal].[SaffronParticipant] WHERE SFC_ID = SPT_SFC_ID AND (SPT_Role = 7)) AS Guest_CPN_ID
        ,LTRIM(RTRIM((SELECT (ISNULL((SELECT LKP_Caption FROM [Common].[Lookup] 
			                           WHERE LKP_ID = [CPN_LKP_Title]) , '') + ' ' +[CPN_FirstName]  + ' ' + [CPN_LastName]) 
			            FROM [Common].[CompanyPerson] 
			           WHERE [CPN_ID] = (SELECT SPT_CPN_ID FROM [Orginal].[SaffronParticipant] WHERE SFC_ID = SPT_SFC_ID AND (SPT_Role = 7))))) AS [Guest_CPN_Name]
         			           

        ,(SELECT SPT_CPN_ID FROM [Orginal].[SaffronParticipant] WHERE SFC_ID = SPT_SFC_ID AND (SPT_Role = 8)) AS SaloonPerson_CPN_ID
        ,LTRIM(RTRIM((SELECT (ISNULL((SELECT LKP_Caption 
										FROM [Common].[Lookup] 
										WHERE LKP_ID = [CPN_LKP_Title]) , '') + ' ' + 
												[CPN_FirstName]  + ' ' + [CPN_LastName] + 
												ISNULL((SELECT ' - ' + [CIN_Value]
														FROM [Common].[ContactInformation]
														WHERE [CIN_CPN_ID] = [CPN_ID] AND [CIN_LKP_Type] = 30), '') ) 
			            FROM [Common].[CompanyPerson] 
			           WHERE [CPN_ID] = (SELECT SPT_CPN_ID FROM [Orginal].[SaffronParticipant] WHERE SFC_ID = SPT_SFC_ID AND (SPT_Role = 8))))) AS [SaloonPerson_CPN_Name]
        ,FCA_CPN_ID AS Courier_CPN_ID        		
        ,(SELECT LTRIM(RTRIM([CPN_FirstName]  + ' ' + [CPN_LastName])) FROM [Common].[CompanyPerson] WHERE CPN_ID = FCA_CPN_ID) AS [Courier_CPN_Name]
		,SFC_DiscountCost	
		,[SFC_CPN_ID]  	
		,ISNULL([SFC_AfterTaxPayers], 0) AS [SFC_AfterTaxPayers]
		,[CPN_CustomersClubSendReciept]
		,[CPN_CustomersClubCashBackApply]
			 
	FROM  [Orginal].[SaffronFactor] 
		INNER JOIN [Orginal].[SaffronParticipant] ON (SFC_ID = SPT_SFC_ID) AND (SPT_Role in (1,8))
			INNER JOIN [Common].[View_CPN_Info] ON SPT_CPN_ID = V_CPN_ID
				LEFT OUTER JOIN	[Common].[SaffronScope] ON SFC_SCP_ID = SCP_ID 		
					LEFT OUTER JOIN [Orginal].[SaffronFactorAssign] ON SFC_ID = FCA_SFC_ID
						LEFT OUTER JOIN [Orginal].[SaffronFactorSubAdd] ON SFC_ID = FSA_SFC_ID AND [FSA_Type] = 3


GO