SkiftKæde.sql

USE Trio

SELECT hd.HierarchyDetailId
	,Chain.TEXT Chain
	,DetailNo
FROM Trio..HierarchyDetail hd
CROSS APPLY dbo.GetCommonText_TV(hd.FullNameId, (SELECT CountryId FROM Trio..Country WHERE Code = 'DK')) AS Chain
WHERE hd.ParentHierarchyDetailId = (
		SELECT hd.HierarchyDetailId
		FROM Trio..HierarchyDetail hd
		INNER JOIN HierarchyType AS ht ON ht.HierarchyTypeId = hd.HierarchyTypeId
			AND ht.TypeNo = 4 --Store hierarchy
			AND hd.DetailNo = 'Coop'
		)
ORDER BY DetailNo
GO

DECLARE @RetailUnitNo VARCHAR(13) = '24024'
DECLARE @fromChainNo VARCHAR(2) = '10'
DECLARE @toChainNo VARCHAR(2) = '15'

SELECT StoreText.TEXT Store
	,ChainText.TEXT Chain
	,hd.*
FROM RetailUnit AS ru
INNER JOIN HierarchyRetailUnit AS hru ON hru.RetailUnitId = ru.RetailUnitId
INNER JOIN HierarchyDetail AS hd ON hd.HierarchyDetailId = hru.HierarchyDetailId
INNER JOIN HierarchyDetail AS chain ON chain.HierarchyDetailId = hd.ParentHierarchyDetailId
CROSS APPLY dbo.GetCommonText_TV(hd.FullNameId, (SELECT CountryId FROM Trio..Country WHERE Code = 'DK')) AS StoreText
CROSS APPLY dbo.GetCommonText_TV(chain.FullNameId, (SELECT CountryId FROM Trio..Country WHERE Code = 'DK')) AS ChainText
WHERE ru.RetailUnitNo = @RetailUnitNo
	AND chain.DetailNo = @fromChainNo

BEGIN TRANSACTION;

UPDATE hd
SET ParentHierarchyDetailId = (
		SELECT hd.HierarchyDetailId
		FROM Trio..HierarchyDetail hd
		WHERE hd.ParentHierarchyDetailId = (
				SELECT hd.HierarchyDetailId
				FROM Trio..HierarchyDetail hd
				INNER JOIN HierarchyType AS ht ON ht.HierarchyTypeId = hd.HierarchyTypeId
					AND ht.TypeNo = 4 --Store hierarchy
					AND hd.DetailNo = 'Coop'
				)
			AND DetailNo = @toChainNo
		)
FROM RetailUnit AS ru
INNER JOIN HierarchyRetailUnit AS hru ON hru.RetailUnitId = ru.RetailUnitId
INNER JOIN HierarchyDetail AS hd ON hd.HierarchyDetailId = hru.HierarchyDetailId
INNER JOIN HierarchyDetail AS chain ON chain.HierarchyDetailId = hd.ParentHierarchyDetailId
CROSS APPLY dbo.GetCommonText_TV(hd.FullNameId, (SELECT CountryId FROM Trio..Country WHERE Code = 'DK')) AS StoreText
CROSS APPLY dbo.GetCommonText_TV(chain.FullNameId, (SELECT CountryId FROM Trio..Country WHERE Code = 'DK')) AS ChainText
WHERE ru.RetailUnitNo = @RetailUnitNo
	AND chain.DetailNo = @fromChainNo

--COMMIT;
ROLLBACK;