Mới đi làm hả em, 1 cái job sql ETL vài trăm đến 1k line là bình thường mà :sogood:
Code: USE DataMart GO -- TRUNCATE TABLE FactInventoryTransaction INSERT INTO FactInventoryTransaction ( [LotSK] ,[QualitySK] ,[LocationSK] ,[QtyUnitSK] ,[WeightUnitSK] ,[LocalSK] ,[PurchaseContractDK] ,[ProductionOrderSK] ,[SalesContractDK] ,[BinSK] ,[CropYearSK] ,[TransactionSK] ,[TransactionDateSK] ,[CompanySK] ,[ProfitCenterSK] ,[CommoditySK] ,[Quantity] ,[Weight] ,[PaidWeight] ,[NewLotQty] ,[NewLotWeight] ,[InvoicedWeight] ,[DiscountPercent] ,[DocumentNo_] ,[WeightNoteNo_] ,[EntryNo_] ,[Description] ,[PostingDate] ,[CompanyID] --,[LastModifiedDateTimeOffset] ) SELECT d1.LotSK ,COALESCE(d2.QualitySK, 999000000000001) AS QualitySK ,CASE s.[Location Code] WHEN '' THEN 999000000000000 ELSE COALESCE(d3.LocationSK, 999000000000001) END AS LocationSK ,CASE s.[Packing Unit Code] WHEN '' THEN 999000000000000 ELSE COALESCE(d4.UnitSK, 999000000000001) END AS QtyUnitSK ,CASE s.[Unit of Measure Code] WHEN '' THEN 999000000000000 ELSE COALESCE(d5.UnitSK, 999000000000001) END AS WeightUnitSK , COALESCE(d6.LocalSK, 999000000000001) AS LocalSK ,CASE s.[Purchase Order No_] WHEN '' THEN 999000000000000 ELSE COALESCE(d7.PurchaseContractDK, 999000000000001) END AS PurchaseContractDK ,CASE s.[Prod_ Order No_] WHEN '' THEN 999000000000000 ELSE COALESCE(d8.ProductionOrderSK, 999000000000001) END AS ProductionOrderSK ,CASE s.[Sale Contract No_] WHEN '' THEN 999000000000000 ELSE COALESCE(d9.SalesContractDK, 999000000000001) END AS SalesContractDK , 999000000000000 AS BinSK , COALESCE(g1.CropYearSK, 1) AS CropYearSK , g2.TransactionSK , g3.DateSK AS TransactionDateSK , g4.CompanySK , g5.ProfitCenterSK , COALESCE(g6.CommoditySK, 1) AS CommoditySK -- Measures ,s.[Packing Units] AS Quantity ,s.[Quantity] AS [Weight] --,(s.[Quantity]*(100 - s.[Qty_ Discount %])/100) AS PaidWeight ,s.[Quantity] AS PaidWeight ,CASE WHEN s.[Entry Type] = 0 OR s.[Entry Type] = 6 THEN s.[Packing Units] ELSE NULL END AS NewLotQty ,CASE WHEN s.[Entry Type] = 0 OR s.[Entry Type] = 6 THEN s.[Quantity] ELSE NULL END AS NewLotWeight ,s.[Invoiced Quantity] AS InvoicedWeight -- Others ,s.[Qty_ Discount %] DiscountPercent ,s.[Document No_] DocumentNo_ ,CASE s.[Entry Type] WHEN 0 THEN doc_Re.[Document 2 No_] WHEN 4 THEN CASE s.[Positive] WHEN 1 THEN 'Transfer Receive' WHEN 0 THEN 'Transfer Shipment' ELSE NULL END WHEN 1 THEN SL.[Weight Note Reference] ELSE NULL END WeightNoteNo_ ,s.[Entry No_] EntryNo_ ,'Item Ledger Entry -' s.[Description] ,s.[Posting Date] PostingDate ,0 CompanyID --,26 CompanyID --,SYSDATETIMEOFFSET() LastModifiedDateTimeOffset FROM NAV_staging.dbo.[Item Ledger Entry] s INNER JOIN DimLot d1 ON d1.LotCode = s.[Lot No_] AND d1.ItemCode = s.[Item No_] AND d1.OP_CMS = 'OP' LEFT JOIN DimQuality d2 ON d2.QualityCode = s.[Item No_] AND d2.OP_CMS = 'OP' LEFT JOIN DimLocation d3 ON d3.LocationCode = s.[Location Code] AND d3.OP_CMS = 'OP' LEFT JOIN DimUnit d4 ON d4.UnitCode = s.[Packing Unit Code] -- QtyUnit LEFT JOIN DimUnit d5 ON d5.UnitCode = s.[Unit of Measure Code] -- WeightUnit LEFT JOIN NAV_Staging.dbo.[Lot Dimension] LD3 ON LD3.[Lot No_] = s.[Lot No_] AND LD3.[Item No_] = s.[Item No_] AND LD3.[Dimension Code] = 'LOCAL' LEFT JOIN DimLocal d6 ON d6.LocalCode = LD3.[Dimension Value Code] --LEFT JOIN DimLocal d6 ON d6.LocalCode = s.[Global Dimension 2 Code] LEFT JOIN DimPurchaseContract d7 ON d7.PurchaseContractCode = s.[Purchase Order No_] LEFT JOIN DimProductionOrder d8 ON d8.ProductionOrderCode = s.[Prod_ Order No_] LEFT JOIN DimSalesContract d9 ON d9.SalesContractCode = s.[Sale Contract No_] LEFT JOIN NAV_Staging.dbo.[Lot Dimension] LD1 ON LD1.[Lot No_] = s.[Lot No_] AND LD1.[Item No_] = s.[Item No_] AND LD1.[Dimension Code] = 'CROP YEAR' LEFT JOIN DimCropYear g1 ON g1.CropYearCode = LD1.[Dimension Value Code] INNER JOIN DimTransaction g2 ON g2.TransactionName = (CASE s.[Entry Type] WHEN 0 THEN 'Purchase Receive' WHEN 1 THEN 'Sales Order Call' WHEN 2 THEN 'Positive Adjustment' WHEN 3 THEN 'Negative Adjustment' WHEN 4 THEN 'Reclassification' WHEN 5 THEN 'Production Consumption' WHEN 6 THEN 'Production Output' ELSE '' END) INNER JOIN DimDate g3 ON g3.[Date] = CONVERT(DATE, s.[Document Date]) INNER JOIN DimCompany g4 ON g4.CompanyID = 26 INNER JOIN DimProfitCenter g5 ON g5.ProfitCenterCode = '97' LEFT JOIN NAV_Staging.dbo.[Lot Dimension] LD2 ON LD2.[Lot No_] = s.[Lot No_] AND LD2.[Item No_] = s.[Item No_] AND LD2.[Dimension Code] = 'COMMODITY' LEFT JOIN DimCommodity g6 ON g6.CommodityCode = LD2.[Dimension Value Code] --LEFT JOIN DimCommodity g6 ON g6.CommodityCode = s.[Global Dimension 1 Code] LEFT JOIN (SELECT * FROM (SELECT *, ROW_NUMBER() OVER ( PARTITION BY [Document 1 Source], [Document 1 Type], [Document 2 Type], [Document 1 No_], [Applied Qty_] ORDER BY [Document 2 No_] DESC ) row_num FROM NAV_Staging.dbo.[Document Relation] WHERE [Document 1 Source] = 2 -- Purchase AND [Document 1 Type] = 8 -- Posted Receipt AND [Document 2 Type] = 7 ) AS t WHERE row_num = 1 ) doc_Re ON (doc_Re.[Document 1 No_] = s.[Document No_] AND doc_Re.[Applied Qty_] = s.[Quantity]) LEFT JOIN NAV_Staging.dbo.[Sales Line] SL ON SL.[Document No_] = s.[Document No_] AND SL.[CMS Lot Number] = s.[CMS Lot Number] AND SL.[No_] = s.[Item No_] AND SL.[Shipment Date] = s.[Posting Date] where s.STG_CURRENT_IND = '1' and s.STG_OPERATION_TYPE <> 'D' --and s.STG_DWLOAD_TS is null ORDER BY s.[Entry No_] select @@ROWCOUNT extract_count -- Create Indexes for EntryNo_, DocumentNo_ IF NOT EXISTS ( SELECT 1 FROM sys.indexes WHERE name='IX_FactInventoryTransaction_EntryNo_' AND [object_id] = OBJECT_ID('FactInventoryTransaction') ) CREATE NONCLUSTERED INDEX [IX_FactInventoryTransaction_EntryNo_] ON FactInventoryTransaction (EntryNo_); ELSE ALTER INDEX [IX_FactInventoryTransaction_EntryNo_] ON dbo.FactInventoryTransaction REORGANIZE; IF NOT EXISTS ( SELECT 1 FROM sys.indexes WHERE name = 'IX_FactInventoryTransaction_DocumentNo_' AND [object_id] = OBJECT_ID('FactInventoryTransaction') ) CREATE NONCLUSTERED INDEX [IX_FactInventoryTransaction_DocumentNo_] ON FactInventoryTransaction (DocumentNo_) ; ELSE ALTER INDEX [IX_FactInventoryTransaction_DocumentNo_] ON dbo.FactInventoryTransaction REORGANIZE; ---------------------------------------------------- DECLARE @delete_duplicate int, @warehouse_entry int, @delete_duplicate_warehouse_entry int -- 1. Delete Duplicate ; WITH f AS ( SELECT ROW_NUMBER() OVER (PARTITION BY EntryNo_ ORDER BY InventoryTransactionSK) AS row_num FROM FactInventoryTransaction --WHERE [Description] <> 'Warehouse Entry' ) DELETE FROM f WHERE f.row_num > 1 SELECT @delete_duplicate = @@ROWCOUNT -- 2. update BinSK ReasonCode Description ; with t as ( select ile.[Entry No_] ile_Entry, we.[Bin Code], we.[Location Code], we.[Reason Code], we.[Entry No_] we_Entry from NAV_Staging.dbo.[Warehouse Entry] we inner join NAV_Staging.dbo.[Item Ledger Entry] ile on ile.[Item No_] = we.[Item No_] and ile.[Lot No_] = we.[Lot No_] and ile.[Location Code] = we.[Location Code] and ile.[Document No_] = we.[Reference No_] and ile.[Quantity] = we.[Quantity] where ile.[Document No_] <> '' and we.[Reference No_] <> '' ), s as ( select ROW_NUMBER() over (partition by ile_Entry order by ile_Entry, we_Entry) row_num, * from t ), w as (select f.InventoryTransactionSK, [Bin Code], [Location Code], [Reason Code] from s inner join FactInventoryTransaction f on f.EntryNo_ = convert(varchar(10), s.ile_Entry) and f.[Description] <> 'Warehouse Entry' where s.row_num = 1) --select * from w update f set f.BinSK = b.BinSK , f.ReasonCode = w.[Reason Code] from FactInventoryTransaction f, w , DimBin b where f.InventoryTransactionSK = w.InventoryTransactionSK and w.[Bin Code] = b.BinCode and w.[Location Code] = b.LocationCode --and f.LastModifiedDateTimeOffset IS NULL UPDATE f SET f.[Description] = LEFT(s.[Journal Batch Name] ' - ' f.[Description], 255) FROM FactInventoryTransaction f INNER JOIN DimLot d1 ON f.LotSK=d1.LotSK INNER JOIN DimLocation d2 ON d2.LocationSK=f.LocationSK INNER JOIN (SELECT DISTINCT [Lot No_],[Item No_],[Location Code],[Source No_],[Reason Code],[Journal Batch Name] FROM NAV_Staging.dbo.[Warehouse Entry] WHERE [Journal Batch Name] LIKE '%REMOVAL%' ) s ON (d1.LotCode=s.[Lot No_] AND d1.ItemCode=s.[Item No_] AND d2.LocationCode=s.[Location Code] AND f.DocumentNo_=s.[Source No_]) WHERE f.[Description] <> 'Warehouse Entry' --AND f.LastModifiedDateTimeOffset IS NULL -- 3. update PaidWeight update f set f.PaidWeight = f.[Weight]*(100 - s.[Qty_ Discount %])/100 from FactInventoryTransaction f, DimQuality q, DimLot d, ( select * from ( select ROW_NUMBER() over (partition by [Item No_], [Lot No_], [Qty_ Discount %] order by [Entry No_]) row_num, [Item No_], [Lot No_] ,case when [Qty_ Discount %] > 100 then 0 else [Qty_ Discount %] end [Qty_ Discount %] from NAV_Staging.dbo.[Item Ledger Entry] where [Entry Type] = 0 -- Purchase Receive and [Qty_ Discount %] <> 0 ) ILE where row_num = 1 ) s where f.QualitySK = q.QualitySK and f.LotSK = d.LotSK and s.[Lot No_] = d.LotCode and s.[Item No_] = q.QualityCode and f.ReasonCode <> 'ADJ PAID' --and f.LastModifiedDateTimeOffset IS NULL -- 4. add [Warehouse Entry] records INSERT INTO FactInventoryTransaction ( [LotSK] ,[QualitySK] ,[LocationSK] ,[QtyUnitSK] ,[WeightUnitSK] ,[LocalSK] ,[PurchaseContractDK] ,[ProductionOrderSK] ,[SalesContractDK] ,[BinSK] ,[CropYearSK] ,[TransactionSK] ,[TransactionDateSK] ,[CompanySK] ,[ProfitCenterSK] ,[CommoditySK] ,[Quantity] ,[Weight] ,[PaidWeight] ,[DocumentNo_] ,[ReferenceNo_] ,[EntryNo_] ,[ReasonCode] ,[Description] ,[PostingDate] ,[CompanyID] --,[LastModifiedDateTimeOffset] ) SELECT d1.LotSK ,COALESCE(d2.QualitySK, 999000000000001) AS QualitySK ,CASE s.[Location Code] WHEN '' THEN 999000000000000 ELSE COALESCE(d3.LocationSK, 999000000000001) END AS LocationSK ,CASE s.[Packing Unit Code] WHEN '' THEN 999000000000000 ELSE COALESCE(d4.UnitSK, 999000000000001) END AS QtyUnitSK ,CASE s.[Unit of Measure Code] WHEN '' THEN 999000000000000 ELSE COALESCE(d5.UnitSK, 999000000000001) END AS WeightUnitSK ,999000000000000 AS LocalSK ,999000000000000 AS PurchaseContractDK ,999000000000000 AS ProductionOrderSK ,999000000000000 AS SalesContractDK , COALESCE(b.BinSK, 999000000000001) AS BinSK , COALESCE(g1.CropYearSK, 1) AS CropYearSK , g2.TransactionSK , g3.DateSK AS TransactionDateSK , g4.CompanySK , g5.ProfitCenterSK , COALESCE(g6.CommoditySK, 1) AS CommoditySK -- Measures ,s.[Packing Units] AS Quantity ,s.[Quantity] AS [Weight] ,s.[Quantity] AS PaidWeight -- Others ,s.[Source No_] AS DocumentNo_ ,s.[Reference No_] AS ReferenceNo_ ,s.[Entry No_] AS EntryNo_ ,s.[Reason Code] AS ReasonCode ,'Warehouse Entry' AS [Description] ,s.[Registering Date] AS PostingDate ,0 CompanyID --,26 CompanyID --,SYSDATETIMEOFFSET() LastModifiedDateTimeOffset FROM NAV_staging.dbo.[Warehouse Entry] s INNER JOIN DimLot d1 ON d1.LotCode = s.[Lot No_] AND d1.ItemCode = s.[Item No_] AND d1.OP_CMS = 'OP' LEFT JOIN DimBin b ON s.[Bin Code] = b.BinCode AND s.[Location Code] = b.LocationCode LEFT JOIN DimQuality d2 ON d2.QualityCode = s.[Item No_] AND d2.OP_CMS = 'OP' LEFT JOIN DimLocation d3 ON d3.LocationCode = s.[Location Code] AND d3.OP_CMS = 'OP' LEFT JOIN DimUnit d4 ON d4.UnitCode = s.[Packing Unit Code] -- QtyUnit LEFT JOIN DimUnit d5 ON d5.UnitCode = s.[Unit of Measure Code] -- WeightUnit LEFT JOIN DimCropYear g1 ON g1.CropYearCode = d1.CropYear INNER JOIN DimTransaction g2 ON g2.TransactionName = 'Reclassification' INNER JOIN DimDate g3 ON g3.[Date] = CONVERT(DATE, s.[Registering Date]) INNER JOIN DimCompany g4 ON g4.CompanyID = 26 INNER JOIN DimProfitCenter g5 ON g5.ProfitCenterCode = '97' LEFT JOIN DimCommodity g6 ON g6.CommodityCode = d1.CommodityCode WHERE s.[Reference No_] '_' s.[Lot No_] '_' s.[Item No_] IN ( select ReferenceLotItem from ( select [Reference No_] '_' [Lot No_] '_' [Item No_] ReferenceLotItem, sum(Quantity) sum_Qty from NAV_Staging.dbo.[Warehouse Entry] where [Reference No_] '_' [Lot No_] '_' [Item No_] not in (select distinct [Document No_] '_' [Lot No_] '_' [Item No_] from NAV_Staging.dbo.[Item Ledger Entry] where [Document No_] <> '' and STG_CURRENT_IND = 1 and STG_OPERATION_TYPE <> 'D' ) group by [Reference No_] '_' [Lot No_] '_' [Item No_] ) r where r.sum_Qty = 0 ) and s.STG_CURRENT_IND = '1' and s.STG_OPERATION_TYPE <> 'D' --and s.STG_DWLOAD_TS is null ORDER BY s.[Entry No_] SELECT @warehouse_entry = @@ROWCOUNT ; WITH w AS (SELECT * FROM FactInventoryTransaction WHERE [Description] = 'Warehouse Entry' ), f AS ( SELECT ROW_NUMBER() OVER (PARTITION BY EntryNo_ ORDER BY EntryNo_, BinSK) AS row_num FROM w ) DELETE FROM f WHERE f.row_num > 1 SELECT @delete_duplicate_warehouse_entry = @@ROWCOUNT -- 5. update Std_ measures ReferenceNo_ UPDATE f SET f.StdQuantity = (SELECT outValue FROM dbo.ufnConvertUnit(s.[Packing Units],s.[Packing Unit Code], 2, d1.CommodityCode, DEFAULT, DEFAULT)) ,f.StdWeight = (SELECT outValue FROM dbo.ufnConvertUnit(s.[Quantity],s.[Unit of Measure Code], 1, d1.CommodityCode, DEFAULT, DEFAULT)) ,f.StdPaidWeight = (SELECT outValue FROM dbo.ufnConvertUnit(f.PaidWeight, s.[Unit of Measure Code], 1, d1.CommodityCode, DEFAULT, DEFAULT)) ,f.StdInvoicedWeight = (SELECT outValue FROM dbo.ufnConvertUnit(s.[Invoiced Quantity],s.[Unit of Measure Code], 1, d1.CommodityCode, DEFAULT, DEFAULT)) FROM FactInventoryTransaction f INNER JOIN NAV_Staging.dbo.[Item Ledger Entry] s ON f.EntryNo_ = convert(varchar(10), s.[Entry No_]) INNER JOIN DimLot d1 ON d1.LotCode = s.[Lot No_] AND d1.ItemCode = s.[Item No_] AND d1.OP_CMS = 'OP' WHERE f.[Description] <> 'Warehouse Entry' --AND f.LastModifiedDateTimeOffset IS NULL UPDATE f SET f.StdQuantity = (SELECT outValue FROM dbo.ufnConvertUnit(s.[Packing Units],s.[Packing Unit Code], 2, d1.CommodityCode, DEFAULT, DEFAULT)) ,f.StdWeight = (SELECT outValue FROM dbo.ufnConvertUnit(s.[Quantity],s.[Unit of Measure Code], 1, d1.CommodityCode, DEFAULT, DEFAULT)) FROM FactInventoryTransaction f INNER JOIN NAV_Staging.dbo.[Warehouse Entry] s ON f.EntryNo_ = convert(varchar(10), s.[Entry No_]) INNER JOIN DimLot d1 ON d1.LotCode = s.[Lot No_] AND d1.ItemCode = s.[Item No_] AND d1.OP_CMS = 'OP' WHERE f.[Description] = 'Warehouse Entry' --AND f.LastModifiedDateTimeOffset IS NULL UPDATE f SET f.StdNewLotQty = CASE WHEN s.[Entry Type] = 0 OR s.[Entry Type] = 6 THEN f.StdQuantity ELSE NULL END ,f.StdNewLotWeight = CASE WHEN s.[Entry Type] = 0 OR s.[Entry Type] = 6 THEN f.StdWeight ELSE NULL END ,f.StdPaidQty = (SELECT outValue FROM dbo.ufnConvertUnit(f.StdPaidWeight, 'MT', 1, '40', DEFAULT, 'BAG60')) ,f.ReferenceNo_= CASE WHEN s.[Entry Type]=5 OR s.[Entry Type]=6 THEN CONVERT(varchar(20), s.[Prod_ Order Line No_]) WHEN s.[Entry Type]=1 THEN s.[CMS Lot Number] ELSE s.[External Document No_] END FROM FactInventoryTransaction f INNER JOIN NAV_Staging.dbo.[Item Ledger Entry] s ON f.EntryNo_ = convert(varchar(10), s.[Entry No_]) WHERE f.[Description] <> 'Warehouse Entry' --AND f.LastModifiedDateTimeOffset IS NULL --UPDATE FactInventoryTransaction --SET StdPaidWeight = StdWeight --, StdPaidQty = StdQuantity --FROM FactInventoryTransaction --WHERE [Description] = 'Warehouse Entry' --AND LastModifiedDateTimeOffset IS NULL update f set f.PaidWeight = f.[Weight]*(100. - s.[Qty_ Discount %])/100. , f.StdPaidWeight = f.StdWeight*(100. - s.[Qty_ Discount %])/100. , f.DiscountPercent = s.[Qty_ Discount %] from FactInventoryTransaction f, DimQuality q, DimLot d, ( select * from ( select ROW_NUMBER() over (partition by [Item No_], [Lot No_], [Qty_ Discount %] order by [Entry No_]) row_num, [Item No_], [Lot No_] ,case when [Qty_ Discount %] > 100 then 0 else [Qty_ Discount %] end [Qty_ Discount %] from NAV_Staging.dbo.[Item Ledger Entry] where [Entry Type] = 0 -- Purchase Receive and [Qty_ Discount %] <> 0 ) ILE where row_num = 1 ) s where f.QualitySK = q.QualitySK and f.LotSK = d.LotSK and s.[Lot No_] = d.LotCode and s.[Item No_] = q.QualityCode and f.[Description] = 'Warehouse Entry' and f.PaidWeight <> f.[Weight]*(100. - s.[Qty_ Discount %])/100. and f.StdPaidWeight <> f.StdWeight*(100. - s.[Qty_ Discount %])/100. --and f.LastModifiedDateTimeOffset IS NULL -- 6. update for Transfer Shipment Transfer Receive UPDATE f SET f.TransactionSK = (SELECT TransactionSK FROM DimTransaction WHERE TransactionName = 'Transfer Shipment') , f.WeightNoteNo_ = s.[Weight Note No_ Shipment] , f.TransferOrderNo_ = s.[Transfer Order No_] , f.[Description] = s.[Transfer-to Code] , f.SalesContractDK = COALESCE(s.SalesContractDK, 999000000000000) FROM FactInventoryTransaction f INNER JOIN DimBin b ON b.BinSK = f.BinSK INNER JOIN ( SELECT TSL.[Transfer Order No_], TSL.[Document No_], TSL.[Line No_], TSL.[Weight Note No_ Shipment], TSL.[Transfer-to Code], TSH.[Posting Date], TSL.[Transfer-from Bin Code], dsc.SalesContractDK --, TSL.STG_CURRENT_IND, TSL.STG_OPERATION_TYPE, TSL.STG_DWLOAD_TS FROM NAV_Staging.dbo.[Transfer Shipment Line] TSL INNER JOIN NAV_Staging.dbo.[Transfer Shipment Header] TSH ON TSH.[No_] = TSL.[Document No_] LEFT JOIN DimSalesContract dsc ON dsc.SalesContractCode = TSH.[CMS Sales No_] WHERE TSL.STG_CURRENT_IND = '1' AND TSL.STG_OPERATION_TYPE <> 'D' ) s ON f.DocumentNo_ = s.[Document No_] --AND s.[Line No_] = 10000 WHERE f.TransactionSK in (SELECT TransactionSK FROM DimTransaction WHERE TransactionName = 'Reclassification') AND f.WeightNoteNo_ = 'Transfer Shipment' AND b.BinCode = s.[Transfer-from Bin Code] AND f.[Description] <> 'Warehouse Entry' --AND f.LastModifiedDateTimeOffset IS NULL UPDATE f SET f.TransactionSK = (SELECT TransactionSK FROM DimTransaction WHERE TransactionName = 'Transfer Receive') , f.WeightNoteNo_ = s.[Weight Note No_ Receipt] , f.TransferOrderNo_ = s.[Transfer Order No_] --, f.PaidWeight = f.PaidWeight*(1 (s.[Weighed Qty_ Received] - s.[Quantity])/s.[Quantity]) --, f.DiscountPercent = -100*(s.[Weighed Qty_ Received] - s.[Quantity])/s.[Quantity] , f.[Description] = s.[Transfer-from Code] , f.SalesContractDK = COALESCE(s.SalesContractDK, 999000000000000) FROM FactInventoryTransaction f INNER JOIN ( SELECT TRL.[Transfer Order No_], TRL.[Document No_], TRL.[Line No_], TRL.[Weight Note No_ Receipt], TRL.[Transfer-from Code], dsc.SalesContractDK --, TRL.[Weighed Qty_ Received], TRL.[Quantity] --, TRL.STG_CURRENT_IND, TRL.STG_OPERATION_TYPE, TRL.STG_DWLOAD_TS FROM NAV_Staging.dbo.[Transfer Receipt Line] TRL INNER JOIN NAV_Staging.dbo.[Transfer Receipt Header] TRH ON TRH.[No_] = TRL.[Document No_] LEFT JOIN DimSalesContract dsc ON dsc.SalesContractCode = TRH.[CMS Sales No_] WHERE TRL.STG_CURRENT_IND = '1' AND TRL.STG_OPERATION_TYPE <> 'D' ) s ON f.DocumentNo_ = s.[Document No_] AND s.[Line No_] = 10000 WHERE f.TransactionSK in (SELECT TransactionSK FROM DimTransaction WHERE TransactionName = 'Reclassification') AND f.WeightNoteNo_ = 'Transfer Receive' AND f.[Description] <> 'Warehouse Entry' --AND f.LastModifiedDateTimeOffset IS NULL update f set f.WeightNoteNo_ = s.WeightNoteNo_ , f.SalesContractDK = s.SalesContractDK , f.ReasonCode = left(t.TransactionName, 8) -- Positive | Negative from FactInventoryTransaction f, DimTransaction t, (select distinct DocumentNo_, TransferOrderNo_, WeightNoteNo_, SalesContractDK from FactInventoryTransaction where TransactionSK in (select TransactionSK from DimTransaction where TransactionName = 'Transfer Receive') ) s where f.DocumentNo_ = s.DocumentNo_ and f.TransactionSK = t.TransactionSK and t.TransactionName in ('Positive Adjustment', 'Negative Adjustment') and f.WeightNoteNo_ is null and f.TransferOrderNo_ is null and f.[Description] <> 'Warehouse Entry' update f set f.TransactionSK = (select TransactionSK from DimTransaction where TransactionName = 'Transfer Receive') , f.TransferOrderNo_ = s.TransferOrderNo_ , f.[Description] = s.[Description] from FactInventoryTransaction f, (select distinct DocumentNo_, TransferOrderNo_, [Description] from FactInventoryTransaction where TransactionSK in (select TransactionSK from DimTransaction where TransactionName = 'Transfer Receive') ) s where f.DocumentNo_ = s.DocumentNo_ and f.TransactionSK in (SELECT TransactionSK FROM DimTransaction WHERE TransactionName in ('Positive Adjustment', 'Negative Adjustment', 'Reclassification') ) and f.TransferOrderNo_ is null and f.[Description] <> 'Warehouse Entry' -- 7. Update WeightNoteNo_ update f set f.WeightNoteNo_ = s.[Document 2 No_] from FactInventoryTransaction f, (SELECT DR.*, SL.[CMS Lot Number] FROM (SELECT DISTINCT [Document 1 Type], [Document 2 Type] , [Document 1 No_], [Document 1 Line No_], [Document 2 No_] FROM NAV_Staging.dbo.[Document Relation] WHERE [Document 1 Source] = 1 -- Sales AND [Document 1 Type] = 1 -- Order ) DR INNER JOIN NAV_Staging.dbo.[Sales Line] SL ON DR.[Document 1 No_] = SL.[Document No_] AND DR.[Document 1 Line No_] = SL.[Line No_] AND SL.[Call line Executed] = 1 ) s where f.TransactionSK in (select TransactionSK from DimTransaction where TransactionName = 'Sales Order Call') and f.ReferenceNo_ = s.[CMS Lot Number] and f.WeightNoteNo_ IS NULL and f.[Description] <> 'Warehouse Entry' UPDATE FactInventoryTransaction SET WeightNoteNo_= NULL WHERE WeightNoteNo_ LIKE 'Transfer%' UPDATE f SET f.TransactionSK = (SELECT TransactionSK FROM DimTransaction WHERE TransactionName = 'Transfer Shipment') , f.WeightNoteNo_ = s.[Weight Note No_ Shipment] , f.TransferOrderNo_ = s.[Transfer Order No_] , f.[Description] = s.[Transfer-to Code] , f.SalesContractDK = COALESCE(s.SalesContractDK, 999000000000000) FROM FactInventoryTransaction f INNER JOIN ( SELECT TSL.[Transfer Order No_], TSL.[Document No_], TSL.[Line No_], TSL.[Weight Note No_ Shipment], TSL.[Transfer-to Code], dsc.SalesContractDK , TSL.STG_CURRENT_IND, TSL.STG_OPERATION_TYPE, TSL.STG_DWLOAD_TS FROM NAV_Staging.dbo.[Transfer Shipment Line] TSL INNER JOIN NAV_Staging.dbo.[Transfer Shipment Header] TSH ON TSH.[No_] = TSL.[Document No_] LEFT JOIN DimSalesContract dsc ON dsc.SalesContractCode = TSH.[CMS Sales No_] ) s ON f.DocumentNo_ = s.[Document No_] AND s.[Line No_] = 10000 WHERE f.TransactionSK in (SELECT TransactionSK FROM DimTransaction WHERE TransactionName = 'Reclassification') AND f.WeightNoteNo_ IS NULL AND s.STG_CURRENT_IND = '1' AND s.STG_OPERATION_TYPE <> 'D' AND f.[Description] <> 'Warehouse Entry' UPDATE FactInventoryTransaction SET WeightNoteNo_= NULL WHERE TransactionSK = (SELECT TransactionSK FROM DimTransaction WHERE TransactionName = 'Transfer Shipment') AND BinSK = 999000000000000 AND [Description] <> 'Warehouse Entry' update f set f.WeightNoteNo_ = s.WeightNoteNo_ from FactInventoryTransaction f, (select distinct WeightNoteNo_, DocumentNo_, TransferOrderNo_ from FactInventoryTransaction where TransactionSK in (select TransactionSK from DimTransaction where TransactionName = 'Transfer Receive') and WeightNoteNo_ is not null ) s where f.DocumentNo_ = s.DocumentNo_ and f.TransactionSK in (SELECT TransactionSK FROM DimTransaction WHERE TransactionName = 'Transfer Receive') and f.[Weight] > 0 and f.WeightNoteNo_ is null and f.[Description] <> 'Warehouse Entry' -- 8. Update Shipping Advice [No_] to Description for [Sales Order Call] ; with t as ( select f.InventoryTransactionSK, f.EntryNo_, fst.ReferenceNo_ SalesAdviceNo_ from FactInventoryTransaction f, FactSalesTransaction fst where f.TransactionSK in (select TransactionSK from DimTransaction where TransactionName = 'Sales Order Call') and fst.TransactionSK in (select TransactionSK from DimTransaction where TransactionName = 'CMS Lot') and fst.CMS_LotNo_ = f.ReferenceNo_ ), s as ( select ROW_NUMBER() over (partition by EntryNo_ order by EntryNo_) row_num , * from t ) --select * from s where row_num = 1 update f set f.[Description] = s.SalesAdviceNo_ from FactInventoryTransaction f, s where f.InventoryTransactionSK = s.InventoryTransactionSK and s.row_num = 1 and f.[Description] <> 'Warehouse Entry' --and f.LastModifiedDateTimeOffset IS NULL -- 9. Update SalesContractDK update f set f.SalesContractDK = s.SalesContractDK from FactInventoryTransaction f inner join DimProductionOrder d on f.ProductionOrderSK = d.ProductionOrderSK and f.DocumentNo_ = d.ProductionOrderCode inner join DimSalesContract s on s.SalesContractCode = d.SalesContractCode where f.[Description] <> 'Warehouse Entry' and f.SalesContractDK in (999000000000000, 999000000000001) SELECT @delete_duplicate delete_duplicate_count, @warehouse_entry warehouse_entry_count, @delete_duplicate_warehouse_entry delete_duplicate_warehouse_entry_count
