/****** Object:  View [dbo].[BM_211_URETIM_PLANLANAN]    Script Date: 29.4.2019 14:19:42 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

  create VIEW [dbo].[BM_211_URETIM_PLANLANAN] as
SELECT
c.LOGICALREF,Tarih,C.[Fiş No],LOGICALREFI,C.[Malzeme Kodu],C.[Malzeme Açıklaması],
 CASE
 WHEN C.[Miktar Formülü]='' THEN '0'
  WHEN  REPLACE(C.[Miktar Formülü],'P1', [Uretim Miktar])='P1' THEN ISNULL(CONVERT(VARCHAR(20),C.[Uretim Miktar]),'0') ELSE  ISNULL(REPLACE(C.[Miktar Formülü],'P1', [Uretim Miktar]),'0') END Formül  ,

C.[Gerçekleşen Miktar],C.[Planlanan Miktar]

 FROM (

SELECT
PRDLN.LOGICALREF ,
PO.DATE_ Tarih,
po.FICHENO [Fiş No],
 ITMSC.LOGICALREF LOGICALREFI,
  ITMSC.CODE [Malzeme Kodu],
  ITMSC.NAME [Malzeme Açıklaması],
  --PRDLN.FORMULA [Miktar Formülü],
CASE WHEN PRDLN.FORMULA='' THEN '1' ELSE   ISNULL(REPLACE(PRDLN.FORMULA,' ',''),'0') END [Miktar Formülü] ,
CASE WHEN  PRDLN.AMOUNT ='' THEN '0' ELSE   PRDLN.AMOUNT END [Gerçekleşen Miktar],
  0 [Planlanan Miktar],
  (SELECT TOP 1 amount FROM LG_211_POLINE pl2 WHERE pl2.PRODORDREF=PRDLN.PRODORDREF AND pl2.LINETYPE=4 ORDER BY PRDLN.LOGICALREF ASC ) [Uretim Miktar]

FROM LG_211_POLINE PRDLN WITH (NOLOCK)
LEFT OUTER JOIN LG_211_ITEMS ITMSC WITH (NOLOCK)
  ON (PRDLN.ITEMREF = ITMSC.LOGICALREF)
LEFT OUTER JOIN LG_211_VARIANT VARIANT WITH (NOLOCK)
  ON (PRDLN.VARIANTREF = VARIANT.LOGICALREF)
 LEFT JOIN LG_211_PRODORD po ON po.LOGICALREF = PRDLN.PRODORDREF
WHERE
--PRDLN.DISPLINEREF = 22139  AND
 (PRDLN.DETLINE = 0)

) AS C

GO

 CREATE PROC BM_P_211_URETIM_PLANLANAN AS

declare @sql nvarchar(max) = (
SELECT  STUFF( (SELECT ' select ' + str([LOGICALREF])  + ', ' + str([LOGICALREFI])  + ', ' + REPLACE( Formül,',','.') +';'
               from BM_211_URETIM_PLANLANAN t
               for xml path ('')
              ), 1, 1, '' )
)
    declare @temp table ( [LOGICALREF] int, [LOGICALREFI] int, [Planlanan Miktar] FLOAT);
    insert @temp exec(@sql);
    SELECT --LOGICALREF LOGICALREFPL,
YEAR(DATE_) YIL,
YEAR(DATE_)*100+ MONTH(DATE_) AY,
PO.DATE_ Tarih,
    PO.FICHENO [Fiş No],
CASE WHEN  PL.LINETYPE='4' THEN 'Ana Ürün' WHEN  PL.LINETYPE='0' THEN 'Girdi' ELSE CONVERT(VARCHAR(3),LINETYPE) end [Satır Tipi],
I.CODE [Malzeme Kodu],
I.NAME [Malzeme Açıklaması],
PL.FORMULA Formül,
PL.AMOUNT [Gerçekleşen Miktar],
[Planlanan Miktar] from @temp T
LEFT JOIN LG_211_POLINE PL ON PL.LOGICALREF=T.LOGICALREF
    LEFT JOIN LG_211_PRODORD PO ON PO.LOGICALREF = PL.PRODORDREF
LEFT JOIN LG_211_ITEMS I ON I.LOGICALREF = LOGICALREFI
WHERE  PL.DETLINE = 0

 EXEC BM_P_211_URETIM_PLANLANAN