C:\Users\nguyennp>nslookup
Default Server: google-public-dns-a.google.com
Address: 8.8.8.8

> yahoo.com
Server: google-public-dns-a.google.com
Address: 8.8.8.8

Non-authoritative answer:
Name: yahoo.com.mydomain.com
Address: 222.239.76.238

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER                                   PROCEDURE [dbo].[GL_BK_ThanhPhamXuat]
		@Para1		NVARCHAR(20)	--@CompID
		,@Para2		NVARCHAR(20)	--@FYear
		
		,@Para3		NVARCHAR(20)	--CustCodeFrom
		,@Para4		NVARCHAR(20)	--CustCodeTo

		,@Para5		NVARCHAR(20)	--StockFrom
		,@Para6		NVARCHAR(20)	--StockTo

		,@Para7		NVARCHAR(20)	--@FromDate
		,@Para8		NVARCHAR(20)	--@ToDate

AS
--[GL_BK_ThanhPhamXuat] '01','10','','','M6009','M6009','100420','100426'
SET NOCOUNT ON
BEGIN
---==DECLARE AND INTINITIALIZE THE GLOBAL VARIANCE==-- 

	DECLARE @CompID as NVARCHAR(20)
	SET @CompID = @Para1
	 
	DECLARE @FYear as NVARCHAR(20)
	SET @FYear = @Para2

	DECLARE @CustCodeFrom as NVARCHAR(20)
	SET @CustCodeFrom = @Para3
	
DECLARE @CustCodeTo as NVARCHAR(20)
	SET @CustCodeTo = @Para4

DECLARE @StockFrom as NVARCHAR(20)
	SET @StockFrom = @Para5

DECLARE @StockTo as NVARCHAR(20)
	SET @StockTo = @Para6

	DECLARE @FromDate as NVARCHAR(20)
	SET @FromDate = @Para7
	 
	DECLARE @ToDate as NVARCHAR(20)
	SET @ToDate = @Para8

	DECLARE @SqlStr NVARCHAR(4000)

	IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[tbThanhPhamXuat]')
						AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
				DROP TABLE [dbo].[tbThanhPhamXuat]

	IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[tbBangLuuHoaDonChoTPXuat]')
						AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
				DROP TABLE [dbo].[tbBangLuuHoaDonChoTPXuat]
CREATE TABLE tbBangLuuHoaDonChoTPXuat
(
SoHoaDon nvarchar(50)
)
	SET @SqlStr = " INSERT INTO tbBangLuuHoaDonChoTPXuat SELECT "
+" distinct SL03002 from SL03" + @CompID + "00 "
+ "  where 	SL03005 BETWEEN '" + CONVERT(NVARCHAR(10),convert(datetime,@FromDate),112) + "'"
		+ "  AND	'" + CONVERT(NVARCHAR(10),convert(datetime,@ToDate),112) + "'"
	EXECUTE sp_executesql @SqlStr
update tbBangLuuHoaDonChoTPXuat
set SoHoaDon=Replace (SoHoaDon,'R','')
--select * from tbBangLuuHoaDonChoTPXuat
	CREATE TABLE tbThanhPhamXuat
	(
		MA_KH			NVARCHAR(20)
		,MA_HANG 		NVARCHAR(20)		
		,TEN_HANG		NVARCHAR(50)	
		,QUY_CACH		NVARCHAR(100)	
		,DON_VI_TINH		NVARCHAR(50)
		,TAI_KHOAN_DU		NVARCHAR(50)
		,SO_LUONG		FLOAT
		,DON_GIA_BAN		FLOAT	
--		,GIA_VON		FLOAT
		,THANH_TIEN		FLOAT
--		,THANH_TIEN_VON		FLOAT
		,MA_KHO			NVARCHAR(50)
--		,TEN_KHO		NVARCHAR(50)
--		,LOAI_NV		NVARCHAR(50)					bo 5/8/09              
--		,TEN_NV		NVARCHAR(50)                        bo 5/8/09 
		,SO_DON_HANG		NVARCHAR(50)
		,SO_HOA_DON		NVARCHAR(50)
		,GHI_CHU		NVARCHAR(50)
		,LOAI_NHAP_XUAT		NVARCHAR(50)
		,TI_LE_CK		NVARCHAR(50)
		,NGAY_HOA_DON		DATETIME	
--,SO_LO NVARCHAR(50)
	)
	SET @SqlStr = " INSERT INTO tbThanhPhamXuat SELECT "
		+ "  SC07006		AS	[MA_KH]"
		+ ", SC07003 		AS 	[MA_HANG]"
		+ ", SC01002		AS 	[TÊN_HÀNG]"
		+ ", SC01003		AS 	[QUY_CACH]"
		+ ", SC01135		AS 	[DON_VI_TINH]"
		+ ", ''			AS 	[TAI_KHOAN_DU]"
		+ ", SC07004		AS 	[SO_LUONG]"
		+ ", ROUND(SC07010,2)	AS 	[DON_GIA_BAN]"
--		+ ", SC07005		AS	[GIA_VON]"
		+ ", 0			AS 	[THANH_TIEN]"
--		+ ", 0			AS 	[THANH_TIEN_VON]"
		+ ", SC07009		AS 	[MA_KHO]"
--		+ ", SC23002		AS 	[TEN_KHO]"	
--		+ ", SUBSTRING(SC07012,49,2)			AS 	[LOAI_NV]"            bo 5/8/09
--		+ ", ''			AS	[TEN_NV]"                                     bo 5/8/09
		+ ", SC07007		AS	[SO_DON_HANG]"
		+ ", SC07056			AS	[SO_HOA_DON]"
		+ ", SC07055			AS	[GHI_CHU]"
		+ ", CONVERT(INT, SC07013)	AS	[LOAI_NHAP_XUAT]"
		+ ", ''				AS	[TI_LE_CK]"
		+ ", '1900/01/01'		AS 	[NGAY_HOA_DON]"
		--+",SC07029 "
		+ "  FROM 	SC07" + @CompID + "00 INNER JOIN SC01" + @CompID + "00 ON  SC07003 = SC01001"
	--	+ "  INNER JOIN SC23" + @CompID + "00 ON SC07009 = SC23001"
	--	+ "  INNER JOIN SL03" + @CompID + "00 ON SC07056 = SL03002 OR RTRIM(SC07007) + 'R' = SL03002"
		+ "  WHERE 	SC07001 = '01'"
		+ "  AND 	LEFT(SC07003,1) in ( 'A','M')"
--		+ "  AND 	(CONVERT(INT, SC07013) = 49"				-- XUAT BAN
--		+ "  OR		(CONVERT(INT, SC07013) NOT IN (66) AND SC07004 > 0))"	--TRA HANG TRONG STOCK ISSUED VOI SO LUONG AM
--		+ "  AND	(CONVERT(INT, SC07013) NOT IN (66) AND SC07004 > 0))"	--TRA HANG TRONG STOCK ISSUED VOI SO LUONG AM
--		+ "  AND 	SC07009 BETWEEN '" + @WareCodeFrom + "' AND '" + @WareCodeTo + "'"
	--	+ "  AND	SL03005 BETWEEN '" + CONVERT(NVARCHAR(10),@FromDate,112) + "'"
		--+ "  AND	'" + CONVERT(NVARCHAR(10),@ToDate,112) + "'"
+" and SC07056 in (select SoHoaDon from tbBangLuuHoaDonChoTPXuat)"
		+ "  AND 	SUBSTRING(SC07012,49,2) <> '92'"             
--if @CustCodeFrom<>''
--SET @SqlStr =@SqlStr  + " and SC07006 between '" +@CustCodeFrom+"' and '"+@CustCodeTo+"'"
--
if @StockFrom <>''
SET @SqlStr =@SqlStr  + " and  SC07003 between '" +@StockFrom+"' and '"+@StockTo+"'"


		SET @SqlStr =@SqlStr  + "  ORDER BY 	SC01002"
print @SqlStr
		EXECUTE sp_executesql @SqlStr

--SELECT * FROM tbThanhPhamXuat ORDER BY SO_DON_HANG

 
/*
	UPDATE 	tbThanhPhamXuat
	SET 	THANH_TIEN_VON = SO_LUONG * GIA_VON
*/
	SET @SqlStr = "	UPDATE tbThanhPhamXuat"
		+ " SET		SO_HOA_DON  =	OR20021"
		+ "		,TI_LE_CK   = 	OR20051"
		+ "		,NGAY_HOA_DON   = 	OR20022"
		+ " FROM 	tbThanhPhamXuat, OR20" + @CompID + "00"
		+ " WHERE 	SO_DON_HANG = OR20001"


		EXECUTE sp_executesql @SqlStr
/*
SELECT * FROM tbThanhPhamXuat
WHERE SO_DON_HANG = '0309000040'
*/
	UPDATE tbThanhPhamXuat
	SET 	SO_HOA_DON 	= 	SO_DON_HANG
	WHERE 	SO_HOA_DON  = 	''


	-----------------------------------
	--GIA DUOI KHO KHAC TREN DON HANG--
	-----------------------------------

	UPDATE tbThanhPhamXuat
	SET DON_GIA_BAN = 0
	WHERE SO_HOA_DON = '08/0109319'

	UPDATE tbThanhPhamXuat
	SET DON_GIA_BAN = 368.6
	WHERE SO_HOA_DON = '08/083294'
	
	DELETE tbThanhPhamXuat
	WHERE SO_DON_HANG = '08/076853' --ĐIỀU CHỈNH HỦY HĐ CỦA THÁNG KHÁC
	-----------------------------------


	UPDATE 	tbThanhPhamXuat
	SET 	THANH_TIEN = SO_LUONG * ROUND(DON_GIA_BAN, 2)

	SET @SqlStr = "	UPDATE tbThanhPhamXuat"
		+ " SET		GHI_CHU  =	OR22008"
		+ " FROM 	tbThanhPhamXuat, OR22" + @CompID + "00"
		+ " WHERE 	SO_DON_HANG = OR22001"


		EXECUTE sp_executesql @SqlStr

--	SET @SqlStr = " UPDATE tbThanhPhamXuat"                                  -- bo 5/8/09
----		+ " SET 	TEN_NV = GL03003"--, MO_TA = GL03004"                -- doan nay
--		+ " FROM	tbThanhPhamXuat, GL03" + @CompID + @FYear                --
----		+ " WHERE	LOAI_NV = GL03002"                                   --  
--		+ " AND 	GL03001 = 'J'"                                           --
--
--		EXECUTE sp_executesql @SqlStr                                        --

	---------------------------------------------------
	 /* Dinh nghia bang tam luu don vi tinh (Unit) */
	---------------------------------------------------
	IF EXISTS(SELECT name FROM sysobjects WHERE name = 'tbUnitText_ThahPhamXuat')
		DROP TABLE tbUnitText_ThahPhamXuat

	DECLARE @dem AS INT
	SET @dem = 1

	CREATE TABLE tbUnitText_ThahPhamXuat
	(
		idcol int primary key
		,UnitText nvarchar(50)
	)
	
	SET @dem = 0
	WHILE @dem <= 40
	  BEGIN
		IF @dem <= 7
			SET @SqlStr = " INSERT INTO tbUnitText_ThahPhamXuat "
						+ " SELECT '" + CONVERT(nvarchar, @dem)  + "', SC0900" + CONVERT(nvarchar,(@dem + 2)) 
						+ " FROM SC09" + @CompID + "00 WHERE SC09001 = 'AME'"
		ELSE
			SET @SqlStr = "INSERT INTO tbUnitText_ThahPhamXuat "
						+ " SELECT '" + CONVERT(nvarchar, @dem)  + "', SC090" + CONVERT(nvarchar,(@dem + 2)) 
						+ " FROM SC09" + @CompID + "00 WHERE SC09001 = 'AME'"
	
		EXECUTE sp_executesql @SqlStr
		SET @dem = @dem + 1
	  END

	UPDATE tbThanhPhamXuat
	SET 	DON_VI_TINH = UnitText
	FROM	tbThanhPhamXuat, tbUnitText_ThahPhamXuat
	WHERE 	DON_VI_TINH = idcol



	SET @SqlStr = " UPDATE tbThanhPhamXuat"
		+ " SET	TAI_KHOAN_DU = SUBSTRING(SL04006, 1, 4)"
		+ " FROM	tbThanhPhamXuat, SL04" + @CompID + @FYear	--CO DINH MA CONG TY, NAM TAI CHINH	
		+ " WHERE 	SO_HOA_DON = SL04013"
		+ " AND		LEFT(SL04006,4) IN ('5112', '5122', '5312','5111')"


		EXECUTE sp_executesql @SqlStr


	SET @SqlStr = " UPDATE tbThanhPhamXuat"
		+ " SET	TAI_KHOAN_DU = SUBSTRING(GL06001, 1, 4)"
		+ " FROM	tbThanhPhamXuat, GL06" + @CompID + @FYear	--CO DINH MA CONG TY, NAM TAI CHINH	
		+ " WHERE 	SO_HOA_DON = GL06007"
		+ " AND		LEFT(GL06001,4) IN ('5112', '5122', '5312','5111')"
		+ " AND		TAI_KHOAN_DU = ''"


		EXECUTE sp_executesql @SqlStr


	-------------------------------------
	--DON GIA = 0 LA HOA DON KHUYEN MAI, UPDATE LAI TK--
	-------------------------------------
	UPDATE tbThanhPhamXuat 
	SET TAI_KHOAN_DU = '5122'
	WHERE DON_GIA_BAN = 0

	-----------------------------------------------
	--UPDATE TAI KHOAN DO MAT BUT TOAN TRONG SL04--
	-----------------------------------------------

	UPDATE tbThanhPhamXuat 
	SET TAI_KHOAN_DU = '5112'
	WHERE SO_HOA_DON = '08/112567'

	-------------------------------------
		--GET RESULT--
	-------------------------------------
	SELECT *--MA_HANG, TEN_HANG, QUY_CACH, DON_VI_TINH, TAI_KHOAN_DU, SUM(SO_LUONG) AS SO_LUONG, SUM(THANH_TIEN) AS THANH_TIEN
	FROM tbThanhPhamXuat
order by MA_HANG
--	WHERE SO_HOA_DON = '08/153030'
--	WHERE TAI_KHOAN_DU <> '' --AND THANH_TIEN <> 0
--	AND MA_HANG = 'A0245'
--	GROUP BY MA_HANG, TEN_HANG, QUY_CACH, DON_VI_TINH, TAI_KHOAN_DU
--	ORDER BY TAI_KHOAN_DU

	IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[tbThanhPhamXuat]')
						AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
				DROP TABLE [dbo].[tbThanhPhamXuat]
	IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[tbSupplier]')
						AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
				DROP TABLE [dbo].[tbSupplier]

END

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

----*******************************************----
--Made Date:		01/01/2005
--Updating Date:	19/04/2007	( Version:1000.0002 ) 
--For Purpose: 		Bao Cao Xuat Nhap Ton -> For Design Template
--For Company:	 	Designed Template
--Description:		In this store procedure, we used three tables such as
					-- SC07 -> Stock Transaction File I/O 
					-- SC09 -> Get Unit of Measure
	--Note: 		
		-- @Para1	nvarchar(50)	-- @CompID
		--,@Para2	nvarchar(50)	-- @FYear 
		--,@Para3	nvarchar(50)	-- @Language
		--,@Para4	nvarchar(50)	-- @StockFrom
		--,@Para5	nvarchar(50)	-- @StockTo	
		--,@Para6	nvarchar(50)	--,@WHFrom	
		--,@Para7	nvarchar(50)	--,@WHTo  	
		--,@Para8	nvarchar(50)	--,@DateFrom
		--,@Para9	nvarchar(50)	--,@DateTo  
----*******************************************----
ALTER                     PROCEDURE [dbo].[SC_XuatNhapTon_KeToan]
	 @Para1	nvarchar(50)	-- @CompID  	
	,@Para2	nvarchar(50)	-- @FYear  		
	,@Para3	nvarchar(50)	-- @Language 	
	,@Para4 nvarchar(50)	-- @StockFrom	
	,@Para5 nvarchar(50)	-- @StockTo		
	,@Para6 nvarchar(50)	--,@WHFrom		
	,@Para7	nvarchar(50)	--,@WHTo  		
	,@Para8	nvarchar(50)	--,@DateFrom  	
	,@Para9	nvarchar(50)	--,@DateTo  	
WITH RECOMPILE
-- [XuatNhapTon_KeToan] '01','10','AME','A0144','A0144','K1','KTGC12','100401','100430'
AS
BEGIN
/* ************************************************* */
DECLARE @CompID		AS NVARCHAR(50)	SET @CompID		=	@Para1
DECLARE @FYear		AS NVARCHAR(50)	SET @FYear		=	@Para2
DECLARE @Language	AS NVARCHAR(50)	SET @Language	=	@Para3
DECLARE @StockFrom	AS NVARCHAR(50) SET @StockFrom	= 	@Para4
DECLARE @StockTo	AS NVARCHAR(50) SET @StockTo	=	@Para5
DECLARE @WHFrom		AS NVARCHAR(50) SET @WHFrom 	=	@Para6
DECLARE @WHTo		AS NVARCHAR(50)	SET @WHTo		=	@Para7
DECLARE @DateFrom	AS NVARCHAR(50)	SET @DateFrom	=	@Para8
DECLARE @DateTo		AS NVARCHAR(50)	SET @DateTo		=	@Para9

DECLARE @SqlStr as nvarchar(4000)
DECLARE @dem as INT 
SET @dem = 0

SET ANSI_WARNINGS OFF
SET ARITHABORT OFF
SET QUOTED_IDENTIFIER OFF

END

BEGIN

	CREATE TABLE #TempUnitTable
	(
		idcol int PRIMARY KEY,
		UnitText nvarchar(512)
	)
--DROP TABLE #tbStock
IF EXISTS (	SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[#tbStock]') 
			AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DROP TABLE [dbo].[#tbStock]

IF EXISTS (	SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[#tbStockEnd]') 
			AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DROP TABLE [dbo].[#tbStockEnd]

IF EXISTS (	SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[#tbStockBegin]') 
			AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DROP TABLE [dbo].[#tbStockBegin]
--DROP TABLE QRYTMP_StockImMid
IF EXISTS (	SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[tbStockImMid]') 
			AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DROP TABLE [dbo].[tbStockImMid]

--DROP TABLE QRYTMP_StockExMid
IF EXISTS (	SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[tbStockExMid]') 
			AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DROP TABLE [dbo].[tbStockExMid]

	CREATE TABLE #tbStockEnd
	(
		WarehouseNo			nvarchar(50),
		WHName				nvarchar(50),
		StockCode			nvarchar(50),
		Description1		nvarchar(512),
		Description2		nvarchar(512),
		UnitConvStoc		nvarchar(50),	--DON VI TINH
		UnitCodeMPC		nvarchar(50),	--DON VI TINH NHO NHAT
		UnitConvMPC		INT,	--HE SO QUI DOI
--		LotID			nvarchar(20),	--SO LO
		ExpiryDate		DATETIME,
		UserDefFld1		NVARCHAR(50),	--SO PHIEU KIEM NGHIEM
		UserDefFld2		NVARCHAR(50),	--DO AM
		UserDefFld3		NVARCHAR(50),	--HAM LUONG
		ImQtyMid		numeric(30,4),	--SO LUONG HANG NHAP
		ImTransfPrice1Mid 	numeric(30,4),	--GIA TIEN HANG NHAP
		ExQtyMid		numeric(30,4),	--SO LUONG HANG XUAT
		ExTransfPrice1Mid	numeric(30,4),	--GIA TIEN HANG XUAT
		QtyEnd			numeric(30,4),	--SO LUONG HANG CON LAI TRONG KHO
		TransfPrice1End		numeric(30,4),	--GIA TIEN HANG CON LAI TRONG KHO
		CurrName 		nvarchar(50),
		FromDate		datetime,
		ToDate			datetime
	,QtyBegin			numeric(30,4)
,	TransfPrice1Begin		numeric(30,4)
	)

	CREATE TABLE #tbStockBegin
	(
		WarehouseNo			nvarchar(50),
		WHName				nvarchar(50),
		StockCode			nvarchar(50),
		Description1		nvarchar(512),
		Description2		nvarchar(512),
		UnitConvStoc		nvarchar(50),	--DON VI TINH
		UnitCodeMPC		nvarchar(50),	--DON VI TINH NHO NHAT
		UnitConvMPC		INT,	--HE SO QUI DOI
--		LotID			nvarchar(20),	--SO LO
		ExpiryDate		DATETIME,
		UserDefFld1		NVARCHAR(50),	--SO PHIEU KIEM NGHIEM
		UserDefFld2		NVARCHAR(50),	--DO AM
		UserDefFld3		NVARCHAR(50),	--HAM LUONG
		ImQtyMid		numeric(30,4),	--SO LUONG HANG NHAP
		ImTransfPrice1Mid 	numeric(30,4),	--GIA TIEN HANG NHAP
		ExQtyMid		numeric(30,4),	--SO LUONG HANG XUAT
		ExTransfPrice1Mid	numeric(30,4),	--GIA TIEN HANG XUAT
		QtyBeGin			numeric(30,4),	--SO LUONG HANG CON LAI dau ki TRONG KHO
		TransfPrice1Begin		numeric(30,4),	--GIA TIEN HANG CON LAI TRONG KHO
		CurrName 		nvarchar(50),
		FromDate		datetime,
		ToDate			datetime
	)
----*******************************************----
--Insert the Unit Text into the temporary table
----*******************************************----
WHILE @dem < 40
	BEGIN
		INSERT INTO #TempUnitTable (idcol, UnitText) VALUES (@dem,' ')
      	SET @dem = @dem + 1
	END

SET @dem = 0

WHILE @dem < 8
	BEGIN
		SET @SqlStr = " UPDATE #TempUnitTable "
					+ " SET #TempUnitTable.UnitText = T1.SC0900" + convert(nvarchar(3), @dem + 2)
					+ " FROM #TempUnitTable, SC09" + @CompID + "00 T1"
					+ " WHERE T1.SC09001 = '" + @Language + "'"
					+ " AND #TempUnitTable.idcol = " + convert(nvarchar(3), @dem)
       	EXECUTE  sp_executesql @SqlStr 		
		SET @dem = @dem + 1
	END 

WHILE @dem < 40
	BEGIN
		SET @SqlStr = " UPDATE #TempUnitTable "
					+ " SET #TempUnitTable.UnitText = T1.SC090" + convert(nvarchar(3), @dem + 2) 
					+ " FROM #TempUnitTable, SC09" + @CompID + "00 T1"
					+ " WHERE T1.SC09001 = '" + @Language + "'"
					+ " AND #TempUnitTable.idcol = " + convert(nvarchar(3), @dem)
       	EXECUTE  sp_executesql @SqlStr 		
		SET @dem = @dem + 1
	END
	
----*******************************************----
	----tbTmp lay du lieu hoan toan tu bang SC07	
	----nhung duoc cap nhat lai voi TransPrice theo TransType
	----*******************************************----
	CREATE TABLE #tbStock
	(
		WareNo		nvarchar(50) collate Latin1_General_BIN,
		WareNoTo		nvarchar(50) collate Latin1_General_BIN,
		TransDate			datetime ,
		TransType			nvarchar(50) collate Latin1_General_BIN,
		StockCode		nvarchar(512) collate Latin1_General_BIN,
		LotID		nvarchar(512) collate Latin1_General_BIN,
		Qty		numeric(30,4),
		TransPrice	numeric(30,4),	
		Amount		NUMERIC(30,4),
		Reference		nvarchar(50) collate Latin1_General_BIN ,
		OrderNumber		nvarchar(50) collate Latin1_General_BIN ,
		Invoice		NVARCHAR(50)	collate Latin1_General_BIN ,
		Receiver		NVARCHAR(50)	collate Latin1_General_BIN ,
		GLTransaction		NVARCHAR(50)	collate Latin1_General_BIN 
	)
	SET @SqlStr = " SELECT "
				+ "  T2.SC07009 	WareNo"
				+ ", T2.SC07027 	WareNoTo"
				+ ", T2.SC07002 	TransDate"
				+ ", T2.SC07001		TransType"
				+ ", T2.SC07003 	StockCode"
				+ ", T2.SC07021		LotID"
--				+ ", T2.SC07021		BatchID"
				+ ", T2.SC07004 	Qty"
				+ ", T2.SC07005 	TransPrice"
				+ ", T2.SC07005*T2.SC07004	Amount"
				+ ", T2.SC07007		Reference"
				+ ", T2.SC07006		OrderNumber"
				+ ", T2.SC07056		Invoice"
				+ ", T2.SC07011		Receiver"
				+ ", T2.SC07020		GLTransaction"
--				+ "	 INTO #tbStock"
				+ " FROM SC07" + @CompID + "00 T2"
				+ " WHERE	T2.SC07009 BETWEEN '" + @WHFrom + "' AND '" + @WHTo + "'"
				+ " AND	T2.SC07003 BETWEEN '" + @StockFrom + "' AND '" + @StockTo + "'"
				+ " AND T2.SC07002 <= '" + CONVERT(nvarchar(50), @DateTo, 112) + "'"
--				+ " AND T2.SC07006 <> 'DDCK2008'"
				+ " ORDER BY T2.SC07003, T2.SC07002  ASC"

	insert #tbStock execute sp_executesql @SqlStr
PRINT @SqlStr

--XOA DI CAC DONG CHUYEN KHO TU KN->KT HOAC KT->KN : HANG  KIEM NGHIEM 
--DELETE #tbStock
--SELECT * FROM #tbStock
-- WHERE Reference = 'X0513/09'

--SELECT * FROM #tbStock WHERE StockCode = 'B0131'



--SELECT * FROM #tbStock WHERE StockCode = 'B0027'

--delete #tbStock where Reference = 'DD001/08'-- OR OrderNumber = 'DDCK2009'

--SELECT * FROM #tbStock WHERE StockCode = 'C0047'
--	SET @SqlStr = "	UPDATE #tbStock"
--				+ "    SET TransDate = PL03005"
--				+ "	  FROM PL03" + @CompID + "00"
--				+ "  WHERE Invoice = PL03002 AND Receiver = PL03001"
--	execute sp_executesql @SqlStr


	UPDATE #tbStock
	SET Amount = TransPrice
	WHERE TransType = '03' OR TransType = '10'
	
	UPDATE #tbStock
	SET Amount = (-1)* TransPrice
	WHERE TransType = '07'

--SELECT * FROM #tbStock WHERE StockCode = 'B0651' --AND (TransType = '01' OR (TransType = '04' AND Qty < 0))

	----*******************************************----
	----##tbStockEnd tính số lượng và giá cuối kỳ
	----Bang nay la bang chính để lấy dữ liệu
	----*******************************************----
	
	SET @SqlStr = " INSERT INTO #tbStockEnd "
				+ "  SELECT DISTINCT"
				+ "  T2.WareNo "
				+ ", T3.SC23002 "
				+ ", T2.StockCode "
				+ ", T1.SC01002 	Description1"
				+ ", SUBSTRING(T1.SC01003, 1, ABS(CHARINDEX('TH',T1.SC01003,1) - 1)) 	Description2"
				+ ", T4.UnitText  	UnitText"
--				+ ", T2.LotID		LotID"
				+ ", T1.SC01136		UnitCodeMPC"
				+ ", T1.SC01142		UnitConvMPC"
				+ ", NULL"
				+ ", ' '"
				+ ", ' '"
				+ ", ' '"
				+ ", 0   "
				+ ", 0   "
				+ ", 0   "
				+ ", 0   "
				+ ", SUM(T2.Qty)  "
				+ ", SUM(T2.Amount) "
				+ ", SY.SYCD009  CurrName" 
				+ ", '" + CONVERT(nvarchar(50), @DateFrom, 1) + "'"
				+ ", '" + CONVERT(nvarchar(50), @DateTo, 1) + "'"
+",0"
+",0"
				+ "  FROM SC01" + @CompID + "00 T1, #tbStock T2"
				+ ", SC23" + @CompID + "00 T3, #TempUnitTable T4"
				+ ", SYCD" + @CompID + "00 SY"
				+ " WHERE	T2.WareNo BETWEEN '" + @WHFrom + "' AND '" + @WHTo + "'"
				+ " AND	T2.StockCode BETWEEN '" + @StockFrom + "' AND '" + @StockTo + "'"
				+ " AND T2.WareNo =  T3.SC23001"
				+ " AND T1.SC01001 = T2.StockCode"
				+ " AND T2.TransDate  <= '" + CONVERT(nvarchar(50), @DateTo, 112) + "'"
				+ " AND T4.idcol = T1.SC01133"
				+ " AND SY.SYCD001 = 0 "
				+ " GROUP BY T2.StockCode, T2.WareNo, T3.SC23002, T1.SC01002, T1.SC01003,"
				+ " T1.SC01136, T1.SC01142, T4.UnitText, SY.SYCD009"--- T2.LotID"
				+ " ORDER BY T2.StockCode ASC"
	EXECUTE sp_executesql @SqlStr

--SELECT * FROM #tbStockEnd
	SET @SqlStr = " INSERT INTO #tbStockBegin "
				+ "  SELECT DISTINCT"
				+ "  T2.WareNo "
				+ ", T3.SC23002 "
				+ ", T2.StockCode "
				+ ", T1.SC01002 	Description1"
				+ ", SUBSTRING(T1.SC01003, 1, ABS(CHARINDEX('TH',T1.SC01003,1) - 1)) 	Description2"
				+ ", T4.UnitText  	UnitText"
--				+ ", T2.LotID		LotID"
				+ ", T1.SC01136		UnitCodeMPC"
				+ ", T1.SC01142		UnitConvMPC"
				+ ", NULL"
				+ ", ' '"
				+ ", ' '"
				+ ", ' '"
				+ ", 0   "
				+ ", 0   "
				+ ", 0   "
				+ ", 0   "
				+ ", SUM(T2.Qty)  "
				+ ", SUM(T2.Amount) "
				+ ", SY.SYCD009  CurrName" 
				+ ", '" + CONVERT(nvarchar(50), @DateFrom, 1) + "'"
				+ ", '" + CONVERT(nvarchar(50), @DateTo, 1) + "'"
				+ "  FROM SC01" + @CompID + "00 T1, #tbStock T2"
				+ ", SC23" + @CompID + "00 T3, #TempUnitTable T4"
				+ ", SYCD" + @CompID + "00 SY"
				+ " WHERE	T2.WareNo BETWEEN '" + @WHFrom + "' AND '" + @WHTo + "'"
				+ " AND	T2.StockCode BETWEEN '" + @StockFrom + "' AND '" + @StockTo + "'"
				+ " AND T2.WareNo =  T3.SC23001"
				+ " AND T1.SC01001 = T2.StockCode"
				+ " AND T2.TransDate  < '" + CONVERT(nvarchar(50), @DateFrom, 112) + "'"
				+ " AND T4.idcol = T1.SC01133"
				+ " AND SY.SYCD001 = 0 "
				+ " GROUP BY T2.StockCode, T2.WareNo, T3.SC23002, T1.SC01002, T1.SC01003,"
				+ " T1.SC01136, T1.SC01142, T4.UnitText, SY.SYCD009"--- T2.LotID"
				+ " ORDER BY T2.StockCode ASC"
	EXECUTE sp_executesql @SqlStr
--select * from #tbStockBegin
print @SqlStr
DELETE #tbStock
 WHERE ((WareNo = 'KN' AND WareNoTo = 'KT') OR (WareNo = 'KT' AND WareNoTo = 'KN') 
		OR (WareNo = 'KB' AND WareNoTo = 'KT') OR (WareNo = 'KB' AND WareNoTo = 'KT')) 
   AND Receiver = '20' 
AND TransDate BETWEEN '20090201' AND '20091230'

	----*******************************************----
	----tbStockImMid tính số lượng và giá nhập trong kỳ
	----ĐK: theo TransType
	----*******************************************----

	SET @SqlStr = "	 SELECT DISTINCT"
				+ "  T2.WareNo  	WarehouseNo"
				+ ", T2.StockCode 	StockCode"
				+ ", T1.SC01002		Description1"
				+ ", SUBSTRING(T1.SC01003, 1, ABS(CHARINDEX('TH',T1.SC01003,1) - 1))		Description2"
				+ ", T4.UnitText	UnitText"
--				+ ", T2.LotID	 	LotID"	
				+ ", T1.SC01136		UnitCodeMPC"
				+ ", T1.SC01142		UnitConvMPC"			
				+ ", SUM(T2.Qty)   	Qty"
				+ ", SUM(round(T2.Amount,4)) Amount"
				+ ", 0   			ExQty"
				+ ", 0   			ExAmount"
				+ ", 0   			EndQty"
				+ ", 0   			EndAmount"
				+ ", SY.SYCD009			CurrName" 
--				+ ", T2.GLTransaction	GLTransaction"
				+ ", '" + CONVERT(nvarchar(50), @DateFrom, 1) + "'		DateFrom"
				+ ", '" + CONVERT(nvarchar(50), @DateTo, 1) + "'		DateTo"
				+ "	 INTO tbStockImMid"
				+ "  FROM  SC01" + @CompID + "00 T1, #tbStock T2"
				+ ", SC23" + @CompID + "00 T3, #TempUnitTable T4"
				+ ", SYCD" + @CompID + "00 SY"
				+ " WHERE T2.WareNo BETWEEN '" + @WHFrom + "' AND '" + @WHTo + "'"
				+ " AND	T2.StockCode BETWEEN '" + @StockFrom + "' AND '" + @StockTo + "'"
				+ " AND T2.WareNo =  T3.SC23001"
				+ " AND T1.SC01001 = T2.StockCode"
				+ " AND T4.idcol = T1.SC01133"
				+ " AND SY.SYCD001 = 0 "
				+ "	AND (T2.TransType IN ('00','03') OR (T2.Qty > 0 AND T2.TransType = '04')"	--Điều kiện để lấy 
				+ "	OR (T2.TransType = '02' AND T2.Qty > 0))"						--hàng nhập kho
--				+ "	OR (T2.TransType = '01' AND T2.Qty > 0))"	
				+ " AND T2.TransDate  >= '" + CONVERT(nvarchar(50), @DateFrom, 112) + "'"
				+ " AND T2.TransDate  <= '" + CONVERT(nvarchar(50), @DateTo, 112) + "'"
				+ " GROUP BY T2.StockCode, T2.WareNo, T3.SC23002, T1.SC01002, T1.SC01003,"
				+ " T1.SC01136, T1.SC01142, T4.UnitText, SY.SYCD009"--, T2.GLTransaction"--, T2.LotID"
				+ " ORDER BY T2.StockCode ASC"
	EXECUTE sp_executesql @SqlStr

	--SELECT * FROM tbStockImMid --WHERE StockCode LIKE 'A0281%' --AND WarehouseNo = 'KN'
--	AND SC25002 = GLTransaction 


	----*******************************************----
	----tbStockExMid tính số lượng và giá xuất trong kỳ
	----ĐK: theo TransType
	----*******************************************----
	SET @SqlStr = "	 SELECT DISTINCT"
				+ "  T2.WareNo  	WarehouseNo"
				+ ", T2.StockCode 	StockCode"
				+ ", T1.SC01002		Description1"
				+ ", SUBSTRING(T1.SC01003, 1, ABS(CHARINDEX('TH',T1.SC01003,1) - 1))		Description2"
				+ ", T4.UnitText	UnitText"
				+ ", T1.SC01136		UnitCodeMPC"
				+ ", T1.SC01142		UnitConvMPC"
--				+ ", T2.LotID	 	LotID"				
				+ ", 0   			ImQty"
				+ ", 0   			ImAmount"
				+ ", SUM(T2.Qty)   	Qty"
				+ ", SUM(round(T2.Amount,4)) Amount"
				+ ", 0   			EndQty"
				+ ", 0   			EndAmount"
				+ ", SY.SYCD009  CurrName" 
				+ ", '" + CONVERT(nvarchar(50), @DateFrom, 1) + "'		DateFrom"
				+ ", '" + CONVERT(nvarchar(50), @DateTo, 1) + "'		DateTo"
				+ "	 INTO tbStockExMid"
				+ "  FROM   SC01" + @CompID + "00 T1, #tbStock T2"
				+ ", SC23" + @CompID + "00 T3, #TempUnitTable T4"
				+ ", SYCD" + @CompID + "00 SY"
				+ " WHERE	T2.WareNo BETWEEN '" + @WHFrom + "' AND '" + @WHTo + "'"
				+ " AND	T2.StockCode BETWEEN '" + @StockFrom + "' AND '" + @StockTo + "'"
				+ " AND T2.WareNo =  T3.SC23001"
				+ " AND T1.SC01001 = T2.StockCode"
				+ " AND T4.idcol = T1.SC01133"
				+ " AND SY.SYCD001 = 0 "	
				+ " 	AND (T2.TransType IN ('01','07','10') OR (T2.Qty < 0 AND T2.TransType = '04')"		--Điều kiện để lấy
				+ "	OR (T2.TransType = '02' AND T2.Qty < 0))"
--				+ " OR (T2.TransType = '01' AND T2.Qty < 0))"							--hàng xuất kho
				+ " AND T2.TransDate  >= '" + CONVERT(nvarchar(50), @DateFrom, 112) + "'"
				+ " AND T2.TransDate  <= '" + CONVERT(nvarchar(50), @DateTo, 112) + "'"
				+ " GROUP BY T2.StockCode, T2.WareNo, T3.SC23002, T1.SC01002, T1.SC01003,"
				+ " T1.SC01136, T1.SC01142, T4.UnitText, SY.SYCD009"--, T2.LotID"
				+ " ORDER BY T2.StockCode ASC"
	EXECUTE sp_executesql @SqlStr

--SELECT * FROM tbStockExMid WHERE StockCode = 'A0281'

	--Them điều kiện kết LotID- 
	UPDATE #tbStockEnd
	SET #tbStockEnd.ImQtyMid = tbStockImMid.Qty
	,#tbStockEnd.ImTransfPrice1Mid = tbStockImMid.Amount
	FROM #tbStockEnd, tbStockImMid
	WHERE #tbStockEnd.WarehouseNo collate Latin1_General_BIN = tbStockImMid.WarehouseNo

	AND #tbStockEnd.StockCode collate Latin1_General_BIN = tbStockImMid.StockCode 
--	AND #tbStockEnd.LotID = tbStockImMid.LotID	--LotID
	
	UPDATE #tbStockEnd
	SET #tbStockEnd.ExQtyMid = tbStockExMid.Qty
	,#tbStockEnd.ExTransfPrice1Mid = tbStockExMid.Amount
	FROM #tbStockEnd, tbStockExMid
	WHERE #tbStockEnd.WarehouseNo collate Latin1_General_BIN = tbStockExMid.WarehouseNo
	AND #tbStockEnd.StockCode collate Latin1_General_BIN = tbStockExMid.StockCode 


	UPDATE #tbStockEnd
	SET #tbStockEnd.QtyBegin = #tbStockBegin.QtyBegin
	,#tbStockEnd.TransfPrice1Begin = #tbStockBegin.TransfPrice1Begin
	FROM #tbStockEnd, #tbStockBegin
	WHERE #tbStockEnd.WarehouseNo collate Latin1_General_BIN = #tbStockBegin.WarehouseNo
	AND #tbStockEnd.StockCode collate Latin1_General_BIN = #tbStockBegin.StockCode 
--	AND #tbStockEnd.LotID = tbStockExMid.LotID	--LotID

	--UPDATE NEU HS QUI DOI = 0--
/*
	UPDATE #tbStockEnd
	SET UnitConvMPC = 1
	WHERE UnitConvMPC = 0

	--NHAN SL VOI HE SO QUI DOI--
	UPDATE #tbStockEnd
	SET ImQtyMid = ImQtyMid * UnitConvMPC, 
	    ExQtyMid = ExQtyMid * UnitConvMPC,
	    QtyEnd = QtyEnd * UnitConvMPC
	    */

	--NHAN SL VOI HE SO QUI DOI--
/*
	UPDATE #tbStockEnd
	SET ImQtyMid = ImQtyMid * ISNULL(QDGBB, 1), 
	    ExQtyMid = ExQtyMid * ISNULL(QDGBB, 1),
	    QtyEnd = QtyEnd * ISNULL(QDGBB, 1)
	FROM 	#tbStockEnd, SCGCD0000
	WHERE StockCode = MAHH

*/
	UPDATE #tbStockEnd
	SET UnitCodeMPC = ( SELECT UnitText FROM #TempUnitTable	WHERE UnitCodeMPC = idcol)

	--UPDATE Do am, ham luong, Phieu KN	--

	--UPDATE MA PHAN XUONG, TEN PHAN XUONG--

	SET @SqlStr = " UPDATE #tbStockEnd"
		    + " SET UserDefFld1 = SC01010"
		    + " FROM #tbStockEnd, SC01" + @CompID + "00" 
		    + " WHERE StockCode collate Latin1_General_BIN = SC01001"

	EXECUTE sp_executesql @SqlStr

	SET @SqlStr = " UPDATE #tbStockEnd"
		    + " SET UserDefFld1 = SY24003"
		    + " FROM #tbStockEnd, SY24" + @CompID + "00" 
		    + " WHERE UserDefFld1 collate Latin1_General_BIN = SY24002"
		    + " AND 	SY24001 = 'IQ'"

	EXECUTE sp_executesql @SqlStr

	---------------------------------------


/*
	SET @SqlStr = " UPDATE #tbStockEnd"
		    + " SET LotID = SC33009"
		    + " FROM #tbStockEnd, SC33" + @CompID + "00" 
		    + " WHERE WarehouseNo = SC33002 AND StockCode = SC33001 AND LotID = SC33003"

	EXECUTE sp_executesql @SqlStr
*/
	--**********GET RESULT*************--
/*SELECT WarehouseNo, WHName, StockCode, Description1, Description2, UnitConvStoc
			,LotID, ExpiryDate, UserDefFld1, UserDefFld2, UserDefFld3,
			SUM(ImQtyMid) as ImQtyMid, SUM(ExQtyMid) AS ExQtyMid, SUM(QtyEnd) AS QtyEnd, CurrName, FromDate, ToDate
		FROM #tbStockEnd
		WHERE ImQtyMid <> 0.0 OR ExQtyMid <> 0.0 OR QtyEnd <> 0.0
		group by WarehouseNo, WHName, StockCode, Description1, Description2, UnitConvStoc
			,LotID, ExpiryDate, UserDefFld1, UserDefFld2, UserDefFld3, CurrName, FromDate, ToDate
--		AND LotID = '0030408'
*/

	IF @StockFrom LIKE 'A%'
		SELECT *
		FROM #tbStockEnd
		WHERE (ImQtyMid <> 0.0 OR ExQtyMid <> 0.0 OR QtyEnd <> 0.0
				OR ImTransfPrice1Mid <> 0.0 OR ExTransfPrice1Mid <> 0.0 OR TransfPrice1End <> 0.0)
		AND WarehouseNo IN ('K1','K2','K8','K13','K6','K9','K14','K7')
	ELSE 
		SELECT *
		FROM #tbStockEnd
		WHERE (ImQtyMid <> 0.0 OR ExQtyMid <> 0.0 OR QtyEnd <> 0.0 
				OR ImTransfPrice1Mid <> 0 OR ExTransfPrice1Mid <> 0 OR TransfPrice1End <> 0)

	
SELECT StockCode,ImTransfPrice1Mid
		FROM #tbStockEnd
		WHERE (ImTransfPrice1Mid <> 0.0  )

--SELECT StockCode, SUM(ExTransfPrice1Mid)  FROM #tbStockEnd --WHERE TransDate BETWEEN '090301' AND '090331'
--GROUP BY StockCode
--SELECT StockCode, SUM(ExTransfPrice1Mid)  FROM #tbStockEnd --WHERE TransDate BETWEEN '090301' AND '090331'
--w
--GROUP BY StockCode
--SELECT * FROM #tbStock WHERE TransDate BETWEEN '090601' AND '090630' AND StockCode = 'B0005'

	DROP TABLE #tbStock
	DROP TABLE #tbStockEnd
	DROP TABLE tbStockImMid
	DROP TABLE tbStockExMid
--DROP TABLE tbStock
IF EXISTS (	SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[#tbStock]') 
			AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DROP TABLE [dbo].[#tbStock]

IF EXISTS (	SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[#tbStockEnd]') 
			AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DROP TABLE [dbo].[#tbStockEnd]

--DROP TABLE QRYTMP_StockImMid
IF EXISTS (	SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[tbStockImMid]') 
			AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DROP TABLE [dbo].[tbStockImMid]

--DROP TABLE QRYTMP_StockExMid
IF EXISTS (	SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[tbStockExMid]') 
			AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DROP TABLE [dbo].[tbStockExMid]

END

--[XuatNhapTon_KeToan] '01','09','AME','B0001','B9999','KN','KN','090801','090831'