Untitled

-----------------------
-- Report Parameters --
-----------------------
DECLARE
	@BuyingGroupDimId INT
	,@ChainLevel3DimId INT
	,@PartnerDimId INT
	,@ProductDimId INT
	,@TransactionTypeDimId INT
	,@UnitCostDimId INT
	,@CountryTPAttId INT
	,@EdiAddressVariantsTPAttId INT
	,@BuyingGroupIRFCustRefImportId INT
	,@CustomerNumberIRFCustRefImportId INT
	,@AddressLine1TPAttId INT
	,@AddressLine2TPAttId INT
	,@CityTPAttId INT
	,@StateTPAttId INT
	,@ZipTPAttId INT
	,@DateFormat NVARCHAR(10)
	,@ValueFormat NVARCHAR(255)
	,@UnitsFormat NVARCHAR(255);

SELECT
	@BuyingGroupDimId = MAX(CASE WHEN [PrimaryLabel] = 'Buying Groups' THEN [Id] END)
	, @ChainLevel3DimId = MAX(CASE WHEN [PrimaryLabel] = 'Chain Level 3' THEN [Id] END)
	, @PartnerDimId = MAX(CASE WHEN [PrimaryLabel] = 'Partners' THEN [Id] END)
	, @ProductDimId = MAX(CASE WHEN [PrimaryLabel] = 'Products' THEN [Id] END)
	, @TransactionTypeDimId = MAX(CASE WHEN [PrimaryLabel] = 'Transaction Types' THEN [Id] END)
	, @UnitCostDimId = MAX(CASE WHEN [PrimaryLabel] = 'Unit Costs' THEN [Id] END)
FROM Dimensions;

SELECT @CountryTPAttId = (SELECT TOP 1 [Id] FROM TradingPartnerAttributes WHERE [Name] = 'Country');
SELECT @EdiAddressVariantsTPAttId = (SELECT TOP 1 [Id] FROM TradingPartnerAttributes WHERE [Name] = 'EDI Address Variants');
SELECT @BuyingGroupIRFCustRefImportId = (SELECT TOP 1 [Id] FROM CustomImportReferenceLookups WHERE [Name] = 'Buying Group IRF');
SELECT @CustomerNumberIRFCustRefImportId = (SELECT TOP 1 [Id] FROM CustomImportReferenceLookups WHERE [Name] = 'Customer Reference IRF');

SELECT @AddressLine1TPAttId = (SELECT TOP 1 [Id] FROM TradingPartnerAttributes WHERE [Name] = 'Address Line 1');
SELECT @AddressLine2TPAttId = (SELECT TOP 1 [Id] FROM TradingPartnerAttributes WHERE [Name] = 'Address Line 2');
SELECT @CityTPAttId = (SELECT TOP 1 [Id] FROM TradingPartnerAttributes WHERE [Name] = 'City');
SELECT @StateTPAttId = (SELECT TOP 1 [Id] FROM TradingPartnerAttributes WHERE [Name] = 'State');
SELECT @ZipTPAttId = (SELECT TOP 1 [Id] FROM TradingPartnerAttributes WHERE [Name] = 'Zip');

SELECT
	@DateFormat = 'yyyy-MM-dd'
	, @ValueFormat = '0.00'
	, @UnitsFormat = '0.00';

------------------------------
-- #Temp Tables --
------------------------------

DROP TABLE IF EXISTS #CountryTPAttVals;
CREATE TABLE #CountryTPAttVals (
	TradingPartnerId INT PRIMARY KEY,
	Country NVARCHAR(255)
);

INSERT INTO #CountryTPAttVals
SELECT TradingPartnerId, ValueText
FROM TradingPartnerAttributeValues
WHERE TradingPartnerAttributeId = @CountryTPAttId;

DROP TABLE IF EXISTS #EdiAddressVariantsTPAttVals;
CREATE TABLE #EdiAddressVariantsTPAttVals
(
    TradingPartnerId INT,
    TradingPartnerReference NVARCHAR(255),
    EDIAddressVariant NVARCHAR(255),
    CleanedEDIAddress NVARCHAR(255)
);

-- Replace your existing INSERT INTO #EdiAddressVariantsTPAttVals ... SELECT ... block with this:

INSERT INTO #EdiAddressVariantsTPAttVals (
    TradingPartnerId,
    TradingPartnerReference,
    EDIAddressVariant,
    CleanedEDIAddress
)
SELECT
    TPAV.TradingPartnerId,
    CAST(TP.Reference AS NVARCHAR(255)) AS TradingPartnerReference,
    -- EDIAddressVariant: trim + remove space after commas + collapse double spaces
    LTRIM(RTRIM(
        REPLACE(
            REPLACE(SplitVals.[value], ', ', ','), -- remove space after comma
        '  ', ' ')
    )) AS EDIAddressVariant,
    -- CleanedEDIAddress: further remove commas, periods and collapse double spaces for matching
    LTRIM(REPLACE(
        REPLACE(
            REPLACE(
                LTRIM(RTRIM(
                    REPLACE(REPLACE(SplitVals.[value], ', ', ','), '  ', ' ')
                )),
            ',', ''), -- remove commas
        '.', ''), -- remove periods
    '  ', ' ')) AS CleanedEDIAddress
FROM TradingPartnerAttributeValues AS TPAV
    INNER JOIN TradingPartners AS TP ON TPAV.TradingPartnerId = TP.Id
    CROSS APPLY STRING_SPLIT(TPAV.ValueText, ',') AS SplitVals
WHERE TPAV.TradingPartnerAttributeId = @EdiAddressVariantsTPAttId
    AND TPAV.ValueText IS NOT NULL 
    AND TPAV.ValueText != ''
    AND TPAV.ValueText != '-';


CREATE INDEX IX_EdiVariants_Cleaned ON #EdiAddressVariantsTPAttVals(CleanedEDIAddress);

DROP TABLE IF EXISTS #CustomerAddressAttributes;
CREATE TABLE #CustomerAddressAttributes (
    TradingPartnerId INT PRIMARY KEY,
    TradingPartnerReference NVARCHAR(255),
    ConcatenatedAddress NVARCHAR(MAX),
    CleanedAddress NVARCHAR(255)  -- fixed length
);


INSERT INTO #CustomerAddressAttributes
SELECT
	TP.Id,
	TP.Reference,
	LTRIM(RTRIM(
		CASE WHEN ISNULL(Addr1.ValueText, '') = '-' THEN '' ELSE ISNULL(Addr1.ValueText, '') END +
		CASE WHEN ISNULL(Addr2.ValueText, '') IN ('', '-') THEN '' ELSE ' ' + Addr2.ValueText END +
		CASE WHEN ISNULL(City.ValueText, '') IN ('', '-') THEN '' ELSE ' ' + City.ValueText END +
		CASE WHEN ISNULL(State.ValueText, '') IN ('', '-') THEN '' ELSE ' ' + State.ValueText END +
		CASE WHEN ISNULL(Zip.ValueText, '') IN ('', '-') THEN '' ELSE ' ' + TRIM('0' FROM Zip.ValueText) END
	)) AS ConcatenatedAddress,
	LTRIM(REPLACE(REPLACE(REPLACE(
		LTRIM(RTRIM(
			CASE WHEN ISNULL(Addr1.ValueText, '') = '-' THEN '' ELSE ISNULL(Addr1.ValueText, '') END +
			CASE WHEN ISNULL(Addr2.ValueText, '') IN ('', '-') THEN '' ELSE ' ' + Addr2.ValueText END +
			CASE WHEN ISNULL(City.ValueText, '') IN ('', '-') THEN '' ELSE ' ' + City.ValueText END +
			CASE WHEN ISNULL(State.ValueText, '') IN ('', '-') THEN '' ELSE ' ' + State.ValueText END +
			CASE WHEN ISNULL(Zip.ValueText, '') IN ('', '-') THEN '' ELSE ' ' + Zip.ValueText END
		)),
		',', ''), '.', ''), '  ', ' ')) AS CleanedAddress
FROM TradingPartners AS TP
	LEFT JOIN TradingPartnerAttributeValues AS Addr1 ON TP.Id = Addr1.TradingPartnerId AND Addr1.TradingPartnerAttributeId = @AddressLine1TPAttId
	LEFT JOIN TradingPartnerAttributeValues AS Addr2 ON TP.Id = Addr2.TradingPartnerId AND Addr2.TradingPartnerAttributeId = @AddressLine2TPAttId
	LEFT JOIN TradingPartnerAttributeValues AS City ON TP.Id = City.TradingPartnerId AND City.TradingPartnerAttributeId = @CityTPAttId
	LEFT JOIN TradingPartnerAttributeValues AS State ON TP.Id = State.TradingPartnerId AND State.TradingPartnerAttributeId = @StateTPAttId
	LEFT JOIN TradingPartnerAttributeValues AS Zip ON TP.Id = Zip.TradingPartnerId AND Zip.TradingPartnerAttributeId = @ZipTPAttId
WHERE (Addr1.ValueText IS NOT NULL OR Addr2.ValueText IS NOT NULL OR City.ValueText IS NOT NULL 
       OR State.ValueText IS NOT NULL OR Zip.ValueText IS NOT NULL);

CREATE INDEX IX_CustAddr_Cleaned ON #CustomerAddressAttributes(CleanedAddress);

DROP TABLE IF EXISTS #CustomerNumberIrf;

CREATE TABLE #CustomerNumberIrf (
    LookupKey NVARCHAR(255) PRIMARY KEY,
    Reported_Sold_To_Number NVARCHAR(255),
    Partner_Name NVARCHAR(255),
    Validated_Sold_To_Customer_ID NVARCHAR(255)
);

INSERT INTO #CustomerNumberIrf
SELECT
    CustImpRefLookKey.[Key],
    MAX(CASE WHEN CustImpRefLookCol.Name = 'Reported_Sold_To_Number' THEN CustImpRefLookVal.Value END),
    MAX(CASE WHEN CustImpRefLookCol.Name = 'Partner_Name' THEN CustImpRefLookVal.Value END),
    MAX(CASE WHEN CustImpRefLookCol.Name = 'Validated_Sold_To_Customer_ID' THEN CustImpRefLookVal.Value END)
FROM CustomImportReferenceLookupKeys CustImpRefLookKey
    INNER JOIN CustomImportReferenceLookupValues CustImpRefLookVal 
        ON CustImpRefLookVal.ReferenceLookupKeyId = CustImpRefLookKey.Id
    INNER JOIN CustomImportReferenceLookupColumns CustImpRefLookCol 
        ON CustImpRefLookVal.ReferenceLookupColumnId = CustImpRefLookCol.Id
WHERE CustImpRefLookKey.ReferenceLookupId = @CustomerNumberIRFCustRefImportId
GROUP BY CustImpRefLookKey.[Key];


CREATE INDEX IX_CustNumIRF_Lookup ON #CustomerNumberIrf(Reported_Sold_To_Number, Partner_Name);

DROP TABLE IF EXISTS #BuyingGroupIrf;
CREATE TABLE #BuyingGroupIrf (
	CustomerNumber NVARCHAR(255),
	Chainlevel2 NVARCHAR(255),
	Chainlevel3 NVARCHAR(255),
	ValidFrom DATE,
	ValidTo DATE
);

INSERT INTO #BuyingGroupIrf
SELECT DISTINCT
	MAX(CASE WHEN CustImpRefLookCol.Name = 'CustomerNumber' THEN CustImpRefLookVal.Value END),
	MAX(CASE WHEN CustImpRefLookCol.Name = 'Chainlevel2' THEN CustImpRefLookVal.Value END),
	MAX(CASE WHEN CustImpRefLookCol.Name = 'Chainlevel3' THEN CustImpRefLookVal.Value END),
	TRY_CAST(MAX(CASE WHEN CustImpRefLookCol.Name = 'ValidFrom' THEN CustImpRefLookVal.Value END) AS DATE),
	TRY_CAST(MAX(CASE WHEN CustImpRefLookCol.Name = 'ValidTo' THEN CustImpRefLookVal.Value END) AS DATE)
FROM CustomImportReferenceLookupKeys CustImpRefLookKey
	INNER JOIN CustomImportReferenceLookupValues CustImpRefLookVal ON CustImpRefLookVal.ReferenceLookupKeyId = CustImpRefLookKey.Id
	INNER JOIN CustomImportReferenceLookupColumns CustImpRefLookCol ON CustImpRefLookVal.ReferenceLookupColumnId = CustImprefLookCol.Id
WHERE CustImpRefLookKey.ReferenceLookupId = @BuyingGroupIRFCustRefImportId
GROUP BY CustImpRefLookKey.[Key];

CREATE INDEX IX_BuyingGroupIRF_Customer ON #BuyingGroupIrf(CustomerNumber, ValidFrom, ValidTo);

-- Pre-aggregate dimension references to avoid repeated subqueries
DROP TABLE IF EXISTS #DimensionRefs;
CREATE TABLE #DimensionRefs (
	ImportedTurnoverLineId INT,
	DimensionId INT,
	DimensionItemReference NVARCHAR(MAX),
	PRIMARY KEY (ImportedTurnoverLineId, DimensionId)
);

INSERT INTO #DimensionRefs
SELECT ImportedTurnoverLineId, DimensionId, DimensionItemReference
FROM ImportedTurnoverLineDimensionItemRefs
WHERE DimensionId IN (@BuyingGroupDimId, @PartnerDimId, @ProductDimId, @TransactionTypeDimId, @UnitCostDimId);

CREATE INDEX IX_DimRefs_Line ON #DimensionRefs(ImportedTurnoverLineId);

--------------------
----	Logic	----
--------------------

-- Helper function for parsing pipe-delimited strings (avoiding repeated STRING_SPLIT logic)
DROP TABLE IF EXISTS #ParsedAddresses;
CREATE TABLE #ParsedAddresses (
	Id INT PRIMARY KEY,
	OriginalCustomerString NVARCHAR(MAX),
	BT_AddressLine1 NVARCHAR(255),
	BT_AddressLine2 NVARCHAR(255),
	BT_City NVARCHAR(255),
	BT_State NVARCHAR(255),
	BT_PostalCode NVARCHAR(255),
	BT_ConcatenatedAddress NVARCHAR(MAX),
	BT_CleanedAddress NVARCHAR(MAX),
	ST_AddressLine1 NVARCHAR(255),
	ST_AddressLine2 NVARCHAR(255),
	ST_City NVARCHAR(255),
	ST_State NVARCHAR(255),
	ST_PostalCode NVARCHAR(255),
	ST_ConcatenatedAddress NVARCHAR(MAX),
	ST_CleanedAddress NVARCHAR(MAX),
	Sold_To_Number NVARCHAR(255),
	Partner_Name NVARCHAR(255)
);

INSERT INTO #ParsedAddresses
SELECT
	ImpTL.Id,
	ImpTL.TradingPartnerReference,
	-- BT Address parsing
	REPLACE(REPLACE((SELECT value FROM (SELECT value, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rn FROM STRING_SPLIT(ImpTL.TradingPartnerReference, '|')) x WHERE rn = 3), ',', ''), '.', ''),
	REPLACE(REPLACE((SELECT value FROM (SELECT value, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rn FROM STRING_SPLIT(ImpTL.TradingPartnerReference, '|')) x WHERE rn = 4), ',', ''), '.', ''),
	(SELECT value FROM (SELECT value, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rn FROM STRING_SPLIT(ImpTL.TradingPartnerReference, '|')) x WHERE rn = 5),
	(SELECT value FROM (SELECT value, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rn FROM STRING_SPLIT(ImpTL.TradingPartnerReference, '|')) x WHERE rn = 6),
	(SELECT value FROM (SELECT value, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rn FROM STRING_SPLIT(ImpTL.TradingPartnerReference, '|')) x WHERE rn = 7),
	NULL, -- Will compute concatenated addresses below
	NULL,
	-- ST Address parsing
	REPLACE(REPLACE((SELECT value FROM (SELECT value, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rn FROM STRING_SPLIT(COALESCE(DR_BG.DimensionItemReference, ''), '|')) x WHERE rn = 3), ',', ''), '.', ''),
	REPLACE(REPLACE((SELECT value FROM (SELECT value, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rn FROM STRING_SPLIT(COALESCE(DR_BG.DimensionItemReference, ''), '|')) x WHERE rn = 4), ',', ''), '.', ''),
	(SELECT value FROM (SELECT value, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rn FROM STRING_SPLIT(COALESCE(DR_BG.DimensionItemReference, ''), '|')) x WHERE rn = 5),
	(SELECT value FROM (SELECT value, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rn FROM STRING_SPLIT(COALESCE(DR_BG.DimensionItemReference, ''), '|')) x WHERE rn = 6),
	(SELECT value FROM (SELECT value, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rn FROM STRING_SPLIT(COALESCE(DR_BG.DimensionItemReference, ''), '|')) x WHERE rn = 7),
	NULL, -- Will compute concatenated addresses below
	NULL,
	-- Extract Sold To Number
	CASE 
		WHEN ImpTL.DeliveryReference LIKE '%#%' THEN 
			TRIM('0' FROM LTRIM(LEFT(ImpTL.DeliveryReference, CHARINDEX('#', ImpTL.DeliveryReference) - 1), '0'))
		ELSE NULL
	END,
	DR_Partner.DimensionItemReference
FROM ImportedTurnoverLines AS ImpTL
	LEFT JOIN #DimensionRefs DR_BG ON ImpTL.Id = DR_BG.ImportedTurnoverLineId AND DR_BG.DimensionId = @BuyingGroupDimId
	LEFT JOIN #DimensionRefs DR_Partner ON ImpTL.Id = DR_Partner.ImportedTurnoverLineId AND DR_Partner.DimensionId = @PartnerDimId
WHERE ISNUMERIC(ImpTL.TradingPartnerReference) = 0;

-- Update concatenated addresses
UPDATE #ParsedAddresses
SET 
	BT_ConcatenatedAddress = LTRIM(RTRIM(
		CASE WHEN ISNULL(BT_AddressLine1, '') = '-' THEN '' ELSE ISNULL(BT_AddressLine1, '') END +
		CASE WHEN ISNULL(BT_AddressLine2, '') IN ('', '-') THEN '' ELSE ' ' + BT_AddressLine2 END +
		CASE WHEN ISNULL(BT_City, '') IN ('', '-') THEN '' ELSE ' ' + BT_City END +
		CASE WHEN ISNULL(BT_State, '') IN ('', '-') THEN '' ELSE ' ' + BT_State END +
		CASE WHEN ISNULL(BT_PostalCode, '') IN ('', '-') THEN '' ELSE ' ' + TRIM('0' FROM BT_PostalCode) END
	)),
	ST_ConcatenatedAddress = LTRIM(RTRIM(
		CASE WHEN ISNULL(ST_AddressLine1, '') = '-' THEN '' ELSE ISNULL(ST_AddressLine1, '') END +
		CASE WHEN ISNULL(ST_AddressLine2, '') IN ('', '-') THEN '' ELSE ' ' + ST_AddressLine2 END +
		CASE WHEN ISNULL(ST_City, '') IN ('', '-') THEN '' ELSE ' ' + ST_City END +
		CASE WHEN ISNULL(ST_State, '') IN ('', '-') THEN '' ELSE ' ' + ST_State END +
		CASE WHEN ISNULL(ST_PostalCode, '') IN ('', '-') THEN '' ELSE ' ' + TRIM('0' FROM ST_PostalCode) END
	));

-- Update cleaned addresses
UPDATE #ParsedAddresses
SET 
	BT_CleanedAddress = LTRIM(REPLACE(REPLACE(REPLACE(BT_ConcatenatedAddress, ',', ''), '.', ''), '  ', ' ')),
	ST_CleanedAddress = LTRIM(REPLACE(REPLACE(REPLACE(ST_ConcatenatedAddress, ',', ''), '.', ''), '  ', ' '));

ALTER TABLE #ParsedAddresses
ADD BT_CleanedAddressShort AS CAST(LEFT(BT_CleanedAddress, 255) AS NVARCHAR(255)) PERSISTED,
    ST_CleanedAddressShort AS CAST(LEFT(ST_CleanedAddress, 255) AS NVARCHAR(255)) PERSISTED;

CREATE INDEX IX_ParsedAddr_BTCleaned ON #ParsedAddresses(BT_CleanedAddressShort);
CREATE INDEX IX_ParsedAddr_STCleaned ON #ParsedAddresses(ST_CleanedAddressShort);

CREATE INDEX IX_ParsedAddr_SoldTo ON #ParsedAddresses(Sold_To_Number, Partner_Name);

-- Compute match counts once
DROP TABLE IF EXISTS #MatchCounts;
CREATE TABLE #MatchCounts (
	Id INT PRIMARY KEY,
	BT_MatchCount INT,
	ST_MatchCount INT
);

INSERT INTO #MatchCounts
SELECT 
	PA.Id,
	COUNT(DISTINCT CASE WHEN CAA_BT.TradingPartnerId IS NOT NULL AND PA.BT_CleanedAddress != '' THEN CAA_BT.TradingPartnerId END),
	COUNT(DISTINCT CASE WHEN CAA_ST.TradingPartnerId IS NOT NULL AND PA.ST_CleanedAddress != '' THEN CAA_ST.TradingPartnerId END)
FROM #ParsedAddresses PA
	LEFT JOIN #CustomerAddressAttributes CAA_BT ON PA.BT_CleanedAddress = CAA_BT.CleanedAddress
	LEFT JOIN #CustomerAddressAttributes CAA_ST ON PA.ST_CleanedAddress = CAA_ST.CleanedAddress
GROUP BY PA.Id;

-- Final matching logic
DROP TABLE IF EXISTS #FinalMatches;
CREATE TABLE #FinalMatches (
	ImportedTurnoverLineId INT PRIMARY KEY,
	MatchedCustomerReference NVARCHAR(255),
	MatchedTradingPartnerId INT,
	MatchType NVARCHAR(50),
	ShouldDropRow BIT
);

INSERT INTO #FinalMatches
SELECT
	PA.Id,
	-- Final customer reference (with hyphen handling)
	CASE 
		WHEN MatchedRef LIKE '%-%' THEN LEFT(MatchedRef, CHARINDEX('-', MatchedRef) - 1)
		ELSE MatchedRef
	END,
	MatchedTPId,
	MatchType,
	CASE WHEN MC.BT_MatchCount > 1 OR MC.ST_MatchCount > 1 THEN 1 ELSE 0 END
FROM #ParsedAddresses PA
	INNER JOIN #MatchCounts MC ON PA.Id = MC.Id
	CROSS APPLY (
		SELECT TOP 1
			COALESCE(
				-- Step 1: IRF Match
				IRF.Validated_Sold_To_Customer_ID,
				-- Step 2: BT Address Match
				CASE WHEN MC.BT_MatchCount = 1 THEN CAA_BT.TradingPartnerReference END,
				-- Step 3: BT EDI Match
				EDI_BT.TradingPartnerReference,
				-- Step 4: ST Address Match
				CASE WHEN MC.ST_MatchCount = 1 AND MC.BT_MatchCount = 0 THEN CAA_ST.TradingPartnerReference END,
				-- Step 5: ST EDI Match
				EDI_ST.TradingPartnerReference,
				-- Fallback
				PA.OriginalCustomerString
			) AS MatchedRef,
			COALESCE(
				TP_IRF.Id,
				CAA_BT.TradingPartnerId,
				EDI_BT.TradingPartnerId,
				CASE WHEN MC.ST_MatchCount = 1 AND MC.BT_MatchCount = 0 THEN CAA_ST.TradingPartnerId END,
				EDI_ST.TradingPartnerId
			) AS MatchedTPId,
			CASE 
				WHEN IRF.Validated_Sold_To_Customer_ID IS NOT NULL THEN 'Sold_To_IRF_Match'
				WHEN MC.BT_MatchCount = 1 AND CAA_BT.TradingPartnerId IS NOT NULL THEN 'BT_Address_Match'
				WHEN EDI_BT.TradingPartnerId IS NOT NULL THEN 'BT_EDI_Match'
				WHEN MC.ST_MatchCount = 1 AND MC.BT_MatchCount = 0 AND CAA_ST.TradingPartnerId IS NOT NULL THEN 'ST_Address_Match'
				WHEN EDI_ST.TradingPartnerId IS NOT NULL THEN 'ST_EDI_Match'
				ELSE 'No_Match'
			END AS MatchType
		FROM (SELECT 1 AS Dummy) D
			-- Step 1: IRF lookup
			LEFT JOIN #CustomerNumberIrf IRF 
			ON PA.Sold_To_Number = TRIM('0' FROM IRF.Reported_Sold_To_Number)
			AND PA.Partner_Name = IRF.Partner_Name
			LEFT JOIN TradingPartners TP_IRF 
				ON IRF.Validated_Sold_To_Customer_ID = TP_IRF.Reference
			-- Step 2 & 4: Address matches (only if no IRF)
			LEFT JOIN #CustomerAddressAttributes CAA_BT 
				ON IRF.Validated_Sold_To_Customer_ID IS NULL 
				AND PA.BT_CleanedAddress = CAA_BT.CleanedAddress 
				AND PA.BT_CleanedAddress != ''
				AND MC.BT_MatchCount = 1
			LEFT JOIN #CustomerAddressAttributes CAA_ST 
				ON IRF.Validated_Sold_To_Customer_ID IS NULL 
				AND PA.ST_CleanedAddress = CAA_ST.CleanedAddress 
				AND PA.ST_CleanedAddress != ''
				AND MC.ST_MatchCount = 1
				AND MC.BT_MatchCount = 0
			-- Step 3 & 5: EDI matches (only if no IRF and no address match)
			LEFT JOIN #EdiAddressVariantsTPAttVals EDI_BT 
				ON IRF.Validated_Sold_To_Customer_ID IS NULL 
				AND CAA_BT.TradingPartnerId IS NULL
				AND PA.BT_CleanedAddress = EDI_BT.CleanedEDIAddress
			LEFT JOIN #EdiAddressVariantsTPAttVals EDI_ST 
				ON IRF.Validated_Sold_To_Customer_ID IS NULL 
				AND CAA_BT.TradingPartnerId IS NULL
				AND EDI_BT.TradingPartnerId IS NULL
				AND PA.ST_CleanedAddress = EDI_ST.CleanedEDIAddress
	) Matched;

CREATE INDEX IX_FinalMatches_Customer ON #FinalMatches(MatchedCustomerReference);

-- Pre-compute buying group mappings
DROP TABLE IF EXISTS #BuyingGroupMappings;
CREATE TABLE #BuyingGroupMappings (
	ImportedTurnoverLineId INT PRIMARY KEY,
	Mapped_Buying_Group NVARCHAR(MAX),
	Mapped_Chain_Level_3 NVARCHAR(MAX)
);

INSERT INTO #BuyingGroupMappings
SELECT
	FM.ImportedTurnoverLineId,
	CASE
		WHEN EXISTS (SELECT 1 FROM #BuyingGroupIrf WHERE UPPER(CustomerNumber) = UPPER(FM.MatchedCustomerReference)) THEN
			COALESCE(
				(SELECT STRING_AGG(Chainlevel2, '-')
				 FROM (
					SELECT DISTINCT Chainlevel2
					FROM #BuyingGroupIrf BGI
					WHERE UPPER(BGI.CustomerNumber) = UPPER(FM.MatchedCustomerReference)
					  AND ImpTL.TransactionDate >= BGI.ValidFrom
					  AND (BGI.ValidTo IS NULL OR BGI.ValidTo = '' OR ImpTL.TransactionDate <= BGI.ValidTo)
					  AND BGI.Chainlevel2 IS NOT NULL
					  AND BGI.Chainlevel2 NOT IN ('-', CHAR(39) + '-', '')
				 ) X),
				'-'
			)
		ELSE COALESCE(DR_BG.DimensionItemReference, '-')
	END,
	CASE
		WHEN EXISTS (SELECT 1 FROM #BuyingGroupIrf WHERE UPPER(CustomerNumber) = UPPER(FM.MatchedCustomerReference)) THEN
			COALESCE(
				(SELECT STRING_AGG(Chainlevel3, '-')
				 FROM (
					SELECT DISTINCT Chainlevel3
					FROM #BuyingGroupIrf BGI
					WHERE UPPER(BGI.CustomerNumber) = UPPER(FM.MatchedCustomerReference)
					  AND ImpTL.TransactionDate >= BGI.ValidFrom
					  AND (BGI.ValidTo IS NULL OR BGI.ValidTo = '' OR ImpTL.TransactionDate <= BGI.ValidTo)
					  AND BGI.Chainlevel3 IS NOT NULL
					  AND BGI.Chainlevel3 NOT IN ('-', CHAR(39) + '-', '')
				 ) X),
				'-'
			)
		ELSE '-'
	END
FROM #FinalMatches FM
	INNER JOIN ImportedTurnoverLines ImpTL ON FM.ImportedTurnoverLineId = ImpTL.Id
	LEFT JOIN #DimensionRefs DR_BG ON ImpTL.Id = DR_BG.ImportedTurnoverLineId AND DR_BG.DimensionId = @BuyingGroupDimId
WHERE FM.ShouldDropRow = 0;

--------------------
----	Output	----
--------------------
SELECT
	FORMAT(ImpTL.TransactionDate, @DateFormat) AS [Date],
	FM.MatchedCustomerReference AS [Customer],
	CASE WHEN BGM.Mapped_Buying_Group LIKE '%|%' THEN '-' ELSE ISNULL(BGM.Mapped_Buying_Group, '-') END AS [Buying Group],
	ISNULL(BGM.Mapped_Chain_Level_3, '-') AS [Chain Level 3],
	DR_Partner.DimensionItemReference AS [Partner],
	DR_Product.DimensionItemReference AS [Product],
	DR_TxnType.DimensionItemReference AS [Transaction Type],
	DR_UnitCost.DimensionItemReference AS [Unit Cost],
	FORMAT(ImpTL.Units, @UnitsFormat) AS [Units],
	FORMAT(ImpTL.Value, @ValueFormat) AS [Value],
	CASE WHEN Country.Country IN ('CA', 'Canada') THEN 'CAD' ELSE 'USD' END AS [Currency],
	ImpTL.ExternalReference AS [External Reference],
	FORMAT(ImpTL.InterfaceDate, @DateFormat) AS [Interface Date],
	ImpTL.ExternalPrimaryKey AS [Primary Key],
	ImpTL.AgreementReference AS [Agreement ID],
	ImpTL.AdvisedEarnings AS [Advised Earnings],
	ImpTL.OrderReference AS [Order Reference],
	ImpTL.DeliveryReference AS [Delivery Reference],
	ImpTL.InvoiceReference AS [Invoice Reference]
FROM ImportedTurnoverLines AS ImpTL
	INNER JOIN #FinalMatches FM ON ImpTL.Id = FM.ImportedTurnoverLineId
	LEFT JOIN #BuyingGroupMappings BGM ON ImpTL.Id = BGM.ImportedTurnoverLineId
	LEFT JOIN #CountryTPAttVals Country ON FM.MatchedTradingPartnerId = Country.TradingPartnerId
	LEFT JOIN #DimensionRefs DR_Partner ON ImpTL.Id = DR_Partner.ImportedTurnoverLineId AND DR_Partner.DimensionId = @PartnerDimId
	LEFT JOIN #DimensionRefs DR_Product ON ImpTL.Id = DR_Product.ImportedTurnoverLineId AND DR_Product.DimensionId = @ProductDimId
	LEFT JOIN #DimensionRefs DR_TxnType ON ImpTL.Id = DR_TxnType.ImportedTurnoverLineId AND DR_TxnType.DimensionId = @TransactionTypeDimId
	LEFT JOIN #DimensionRefs DR_UnitCost ON ImpTL.Id = DR_UnitCost.ImportedTurnoverLineId AND DR_UnitCost.DimensionId = @UnitCostDimId
WHERE FM.ShouldDropRow = 0
	AND FM.MatchedCustomerReference IS NOT NULL
	AND ISNUMERIC(FM.MatchedCustomerReference) = 1
	AND FM.MatchType != 'No_Match';