WITH

/*

[NAME]

- HANA_ABAP_InstalledComponents

[DESCRIPTION]

- Overview of installed ABAP components

[SOURCE]

- SAP Note 1969700

[DETAILS AND RESTRICTIONS]

- Tables CVERS, PAT03, PRDVERS and STACKHDR only available in SAP ABAP environments
- You have to be connected to the SAP<sid> schema otherwise the following error is
issued:

[259]: invalid table name: Could not find table/view PRDVERS in schema

- If access to ABAP objects is possible but you cannot log on as ABAP user, you can
switch the default schema before executing the command:

SET SCHEMA SAP<sid>

[VALID FOR]

- Revisions: all
- Client application: ABAP

[SQL COMMAND VERSION]

- 2020/07/19: 1.0 (initial version)

- 2020/09/15: 1.1 (SP_LEVEL included)
- 2020/11/29: 1.2 (EXCLUDE_SUB_COMPONENTS included)
- 2021/11/10: 1.3 (significant redesign)
- 2021/12/03: 1.4 (SP included)
- 2022/09/30: 1.5 (AVERS removed, now PAT03 and STACKHEADR used)

[INVOLVED TABLES]

- CVERS
- PAT03
- PRDVERS
- STACKHDR

[INPUT PARAMETERS]

- NAME

Name of installed component

'SAP_BASIS' --> Show details for installed component SAP_BASIS

'PI%' --> Show details for installed components starting with "PI"
'%' --> No restriction related to installed component
- VERSION

Component version

'750' --> Show components with release 750

'%' --> No restriction related installed release

- DESCRIPTION

Component description

'Basis Plug-In' --> Show components with description "Basis Plug-In"

'DMIS%' --> Show components with descriptions starting with "DMIS"
'%' --> No restriction related to component description

- SKIP_PREVIOUS_RELEASES

Possibility to skip older releases meanwhile replaced with a newer version

'X' --> Only show most recent release of a component

' ' --> Also show previously installed releases of a component

- EXCLUDE_SUB_COMPONENTS

Possibility to exclude sub components

'X' --> Only display main components

' ' --> No restriction related to sub components

[OUTPUT PARAMETERS]

NAME,
VERSION,
SP,
DESCRIPTION,
INSTALL_TIME

- NAME: Component name

- VERSION: Component version
- SP: Component support package level
- DESCRIPTION: Component description
- INSTALL_TIME: Component installation time

[EXAMPLE OUTPUT]

-----------------------------------------------------------------------------------
--------------------------------------------------
|NAME |VERSION |SP |DESCRIPTION
|INSTALL_TIME |
-----------------------------------------------------------------------------------
--------------------------------------------------
|EHP3 FOR SAP SCM 7.0 |EHP3 FOR SAP SCM 7.0|22 (03/2021) |EHP3 FOR SAP
SCM 7.0 |2021/10/24 [Link]|
|SAP GRC ACCESS CONTROL |11 |27 (11/2020) |SAP ACCESS
CONTROL 10.1 |2021/10/24 [Link]|
|SAP GRC PROCESS CONTROL |11 |27 (11/2020) |SAP PROCESS
CONTROL 10.1 |2021/10/24 [Link]|
|SAP LANDSCAPE MGMT ENTERPRISES|1.0 |04 (09/2012) |SAP NW
LANDSC VIRT MGT ENT 1.0 |2018/10/20 [Link]|
|SAP NETWEAVER |7.4 |25 (12/2020) |SAP
NETWEAVER 7.4 |2021/10/24 [Link]|
|SAP SCM |7.0 |23 (01/2021) |SAP SCM 7.0
|2021/10/24 [Link]|
|SAP TDMS 4.0 |SAP TDMS 4.0 |SPS 20 (02/2021) |SAP TDMS 4.0
|2021/10/24 [Link]|
|SLT |2.0 |SP20 (02/2021) SP|SAP LT
REPLICATION SERVER 2.0 |2021/10/24 [Link]|
| | | |
| |
|BI_CONT |747 |0034 |BI_CONT 747:
Support Package 0034 |2021/10/24 [Link]|
|DMIS |2011_1_731 |0021 |DMIS
2011_1_731: SP 0021 |2022/03/12 [Link]|
|DMIS_CNT |2011_1_731 |0021 |DMIS_CNT
2011_1_731: SP 0021 |2022/03/12 [Link]|
|EA-IPPE |400 |0033 |SP 33 for
EA-IPPE 4.00 |2021/10/24 [Link]|
|GRCFND_A |V1100 |0027 |GRCFND_A
V1100: SP 0027 |2021/03/13 [Link]|
|GRCPINW |V1100_731 |0028 |GRCPINW
V1100_731: SP 0028 |2021/03/13 [Link]|
...
-----------------------------------------------------------------------------------
--------------------------------------------------

*/

BASIS_INFO AS
( SELECT /* Modification section */
'%' NAME,
'%' VERSION,
'%' DESCRIPTION,
'X' SKIP_PREVIOUS_RELEASES,
'X' EXCLUDE_SUB_COMPONENTS
FROM
DUMMY
)
SELECT
NAME,
VERSION,
SP,
DESCRIPTION,
INSTALL_TIME
FROM
( SELECT
LINE_NO LINE_NO,
NAME,
VERSION,
SP,
DESCRIPTION,
INSTALL_TIME
FROM
( SELECT
ROW_NUMBER () OVER (ORDER BY [Link], P. VERSION) LINE_NO,
ROW_NUMBER () OVER (PARTITION BY [Link] ORDER BY S.INST_DATE || S.INST_TIME
DESC) LINE_NO_2,
[Link] NAME,
 [Link] VERSION,
IFNULL([Link], '') SP,
[Link] DESCRIPTION,
TO_VARCHAR(TO_TIMESTAMP(S.INST_DATE || S.INST_TIME, 'YYYYMMDDHH24MISS'),
'YYYY/MM/DD HH24:MI:SS') INSTALL_TIME,
BI.SKIP_PREVIOUS_RELEASES
FROM
BASIS_INFO BI,
PRDVERS P LEFT OUTER JOIN
STACKHEADR S ON
S.PROD_ID = [Link]
WHERE
[Link] LIKE [Link] AND
[Link] LIKE [Link] AND
[Link] LIKE [Link] AND
[Link] = '+'
)
WHERE
( SKIP_PREVIOUS_RELEASES = ' ' OR LINE_NO_2 = 1 )
UNION ALL
SELECT 999, '', '', '', '', '' FROM DUMMY
UNION ALL
SELECT
2000 + LINE_NO LINE_NO,
NAME,
VERSION,
SP,
DESCRIPTION,
INSTALL_TIME
FROM
( SELECT
ROW_NUMBER () OVER (ORDER BY [Link], [Link]) LINE_NO,
ROW_NUMBER () OVER (PARTITION BY [Link] ORDER BY P.IMPLE_DATE ||
P.IMPLE_TIME || P.DELIV_DATE || P.DELIV_TIME DESC) LINE_NO_2,
[Link] NAME,
[Link] VERSION,
MAP(IFNULL(P.TO_REL, ''), '', IFNULL([Link], ''), IFNULL(P.TO_REL, ''))
SP,
P.SHORT_TEXT DESCRIPTION,
TO_VARCHAR(TO_TIMESTAMP(P.IMPLE_DATE || P.IMPLE_TIME, 'YYYYMMDDHH24MISS'),
'YYYY/MM/DD HH24:MI:SS') INSTALL_TIME,
BI.SKIP_PREVIOUS_RELEASES
FROM
BASIS_INFO BI,
CVERS C LEFT OUTER JOIN
( SELECT ADDON_ID COMP, ADDON_REL RELEASE, SHORT_TEXT, IMPLE_DATE,
IMPLE_TIME, DELIV_DATE, DELIV_TIME, TO_REL FROM PAT03 UNION ALL
SELECT COMPONENT COMP, COMP_REL RELEASE, SHORT_TEXT, IMPLE_DATE,
IMPLE_TIME, DELIV_DATE, DELIV_TIME, TO_REL FROM PAT03
) P ON
[Link] = [Link] AND [Link] = [Link]
WHERE
[Link] LIKE [Link] AND
[Link] LIKE [Link] AND
P.SHORT_TEXT LIKE [Link] AND
( BI.EXCLUDE_SUB_COMPONENTS = ' ' OR UPPER(SUBSTR(P.SHORT_TEXT, 1, 10)) NOT
IN ('SUB COMPON', 'SUBCOMPONE') )
)
WHERE
 ( SKIP_PREVIOUS_RELEASES = ' ' OR LINE_NO_2 = 1 )
)
ORDER BY
LINE_NO