Nhà sách ADCBOOK

XỬ LÝ DANH MỤC VẬT TƯ

DECLARE @_PHAN_LOAI VARCHAR(24) = 'RTL', @_PHAN_LOAI_ID INT
 
SET @_PHAN_LOAI_ID = (SELECT TOP(1) Id FROM dbo.B20CustomField3 WHERE Code = @_PHAN_LOAI)
 
IF OBJECT_ID(N'TempDb..#ImportItem') IS NOT NULL DROP TABLE #ImportItem
SELECT * INTO #ImportItem
FROM dbo.[20250520_ItemRTL$]
UNION ALL
SELECT *
FROM dbo.[20250520_ItemWHSL$]
 
	UPDATE #ImportItem
		SET NHOM_HANG = '1'+CONVERT(VARCHAR(24),NHOM_HANG), NGANH_HANG = '1'+CONVERT(VARCHAR(24),NGANH_HANG)
		WHERE PHAN_LOAI = 'RTL'
 
	UPDATE #ImportItem
		SET NHOM_HANG = '2'+CONVERT(VARCHAR(24),NHOM_HANG), NGANH_HANG = '2'+CONVERT(VARCHAR(24),NGANH_HANG)
		WHERE PHAN_LOAI = 'WHSL'
 
	UPDATE #ImportItem
		SET MA_HH = NULL WHERE MA_HH = 'null'
 
	UPDATE #ImportItem
		SET MA_NCC = NULL WHERE MA_NCC IN (N'#N/A',N'NULL')
 
	UPDATE #ImportItem
		SET MA_VT = TRIM(MA_VT), MA_HH = TRIM(MA_HH), BARCODE = TRIM(BARCODE),
			TEN_VT = TRIM(TEN_VT), DVT = TRIM(DVT), DVT1 = TRIM(DVT1),
			MA_NCC = UPPER(TRIM(MA_NCC))
 
	UPDATE #ImportItem SET TEN_VT = REPLACE(TEN_VT, CHAR(10), ' ')
 
	UPDATE #ImportItem SET TEN_VT = REPLACE(TEN_VT, CHAR(13), ' ')
 
	UPDATE #ImportItem SET THUE_DAU_VAO = LOAI_THUE WHERE THUE_DAU_VAO = 'null'
 
	UPDATE #ImportItem SET THUE_DAU_RA = LOAI_THUE WHERE THUE_DAU_RA = 'null'
 
	IF OBJECT_ID(N'TempDb..#ImportItem1') IS NOT NULL DROP TABLE #ImportItem1
	SELECT d.MA_VT AS ItemCode, d.MA_HH AS IntCode, d.BARCODE AS BarCode, d.TEN_VT AS ItemName,
		d.DVT AS Unit, d.DVT1 AS Unit0, d.TY_LE1 AS ConvertRate0,
		d.NGANH_HANG AS ItemCatgCode, d.NHOM_HANG AS ItemGroupCode,
		d.MA_NCC AS CustomerCode,
		ISNULL(d.GIA_MUA,0) AS PurchasePrice, ISNULL(d.GIA_GOC,0) AS OriginalPrice,
		ISNULL(d.GIA_VON,0) AS UnitCost, CONVERT(NUMERIC(18,4),ISNULL(d.GIA_BAN,0)) AS SalesPrice,
		d.LOAI_THUE AS LOAI_THUE, d.THUE_DAU_VAO, d.THUE_DAU_RA,
		CASE
			WHEN d.THUE_DAU_VAO IN ('K00') THEN ''
			WHEN d.THUE_DAU_VAO IN ('T00') THEN 'V00'
			WHEN d.THUE_DAU_VAO IN ('5','05','T05') THEN 'V05'
			WHEN d.THUE_DAU_VAO IN ('8','08','T08') THEN 'V08'
			WHEN d.THUE_DAU_VAO IN ('10','T10') THEN 'V10'
			ELSE ''
		END AS PurchaseTaxCode,
		CASE
			WHEN d.LOAI_THUE IN ('K00') THEN 'R00K'
			WHEN d.THUE_DAU_RA IN ('0') THEN 'R00'
			WHEN d.THUE_DAU_RA IN ('5') THEN 'R05A'
			WHEN d.THUE_DAU_RA IN ('8') THEN 'R08A'
			WHEN d.THUE_DAU_RA IN ('10') THEN 'R10A'
			ELSE ''
		END AS SalesTaxCode,
		supplier.Id AS SupplierId, ParentItem.Id AS ParentId,
		ItemCatg.Id AS ItemCatgId, ItemGroup.Id AS ItemGroupId,
		@_PHAN_LOAI_ID AS CustomFieldId3, d.PHAN_LOAI AS CustomFieldCode3
	INTO #ImportItem1
	FROM dbo.#ImportItem d
		LEFT OUTER JOIN dbo.vB20Customer supplier ON supplier.Code = d.MA_NCC AND supplier.CustomFieldCode3 = d.PHAN_LOAI
		LEFT OUTER JOIN dbo.B20ItemCatg ItemCatg ON ItemCatg.Code = d.NGANH_HANG
		LEFT OUTER JOIN dbo.B20ItemGroup ItemGroup ON ItemGroup.Code = d.NHOM_HANG
		LEFT OUTER JOIN dbo.B20Item ParentItem ON ParentItem.Code = d.NHOM_HANG
	WHERE d.PHAN_LOAI = @_PHAN_LOAI
 
	UPDATE #ImportItem1
		SET Unit = UPPER(LEFT(Unit,1))+RIGHT(LOWER(Unit),LEN(Unit)-1), Unit0 = UPPER(LEFT(Unit0,1))+RIGHT(LOWER(Unit0),LEN(Unit0)-1)
 
	SELECT * FROM #ImportItem1
 
DROP TABLE #ImportItem1
DROP TABLE #ImportItem

KIỂM TRA TRÙNG CODE HOẶC BARCODE TRONG DANH MỤC VẬT TƯ

DECLARE @_PHAN_LOAI VARCHAR(24) = 'RTL', @_PHAN_LOAI_ID INT
 
SET @_PHAN_LOAI_ID = (SELECT TOP(1) Id FROM dbo.B20CustomField3 WHERE Code = @_PHAN_LOAI)
 
;WITH cte AS (
	SELECT Code, IntCode, BarCode, Name, SalesPrice, Unit, Loai_Ps,
		IsActive, CreatedBy, CreatedAt, ROW_NUMBER() OVER(PARTITION BY Code ORDER BY IsActive DESC) AS Stt_Order
	FROM dbo.B20Item
	WHERE CustomFieldId3 = @_PHAN_LOAI_ID
)
SELECT * FROM cte WHERE cte.Code IN (SELECT cte.Code FROM cte WHERE cte.Stt_Order >= 2) ORDER BY cte.Code ASC, cte.IsActive DESC
 
;WITH cte AS (
	SELECT Code, IntCode, BarCode, Name, SalesPrice, Unit, Loai_Ps,
		IsActive, CreatedBy, CreatedAt, ROW_NUMBER() OVER(PARTITION BY BarCode ORDER BY IsActive DESC) AS Stt_Order
	FROM dbo.B20Item
	WHERE CustomFieldId3 = @_PHAN_LOAI_ID
)
SELECT * FROM cte WHERE cte.BarCode IN (SELECT cte.BarCode FROM cte WHERE cte.Stt_Order >= 2) ORDER BY cte.BarCode ASC, cte.IsActive DESC

GIÁ NHẬP GẦN NHẤT TỪ NGÂN LONG

DECLARE @_PHAN_LOAI VARCHAR(24) = 'RTL', @_PHAN_LOAI_ID INT
 
SET @_PHAN_LOAI_ID = (SELECT TOP(1) Id FROM dbo.B20CustomField3 WHERE Code = @_PHAN_LOAI)
 
IF OBJECT_ID(N'TempDb..#AccDocPurchaseLatest') IS NOT NULL DROP TABLE #AccDocPurchaseLatest
;WITH cte AS (
	SELECT d.*, cust.Id AS CustomerId, Item.Id AS ItemId
	FROM dbo.[GIA_NHAP_GAN_NHAT$] d
		LEFT OUTER JOIN dbo.B20Item Item ON Item.Code = d.MAVT AND Item.CustomFieldId3 = @_PHAN_LOAI_ID
		LEFT OUTER JOIN dbo.B20Customer cust ON cust.Code = d.MANCC AND cust.CustomFieldId3 = @_PHAN_LOAI_ID
)
SELECT a.Id, a.BranchCode, a.Stt, a.DocCode, a.DocDate, a.CustomerId, cte.MANCC, a.ItemId, cte.MAVT,
	cte.HOADON_GIA AS HINH_THUC_GIA, cte.THUE_SUAT, cte.DONGIA AS DON_GIA, cte.TL_CK,
	IIF(ISNULL(cte.TL_CK,0)<>0, CONVERT(NUMERIC(18,5), (cte.DONGIA-(cte.DONGIA*(cte.TL_CK/100)))), CONVERT(NUMERIC(18,5), cte.DONGIA)) AS DON_GIA_SAU_CK,
	CAST(0 AS NUMERIC(18,5)) AS UnitCost,
	CAST(0 AS NUMERIC(18,5)) AS UnitCostInclVAT
INTO #AccDocPurchaseLatest
FROM dbo.B30AccDocPurchaseLatest a
	INNER JOIN cte ON a.CustomerId = cte.CustomerId AND a.ItemId = cte.ItemId
WHERE ISNULL(a.Stt,'') = '' AND cte.HOADON_GIA IN ('01','02','03')
ORDER BY a.DocDate DESC
 
UPDATE #AccDocPurchaseLatest
SET UnitCost =
				CASE
					WHEN HINH_THUC_GIA = '01' THEN DON_GIA_SAU_CK
					WHEN HINH_THUC_GIA = '02' THEN DON_GIA_SAU_CK
					WHEN HINH_THUC_GIA = '03' THEN ROUND(DON_GIA_SAU_CK/(1+(THUE_SUAT/100)),5)
				END,
	UnitCostInclVAT =
				CASE
					WHEN HINH_THUC_GIA = '01' THEN DON_GIA_SAU_CK
					WHEN HINH_THUC_GIA = '02' THEN ROUND(DON_GIA_SAU_CK*(1+(THUE_SUAT/100)),5)
					WHEN HINH_THUC_GIA = '03' THEN DON_GIA_SAU_CK
				END
 
SELECT * FROM #AccDocPurchaseLatest ORDER BY DocDate DESC
 
/*
UPDATE dbo.B30AccDocPurchaseLatest
SET PurchasePrice = ISNULL(b.DON_GIA,0),
	OriginalPurchasePrice = ISNULL(b.DON_GIA,0),
	UnitCost = ISNULL(b.UnitCost,0),
	OriginalUnitCost = ISNULL(b.UnitCost,0),
	UnitCostInclVAT = ISNULL(b.UnitCostInclVAT,0),
	OriginalUnitCostInclVAT = ISNULL(b.UnitCostInclVAT,0),
	DiscountRate = ROUND(ISNULL(b.TL_CK,0)/100,5),
	DocDate = b.DocDate
FROM dbo.B30AccDocPurchaseLatest a
	INNER JOIN #AccDocPurchaseLatest b ON a.Id = b.Id
WHERE ISNULL(a.Stt, '') = ''
*/
 
DROP TABLE #AccDocPurchaseLatest

GIÁ NHẬP, GIÁ VỐN GẦN NHẤT

DECLARE @_BranchCode VARCHAR(24) = 'A01', @_DocDate1 DATE = '2025-01-01', @_DocDate2 DATE = '2025-12-31'
 
IF OBJECT_ID(N'TempDb..#AccDocPurchaseLatest') IS NOT NULL DROP TABLE #AccDocPurchaseLatest
SELECT d.BranchCode, d.Stt, MAX(d.DocCode) AS DocCode, MAX(d.DocDate) AS DocDate, MAX(d.DocNo) AS DocNo,
	d.CustomerId, d.ItemId, SUM(d.Quantity) AS Quantity, SUM(d.Quantity9) AS Quantity9,
	-- Giá nhập ban đầu;
	MAX(d.OriginalUnitCost) AS OriginalPurchasePrice, MAX(d.UnitCost) AS PurchasePrice, MAX(d.DiscountRate) AS DiscountRate,
	-- Giá vốn sau chiết khấu và KHÔNG BAO GỒM THUẾ;
	CASE WHEN SUM(d.Quantity) = 0 THEN 0 ELSE CONVERT(NUMERIC(16,5), SUM(d.OriginalAmount)/SUM(d.Quantity)) END AS OriginalUnitCost,
	CASE WHEN SUM(d.Quantity) = 0 THEN 0 ELSE CONVERT(NUMERIC(16,5), SUM(d.Amount)/SUM(d.Quantity)) END AS UnitCost,
	-- Giá vốn sau chiết khấu và BAO GỒM THUẾ;
	CASE WHEN SUM(d.Quantity) = 0 THEN 0 ELSE CONVERT(NUMERIC(16,5), SUM(d.OriginalAmount+d.OriginalAmount3)/SUM(d.Quantity)) END AS OriginalUnitCostInclVAT,
	CASE WHEN SUM(d.Quantity) = 0 THEN 0 ELSE CONVERT(NUMERIC(16,5), SUM(d.Amount+d.Amount3)/SUM(d.Quantity)) END AS UnitCostInclVAT
INTO #AccDocPurchaseLatest
FROM (
	SELECT Ct.BranchCode, Ct.Stt, Ct.DocCode, Ct.DocDate, Ct.DocNo, Ct0.CustomerId, Ct0.WarehouseId, Ct0.ItemId, Ct0.Unit,
		Ct0.Quantity, Ct0.ConvertRate9, Ct0.Quantity9, Ct0.UnitCost, Ct0.OriginalUnitCost, Ct0.DiscountRate,
		Ct0.OriginalAmount9, Ct0.Amount9, Ct0.OriginalAmount, Ct0.Amount, Ct0.OriginalAmount3, Ct0.Amount3,
		Ct0.TaxCode, Ct0.TaxRate, tax.PriceIncludeTax, Ct.IsActive,
		ROW_NUMBER() OVER(PARTITION BY Ct.CustomerId, Ct0.ItemId ORDER BY Ct.DocDate DESC) AS Stt_Order
	FROM dbo.B30AccDocPurchase1 Ct0
		INNER JOIN dbo.B30AccDocPurchase Ct ON Ct.Stt = Ct0.Stt
		INNER JOIN dbo.B00DocStatus DocStatus ON DocStatus.DocStatusKey = Ct.DocStatus
		LEFT OUTER JOIN dbo.B20Tax tax ON tax.Code = Ct0.TaxCode
	WHERE Ct.IsActive = 1 AND Ct.BranchCode = @_BranchCode
		AND Ct.DocCode = 'NM' AND Ct0.WarehouseId IS NOT NULL
		AND Ct.DocDate BETWEEN @_DocDate1 AND @_DocDate2
		AND Ct0.Amount <> 0
) d
WHERE d.Stt_Order = 1
GROUP BY d.BranchCode, d.Stt, d.CustomerId, d.ItemId
 
	/* THÊM MỚI DỮ LIỆU */
	--INSERT INTO dbo.B30AccDocPurchaseLatest (
	--	BranchCode, Stt, DocCode, DocDate, CustomerId,
	--	ItemId, OriginalPurchasePrice, PurchasePrice,
	--	OriginalUnitCost, UnitCost,
	--	OriginalUnitCostInclVAT, UnitCostInclVAT,
	--	DiscountRate,
	--	IsActive, CreatedBy, CreatedAt
	--)
	SELECT ct0.BranchCode, ct0.Stt, ct0.DocCode, ct0.DocDate, ct0.CustomerId,
		ct0.ItemId, ct0.OriginalPurchasePrice, ct0.PurchasePrice,
		ct0.OriginalUnitCost, ct0.UnitCost,
		ct0.OriginalUnitCostInclVAT, ct0.UnitCostInclVAT, ct0.DiscountRate,
		'1' AS Active, '-1' AS CreatedBy, GETUTCDATE() AS CreatedAt
	FROM #AccDocPurchaseLatest Ct0
		LEFT OUTER JOIN dbo.B30AccDocPurchaseLatest lst ON Ct0.CustomerId = lst.CustomerId AND Ct0.ItemId = lst.ItemId
	WHERE ISNULL(lst.ItemId,0) = 0
	ORDER BY lst.DocDate DESC
 
	/* CẬP NHẬT DỮ LIỆU */
	SELECT a.BranchCode, a.CustomerId, a.ItemId, Item.Name AS ItemNam, Item.Unit AS Unit,
		a.UnitCost, b.UnitCost, a.UnitCostInclVAT, b.UnitCostInclVAT
	FROM dbo.B30AccDocPurchaseLatest a
		INNER JOIN dbo.B20Item Item ON Item.Id = a.ItemId
		INNER JOIN #AccDocPurchaseLatest b ON a.CustomerId = b.CustomerId AND a.ItemId = b.ItemId
	WHERE (a.UnitCost <> b.UnitCost)
 
	--UPDATE dbo.B30AccDocPurchaseLatest
	--SET UnitCost = b.UnitCost, OriginalUnitCost = b.OriginalUnitCost
	--FROM dbo.B30AccDocPurchaseLatest a
	--	INNER JOIN dbo.B20Item Item ON Item.Id = a.ItemId
	--	INNER JOIN #AccDocPurchaseLatest b ON a.CustomerId = b.CustomerId AND a.ItemId = b.ItemId
	--WHERE (a.UnitCost <> b.UnitCost)
 
	SELECT a.BranchCode, a.CustomerId, a.ItemId, Item.Name AS ItemNam, Item.Unit AS Unit,
		a.UnitCost, b.UnitCost, a.UnitCostInclVAT, b.UnitCostInclVAT
	FROM dbo.B30AccDocPurchaseLatest a
		INNER JOIN dbo.B20Item Item ON Item.Id = a.ItemId
		INNER JOIN #AccDocPurchaseLatest b ON a.CustomerId = b.CustomerId AND a.ItemId = b.ItemId
	WHERE (a.UnitCostInclVAT <> b.UnitCostInclVAT)
 
	--UPDATE dbo.B30AccDocPurchaseLatest
	--SET UnitCostInclVAT = b.UnitCostInclVAT, OriginalUnitCostInclVAT = b.OriginalUnitCostInclVAT
	--FROM dbo.B30AccDocPurchaseLatest a
	--	INNER JOIN dbo.B20Item Item ON Item.Id = a.ItemId
	--	INNER JOIN #AccDocPurchaseLatest b ON a.CustomerId = b.CustomerId AND a.ItemId = b.ItemId
	--WHERE (a.UnitCostInclVAT <> b.UnitCostInclVAT)
 
DROP TABLE #AccDocPurchaseLatest

TỒN KHO ĐẦU KỲ

DECLARE @_PHAN_LOAI VARCHAR(24) = 'RTL', @_PHAN_LOAI_ID INT,
	@_DocDate DATE = '2024-12-31',
	@_FiscalYear VARCHAR(24) = '2025',
	@_Execute TINYINT = 0
SET @_PHAN_LOAI_ID = (SELECT TOP(1) Id FROM dbo.B20CustomField3 WHERE Code = @_PHAN_LOAI)
 
	IF OBJECT_ID(N'TempDb..#OpenInventoryData') IS NOT NULL DROP TABLE #OpenInventoryData
	;WITH cte AS (
		SELECT inv.MA_KHO, wh.Id AS ID_KHO, inv.BARCODE, item.Id AS ID_VT, item.Name AS TEN_VT,
			ROUND(inv.SO_LUONG,0) AS SO_LUONG, ROUND(inv.GIA_TRI,0) AS GIA_TRI
		FROM dbo.[CAP_NHAT_TON_KHO_115$] inv
			LEFT OUTER JOIN dbo.B20Warehouse wh ON wh.Code = inv.MA_KHO AND wh.CustomFieldId3 = @_PHAN_LOAI_ID
			LEFT OUTER JOIN dbo.B20Item item ON item.Code = inv.BARCODE AND item.CustomFieldId3 = @_PHAN_LOAI_ID
		WHERE ISNULL(inv.BARCODE, '') <> ''
	)
	SELECT @_DocDate AS DocDate, @_FiscalYear AS FiscalYear, 'A01' AS BranhCode,
		Wh.Id AS WarehouseId, Wh.Code AS WarehouseCode, Wh.Name AS WarehouseName,
		Item.Id AS ItemId, Item.Code AS ItemCode, Item.IntCode, a.BARCODE, Item.Name AS ItemName, Item.Unit AS ItemUnit,
		ISNULL(b.Quantity,0) AS OLDQuantity, CONVERT(NUMERIC(18,4), ROUND(ISNULL(a.SO_LUONG,0),0)) AS NEWQuantity,
		CAST(0 AS NUMERIC(18,4)) AS UnitCost, a.GIA_TRI AS Amount, a.GIA_TRI AS CloseAmount13,
		CAST(NULL AS VARCHAR(24)) AS InventoryId, b.InventoryId AS OLDInventoryId
	INTO #OpenInventoryData
	FROM cte AS a
		LEFT OUTER JOIN dbo.B30OpenInventory b ON a.ID_KHO = b.WarehouseId AND a.ID_VT = b.ItemId
		LEFT OUTER JOIN dbo.B20Warehouse Wh ON Wh.Code = a.MA_KHO AND Wh.CustomFieldId3 = @_PHAN_LOAI_ID
		LEFT OUTER JOIN dbo.B20Item Item ON Item.Code = a.BARCODE AND Item.CustomFieldId3 = @_PHAN_LOAI_ID
	ORDER BY wh.Code ASC, item.Name ASC
 
	UPDATE #OpenInventoryData
		SET UnitCost = IIF(ISNULL(NEWQuantity,0)<>0, ROUND(Amount/NEWQuantity,4), 0)
 
	IF @_Execute = 0
		SELECT *, ROW_NUMBER() OVER(PARTITION BY BarCode ORDER BY ItemId DESC) AS Stt_Order FROM #OpenInventoryData ORDER BY ItemName ASC
 
	/*
	IF OBJECT_ID(N'TempDb..#OpenInventoryData1') IS NOT NULL DROP TABLE #OpenInventoryData1
	SELECT * INTO #OpenInventoryData1 FROM #OpenInventoryData
	WHERE WarehouseId IS NOT NULL AND ISNULL(OLDInventoryId,'') <> ''
		AND ISNULL(OLDQuantity,0) <> ISNULL(NEWQuantity,0)
 
	SELECT * FROM #OpenInventoryData1 ORDER BY WarehouseCode ASC
 
	IF EXISTS(SELECT 1 FROM #OpenInventoryData1) AND @_Execute = 1
	BEGIN
		-- CẬP NHẬT TỒN KHO ĐẦU KỲ NẾU TỒN TRƯỚC ĐÓ KHÁC VỚI HIỆN TẠI;
		UPDATE dbo.B30OpenInventory1
		SET Quantity = ISNULL(b.NEWQuantity,0), CloseInventory13 = ISNULL(b.NEWQuantity,0),
			UnitCost = ISNULL(b.UnitCost,0), Amount = ISNULL(b.Amount,0), CloseAmount13 = ISNULL(b.Amount,0),
			ModifiedBy = -1, ModifiedAt = GETUTCDATE()
		FROM dbo.B30OpenInventory1 a
			INNER JOIN #OpenInventoryData1 b ON a.WarehouseId = b.WarehouseId AND a.ItemId = b.ItemId AND a.InventoryId = b.OLDInventoryId
		WHERE a.DocDate = @_DocDate AND [a].[Year] = @_FiscalYear
			AND a.WarehouseId = b.WarehouseId AND a.ItemId = b.ItemId AND a.InventoryId = b.OLDInventoryId
 
		UPDATE dbo.B30OpenInventory
		SET Quantity = b.Quantity, Amount = b.Amount
		FROM dbo.B30OpenInventory a
			INNER JOIN (
				SELECT InventoryId, SUM(Quantity) AS Quantity, SUM(Amount) AS Amount
				FROM dbo.B30OpenInventory1
				GROUP BY InventoryId
			) b ON a.InventoryId = b.InventoryId
			INNER JOIN #OpenInventoryData1 c ON c.WarehouseId = a.WarehouseId AND c.ItemId = a.ItemId AND c.OLDInventoryId = a.InventoryId
		WHERE a.InventoryId = b.InventoryId
	END
 
	DROP TABLE #OpenInventoryData1
	*/
 
	/*
	IF OBJECT_ID(N'TempDb..#OpenInventoryData2') IS NOT NULL DROP TABLE #OpenInventoryData2
	SELECT * INTO #OpenInventoryData2 FROM #OpenInventoryData
	WHERE ISNULL(OLDInventoryId,'') = ''
 
	IF EXISTS(SELECT 1 FROM #OpenInventoryData2) AND @_Execute = 1
	BEGIN
		-- THÊM MỚI TỒN KHO ĐẦU KỲ;
		DECLARE @_B30OpenInventoryId INT
	
		SET @_B30OpenInventoryId = IDENT_CURRENT('B10_ADC_Data.dbo.B30OpenInventory')
 
		INSERT INTO dbo.B30OpenInventory (
			InventoryId, BranchCode, FiscalYear, WarehouseId, ItemId, Quantity, Amount
		)
		SELECT RTRIM('0') + FORMAT(@_B30OpenInventoryId+ROW_NUMBER() OVER(ORDER BY d.FiscalYear, d.WarehouseId, d.ItemId ASC), '#000000') AS InventoryId
			,d.BranhCode AS BranchCode, d.FiscalYear AS FiscalYear
			,d.WarehouseId AS WarehouseId, d.ItemId AS ItemId
			,SUM(d.NEWQuantity) AS Quantity, ISNULL(SUM(d.Amount),0) AS Amount
		FROM #OpenInventoryData2 d
		GROUP BY d.BranhCode, d.FiscalYear, d.WarehouseId, d.ItemId
 
		UPDATE d
		SET d.InventoryId = OpenInventory.InventoryId
		FROM #OpenInventoryData2 d
			INNER JOIN dbo.B30OpenInventory OpenInventory
				ON OpenInventory.BranchCode = d.BranhCode AND OpenInventory.FiscalYear = d.FiscalYear
					AND OpenInventory.WarehouseId = d.WarehouseId AND OpenInventory.ItemId = d.ItemId
 
		INSERT INTO dbo.B30OpenInventory1 (
			BranchCode, InventoryId, Year, Stt_Ntxt, DocDate,
			WarehouseId, ItemId, Quantity, CloseInventory13,
			UnitCost, Amount, CloseAmount13
		)
		SELECT d.BranhCode AS BranchCode, MAX(d.InventoryId) AS InventoryId, d.FiscalYear AS [Year], '' AS Stt_Ntxt, MAX(d.DocDate) AS DocDate,
			d.WarehouseId AS WarehouseId, d.ItemId AS ItemId, SUM(d.NEWQuantity) AS Quantity, SUM(d.NEWQuantity) AS CloseInventory13,
				MAX(d.UnitCost) AS UnitCost, SUM(d.Amount) AS Amount, SUM(d.CloseAmount13) AS CloseAmount13
		FROM #OpenInventoryData2 d
		WHERE ISNULL(d.InventoryId, '') <> ''
		GROUP BY d.BranhCode, d.FiscalYear, d.WarehouseId, d.ItemId
	END
 
	DROP TABLE #OpenInventoryData2
	*/
 
DROP TABLE #OpenInventoryData

SỐ DƯ ĐẦU KỲ TÀI KHOẢN

DECLARE @_PHAN_LOAI VARCHAR(24) = 'WHSL', @_PHAN_LOAI_ID INT, @_BranchCode VARCHAR(24) = 'A01'
SET @_PHAN_LOAI_ID = (SELECT TOP(1) Id FROM dbo.B20CustomField3 WHERE Code = @_PHAN_LOAI)
 
/* INSERT INTO dbo.B30OpenBalance (BranchCode, [Year], Account, CustomerId, DebitAmount, DebitYearBal, CreditAmount, CreditYearBal, CustomFieldId3) */
IF OBJECT_ID(N'TempDb..#OpenBalanceData') IS NOT NULL DROP TABLE #OpenBalanceData
SELECT @_BranchCode AS BranchCode, YEAR(GETDATE()) AS [Year], '13111' AS Account, cust.Id AS CustomerId,
	IIF(obl.SO_TIEN>0, ROUND(ABS(obl.SO_TIEN),0), 0) AS DebitAmount, IIF(obl.SO_TIEN>0, ROUND(ABS(obl.SO_TIEN),0), 0) AS DebitYearBal,
	IIF(obl.SO_TIEN<0, ROUND(ABS(obl.SO_TIEN),0), 0) AS CreditAmount, IIF(obl.SO_TIEN<0, ROUND(ABS(obl.SO_TIEN),0), 0) AS CreditYearBal,
	@_PHAN_LOAI_ID AS CustomFieldId3
INTO #OpenBalanceData
FROM dbo.[20250618_OpenBalance$] obl
	LEFT OUTER JOIN dbo.B20Customer cust ON cust.Code = obl.MA_NCC AND cust.CustomFieldId3 = @_PHAN_LOAI_ID
WHERE obl.PHAN_LOAI = @_PHAN_LOAI AND obl.TAI_KHOAN LIKE '131%'
UNION ALL
SELECT @_BranchCode AS BranchCode, YEAR(GETDATE()) AS [Year], '3311' AS Account, cust.Id AS CustomerId,
	IIF(obl.SO_TIEN<0, ROUND(ABS(obl.SO_TIEN),0), 0) AS DebitAmount, IIF(obl.SO_TIEN<0, ROUND(ABS(obl.SO_TIEN),0), 0) AS DebitYearBal,
	IIF(obl.SO_TIEN>0, ROUND(ABS(obl.SO_TIEN),0), 0) AS CreditAmount, IIF(obl.SO_TIEN>0, ROUND(ABS(obl.SO_TIEN),0), 0) AS CreditYearBal,
	@_PHAN_LOAI_ID AS CustomFieldId3
FROM dbo.[20250618_OpenBalance$] obl
	LEFT OUTER JOIN dbo.B20Customer cust ON cust.Code = obl.MA_NCC AND cust.CustomFieldId3 = @_PHAN_LOAI_ID
WHERE obl.PHAN_LOAI = @_PHAN_LOAI AND obl.TAI_KHOAN LIKE '331%'
UNION ALL
SELECT @_BranchCode AS BranchCode, YEAR(GETDATE()) AS [Year], '3311' AS Account, cust.Id AS CustomerId,
	IIF(obl.SO_TIEN<0, ROUND(ABS(obl.SO_TIEN),0), 0) AS DebitAmount, IIF(obl.SO_TIEN<0, ROUND(ABS(obl.SO_TIEN),0), 0) AS DebitYearBal,
	IIF(obl.SO_TIEN>0, ROUND(ABS(obl.SO_TIEN),0), 0) AS CreditAmount, IIF(obl.SO_TIEN>0, ROUND(ABS(obl.SO_TIEN),0), 0) AS CreditYearBal,
	@_PHAN_LOAI_ID AS CustomFieldId3
FROM dbo.[20250618_OpenBalance$] obl
	LEFT OUTER JOIN dbo.B20Customer cust ON cust.Code = obl.MA_NCC AND cust.CustomFieldId3 = @_PHAN_LOAI_ID
WHERE obl.PHAN_LOAI = @_PHAN_LOAI AND ISNULL(obl.TAI_KHOAN,'') = ''
 
SELECT  b.Id AS OpenBalanceID, a.*
FROM dbo.#OpenBalanceData a
	LEFT OUTER JOIN dbo.B30OpenBalance b ON a.BranchCode = b.BranchCode AND a.Year = b.Year AND a.CustomFieldId3 = b.CustomFieldId3
		AND a.Account = b.Account AND a.CustomerId = b.CustomerId
 
--UPDATE dbo.B30OpenBalance
--SET DebitAmount = ISNULL(b.DebitAmount,0), DebitYearBal = ISNULL(b.DebitYearBal,0),
--	CreditAmount = ISNULL(b.CreditAmount,0), CreditYearBal = ISNULL(b.CreditYearBal,0)
--FROM dbo.B30OpenBalance a
--	INNER JOIN #OpenBalanceData b ON a.BranchCode = b.BranchCode AND a.Year = b.Year AND a.CustomFieldId3 = b.CustomFieldId3
--		AND a.Account = b.Account AND a.CustomerId = b.CustomerId
--WHERE a.Account = b.Account AND a.CustomerId = b.CustomerId
 
/*
INSERT INTO dbo.B30OpenBalance (BranchCode, [Year], Account, CustomerId, DebitAmount, DebitYearBal, CreditAmount, CreditYearBal, CustomFieldId3)
SELECT a.BranchCode, a.[Year], a.Account, a.CustomerId, a.DebitAmount, a.DebitYearBal, a.CreditAmount, a.CreditYearBal, a.CustomFieldId3
FROM dbo.#OpenBalanceData a
	LEFT OUTER JOIN dbo.B30OpenBalance b ON a.BranchCode = b.BranchCode AND a.Year = b.Year AND a.CustomFieldId3 = b.CustomFieldId3
		AND a.Account = b.Account AND a.CustomerId = b.CustomerId
WHERE ISNULL(b.Id,0) = 0
*/
 
DROP TABLE #OpenBalanceData

KHÁC

/*
-- KIỂM TRA TRẠNG THÁI DỮ LIỆU LIÊN KẾT TỰ ĐỘNG --
SELECT BranchCode, DocCode, DocDate, DocNo, DocStatus, Stt, SourceKey, IsActive
FROM dbo.B30AccDocItem WHERE IsActive = 1 AND DocCode = 'DC' AND SourceKey IN (SELECT Stt FROM dbo.B30AccDocPurchase WHERE DocCode = 'NM' AND IsActive = 0)
 
SELECT BranchCode, DocCode, DocDate, DocNo, DocStatus, Stt, SourceKey, IsActive
FROM dbo.B30AccDocPurchase WHERE IsActive = 1 AND DocCode = 'XT' AND SourceKey IN (SELECT Stt FROM dbo.B30AccDocItem WHERE DocCode = 'DC' AND IsActive = 0)
*/
 
/*
-- THÊM MỚI VẬT TƯ BÁN LẺ CHƯA TỒN TẠI TRONG BẢNG GIÁ BÁN LẺ --
--INSERT INTO dbo.B20ItemPrice (ItemId, VariantCode, UnitPrice, BizDocId_SP, EffectiveDate, BizDocId_Seq, ValidUntilDate, TerritoryId, strUnitPrice)
SELECT Item.Id AS ItemId, '' AS VariantCode, ISNULL(Item.SalesPrice,0) AS UnitPrice,
	'' AS BizDocId_SP, CONVERT(DATE, DATEADD(HOUR, 7, Item.CreatedAt), 103) AS EffectiveDate,
	NULL AS BizDocId_Seq, NULL AS ValidUntilDate, NULL AS TerritoryId,
	'g' + FORMAT(CAST(ISNULL(Item.SalesPrice, 0) AS FLOAT), '') + 'd' AS strUnitPrice
FROM dbo.vB20Item Item
WHERE IsGroup = 0 AND Item.IsActive = 1 AND CustomFieldCode3 = 'RTL'
	AND Id NOT IN (SELECT ItemId FROM dbo.B20ItemPrice)
*/