CREATE PROCEDURE UIS.GET_APPSITE(IN PACKAGE_ID NVARCHAR(256), IN OBJECT_NAME NVARCHAR(256), OUT APPSITE_DATA GET_APPSITE_TABLE_TYPE)
LANGUAGE SQLSCRIPT
SQL SECURITY DEFINER
DEFAULT SCHEMA UIS
READS SQL DATA AS
internal_site_id integer;
ws_count integer;
session_usr varchar(256);
locale nvarchar(256);
BEGIN
internal_site_id := -1;
SELECT count(*) into ws_count FROM "_SYS_RT"."_UIS_APPSITES" WHERE PACKAGE_ID LIKE :package_id AND OBJECT_NAME LIKE :object_name;
IF :ws_count > 0 THEN
SELECT INTERNAL_SITE_ID INTO internal_site_id FROM "_SYS_RT"."_UIS_APPSITES" WHERE PACKAGE_ID LIKE :package_id AND OBJECT_NAME LIKE :object_name;
END IF;
select session_context('LOCALE') INTO locale from SYS.dummy;
session_usr := SESSION_USER;
APPSITE_DATA =
SELECT
site_name,
site_layout,
pg_layout_id,
pg_id,
pg_name,
pg_layout,
wg_layout_id,
wg_id,
wg_name,
src,
icon,
type as wg_type,
key,
value,
parent_page_id,
'site' as record_type
FROM "UIS"."sap.hana.uis.db::UIS_APPSITES_DATA_VIEW"
WHERE INTERNAL_SITE_ID = :internal_site_id
UNION ALL
select
NULL as site_name,
NULL as site_layout,
NULL as pg_layout_id,
NULL as pg_id,
NULL as pg_name,
NULL as pg_layout,
NULL as wg_layout_id,
wg_id,
NULL as wg_name,
NULL as src,
NULL as icon,
NULL as wg_type,
key,
value,
NULL as parent_page_id,
'userwidgetprops' as record_type
FROM "_SYS_RT"."_UIS_USER_WIDGET_PROPERTIES"
WHERE INTERNAL_SITE_ID like :internal_site_id AND USER_NAME LIKE :session_usr
UNION ALL
select
NULL as site_name,
NULL as site_layout,
NULL as pg_layout_id,
NULL as pg_id,
NULL as pg_name,
NULL as pg_layout,
NULL as wg_layout_id,
NULL as wg_id,
NULL as wg_name,
NULL as src,
NULL as icon,
NULL as wg_type,
text_id as key,
content as value,
NULL as parent_page_id,
'translations' as record_type
from _SYS_REPO.TEXT_ACCESSOR(:package_id, :object_name, 'xsappsite', :locale);
END;