SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE dbo.spReportReqW
(@COMPA nvarchar(4),
--@BRAND nvarchar(255),
@IMPORTDATE nvarchar(50),
@REQDATE nvarchar(50),
@STOCKDATE nvarchar(50))
AS DELETE FROM dbo.TEMPREQ_W
--Insert the Requirements-data that was selected in the TEMP-table
INSERT INTO dbo.TEMPREQ_W
(COMPA, PLANT, WAREH, MAT_NUMBER, REQDATE, [FOR], YEAR, PERIODE, WEEK, DATE_VAN, DATE_TOT, DATE_LEV, QTFOR_UNIT, ORIGINE, SAP, CODE)
SELECT COMPA, PLANT, WAREH, MAT_NUMBER, REQDATE, [FOR], YEAR, PERIODE, WEEK, DATE_VAN, DATE_TOT, DATE_LEV, QTFOR_UNIT, ORIGINE, SAP, CODE
FROM dbo.vwReq_W
WHERE (COMPA = @COMPA) AND --(CUST_BRAND = @BRAND) AND
(DATEADD(Minute, DATEDIFF(Minute, 0, REQDATE), 0)= DATEADD(Minute, DATEDIFF(Minute, 0, @REQDATE), 0))
--Update the TEMP-table with the "SAP Artikel Klant"-data that was selected
UPDATE dbo.TEMPREQ_W
SET IMPORTDATE = case when (dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.IMPORTDATE is null) then 0 else dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.IMPORTDATE END,
CUST_BRAND = case when (dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.[AFH Customer Brand] is null) then '' else dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.[AFH Customer Brand] END,
ArticleGroup = case when (dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.[AFH Article Group] is null) then '' else dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.[AFH Article Group] END,
ArtDescr = case when (dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.[ArtDescr] is null) then '' else dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.[ArtDescr] END,
Remark = case when (dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.Remark is null) then '' else dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.Remark END,
[Customer's description of material] = case when (dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.[Customer's description of material] is null) then '' else dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.[Customer's description of material] END,
[Material Description] = case when (dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.[Material Description] is null) then '' else dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.[Material Description] END,
[Customer Material Number] = case when (dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.[Customer Material Number] is null) then '' else dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.[Customer Material Number] END,
[AFH Specnr] = case when (dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.[AFH Specnr] is null) then '' else dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.[AFH Specnr] END,
[AFH MF Remark] = case when (dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.[AFH MF Remark] is null) then '' else dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.[AFH MF Remark] END,
Material = case when (dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.Material is null) then '' else dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.Material END
FROM dbo.TEMPREQ_W LEFT OUTER JOIN
dbo.vwSAP_Artikel_MF_Artikel_Single_Detail ON dbo.TEMPREQ_W.ORIGINE = dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.ORIGINE AND
dbo.TEMPREQ_W.MAT_NUMBER = dbo.vwSAP_Artikel_MF_Artikel_Single_Detail.CustMatNum AND
(DATEADD(Minute, DATEDIFF(Minute, 0, vwSAP_Artikel_MF_Artikel_Single_Detail.ImportDate), 0)= DATEADD(Minute, DATEDIFF(Minute, 0, @IMPORTDATE), 0))-- OR
-- DATEADD(Minute, DATEDIFF(Minute, 0, ImportDate), 0) IS NULL) AND
-- Update the TEMP-table with the Stockdata that MF has sent us
UPDATE dbo.TEMPREQ_W
SET MAT_DESCRIPTION = case when (dbo.tStock.MAT_DESCRIPTION is null) then '' else dbo.tStock.MAT_DESCRIPTION END,
SPECNR = case when (dbo.tStock.SPECNR is null) then '' else dbo.tStock.SPECNR END,
SAFETY_STOCK = case when (dbo.tStock.SAFETY_STOCK is null) then 0 else dbo.tStock.SAFETY_STOCK END,
EO_DATE = case when (dbo.tStock.EO_DATE is null) then 0 else dbo.tStock.EO_DATE END,
FO_MATERIAL = case when (dbo.tStock.FO_MATERIAL is null) then '' else dbo.tStock.FO_MATERIAL END,
QTY_MARSNETUNSTRICTEDSTK = case when (dbo.tStock.QTY_MARSNETUNSTRICTEDSTK is null) then 0 else dbo.tStock.QTY_MARSNETUNSTRICTEDSTK END,
QTY_MARSTOTALSTK = case when (dbo.tStock.QTY_MARSTOTALSTK is null) then 0 else dbo.tStock.QTY_MARSTOTALSTK END,
QTY_MARSBLOCKEDSTK = case when (dbo.tStock.QTY_MARSBLOCKEDSTK is null) then 0 else dbo.tStock.QTY_MARSBLOCKEDSTK END,
QTY_MARSQUANINSPSTK = case when (dbo.tStock.QTY_MARSQUANINSPSTK is null) then 0 else dbo.tStock.QTY_MARSQUANINSPSTK END,
QTY_VENDORNETUNSTRICTEDSTK = case when (dbo.tStock.QTY_VENDORNETUNSTRICTEDSTK is null) then 0 else dbo.tStock.QTY_VENDORNETUNSTRICTEDSTK END,
Qty_VENDORTOTALSTK = case when (dbo.tStock.Qty_VENDORTOTALSTK is null) then 0 else dbo.tStock.Qty_VENDORTOTALSTK END,
Qty_VENDORBLOCKEDSTK = case when (dbo.tStock.Qty_VENDORBLOCKEDSTK is null) then 0 else dbo.tStock.Qty_VENDORBLOCKEDSTK END,
Qty_VENDORQUANINSPSTK = case when (dbo.tStock.Qty_VENDORQUANINSPSTK is null) then 0 else dbo.tStock.Qty_VENDORQUANINSPSTK END,
QTY_VENDORISSUEDNETUNSTRICTEDSTK = case when (dbo.tStock.QTY_VENDORISSUEDNETUNSTRICTEDSTK is null) then 0 else dbo.tStock.QTY_VENDORISSUEDNETUNSTRICTEDSTK END,
Qty_VENDORISSUEDTOTALSTK = case when (dbo.tStock.Qty_VENDORISSUEDTOTALSTK is null) then 0 else dbo.tStock.Qty_VENDORISSUEDTOTALSTK END,
Qty_VENDORISSUEDBLOCKEDSTK = case when (dbo.tStock.Qty_VENDORISSUEDBLOCKEDSTK is null) then 0 else dbo.tStock.Qty_VENDORISSUEDBLOCKEDSTK END,
Qty_VENDORISSUEDQUANINSPSTK = case when (dbo.tStock.Qty_VENDORISSUEDQUANINSPSTK is null) then 0 else dbo.tStock.Qty_VENDORISSUEDQUANINSPSTK END,
BASE_UNIT_OF_MEASURE = case when (dbo.tStock.BASE_UNIT_OF_MEASURE is null) then '' else dbo.tStock.BASE_UNIT_OF_MEASURE END,
STOCKDATE = case when (dbo.tStock.DOCDATE is null) then 0 else dbo.tStock.DOCDATE END
FROM dbo.TEMPREQ_W LEFT OUTER JOIN
dbo.tStock ON dbo.TEMPREQ_W.COMPA = dbo.tStock.COMPA AND
dbo.TEMPREQ_W.PLANT = dbo.tStock.PLANT AND
dbo.TEMPREQ_W.MAT_NUMBER = dbo.tStock.MAT_NUMBER AND
DATEADD(Minute, DATEDIFF(Minute, 0, dbo.tStock.DOCDATE), 0)=
DATEADD(Minute, DATEDIFF(Minute, 0, @STOCKDATE), 0) --OR
--DATEADD(Minute, DATEDIFF(Minute, 0, dbo.TEMPREQ_W.STOCKdate), 0) IS NULL
--Update the TEMP-table with the "SAP Sales Orders"-data that was selected, adding the cumulated value
UPDATE TEMPREQ_W
SET SALESORD_TOTAL = case when (dbo.vwSalesOrd_cumul.SalesOrd is null) then 0 else dbo.vwSalesOrd_cumul.SalesOrd END
FROM dbo.TEMPREQ_W LEFT OUTER JOIN
dbo.vwSalesOrd_cumul ON dbo.TEMPREQ_W.COMPA = dbo.vwSalesOrd_cumul.CODE AND
dbo.TEMPREQ_W.MAT_NUMBER = dbo.vwSalesOrd_cumul.CustMatNum AND
dbo.TEMPREQ_W.ImportDate = dbo.vwSalesOrd_cumul.ImportDate
--Update the TEMP-table with the "SAP Sales Orders"-data that was selected, adding the values/week
UPDATE TEMPREQ_W
SET SALESORD_WEEK = case when (dbo.vwSalesOrd_perWeek.SalesOrd is null) then 0 else dbo.vwSalesOrd_perWeek.SalesOrd END
FROM dbo.TEMPREQ_W LEFT OUTER JOIN
dbo.vwSalesOrd_perWeek ON dbo.TEMPREQ_W.COMPA = dbo.vwSalesOrd_perWeek.CODE AND
dbo.TEMPREQ_W.MAT_NUMBER = dbo.vwSalesOrd_perWeek.CustMatNum AND
dbo.TEMPREQ_W.ImportDate = dbo.vwSalesOrd_perWeek.ImportDate AND
dbo.TEMPREQ_W.DATE_VAN = dbo.vwSalesOrd_PerWeek.DATE_VAN
--Update the TEMP-table with the "SAP Production Orders"-data that was selected, adding the cumulated value
UPDATE TEMPREQ_W
SET PRODORD_TOTAL = case when (dbo.vwProdOrd_cumul.ProdOrd is null) then 0 else dbo.vwProdOrd_cumul.ProdOrd END
FROM dbo.TEMPREQ_W LEFT OUTER JOIN
dbo.vwProdOrd_cumul ON dbo.TEMPREQ_W.COMPA = dbo.vwProdOrd_cumul.CODE AND
dbo.TEMPREQ_W.MAT_NUMBER = dbo.vwProdOrd_cumul.CustMatNum AND
dbo.TEMPREQ_W.ImportDate = dbo.vwProdOrd_cumul.ImportDate
--Update the TEMP-table with the "SAP Production Orders"-data that was selected, adding the values/week
UPDATE TEMPREQ_W
SET PRODORD_WEEK = case when (dbo.vwProdOrd_perWeek.ProdOrd is null) then 0 else dbo.vwProdOrd_perWeek.ProdOrd END
FROM dbo.TEMPREQ_W LEFT OUTER JOIN
dbo.vwProdOrd_perWeek ON dbo.TEMPREQ_W.COMPA = dbo.vwProdOrd_perWeek.CODE AND
dbo.TEMPREQ_W.MAT_NUMBER = dbo.vwProdOrd_perWeek.CustMatNum AND
dbo.TEMPREQ_W.ImportDate = dbo.vwProdOrd_perWeek.ImportDate AND
dbo.TEMPREQ_W.DATE_VAN = dbo.vwProdOrd_PerWeek.DATE_VAN
--Update the TEMP-table with the "SAP Stock"-data that was selected
UPDATE TEMPREQ_W
SET STOCK_HALEN = case when (dbo.vwStockHalen.Ustock is null) then 0 else dbo.vwStockHalen.Ustock END,
STOCK_HALEN_BUN = case when (dbo.vwStockHalen.Ustock is null) then '' else dbo.vwStockHalen.BUn END
FROM dbo.TEMPREQ_W LEFT OUTER JOIN
dbo.vwStockHalen ON dbo.TEMPREQ_W.COMPA = dbo.vwStockHalen.CODE AND
dbo.TEMPREQ_W.MAT_NUMBER = dbo.vwStockHalen.CustMatNum AND
dbo.TEMPREQ_W.ImportDate = dbo.vwStockHalen.ImportDate
-- Empty and fill a temporary table to put the "SAP Stock"-data that was selected
-- This table will be used to create the subreports
DELETE FROM dbo.TEMPSAPSTOCK
INSERT INTO dbo.TEMPSAPSTOCK
(CustMatNum, Customer, Material, SLoc, Unrestr, InQual, Total, [Special Stock], BUn, StockKlant, ImportDate, CODE)
SELECT CustMatNum, Customer, Material, SLoc, Unrestr, InQual, Total, [Special Stock], BUn, StockKlant, ImportDate, CODE
FROM dbo.vwStock
WHERE (DATEADD(Minute, DATEDIFF(Minute, 0, ImportDate), 0)= DATEADD(Minute, DATEDIFF(Minute, 0, @IMPORTDATE), 0) OR
DATEADD(Minute, DATEDIFF(Minute, 0, ImportDate), 0) IS NULL)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
