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'