Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- USE [SyteLine_BI]
- GO
- /****** Object: StoredProcedure [dbo].[PV_SP_FactRawMaterialGroupDetailsByDay] Script Date: 7/23/2019 3:58:54 PM ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- -- Author: Nguyen Tien Dat
- -- Create date: 2018-10-09
- -- Description: lấy thông tin tôn kho nguyên vật liệu
- -- EXEC dbo.[PV_SP_FactRawMaterialGroupDetailsByDay] @site_ref = N'PVD' -- nvarchar(10)
- ALTER PROC [dbo].[PV_SP_FactRawMaterialGroupDetailsByDay]
- @site_ref NVARCHAR(10)
- AS
- BEGIN
- DECLARE @fromDate DATE ,
- @toDate DATE,
- @dauKi DATE
- SET @dauKi = '2018-06-01'--đầu kì PVD
- SELECT @dauKi = CAST(Uf_GoLiveDate AS DATE) FROM erp.SyteLine_Apps.dbo.site WHERE site = @site_ref
- IF (@dauKi IS NULL)
- BEGIN
- SET @dauKi = '2019-01-01'
- END
- SET @fromDate = DATEADD(MONTH, -1, DATEADD(day, 1 - DAY(GETDATE()), GETDATE()))
- SET @toDate = dbo.EndOfMonth(GETDATE())
- IF (@toDate > CAST(GETDATE() AS DATE))
- BEGIN
- SET @toDate = CAST(GETDATE() AS DATE)
- END
- DECLARE @tb TABLE
- (
- Site NCHAR(10) NULL,
- Year INT NULL,
- Month INT NULL,
- Item NVARCHAR(50) NULL,
- whse NVARCHAR(6) NULL,
- TransDate DATETIME NULL,
- InStock DECIMAL(28, 10) DEFAULT 0,
- OutStock DECIMAL(28, 10) DEFAULT 0,
- StockPrepare DECIMAL(28, 10) DEFAULT 0,
- Measure_Production NVARCHAR(10)
- )
- ----- tính tồn kho hiện tại -------------------------------------------------------
- DECLARE @itemStock TABLE
- (
- SiteRef NVARCHAR(10) ,
- Item NVARCHAR(50) ,
- whse NVARCHAR(6),
- TransDate DATE ,
- instock DECIMAL(28,10) ,
- outstock DECIMAL(28,10) ,
- qty DECIMAL(28,10),
- [trans_type] NVARCHAR(10)
- )
- DECLARE @item TABLE
- (
- SiteRef NVARCHAR(10) ,
- Item NVARCHAR(50),
- whse NVARCHAR(6)
- )
- -- đầu kì
- INSERT INTO @itemStock
- (
- SiteRef ,
- Item ,
- whse,
- TransDate ,
- qty
- )
- SELECT
- m.site_ref ,
- r.Item ,
- whse,
- @dauKi ,
- qty
- FROM ERP.SyteLine_Apps.dbo.matltran_mst m ,
- dbo.DimRawMaterialGroup r
- WHERE m.whse IS NOT NULL
- AND m.loc IS NOT NULL
- AND m.site_ref = @site_ref
- AND m.trans_date < @dauKi
- AND LEFT(m.item, LEN(r.Item)) = r.Item
- --AND m.whse IN ('MAIN','BTL','CKH')
- INSERT INTO @item(SiteRef ,Item, whse)
- SELECT SiteRef ,item, whse FROM @itemStock i WHERE NOT EXISTS(SELECT 1 FROM @item WHERE Item = i.Item AND whse = i.whse AND SiteRef = i.SiteRef) GROUP BY SiteRef, Item,i.whse
- INSERT @tb
- (
- Site,
- Year,
- Month,
- Item,
- whse,
- TransDate,
- InStock,
- OutStock,
- Measure_Production
- )
- SELECT r.SiteRef ,
- s.Year,
- s.Month,
- r.Item ,
- r.whse,
- s.Date ,
- ISNULL(( SELECT SUM(qty)
- FROM @itemStock
- WHERE item = r.item
- AND whse = r.whse
- AND SiteRef = r.SiteRef
- AND TransDate = CAST(s.Date AS DATE)
- AND qty >= 0
- ), 0),
- ISNULL(( SELECT SUM(qty)
- FROM @itemStock
- WHERE item = r.item
- AND whse = r.whse
- AND SiteRef = r.SiteRef
- AND TransDate = CAST(s.Date AS DATE)
- AND qty < 0
- ), 0),
- Measure_Production = 'KLTon'
- FROM @item r ,
- dbo.DimDate s
- WHERE CAST(s.Date AS DATE) = @dauKi
- DELETE @itemStock
- -- tính sl tồn [NHẬP],[XUẤT] trong kỳ
- INSERT INTO @itemStock
- (
- SiteRef ,
- Item ,
- whse,
- TransDate ,
- qty,
- trans_type
- )
- SELECT
- m.site_ref ,
- r.Item ,
- m.whse,
- m.trans_date ,
- qty,
- m.trans_type
- FROM ERP.SyteLine_Apps.dbo.matltran_mst m ,
- dbo.DimRawMaterialGroup r
- WHERE m.whse IS NOT NULL
- AND m.loc IS NOT NULL
- AND m.site_ref = @site_ref
- AND m.trans_date >= @dauKi
- AND m.trans_date <= @toDate
- AND LEFT(m.item, LEN(r.Item)) = r.Item
- --AND m.whse IN ('MAIN','BTL','CKH')
- --AND m.trans_type <> 'T'
- INSERT INTO @item(SiteRef ,Item, whse)
- SELECT SiteRef ,item, i.whse FROM @itemStock i WHERE NOT EXISTS(SELECT 1 FROM @item WHERE Item = i.Item AND whse = i.whse AND SiteRef = i.SiteRef) GROUP BY SiteRef, Item, i.whse
- INSERT @tb
- (
- Site,
- Year,
- Month,
- Item,
- whse,
- TransDate,
- InStock,
- OutStock,
- Measure_Production
- )
- SELECT r.SiteRef ,
- s.Year,
- CAST(s.Month AS INT),
- r.Item ,
- r.whse,
- s.Date ,
- ISNULL(( SELECT SUM(qty)
- FROM @itemStock
- WHERE item = r.item
- AND whse = r.whse
- AND SiteRef = r.SiteRef
- AND TransDate = CAST(s.Date AS DATE)
- AND qty >=0
- --AND trans_type <> 'W'
- ), 0),
- ISNULL(( SELECT SUM(qty)
- FROM @itemStock
- WHERE item = r.item
- AND whse = r.whse
- AND SiteRef = r.SiteRef
- AND TransDate = CAST(s.Date AS DATE)
- AND (qty < 0 )
- --OR trans_type = 'W')
- ), 0),
- Measure_Production = 'KLTon'
- FROM @item r ,
- dbo.DimDate s
- WHERE CAST(s.Date AS DATE) <= @toDate
- ------------- tính số lượng chuẩn bị xuất
- DELETE @itemStock
- INSERT @tb
- (
- Site,
- Year,
- Month,
- Item,
- whse,
- TransDate,
- InStock, -- cho vào instock tạm
- StockPrepare,
- Measure_Production
- )
- SELECT A.site_ref, A.year, A.month, A.ItemCode, A.whse, A.RecordDate, SUM(A.qty), SUM(A.qtyPre), A.Measure_Production
- FROM
- (
- SELECT @site_ref site_ref,
- YEAR(jt.trans_date) year,
- MONTH(jt.trans_date) month,
- r.Item ItemCode,
- jt.whse,
- jt.trans_date RecordDate,
- jm.matl_qty * jt.qty qty,
- 0 qtyPre,
- Measure_Production = 'KLChuanBi'
- FROM ERP.SyteLine_Apps.dbo.jobmatl_mst jm
- INNER JOIN dbo.DimRawMaterialGroup r ON LEFT(jm.item, LEN(r.Item)) = r.Item
- INNER JOIN
- (
- SELECT jt.job, jt.suffix, jt.oper_num, jt.trans_date, jt.whse, SUM(jt.qty_complete + jt.qty_scrapped) qty
- FROM ERP.SyteLine_Apps.dbo.jobtran_mst jt
- WHERE jt.site_ref = @site_ref AND jt.posted = 0 AND CAST(jt.trans_date AS DATE) <= @toDate AND CAST(jt.trans_date AS DATE) >= @dauKi
- GROUP BY jt.job, jt.suffix, jt.oper_num, jt.trans_date, jt.whse
- ) AS jt ON jt.job = jm.job AND jt.suffix = jm.suffix AND jt.oper_num = jm.oper_num
- WHERE jm.site_ref = @site_ref
- ) AS A
- GROUP BY A.site_ref, A.year, A.month, A.ItemCode, A.whse, A.RecordDate, A.Measure_Production
- --SELECT jm.item, jm.matl_qty * jt.qty
- --FROM dbo.jobmatl_mst jm
- --INNER JOIN
- --(
- -- SELECT jt.job, jt.suffix, jt.oper_num, SUM(jt.qty_complete + jt.qty_scrapped) qty
- -- FROM dbo.jobtran_mst jt
- -- WHERE jt.site_ref = 'PVD' AND jt.posted = 0
- -- GROUP BY jt.job, jt.suffix, jt.oper_num
- --) AS jt ON jt.job = jm.job AND jt.suffix = jm.suffix AND jt.oper_num = jm.oper_num
- DELETE FactRawMaterialGroupDetailsByDay WHERE Site = @site_ref
- INSERT dbo.FactRawMaterialGroupDetailsByDay
- (
- Site,
- Year,
- Month,
- Item,
- Whse,
- TransDate,
- InStock,
- OutStock,
- StockPrepare,
- Measure_Production
- )
- SELECT
- Site,
- Year,
- Month,
- Item,
- whse,
- TransDate,
- SUM(InStock)InStock,
- SUM(OutStock)OutStock,
- SUM(StockPrepare)StockPrepare,
- Measure_Production
- FROM @tb
- WHERE TransDate >= '2011-01-01'
- GROUP BY
- Site,
- Year,
- Month,
- Item,
- whse,
- TransDate,
- Measure_Production
- UPDATE fr
- SET fr.u_m = r.U_M
- FROM dbo.FactRawMaterialGroupDetailsByDay fr
- LEFT JOIN dbo.DimRawMaterialGroup r ON r.Site = fr.Site AND r.Item = fr.Item
- END
- --SELECT * FROM dbo.FactRawMaterialGroupByDay where item='CA1710' ORDER BY Measure_Production, Item
- ----SELECT * FROM dbo.FactRawMaterialGroup where item='CA1710' ORDER BY Measure_Production, Item
- /*
- SELECT i.product_code,*
- FROM dbo.FactRawMaterialGroupByDay f
- JOIN ERP.SyteLine_Apps.dbo.item_mst i ON LEFT(i.item, LEN(f.Item)) = f.Item AND i.site_ref = 'PVD'
- */
- RETURN
- SELECT SUM(InStock) + SUM(OutStock) FROM dbo.FactRawMaterialGroupDetailByDay WHERE Item LIKE 'CA2012' AND Measure_Production = 'KLTON' AND Year = 2018
- AND CAST(TransDate AS DATE) < '2018-11-01' AND (InStock <> 0 OR OutStock <> 0) AND CAST(TransDate AS DATE) >= '2018-06-01'
- SELECT
- SUM(InStock)
- ,SUM(OutStock)
- FROM dbo.FactRawMaterialGroupDetailByDay WHERE Item LIKE 'CA2012' AND Measure_Production = 'KLTON' AND Year = 2018
- AND CAST(TransDate AS DATE) >= '2018-11-01' AND CAST(TransDate AS DATE) <= '2018-11-30' AND (InStock <> 0 OR OutStock <> 0)
- SELECT * FROM FactRawMaterialGroupByDay WHERE Item LIKE 'CA2012' AND Stock > 0 AND Measure_Production = 'KLTON' AND Year = 2018
- AND CAST(TransDate AS DATE) >= '2018-11-01'
- SELECT
- m.site_ref ,
- r.Item ,
- m.trans_date ,
- qty,
- m.whse,
- m.trans_type,*
- FROM ERP.SyteLine_Apps.dbo.matltran_mst m ,
- dbo.DimRawMaterialGroup r
- WHERE m.whse IS NOT NULL
- AND m.loc IS NOT NULL
- AND m.site_ref = 'PVD'
- AND m.trans_date >= '2018-06-01'
- AND m.trans_date < '2018-11-01'
- AND LEFT(m.item, LEN(r.Item)) = r.Item
- --AND m.whse IN ('MAIN','BTL','CKH')
- AND r.Item = 'CA2012'
- AND m.trans_date = '2018-06-05'
- AND m.trans_type= 'T'
- ORDER BY m.trans_date
- SELECT * FROM dbo.FactRawMaterialGroupDetailByDay WHERE Item LIKE 'CA2012' AND Measure_Production = 'KLTON' AND Year = 2018
- AND CAST(TransDate AS DATE) = '2018-06-01' AND (InStock <> 0 OR OutStock <> 0)
- SELECT * FROM dbo.FactProductionDetailPosted
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement