USE [X];
GO

/****** Object:  View [dbo].[BM_211_URETIM_PLANLANAN]    Script Date: 28.1.2020 14:34:41 ******/
SET ANSI_NULLS ON;
GO

SET QUOTED_IDENTIFIER ON;
GO





CREATE VIEW [dbo].[BM_211_URETIM_PLANLANAN]
AS
SELECT *,
       CASE
           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
FROM
(
    SELECT PL.LOGICALREF,
           I.LOGICALREF LOGICALREFI,
           STFICHE.FICHENO,
           CASE
               WHEN STFICHE.TRCODE = 11 THEN
                   'Fire Fişi'
               WHEN STFICHE.TRCODE = 12 THEN
                   'Sarf Fişi'
               WHEN STFICHE.TRCODE = 13 THEN
                   'Üretimden Giriş Fiişi'
               ELSE
                   CONVERT(VARCHAR, STFICHE.TRCODE)
           END [Fiş Türü],
           CASE
               WHEN YEAR(PO.DATE_) IS NULL THEN
                   YEAR(STFICHE.DATE_)
               ELSE
                   YEAR(PO.DATE_)
           END YIL,
           CASE
               WHEN YEAR(PO.DATE_) * 100 + MONTH(PO.DATE_) IS NULL THEN
                   YEAR(STFICHE.DATE_) * 100 + MONTH(STFICHE.DATE_)
               ELSE
                   YEAR(PO.DATE_) * 100 + MONTH(PO.DATE_)
           END AY,
           CASE
               WHEN PO.DATE_ IS NULL THEN
                   STFICHE.DATE_
               ELSE
                   PO.DATE_
           END Tarih,
           CASE
               WHEN PO.FICHENO IS NULL THEN
                   STFICHE.PORDERFICHENO
               ELSE
                   PO.FICHENO
           END [Üretim No],
           STFICHE.BRANCH [İş Yeri Nr],
           (
               SELECT NAME
               FROM L_CAPIDIV WITH (NOLOCK)
               WHERE FIRMNR = 211
                     AND NR = STFICHE.BRANCH
           ) [İş Yeri Adı],
           CASE
               WHEN
               (
                   PL.LINETYPE = '4'
                   OR PL.LINETYPE IS NULL
               ) THEN
                   'Ana Ürün'
               WHEN PL.LINETYPE = '2' THEN
                   'Ek Ürün'
               WHEN PL.LINETYPE = '3' THEN
                   'Yan Ürün'
               WHEN PL.LINETYPE = '0' THEN
                   'Girdi'
               ELSE
                   CONVERT(VARCHAR(3), PL.LINETYPE)
           END [Satır Tipi],
           I.CODE [Malzeme Kodu],
           I.SPECODE [Malzeme Özel Kodu],
           I.NAME [Malzeme Açıklaması],
           (
               SELECT CODE
               FROM LG_211_UNITSETF WITH (NOLOCK)
               WHERE LOGICALREF = I.UNITSETREF
           ) AS [Malzeme Birimi],
           CASE
               WHEN PL.FORMULA = '' THEN
                   '1'
               ELSE
                   ISNULL(REPLACE(PL.FORMULA, ' ', ''), '0')
           END [Miktar Formülü],
           ISNULL(PL.FORMULA, '') [Formul Miktarı],
           --CASE WHEN PL.LINETYPE=0 THEN (SELECT SUM(STL.AMOUNT) FROM LG_211_01_STLINE STL WITH (NOLOCK) WHERE STL.STOCKREF=PL.ITEMREF AND STL.POLINEREF=pl.LOGICALREF AND STL.TRCODE=12 
           --AND LPRODSTAT=0  AND YEAR(STL.DATE_)>2018)
           --WHEN PL.LINETYPE=4 THEN (SELECT SUM(STL.AMOUNT) FROM LG_211_01_STLINE STL WITH (NOLOCK) WHERE  STL.STOCKREF=PL.ITEMREF AND STL.POLINEREF=pl.LOGICALREF AND STL.TRCODE=13 
           --AND LPRODSTAT=0 AND YEAR(STL.DATE_)>2018  
           --) ELSE 0 END 
           STLINE.AMOUNT [Gerçekleşen Miktar],
           ISNULL(PL.AMOUNT, '') [Planlanan Miktar],
           ISNULL(
           (
               SELECT TOP 1
                      AMOUNT
               FROM LG_211_POLINE pl2
               WHERE pl2.PRODORDREF = PL.PRODORDREF
                     AND pl2.LINETYPE = 4
               ORDER BY PL.LOGICALREF ASC
           ),
           0
                 ) [Uretim Miktar],
           ISNULL(CONVERT(DECIMAL(10, 2), STLINE.AMOUNT - PL.AMOUNT), 0) Fark
    FROM LG_211_01_STLINE STLINE
        LEFT JOIN LG_211_01_STFICHE STFICHE
            ON STFICHE.LOGICALREF = STLINE.STFICHEREF
        LEFT JOIN LG_211_POLINE PL
            ON PL.LOGICALREF = STLINE.POLINEREF /*ÜRETİM EMRİ SATIRLARI*/
        LEFT JOIN LG_211_PRODORD PO
            ON PO.LOGICALREF = PL.PRODORDREF /*ÜRETİM EMRİ FİŞLERİ*/
        LEFT JOIN LG_211_ITEMS I
            ON I.LOGICALREF = STLINE.STOCKREF
    WHERE
        --STFICHE.PORDERFICHENO='2019080010' AND 
        STLINE.LPRODSTAT = 0
        AND STFICHE.PRODORDERREF <> 0
/*ORDER BY STFICHE.FICHENO*/
) AS C;


GO

USE [X];
GO

/****** Object:  StoredProcedure [dbo].[BM_P_211_URETIM_PLANLANAN]    Script Date: 28.1.2020 14:35:30 ******/
SET ANSI_NULLS ON;
GO

SET QUOTED_IDENTIFIER ON;
GO









CREATE PROC [dbo].[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
    --STFICHENO,
    --,PL2.LOGICALREF,
    YIL,
    AY,
    Tarih,
    [Üretim No],
    [Fiş Türü],
    FICHENO [Fiş No],
    [İş Yeri Adı],
    [Satır Tipi],
    [Malzeme Kodu],
    [Malzeme Özel Kodu],
    [Malzeme Açıklaması],
    [Malzeme Birimi],
    Formül,
    ISNULL(T.[Planlanan Miktar], 0) [Formul Miktarı],
    [Gerçekleşen Miktar],
    PL2.[Planlanan Miktar], /*,[Uretim Miktar]*/
    CONVERT(DECIMAL(10, 2), [Gerçekleşen Miktar] - PL2.[Planlanan Miktar]) Fark
FROM BM_211_URETIM_PLANLANAN PL2
    LEFT JOIN @temp T
        ON T.LOGICALREF = PL2.LOGICALREF;


--GO


--EXEC [dbo].[BM_P_211_URETIM_PLANLANAN]
GO