Create VIEW [dbo].[ViwAcclistRev]
AS

select * from (

SELECT comm.code as CommittteeCode, Comm.title as CommittteeTitle,Comm.Type as CommitteeType,
	 Total.Code AS FullCode, 
	 Total.Title AS FullTitle, 
	Total.TitleNoSpace AS FullTitleNoSpace, 
	Total.Code AS Code, 
	Total.Title AS Title,
	Total.TitleNoSpace AS TitleNoSpace,
	Total.Type AS Type,
	total.CreditType AS CreditType,
	1 as Active,
	COALESCE (Total.ID,0) as TotalID, 0 as ControlID, 0 as DetailID1, 0 as DetailID2
from AccTotal as Total	LEFT OUTER JOIN 
	AccCommittee as Comm on Comm.ID = Total.CommitteRef 

--Select *  from AccDocItem where ControlID=0 and TotalID=10
union

SELECT comm.code as CommittteeCode, Comm.title as CommittteeTitle,Comm.Type as CommitteeType,
	dbo.ConcatAcc(N'‪-‫', Total.Code, Ctrl.Code, null,null) AS FullCode, 
	dbo.ConcatAcc(N'‪-‫', Ctrl.Title, Total.Title, null,null) AS FullTitle, 
	dbo.ConcatAcc(N'', Total.TitleNoSpace, Ctrl.TitleNoSpace,null,null) AS FullTitleNoSpace, 
	COALESCE (Ctrl.Code, Total.Code) AS Code, 
	COALESCE (Ctrl.Title, Total.Title) AS Title,
	COALESCE (Ctrl.TitleNoSpace, Total.TitleNoSpace) AS TitleNoSpace,
	COALESCE (Ctrl.Type, Total.Type) AS Type,
	COALESCE (Ctrl.CreditType, Total.CreditType) AS CreditType,
	COALESCE (Ctrl.Active,1) as Active,
	COALESCE (Total.ID,0) as TotalID, COALESCE (Ctrl.ID,0) as ControlID, 0 as DetailID1, 0 as DetailID2


FROM dbo.AccTotal AS Total Left OUTER JOIN
    dbo.AccTotalControl AS TotalControl ON Total.ID = TotalControl.TotalID LEFT OUTER JOIN
    dbo.AccControl AS Ctrl ON TotalControl.ControlID = Ctrl.ID LEFT OUTER JOIN
	AccCommittee as Comm on Comm.ID = Total.CommitteRef 

	union

SELECT comm.code as CommittteeCode, Comm.title as CommittteeTitle,Comm.Type as CommitteeType,
	dbo.ConcatAcc(N'‪-‫', Total.Code, Ctrl.Code, Detail1.Code, null) AS FullCode, 
	dbo.ConcatAcc(N'‪-‫', Detail1.Title, Ctrl.Title, Total.Title,  Null) AS FullTitle, 
	dbo.ConcatAcc(N'', Total.TitleNoSpace, Ctrl.TitleNoSpace, Detail1.TitleNoSpace, Null) AS FullTitleNoSpace, 
	COALESCE (Detail1.Code, Ctrl.Code, Total.Code) AS Code, 
	COALESCE (Detail1.Title, Ctrl.Title, Total.Title) AS Title,
	COALESCE (Detail1.TitleNoSpace, Ctrl.TitleNoSpace, Total.TitleNoSpace) AS TitleNoSpace,
	COALESCE (Detail1.Type, Ctrl.Type, Total.Type) AS Type,
	COALESCE (Detail1.CreditType, Ctrl.CreditType, Total.CreditType) AS CreditType,
	COALESCE (Detail1.Active, Ctrl.Active, 1) as Active,
	COALESCE (Total.ID,0) as TotalID, COALESCE (Ctrl.ID,0) as ControlID, COALESCE (Detail1.id,0) as DetailID1, 0 as DetailID2

FROM dbo.AccTotal AS Total Left OUTER JOIN
    dbo.AccTotalControl AS TotalControl ON Total.ID = TotalControl.TotalID LEFT OUTER JOIN
    dbo.AccControl AS Ctrl ON TotalControl.ControlID = Ctrl.ID LEFT OUTER JOIN
    dbo.AccControlDetail AS ControlDetail ON Ctrl.ID = ControlDetail.ControlID LEFT OUTER JOIN
    dbo.AccDetail AS Detail1 ON Detail1.ID = ControlDetail.DetailID LEFT OUTER JOIN
	AccCommittee as Comm on Comm.ID = Total.CommitteRef 

union

SELECT comm.code as CommittteeCode, Comm.title as CommittteeTitle,Comm.Type as CommitteeType,
	dbo.ConcatAcc(N'‪-‫', Total.Code, Ctrl.Code, Detail1.Code, Detail2.Code) AS FullCode, 
	dbo.ConcatAcc(N'‪-‫', Detail2.Title, Detail1.Title, Ctrl.Title, Total.Title) AS FullTitle, 
	dbo.ConcatAcc(N'', Total.TitleNoSpace, Ctrl.TitleNoSpace, Detail1.TitleNoSpace, Detail2.TitleNoSpace) AS FullTitleNoSpace, 
	COALESCE (Detail2.Code, Detail1.Code, Ctrl.Code, Total.Code) AS Code, 
	COALESCE (Detail2.Title, Detail1.Title, Ctrl.Title, Total.Title) AS Title,
	COALESCE (Detail2.TitleNoSpace, Detail1.TitleNoSpace, Ctrl.TitleNoSpace, Total.TitleNoSpace) AS TitleNoSpace,
	COALESCE (Detail2.Type, Detail1.Type, Ctrl.Type, Total.Type) AS Type,
	COALESCE (Detail2.Active, Detail1.Active, Ctrl.Active, 1) as Active,
	COALESCE (Detail2.CreditType,Detail1.CreditType, Ctrl.CreditType, Total.CreditType) AS CreditType,
	COALESCE (Total.ID,0) as TotalID, COALESCE (Ctrl.ID,0) as ControlID, COALESCE (Detail1.id,0) as DetailID1, COALESCE (Detail2.id,0) as DetailID2

FROM dbo.AccTotal AS Total Left OUTER JOIN
    dbo.AccTotalControl AS TotalControl ON Total.ID = TotalControl.TotalID LEFT OUTER JOIN
    dbo.AccControl AS Ctrl ON TotalControl.ControlID = Ctrl.ID LEFT OUTER JOIN
    dbo.AccControlDetail AS ControlDetail ON Ctrl.ID = ControlDetail.ControlID LEFT OUTER JOIN
    dbo.AccDetail AS Detail1 ON Detail1.ID = ControlDetail.DetailID LEFT OUTER JOIN
    dbo.AccDetailDetail AS DetailDetail ON Detail1.ID = DetailDetail.DetailID1 LEFT OUTER JOIN
    dbo.AccDetail AS Detail2 ON DetailDetail.DetailID2 = Detail2.ID	LEFT OUTER JOIN 
	AccCommittee as Comm on Comm.ID = Total.CommitteRef 
--WHERE   (COALESCE (Detail2.Active, Detail1.Active, Ctrl.Active, 1) = 1) AND (COALESCE (Detail1.Active, Ctrl.Active, 1) = 1) AND (COALESCE (Ctrl.Active, 1) = 1)
--where Total.Code='03'

--Group by Total.Code,  Total.Title, Ctrl.Code, Ctrl.Title
--and Total.code='99'
	--having Sum(COALESCE (Item.Debit, 0))>0 or Sum(COALESCE (Item.Credit, 0))>0
 	--order by Total.code

)t 
where Active=1 --and FullCode='03-001'

