1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40
|
SELECT
...
(CASE
WHEN dbo.REPCUSTOMATTRIB( evt.EVT_CODE, '', 'REGCAT', 'EVNT', 'R5' ) = 'OEM' THEN
(SELECT TOP 1 DAE_DOCUMENT FROM R5DOCENTITIES INNER JOIN R5DOCUMENTS ON DOC_CODE = DAE_DOCUMENT
WHERE DOC_CLASS='CTR'AND DAE_RENTITY='OBJ' AND DAE_RTYPE='P' AND DAE_CODE = ( evt.EVT_UDFCHAR23 + '#' + (SELECT OBJ_ORG FROM R5OBJECTS WHERE OBJ_CODE = evt.EVT_UDFCHAR23 AND OBJ_OBRTYPE ='P') ) )
WHEN dbo.REPCUSTOMATTRIB( evt.EVT_CODE, '', 'REGCAT', 'EVNT', 'R5' ) = 'SAVC' THEN
(SELECT TOP 1 DAE_DOCUMENT FROM R5DOCENTITIES INNER JOIN R5DOCUMENTS ON DOC_CODE = DAE_DOCUMENT
WHERE DOC_CLASS='CMM'AND DAE_RENTITY='OBJ' AND DAE_RTYPE='P'
AND DAE_CODE = ( evt.EVT_UDFCHAR23 + '#' + (SELECT OBJ_ORG FROM R5OBJECTS WHERE OBJ_CODE = evt.EVT_UDFCHAR23 AND OBJ_OBRTYPE ='P')) )
WHEN dbo.REPCUSTOMATTRIB( evt.EVT_CODE, '', 'REGCAT', 'EVNT', 'R5' ) = 'SAVM' THEN
(SELECT TOP 1 DAE_DOCUMENT FROM R5DOCENTITIES INNER JOIN R5DOCUMENTS ON DOC_CODE = DAE_DOCUMENT
WHERE DOC_CLASS='CMM'AND DAE_RENTITY='OBJ' AND DAE_RTYPE='P'
AND DAE_CODE = ( evt.EVT_UDFCHAR23 + '#' + (SELECT OBJ_ORG FROM R5OBJECTS WHERE OBJ_CODE = evt.EVT_UDFCHAR23 AND OBJ_OBRTYPE ='P')) )
END) as MANUEL,
(select DOC_REVNUMBER from R5DOCUMENTS where DOC_CODE =
(CASE
WHEN dbo.REPCUSTOMATTRIB( evt.EVT_CODE, '', 'REGCAT', 'EVNT', 'R5' ) = 'OEM' THEN
(SELECT TOP 1 DAE_DOCUMENT FROM R5DOCENTITIES INNER JOIN R5DOCUMENTS ON DOC_CODE = DAE_DOCUMENT
WHERE DOC_CLASS='CTR'AND DAE_RENTITY='OBJ' AND DAE_RTYPE='P' AND DAE_CODE = ( evt.EVT_UDFCHAR23 + '#' + (SELECT OBJ_ORG FROM R5OBJECTS WHERE OBJ_CODE = evt.EVT_UDFCHAR23 AND OBJ_OBRTYPE ='P') ) )
WHEN dbo.REPCUSTOMATTRIB( evt.EVT_CODE, '', 'REGCAT', 'EVNT', 'R5' ) = 'SAVC' THEN
(SELECT TOP 1 DAE_DOCUMENT FROM R5DOCENTITIES INNER JOIN R5DOCUMENTS ON DOC_CODE = DAE_DOCUMENT
WHERE DOC_CLASS='CMM'AND DAE_RENTITY='OBJ' AND DAE_RTYPE='P'
AND DAE_CODE = ( evt.EVT_UDFCHAR23 + '#' + (SELECT OBJ_ORG FROM R5OBJECTS WHERE OBJ_CODE = evt.EVT_UDFCHAR23 AND OBJ_OBRTYPE ='P')) )
WHEN dbo.REPCUSTOMATTRIB( evt.EVT_CODE, '', 'REGCAT', 'EVNT', 'R5' ) = 'SAVM' THEN
(SELECT TOP 1 DAE_DOCUMENT FROM R5DOCENTITIES INNER JOIN R5DOCUMENTS ON DOC_CODE = DAE_DOCUMENT
WHERE DOC_CLASS='CMM'AND DAE_RENTITY='OBJ' AND DAE_RTYPE='P'
AND DAE_CODE = ( evt.EVT_UDFCHAR23 + '#' + (SELECT OBJ_ORG FROM R5OBJECTS WHERE OBJ_CODE = evt.EVT_UDFCHAR23 AND OBJ_OBRTYPE ='P')) )
END) ) as REV_NUMBER
FROM
...
WHERE
... |
Partager