USE [SBODemoCH]
GO
/****** Object:  StoredProcedure [dbo].[CS_Print_TranslationsLD]    Script Date: 06.07.2018 09:15:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[CS_Print_TranslationsLD]
-- Add the parameters for the stored procedure here
(@DocKey@ INT, @ObjectId@ INT)

AS BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @object NVARCHAR(4000)

IF (@ObjectId@=13) SET @object = 'INV'
ELSE IF (@ObjectId@=14) SET @object = 'RIN'
ELSE IF (@ObjectId@=15) SET @object = 'DLN'
ELSE IF (@ObjectId@=16) SET @object = 'RDN'
ELSE IF (@ObjectId@=17) SET @object = 'RDR'
ELSE IF (@ObjectId@=18) SET @object = 'PCH'
ELSE IF (@ObjectId@=19) SET @object = 'RPC'
ELSE IF (@ObjectId@=20) SET @object = 'PDN'
ELSE IF (@ObjectId@=21) SET @object = 'RPD'
ELSE IF (@ObjectId@=22) SET @object = 'POR'
ELSE IF (@ObjectId@=23) SET @object = 'QUT'
ELSE IF (@ObjectId@=24) SET @object = 'RCT'
ELSE IF (@ObjectId@=25) SET @object = 'DPS'
ELSE IF (@ObjectId@=46) SET @object = 'VPM'
ELSE IF (@ObjectId@=58) SET @object = 'INM'
ELSE IF (@ObjectId@=59) SET @object = 'IGN'
ELSE IF (@ObjectId@=60) SET @object = 'IGE'
ELSE IF (@ObjectId@=67) SET @object = 'WTR'
ELSE IF (@ObjectId@=68) SET @object = 'WKO'
ELSE IF (@ObjectId@=69) SET @object = 'IPF'
ELSE IF (@ObjectId@=112) SET @object = 'DRF'
ELSE IF (@ObjectId@=132) SET @object = 'CIN'
ELSE IF (@ObjectId@=140) SET @object = 'PDF'
ELSE IF (@ObjectId@=162) SET @object = 'MRV'
ELSE IF (@ObjectId@=163) SET @object = 'CPI'
ELSE IF (@ObjectId@=164) SET @object = 'CPV'
ELSE IF (@ObjectId@=165) SET @object = 'CSI'
ELSE IF (@ObjectId@=166) SET @object = 'CSV'
ELSE IF (@ObjectId@=191) SET @object = 'SCL'
ELSE IF (@ObjectId@=198) SET @object = 'FCT'
ELSE IF (@ObjectId@=199) SET @object = 'MSN'
ELSE IF (@ObjectId@=202) SET @object = 'WOR'
ELSE IF (@ObjectId@=203) SET @object = 'DPI'
ELSE IF (@ObjectId@=204) SET @object = 'DPO'
ELSE IF (@ObjectId@=321) SET @object = 'ITR'
ELSE IF (@ObjectId@=140000009) SET @object = 'OEI'
ELSE IF (@ObjectId@=140000010) SET @object = 'IEI'
ELSE IF (@ObjectId@=540000006) SET @object = 'PQT'

CREATE TABLE #Language (LanguageID INT)
INSERT INTO #Language
EXEC
(
    'SELECT LangCode FROM O' + @object + ' WHERE DocEntry = ' + @DocKey@
)

DECLARE @LanguageID INT
SELECT @LanguageID = LanguageID FROM #Language
DROP TABLE #Language

DECLARE @LKeys VARCHAR(MAX)
SELECT  @LKeys = STUFF
(
    (
        SELECT '],[' + LTRIM(code) FROM [@SWA_LD_TEXT] FOR XML PATH('')
    ), 1, 2, ''
) + ']'

CREATE TABLE #CS_Print_TranslationsLD
(
    DocEntry INT,
    LKey NVARCHAR(100), Translation NVARCHAR(2000)
)
INSERT INTO #CS_Print_TranslationsLD EXEC
(
   /* 'SELECT ' + @DocKey@ + ', T0.Name, ISNULL(T2.Trans, '''')
    FROM OMLT T1 INNER JOIN MLT1 T2
    ON T1.TranEntry = T2.TranEntry
    RIGHT JOIN [@CRTEXT] T0
    ON(T1.PK = T0.Code AND T1.TableName =
    ''@CRTEXT'' AND T1.FieldAlias = ''Name'')
     WHERE T2.LangCode =' + @LanguageID
     */
    'SELECT ' + @DocKey@ + ', T0.code , CONVERT(NVARCHAR(2000), ISNULL(T2.Trans, ISNULL(T0.U_Text, '''')))
    FROM [@SWA_LD_TEXT] T0 LEFT JOIN OMLT T1 ON(T1.PK = T0.code AND T1.TableName =
    ''@SWA_LD_TEXT'' AND T1.FieldAlias = ''U_Text'')
    LEFT JOIN MLT1 T2 ON (T1.TranEntry = T2.TranEntry AND T2.LangCode =  '+ @LanguageID+')'
)


EXEC
(
    'SELECT * FROM #CS_Print_TranslationsLD
    pivot
    (

    MAX(Translation)
    FOR LKey in (' + @LKeys + ')
    ) AS Translations'
)

END