/****** Object:  StoredProcedure [dbo].[factHotelDetailedRevenue]    Script Date: 3/17/2025 1:25:28 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[factHotelDetailedRevenue] 

	@RevEntryRowId bigint,
	@ReservationRowId bigint,
	@RevEntryNewRowId bigint,
	@ReservationNewRowId bigint

AS
BEGIN
SET NOCOUNT ON;
-- Dismiss selecting from source if no rows will be fetched
IF(
SELECT COUNT(*) FROM [stg$Detailed Revenue Entry] sDRE
where sDRE.bigint_timestamp BETWEEN @RevEntryRowId AND @RevEntryNewRowId) = 0
AND
(SELECT COUNT(*) FROM [stg$Hotel Reservation] sHR
where sHR.bigint_timestamp BETWEEN @ReservationRowId AND @ReservationNewRowId) = 0
BEGIN
SELECT 
	'RowCount' = @@rowcount
END
ELSE
BEGIN

WITH 

/*
Get the list of companies used
*/
tCompanies
AS
(SELECT
		dCOM.[SK_Company] as [Company]
	   ,dCOM.[CompanyPrefix]
	FROM [DW].[dCompany] dCOM
	WHERE dCOM.[SK_Company] <> -1
	AND dCOM.[CompanyPrefix] IN (SELECT DISTINCT [CompanyPrefix] FROM [stg$Detailed Revenue Entry])),
/*
Set up dimensions and support tables used with the main data process
*/

tHotelRooms
AS
(SELECT dHR.Room,tCOM.CompanyPrefix, dHR.SK_HotelRooms from DW.dHotelRooms dHR
LEFT OUTER JOIN [tCompanies] tCOM
		ON dHR.[SK_Company] = tCOM.[Company]),

tHotelRoomsType
AS
(SELECT dHR.[Room Type],tCOM.CompanyPrefix, dHR.SK_HotelRooms 
	from DW.dHotelRooms dHR
	LEFT OUTER JOIN [tCompanies] tCOM
		ON dHR.[SK_Company] = tCOM.[Company]
	WHERE dHR.[Room] = dHR.[Room Type]),

tLocation
AS
(SELECT
		dLOC.[SK_location]
	   ,tCOM.[CompanyPrefix]
	   ,dLOC.[LocationCode]
	FROM DW.[dLocation] dLOC
	LEFT OUTER JOIN [tCompanies] tCOM
		ON dLOC.[Company] = tCOM.[Company]),

tPOSTerminal
AS
(SELECT
		dPOT.[SK_POSTerminal]
	   ,dPOT.[No]
	   ,tCOM.[CompanyPrefix]
	FROM [DW].[dPOSTerminal] dPOT
	LEFT OUTER JOIN [tCompanies] tCOM
		ON dPOT.[Company] = tCOM.[Company]),

tVendor
AS
(SELECT dVEN.[Company]
		,dVEN.VendorNo
		,dVEN.SK_Vendor
from DW.dVendor dVEN),

tCustomer 
AS
(SELECT 
	dCUST.[SK_Customer]
	,tCOM.CompanyPrefix
	,dCUST.[CustomerNo]
	FROM [DW].[dCustomer] dCUST
	LEFT OUTER JOIN tCompanies tCOM
		ON dCUST.[Company] = tCOM.[Company]),

tItem
AS
(SELECT
		dITM.[SK_Item]
	   ,tCOM.[CompanyPrefix]
	   ,dITM.[No]
	   ,dVEN.SK_Vendor
	   ,dITM.ValidFrom
	   ,dITM.ValidTo
	FROM [DW].[dItem] dITM
	LEFT OUTER JOIN tCompanies tCOM
		ON dITM.[Company] = tCOM.[Company]
	LEFT OUTER JOIN tVendor dVEN
		ON dITM.Company = dVEN.[Company]
		AND dITM.VendorNo = dVEN.VendorNo),

tHotelDetailRevenue AS(
SELECT
	COALESCE(COM.[Company], -1) AS [SK_Company]
   ,sDRE.[bigint_timestamp] as [RowId]
   ,COALESCE(tITM.SK_Item, -1) AS SK_Item
   ,COALESCE(dLoc.SK_Location, -1) AS SK_Location
   ,COALESCE(dHR.SK_HotelRooms, tHRT.SK_HotelRooms, -1) AS SK_HotelRoom
   ,COALESCE(tCUST.SK_Customer,-1) as SK_Customer
   ,COALESCE(tPOS.SK_POSTerminal,-1) AS SK_POSTerminal
   ,sDRE.[Line No_]
   ,sDRE.[Reservation No_]
   ,sDRE.[Paying Reservation No_]
   ,sDRE.[Group Res No_]
   ,sDRE.[Transaction Type]
   ,sDRE.[Revenue Type]
   ,sDRE.[Revenue Code]
   ,sDRE.[Paid]
   ,sDRE.[Invoiced]
   ,sDRE.[Included in Rate]
   ,sDRE.[Quantity]
   ,sDRE.[Unit Price]
   ,SDRE.[Line Discount _] AS [Line discount %]
   ,sDRE.[Line Discount Amount]
   ,sDRE.[Amount]
   ,sDRE.[Amount incl_ VAT]
   ,sDRE.[VAT Amount]
   ,CASE
		WHEN sDRE.[Amount excl_ VAT] = 0 THEN sDRE.[Amount incl_ VAT]-sDRE.[VAT Amount]
		ELSE sDRE.[Amount excl_ VAT]
	END AS [Amount excl_ VAT]
   ,sDRE.[VAT _]AS [VAT %]
   ,sDRE.[Currency]
   ,sDRE.[Description]
   ,sDRE.[Rate Code]
   ,sDRE.[Sales Invoice No_]
   ,sDRE.[Property] --Already added as dimension connection Loc
   ,sDRE.[Room Type] --Already added as dimension connection HotelRooms
   ,sDRE.[Room No_] --Already added as dimension connection HotelRooms
   ,sDRE.[Store No_] --Already added as dimension connection Loc (store over property)
   ,sDRE.[POS Terminal No_]
   ,sDRE.[Room Charge]
   ,sDRE.[Room Charge Receipt No_]
   ,sDRE.[Receipt No_]
   ,sDRE.[Adults] AS [Adults DRE]
   ,sDRE.[Children] AS [Children DRE]
   ,sHR.[Adults] AS [Adults HRES]
   ,sHR.[Children] AS [Children HRES]
   ,SDRE.[Activity No_]
   ,sDRE.[Activity Location]
   ,sDRE.[Activity Internal Status]
   ,sDRE.[Activity Status]
   ,sDRE.[Date]
   ,sHR.[Status]
   ,sHR.[Reservation Channel] AS [Reservation Channel]
   ,sHR.[Market Segment] AS [Market Segment]
FROM [dbo].[stg$Detailed Revenue Entry] sDRE
LEFT JOIN [dbo].[stg$Hotel Reservation] sHR
ON sDRE.[CompanyPrefix] = sHR.[CompanyPrefix]
AND sDRE.[Reservation No_] = sHR.[Reservation No_]
LEFT JOIN tHotelRooms dHR
ON sHR.[Room No_] = dHR.[Room]
AND sHR.[CompanyPrefix] = dHR.[CompanyPrefix]
LEFT JOIN tHotelRoomsType tHRT
ON sHR.[CompanyPrefix] = tHRT.[CompanyPrefix]
AND sHR.[Room Type] = tHRT.[Room Type]
LEFT JOIN tLocation dLoc
	ON COALESCE(NULLIF(sDRE.[Store No_],''),sDRE.Property) = dLoc.LocationCode
	AND sDRE.CompanyPrefix = dLoc.CompanyPrefix
LEFT JOIN tPOSTerminal tPOS
	ON sDRE.[POS Terminal No_] = tPOS.[No]
	AND sDRE.[CompanyPrefix] = tPOS.[CompanyPrefix]
LEFT JOIN tCompanies COM
	ON sDRE.[CompanyPrefix] = COM.[CompanyPrefix]
LEFT JOIN tCustomer tCUST
	ON sDRE.[Customer No_] = tCUST.[CustomerNo]
	AND sDRE.[CompanyPrefix] = tCUST.[CompanyPrefix]
LEFT JOIN tItem tITM
ON sDRE.[Item No_] = tITM.No
AND sDRE.[CompanyPrefix] = tITM.[CompanyPrefix]
AND sDRE.[Date] >= tITM.[ValidFrom] AND sDRE.[Date] <= tITM.[ValidTo]


UNION ALL

SELECT
	COALESCE(COM.[Company], -1) AS [SK_Company]
   ,sTSE.[bigint_timestamp] as [RowId]
   ,COALESCE(tITM.SK_Item, -1) AS SK_Item
   ,COALESCE(dLoc.SK_Location, -1) AS SK_Location
   ,-1 AS SK_HotelRoom
   ,-1 AS SK_Customer
   ,COALESCE(tPOS.SK_POSTerminal,-1) AS SK_POSTerminal
   ,sTSE.[Line No_] AS [Line No_]
   ,'' AS [Reservation No_]
   ,'' AS [Paying Reservation No_]
   ,'' AS [Group Res No_]
   ,'99' AS [Transaction Type]
   ,'99' AS [Revenue Type]
   ,'' AS [Revenue Code]
   ,'1' AS [Paid]
   ,'0' AS [Invoiced]
   ,'0' AS [Included in Rate]
   ,-sTSE.[Quantity]
   ,sTSE.[Price] as [Unit Price]
   ,'0' AS [Line discount %]
   ,sTSE.[Line Discount] AS [Line Discount Amount]
   ,sTSE.[Net Amount]*-1 AS [Amount]
   ,(sTSE.[Net Amount]+sTSE.[VAT Amount])*-1 AS [Amount incl_ VAT]
   ,sTSE.[VAT Amount] *-1 AS [VAT Amount]
   ,sTSE.[Net Amount]*-1 AS [Amount excl_ VAT]
   ,0 AS [VAT %]
   ,'' AS [Currency]
   ,'' AS [Description]
   ,'' AS [Rate Code]
   ,'' AS [Sales Invoice No_]
   ,'' AS [Property] --Already added as dimension connection Loc
   ,'' AS [Room Type] --Already added as dimension connection HotelRooms
   ,'' AS [Room No_] --Already added as dimension connection HotelRooms
   ,sTSE.[Store No_] --Already added as dimension connection Loc (store over property)
   ,sTSE.[POS Terminal No_]
   ,'0' AS [Room Charge]
   ,'' AS [Room Charge Receipt No_]
   ,sTSE.[Receipt No_]
   ,'0' AS [Adults DRE]
   ,'0' AS [Children DRE]
   ,'0' AS [Adults HRES]
   ,'0' AS [Children HRES]
   ,'' AS [Activity No_]
   ,'' AS [Activity Location]
   ,'0' AS [Activity Internal Status]
   ,'' AS [Activity Status]
   ,sTSE.[Date]
   ,'' AS [Status]
   ,'' AS [Reservation Channel]
   ,'' AS [Market Segment]
FROM [dbo].[stg$Trans_ Sales Entry] sTSE
LEFT JOIN [dbo].[stg$Transaction Header] sTH
	ON sTSE.CompanyPrefix = sTH.CompanyPrefix
	AND sTSE.[Transaction No_] = sTH.[Transaction No_]
LEFT JOIN tLocation dLoc
	ON sTH.[Store No_] = dLoc.LocationCode
	AND sTH.CompanyPrefix = dLoc.CompanyPrefix
LEFT JOIN tPOSTerminal tPOS
	ON sTSE.[POS Terminal No_] = tPOS.[No]
	AND sTSE.[CompanyPrefix] = tPOS.[CompanyPrefix]
JOIN tCompanies COM
	ON sTH.CompanyPrefix = COM.CompanyPrefix
LEFT JOIN tCustomer tCUST
	ON sTH.[Customer No_] = tCUST.CustomerNo
	AND sTH.[CompanyPrefix] = tCUST.CompanyPrefix
LEFT JOIN tItem tITM
ON sTSE.[Item No_] = tITM.No
AND sTSE.CompanyPrefix = tITM.CompanyPrefix
AND sTSE.[Date] >= tITM.ValidFrom AND sTSE.[Date] <= tITM.ValidTo 
WHERE (sTSE.[Barcode No_] not like '#HRES%' 
AND sTSE.[Receipt No_] not in (select [Room Charge Receipt No_] from [stg$Detailed Revenue Entry])) 
AND sTSE.[HT Hotel Reservation No_] = ''
AND sTH.[Post as Shipment] = 0

),
tHotelDetailRevenueRanked AS ( SELECT
		*
		,ROW_NUMBER() OVER (PARTITION BY [SK_Company],[Line No_],[Reservation No_], [Receipt No_] ORDER BY [RowID] DESC) AS [Rank]
	FROM tHotelDetailRevenue)

MERGE [DW].[fHotelDetailedRevenue] AS TARGET USING
	(SELECT
	tHDRR.[SK_Company]
	,tHDRR.[SK_Item]
	,tHDRR.[SK_Location]
	,tHDRR.[SK_HotelRoom]
	,tHDRR.[SK_Customer]
	,tHDRR.[SK_POSTerminal]
	,tHDRR.[Line No_]
    ,tHDRR.[Reservation No_]
    ,tHDRR.[Paying Reservation No_]
    ,tHDRR.[Group Res No_]
    ,tHDRR.[Transaction Type]
    ,tHDRR.[Revenue Type]
    ,tHDRR.[Revenue Code]
    ,tHDRR.[Paid]
    ,tHDRR.[Invoiced]
    ,tHDRR.[Included in Rate]
    ,tHDRR.[Quantity]
    ,tHDRR.[Unit Price]
    ,tHDRR.[Line Discount %]
    ,tHDRR.[Line Discount Amount]
    ,tHDRR.[Amount]
    ,tHDRR.[Amount incl_ VAT]
    ,tHDRR.[VAT Amount]
    ,tHDRR.[Amount excl_ VAT]
    ,tHDRR.[VAT %]
    ,tHDRR.[Currency]
    ,tHDRR.[Description]
    ,tHDRR.[Rate Code]
    ,tHDRR.[Sales Invoice No_]
    ,tHDRR.[Property] --Already added as dimension connection Loc
    ,tHDRR.[Room Type] --Already added as dimension connection HotelRooms
    ,tHDRR.[Room No_] --Already added as dimension connection HotelRooms
    ,tHDRR.[Store No_] --Already added as dimension connection Loc (store over property)
    ,tHDRR.[POS Terminal No_]
    ,tHDRR.[Room Charge]
    ,tHDRR.[Room Charge Receipt No_]
    ,tHDRR.[Receipt No_]
    ,tHDRR.[Adults DRE]
    ,tHDRR.[Children DRE]
    ,tHDRR.[Adults HRES]
    ,tHDRR.[Children HRES]
    ,tHDRR.[Activity No_]
    ,tHDRR.[Activity Location]
    ,tHDRR.[Activity Internal Status]
    ,tHDRR.[Activity Status]
	,tHDRR.[Date]
	,tHDRR.[Status]
	,tHDRR.[Reservation Channel]
	,tHDRR.[Market Segment]
	FROM tHotelDetailRevenueRanked tHDRR
		WHERE tHDRR.[Rank] = 1) AS SOURCE
	ON
	TARGET.[SK_Company] = SOURCE.[SK_Company]
	AND TARGET.[Line No_] = SOURCE.[Line No_]
	AND TARGET.[Reservation No_] = SOURCE.[Reservation No_]
	AND TARGET.[Receipt No_] = SOURCE.[Receipt No_]
WHEN MATCHED
	THEN UPDATE
		SET [SK_HotelRoom] = SOURCE.[SK_HotelRoom]
	,[SK_Location] = SOURCE.[SK_Location]
	,[SK_Item] = SOURCE.[SK_Item]
	,[SK_Customer] = SOURCE.[SK_Customer]
	,[SK_POSTerminal] = SOURCE.[SK_POSTerminal]
	,[Transaction Type] = SOURCE.[Transaction Type]
    ,[Paying Reservation No_] = SOURCE.[Paying Reservation No_]
    ,[Group Res No_] = SOURCE.[Group Res No_]
    ,[Revenue Type] = SOURCE.[Revenue Type]
    ,[Revenue Code] = SOURCE.[Revenue Code]
    ,[Paid] = SOURCE.[Paid] 
    ,[Invoiced] = SOURCE.[Invoiced]
    ,[Included in Rate] = SOURCE.[Included in Rate]
    ,[Quantity] = SOURCE.[Quantity] 
    ,[Unit Price] = SOURCE.[Unit Price]
    ,[Line Discount %] = SOURCE.[Line Discount %]
    ,[Line Discount Amount] = SOURCE.[Line Discount Amount]
    ,[Amount] = SOURCE.[Amount]
    ,[Amount incl_ VAT] = SOURCE.[Amount incl_ VAT]
    ,[VAT Amount] = SOURCE.[VAT Amount] 
    ,[Amount excl_ VAT] = SOURCE.[Amount excl_ VAT]
    ,[VAT %] = SOURCE.[VAT %]
    ,[Currency] = SOURCE.[Currency]
    ,[Description] = SOURCE.[Description]
    ,[Rate Code] = SOURCE.[Rate Code]
    ,[Sales Invoice No_] = SOURCE.[Sales Invoice No_]
    ,[Property] = SOURCE.[Property] --Already added as dimension connection Loc
    ,[Room Type] = SOURCE.[Room Type] --Already added as dimension connection HotelRooms
    ,[Room No_] = SOURCE.[Room No_] --Already added as dimension connection HotelRooms
    ,[Store No_] = SOURCE.[Store No_] --Already added as dimension connection Loc (store over property)
    ,[POS Terminal No_] = SOURCE.[POS Terminal No_]
    ,[Room Charge] = SOURCE.[Room Charge]
    ,[Room Charge Receipt No_] = SOURCE.[Room Charge Receipt No_]
    ,[Adults DRE] = SOURCE.[Adults DRE]
    ,[Children DRE] = SOURCE.[Children DRE]
    ,[Adults HRES] = SOURCE.[Adults HRES]
    ,[Children HRES] = SOURCE.[Children HRES]
    ,[Activity No_] = SOURCE.[Activity No_]
    ,[Activity Location] = SOURCE.[Activity Location]
    ,[Activity Internal Status] = SOURCE.[Activity Internal Status]
    ,[Activity Status] = SOURCE.[Activity Status]
	,[Date] = SOURCE.[Date]
	,[Status] = SOURCE.[Status]
	,[Reservation Channel] = SOURCE.[Reservation Channel]
	,[Market Segment] = SOURCE.[Market Segment]

	WHEN NOT MATCHED BY TARGET
	THEN INSERT
		([SK_Company]
	,[SK_Item]
	,[SK_Location]
	,[SK_HotelRoom]
	,[SK_Customer] 
	,[SK_POSTerminal]
	,[Line No_]
    ,[Reservation No_]
    ,[Paying Reservation No_] 
    ,[Group Res No_] 
	,[Transaction Type] 
    ,[Revenue Type] 
    ,[Revenue Code] 
    ,[Paid] 
    ,[Invoiced] 
    ,[Included in Rate] 
    ,[Quantity] 
    ,[Unit Price] 
    ,[Line Discount %] 
    ,[Line Discount Amount] 
    ,[Amount] 
    ,[Amount incl_ VAT] 
    ,[VAT Amount] 
    ,[Amount excl_ VAT]
    ,[VAT %] 
    ,[Currency] 
    ,[Description] 
    ,[Rate Code] 
    ,[Sales Invoice No_] 
    ,[Property] 
    ,[Room Type] 
    ,[Room No_] 
    ,[Store No_] 
    ,[POS Terminal No_] 
    ,[Room Charge] 
    ,[Room Charge Receipt No_] 
    ,[Receipt No_] 
    ,[Adults DRE] 
    ,[Children DRE] 
    ,[Adults HRES] 
    ,[Children HRES] 
    ,[Activity No_] 
    ,[Activity Location] 
    ,[Activity Internal Status] 
    ,[Activity Status] 
	,[Date] 
	,[Status] 
	,[Reservation Channel] 
	,[Market Segment] 
	)
	VALUES
	(SOURCE.[SK_Company]
	,SOURCE.[SK_Item]
	,SOURCE.[SK_Location]
	,SOURCE.[SK_HotelRoom]
	,SOURCE.[SK_Customer] 
	,SOURCE.[SK_POSTerminal]
	,SOURCE.[Line No_]
    ,SOURCE.[Reservation No_]
    ,SOURCE.[Paying Reservation No_] 
    ,SOURCE.[Group Res No_] 
	,SOURCE.[Transaction Type] 
    ,SOURCE.[Revenue Type] 
    ,SOURCE.[Revenue Code] 
    ,SOURCE.[Paid] 
    ,SOURCE.[Invoiced] 
    ,SOURCE.[Included in Rate] 
    ,SOURCE.[Quantity] 
    ,SOURCE.[Unit Price] 
    ,SOURCE.[Line Discount %] 
    ,SOURCE.[Line Discount Amount] 
    ,SOURCE.[Amount] 
    ,SOURCE.[Amount incl_ VAT] 
    ,SOURCE.[VAT Amount] 
	,SOURCE.[Amount excl_ VAT]
    ,SOURCE.[VAT %] 
    ,SOURCE.[Currency] 
    ,SOURCE.[Description] 
    ,SOURCE.[Rate Code] 
    ,SOURCE.[Sales Invoice No_] 
    ,SOURCE.[Property] 
    ,SOURCE.[Room Type] 
    ,SOURCE.[Room No_] 
    ,SOURCE.[Store No_] 
    ,SOURCE.[POS Terminal No_] 
    ,SOURCE.[Room Charge] 
    ,SOURCE.[Room Charge Receipt No_] 
    ,SOURCE.[Receipt No_] 
    ,SOURCE.[Adults DRE] 
    ,SOURCE.[Children DRE] 
    ,SOURCE.[Adults HRES] 
    ,SOURCE.[Children HRES] 
    ,SOURCE.[Activity No_] 
    ,SOURCE.[Activity Location] 
    ,SOURCE.[Activity Internal Status] 
    ,SOURCE.[Activity Status] 
	,SOURCE.[Date] 
	,SOURCE.[Status] 
	,SOURCE.[Reservation Channel] 
	,SOURCE.[Market Segment])

WHEN NOT MATCHED BY SOURCE
	THEN DELETE
;

END

SELECT
	'RowCount' = @@rowcount
END