· 6 years ago · Aug 30, 2019, 08:04 AM
1USE [SyteLine_BI]
2GO
3/****** Object: StoredProcedure [dbo].[PV_SP_FactProductionDetail] Script Date: 8/30/2019 3:01:04 PM ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8
9-- Author: Nguyen Tien Dat
10-- Create date: 2018-07-19
11-- Description: lấy thông tin sản xuất chi tiết theo ngày
12-- EXEC dbo.[PV_SP_FactProductionDetail] @site_ref = N'PVD' -- nvarchar(10)
13
14ALTER PROC [dbo].[PV_SP_FactProductionDetail]
15 @site_ref NVARCHAR(10)
16AS
17BEGIN
18 DECLARE @GoLiveDate DATETIME
19
20 SELECT @GoLiveDate = Uf_GoLiveDate FROM erp.SyteLine_Apps.dbo.site WHERE site = @site_ref
21 IF (@GoLiveDate IS NULL)
22 BEGIN
23 SET @GoLiveDate = '2019-01-01'
24 END
25
26 DELETE dbo.FactProductionDetail WHERE Site = @site_ref --AND dbo.FAB_CompareCurrDate(CAST(Year AS VARCHAR) + '-' + CAST(Month AS VARCHAR) + '-01') > 0
27
28
29 DECLARE @tb_temp TABLE
30 (
31 [Site] [NCHAR](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
32 [Year] [INT] NULL,
33 [Month] [INT] NULL,
34 [ProjNum] [NVARCHAR](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
35 [PileLength] [NUMERIC](8, 2) NULL,
36 [ItemType] [NVARCHAR](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
37 [Shift] [NVARCHAR](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
38 [TranDate] [DATETIME] NULL,
39 [PlanQty] [DECIMAL](28, 10) NULL,
40 [ProdQty] [DECIMAL](28, 10) NULL,
41 [Resource] [NVARCHAR](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
42 Job NVARCHAR(30),
43 Suffix SMALLINT
44 )
45
46 -- insert thực tế theo kế hoạch
47 INSERT @tb_temp
48 (
49 Site,
50 Year,
51 Month,
52 ProjNum,
53 PileLength,
54 ItemType,
55 Shift,
56 TranDate,
57 PlanQty,
58 ProdQty,
59 Resource,
60 Job,
61 Suffix
62 )
63 SELECT
64 @site_ref,
65 YEAR(p.TransDate),
66 MONTH(p.TransDate),
67 --'PJ' + dbo.ZVN_GetPileProject(j.item) + '01',
68 co.Uf_ProjNum,
69 dbo.ZVN_GetPileLength(j.item),
70 dbo.ZVN_GetPileDiameter(j.item),
71 p.Shift,
72 p.TransDate,
73 SUM(p.PlanQty),
74 SUM(p.ProdQty),
75 p.Resource,
76 j.job,
77 j.suffix
78 FROM ERP.SyteLine_Apps.dbo.FAB_ProductionPlan_Shift_mst p
79 INNER JOIN ERP.SyteLine_Apps.dbo.job_mst j ON j.job = p.Job
80 AND j.site_ref = p.SiteRef
81 AND p.JobSuffix = j.suffix
82 INNER JOIN ERP.SyteLine_Apps.dbo.co_mst co ON co.co_num = j.ord_num AND co.site_ref = j.site_ref
83 INNER JOIN ERP.SyteLine_Apps.dbo.proj_mst pj ON pj.proj_num = co.Uf_ProjNum AND pj.site_ref = co.site_ref
84 WHERE j.site_ref = @site_ref
85 --WHERE EXISTS
86 --(
87 -- SELECT 1
88 -- FROM ERP.SyteLine_Apps.dbo.jobtran_mst
89 -- WHERE j.site_ref = site_ref
90 -- AND j.job = job
91 -- AND j.suffix = suffix
92 -- AND posted = 1
93 --)
94 GROUP BY YEAR(p.TransDate),
95 MONTH(p.TransDate),
96 --'PJ' + dbo.ZVN_GetPileProject(j.item) + '01',
97 co.Uf_ProjNum,
98 dbo.ZVN_GetPileLength(j.item),
99 dbo.ZVN_GetPileDiameter(j.item),
100 p.Shift,
101 p.TransDate,
102 p.Resource,
103 j.job,
104 j.suffix
105
106 INSERT @tb_temp
107 (
108 Site,
109 Year,
110 Month,
111 ProjNum,
112 PileLength,
113 ItemType,
114 Shift,
115 TranDate,
116 PlanQty,
117 ProdQty,
118 Resource,
119 Job,
120 Suffix
121 )
122 SELECT @site_ref,
123 YEAR(jt.trans_date) Year,
124 MONTH(jt.trans_date) Month,
125 --'PJ' + dbo.ZVN_GetPileProject(j.item) + '01' ProjNum,
126 co.Uf_ProjNum,
127 dbo.ZVN_GetPileLength(j.item) PileLength,
128 dbo.ZVN_GetPileDiameter(j.item) ItemType,
129 jt.shift,
130 jt.trans_date,
131 0 PlanQty,
132 SUM(jt.qty_complete) ProdQty,
133 jt.Uf_ZVN_Machine,
134 j.job,
135 j.suffix
136 FROM ERP.SyteLine_Apps.dbo.jobtran_mst jt
137 INNER JOIN ERP.SyteLine_Apps.dbo.job_mst j
138 ON j.job = jt.job
139 AND j.suffix = jt.suffix
140 AND jt.site_ref = j.site_ref
141 INNER JOIN ERP.SyteLine_Apps.dbo.co_mst co ON co.co_num = j.ord_num AND co.site_ref = j.site_ref
142 INNER JOIN ERP.SyteLine_Apps.dbo.proj_mst p ON p.proj_num = co.Uf_ProjNum AND p.site_ref = co.site_ref
143 WHERE j.type = 'J'
144 AND dbo.FAB_IsPileItem(j.item) > 0
145 AND j.rework = 0
146 AND CAST(jt.trans_date AS DATE) >= CAST(@GoLiveDate AS DATE)
147 AND jt.Uf_ZVN_Machine IS NOT NULL
148 AND j.site_ref = @site_ref
149 AND NOT EXISTS
150 (
151 SELECT 1
152 FROM @tb_temp
153 WHERE --('PJ' + dbo.ZVN_GetPileProject(j.item) + '01') = ProjNum
154 co.Uf_ProjNum = ProjNum
155 AND CAST(jt.trans_date AS DATE) = CAST(TranDate AS DATE)
156 AND dbo.ZVN_GetPileLength(j.item) = PileLength
157 AND dbo.ZVN_GetPileDiameter(j.item) = ItemType
158 AND Shift = jt.shift
159 AND Resource = jt.Uf_ZVN_Machine
160 AND Job = jt.job
161 AND Suffix = jt.suffix
162 )
163 GROUP BY YEAR(jt.trans_date),
164 MONTH(jt.trans_date),
165 --'PJ' + dbo.ZVN_GetPileProject(j.item) + '01',
166 co.Uf_ProjNum,
167 dbo.ZVN_GetPileLength(j.item),
168 dbo.ZVN_GetPileDiameter(j.item),
169 jt.shift,
170 jt.trans_date,
171 jt.Uf_ZVN_Machine,
172 j.job,
173 j.suffix
174--SELECT * FROM @tb_temp WHERE ProjNum = 'PJ17320501' ORDER BY TranDate
175
176 INSERT dbo.FactProductionDetail
177 (
178 Site,
179 Year,
180 Month,
181 ProjNum,
182 PileLength,
183 ItemType,
184 Shift,
185 TranDate,
186 PlanQty,
187 ProdQty,
188 Resource
189 )
190 SELECT
191 Site,
192 Year,
193 Month,
194 ProjNum,
195 PileLength,
196 ItemType,
197 Shift,
198 TranDate,
199 SUM(PlanQty),
200 SUM(ProdQty),
201 Resource
202 FROM @tb_temp
203 GROUP BY
204 Site,
205 Year,
206 Month,
207 ProjNum,
208 PileLength,
209 ItemType,
210 Shift,
211 TranDate,
212 Resource
213
214
215 DELETE dbo.FactProductionDetail WHERE ProjNum = 'PJ15101901' AND YEAR = 2016
216 DELETE dbo.FactProductionDetail WHERE ProjNum = 'PJ17318701'
217
218 --nút Update production plan
219 EXEC ERP.SyteLine_Apps.[dbo].[FAB_ProductionPlan_Update] @Site = @site_ref -- nvarchar(8)
220
221END