Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DECLARE @Infobar INFOBARTYPE;
- EXEC dbo.SetSiteSp @Site = 'PVC', -- SiteType
- @Infobar = @Infobar OUTPUT -- InfobarType
- DECLARE @year INT, @month INT,@whse VARCHAR(5), @fromDate DATETIME,@toDate DATETIME
- SET @year = 2019
- SET @month = 6
- SET @whse = 'BTL'
- SET @fromDate = '2019-06-01'
- SET @toDate = '2019-07-01'
- -- Tồn đầu
- SELECT N'00-Tồn đầu','', ISNULL(SUM(m.qty),0), ISNULL(SUM(m.qty*m.cost),0)--, ISNULL(SUM(m.qty*m.cost),0)/ISNULL(SUM(m.qty),1)
- FROM dbo.matltran m
- WHERE
- m.trans_date < @fromDate
- AND LEFT(m.item,2) IN ('AA','AC','AN')
- AND m.whse = @whse
- AND m.loc IS NOT NULL
- UNION
- SELECT N'05-Tồn cuối','', ISNULL(SUM(m.qty),0), ISNULL(SUM(m.qty*m.cost),0)
- FROM dbo.matltran m
- WHERE
- m.trans_date < @toDate
- AND LEFT(m.item,2) IN ('AA','AC','AN')
- AND m.whse = @whse
- AND m.loc IS NOT NULL
- UNION
- SELECT N'01-Nhập kho sản xuất','', ISNULL(SUM(m.qty),0), ISNULL(SUM(m.qty*m.cost),0)
- FROM dbo.matltran m
- LEFT JOIN job j ON m.ref_num = j.job AND m.ref_line_suf = j.suffix
- WHERE
- dbo.FAB_ComparePeriod(m.trans_date,@year,@month) = 1
- AND LEFT(m.item,2) IN ('AA','AC','AN')
- AND m.ref_type = 'J'
- AND loc IS NOT NULL
- AND m.whse = @whse
- AND j.rework = 0
- UNION
- SELECT N'02-Nhập điều chỉnh',N'Nhập kho hoán chuyển', ISNULL(SUM(m.qty),0), ISNULL(SUM(m.qty*m.cost),0)
- FROM dbo.matltran m
- LEFT JOIN job j ON m.ref_num = j.job AND m.ref_line_suf = j.suffix
- WHERE
- dbo.FAB_ComparePeriod(m.trans_date,@year,@month) = 1
- AND LEFT(m.item,2) IN ('AA','AC','AN')
- AND m.ref_type = 'J'
- AND loc IS NOT NULL
- AND m.whse = @whse
- AND j.rework = 1
- UNION
- SELECT N'02-Nhập điều chỉnh',N'KHGB => BTL',ISNULL(SUM(m.qty),0), ISNULL(SUM(m.qty*m.cost),0)
- FROM dbo.matltran m
- LEFT JOIN dbo.trnitem t ON m.ref_num = t.trn_num AND t.trn_line = m.ref_line_suf
- WHERE
- dbo.FAB_ComparePeriod(m.trans_date,@year,@month) = 1
- AND LEFT(m.item,2) IN ('AA','AC','AN')
- AND m.loc IS NOT NULL
- AND m.whse = @whse
- AND m.ref_type = 'T'
- AND t.from_whse = 'KHGB'
- AND t.to_whse = @whse
- UNION
- SELECT N'02-Nhập điều chỉnh',N'Khác => BTL',ISNULL(SUM(m.qty),0), ISNULL(SUM(m.qty*m.cost),0)
- FROM dbo.matltran m
- LEFT JOIN dbo.trnitem t ON m.ref_num = t.trn_num AND t.trn_line = m.ref_line_suf
- WHERE
- dbo.FAB_ComparePeriod(m.trans_date,@year,@month) = 1
- AND LEFT(m.item,2) IN ('AA','AC','AN')
- AND m.loc IS NOT NULL
- AND m.whse = @whse
- AND m.ref_type = 'T'
- AND t.from_whse != 'KHGB'
- AND t.to_whse = @whse
- UNION
- SELECT N'02-Nhập điều chỉnh',N'Misc Receipt', ISNULL(SUM(m.qty),0), ISNULL(SUM(m.qty*m.cost),0)
- FROM dbo.matltran m
- WHERE
- dbo.FAB_ComparePeriod(m.trans_date,@year,@month) = 1
- AND LEFT(m.item,2) IN ('AA','AC','AN')
- AND m.loc IS NOT NULL
- AND m.whse = @whse
- AND m.ref_type = 'I'
- AND m.trans_type = 'H'
- UNION
- SELECT N'03-Xuất cọc công trường',N'BTL => KHGB', ISNULL(SUM(qty),0), ISNULL(SUM(m.qty*m.cost),0)
- FROM dbo.matltran m
- LEFT JOIN dbo.trnitem t ON m.ref_num = t.trn_num AND t.trn_line = m.ref_line_suf
- WHERE
- dbo.FAB_ComparePeriod(m.trans_date,@year,@month) = 1
- AND LEFT(m.item,2) IN ('AA','AC','AN')
- AND m.loc IS NOT NULL
- AND m.whse = @whse
- AND m.ref_type = 'T'
- AND t.from_whse = @whse
- AND t.to_whse = 'KHGB'
- UNION
- SELECT N'04-Xuất điều chỉnh',N'BTL => Khác', ISNULL(SUM(qty),0), ISNULL(SUM(m.qty*m.cost),0)
- FROM dbo.matltran m
- LEFT JOIN dbo.trnitem t ON m.ref_num = t.trn_num AND t.trn_line = m.ref_line_suf
- WHERE
- dbo.FAB_ComparePeriod(m.trans_date,@year,@month) = 1
- AND LEFT(m.item,2) IN ('AA','AC','AN')
- AND m.loc IS NOT NULL
- AND m.whse = @whse
- AND m.ref_type = 'T'
- AND t.from_whse = @whse
- AND t.to_whse != 'KHGB'
- UNION
- SELECT N'04-Xuất điều chỉnh',N'Misc Issue', ISNULL(SUM(m.qty),0), ISNULL(SUM(m.qty*m.cost),0)
- FROM dbo.matltran m
- WHERE
- dbo.FAB_ComparePeriod(m.trans_date,@year,@month) = 1
- AND LEFT(m.item,2) IN ('AA','AC','AN')
- AND m.loc IS NOT NULL
- AND m.whse = @whse
- AND m.ref_type = 'I'
- AND m.trans_type = 'G'
- UNION
- SELECT N'02-Nhập điều chỉnh',N'PO', ISNULL(SUM(m.qty),0), ISNULL(SUM(m.qty*m.cost),0)
- FROM dbo.matltran m
- WHERE
- dbo.FAB_ComparePeriod(m.trans_date,@year,@month) = 1
- AND LEFT(m.item,2) IN ('AA','AC','AN')
- AND m.loc IS NOT NULL
- AND m.whse = @whse
- AND m.ref_type = 'P'
- UNION
- SELECT N'04-Xuất điều chỉnh',N'CO - Xuất Hủy',ISNULL(SUM(qty),0), ISNULL(SUM(m.qty*m.cost),0)
- FROM dbo.matltran m
- WHERE
- dbo.FAB_ComparePeriod(m.trans_date,@year,@month) = 1
- AND LEFT(m.item,2) IN ('AA','AC','AN')
- AND m.loc IS NOT NULL
- AND m.whse = @whse
- AND m.ref_type = 'O'
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement