CREATE VIEW dbo.STOK_RAPOR_SIPARIS_VER
AS
SELECT 
    QQ.[OEM1], 
    QQ.[OEM2], 
    QQ.[ACIKLAMA], 
    CASE 
        WHEN (
            SELECT 
                SUM(CAST(AMOUNT AS INT)) 
            FROM 
                [UNIVERSALDB].[dbo].[STOK_RAPOR_SIPARISLER] 
            WHERE 
                STOCKREF = QQ.LOGICALREF AND TYPE_ = 1 
                AND AMOUNT NOT LIKE '%?%' 
                AND ISNUMERIC(AMOUNT) = 1
        ) IS NOT NULL 
        AND (
            SELECT 
                SUM(CAST(AMOUNT AS INT)) 
            FROM 
                [UNIVERSALDB].[dbo].[STOK_RAPOR_SIPARISLER] 
            WHERE 
                STOCKREF = QQ.LOGICALREF AND TYPE_ = 1 
                AND AMOUNT NOT LIKE '%?%' 
                AND ISNUMERIC(AMOUNT) = 1
        ) > 0 
        THEN CONCAT(QQ.[SHELFLIFE], ' + ', 
            (
                SELECT 
                    STUFF(
                        (
                            SELECT 
                                ' + ' + CAST(AMOUNT AS NVARCHAR(MAX)) 
                            FROM 
                                [UNIVERSALDB].[dbo].[STOK_RAPOR_SIPARISLER] 
                            WHERE 
                                STOCKREF = QQ.LOGICALREF AND TYPE_ = 1 
                                AND AMOUNT NOT LIKE '%?%' 
                                AND ISNUMERIC(AMOUNT) = 1
                            FOR XML PATH(''), TYPE
                        ).value('.', 'NVARCHAR(MAX)'), 1, 3, ''
                    )
            )
        ) 
        ELSE QQ.[SHELFLIFE] 
    END AS [SHELFLIFE], 
    QQ.[LOGICALREF], 
    QQ.USERID
FROM 
    (
        SELECT 
            GEN.[OEM1], 
            GEN.[OEM2], 
            GEN.[ACIKLAMA], 
            ISNULL(SRS.AMOUNT, '') AS [SHELFLIFE], 
            GEN.[LOGICALREF], 
            SRS.USERID
        FROM 
            [UNIVERSALDB].[dbo].[STOK_RAPOR_GENEL] AS GEN 
            LEFT OUTER JOIN [UNIVERSALDB].[dbo].[STOK_RAPOR_SIPARISLER] AS SRS 
            ON SRS.STOCKREF = GEN.LOGICALREF AND SRS.TYPE_ = 0
    ) QQ
WHERE 
    QQ.[SHELFLIFE] IS NOT NULL 
    AND [SHELFLIFE] <> '' 
    AND [SHELFLIFE] NOT IN ('KONTROL EDILECEK', 'BEKLEMEDE', 'ÜRÜN DOGRU', 'ARIZALI', 'UYUMSUZ', 'YANLIS', 'ÜRETIM HATASI');