CREATE PROCEDURE [dbo].[SAP_Translations]
-- 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(Name) FROM [@LAYOUTTRANSLATIONS] FOR XML PATH('')
    ), 1, 2, ''
) + ']'

CREATE TABLE #SAP_TRANSLATIONS
(
    DocEntry INT,
    LKey NVARCHAR(100), Translation NVARCHAR(MAX)
)
INSERT INTO #SAP_TRANSLATIONS EXEC
(
    'SELECT ' + @DocKey@ + ', T0.Name, ISNULL(T2.Trans, '''')
    FROM OMLT T1 INNER JOIN MLT1 T2
    ON T1.TranEntry = T2.TranEntry
    RIGHT JOIN [@LAYOUTTRANSLATIONS] T0
    ON(T1.PK = T0.Code AND T1.TableName =
    ''@LAYOUTTRANSLATIONS'' AND T1.FieldAlias = ''Name'')
     WHERE T2.LangCode =' + @LanguageID
)


EXEC
(
    'SELECT * FROM #SAP_TRANSLATIONS
    pivot
    (

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

END

GO