ECC Admin - Data Sets

Description
Categories: Enginatics, Toolkit - Data Management
Repository: Github Columns: System Name, Application, Data Set, Data Set Key, Description, Enabled, Dataset Id, Incremental Load Proc, Full Load Proc, Metadata Load Proc ...
Enterprise Command Centers applications with data sets and load rule DB procedure names for incremental, full and metadata load.
For description of the load process, see ECC Concurrent Programs https://www.enginatics.com/reports/ecc-concurrent-programs/
select
ess.system_name,
(
select distinct
listagg(eat.application_name,', ') within group (order by eat.application_name) over (partition by eadr.dataset_id) application 
from
ecc.ecc_app_ds_relationships eadr,
ecc.ecc_application_tl eat
where
edb.dataset_id=eadr.dataset_id and
eadr.app_ds_rel_type='OWNED' and
eadr.application_id=eat.application_id and
eat.language=xxen_util.bcp47_language(userenv('lang'))
) application,
edt.display_name data_set,
edb.dataset_key data_set_key,
decode(edt.dataset_description,'null',null,edt.dataset_description) description,
xxen_util.meaning(decode(edb.enabled_flag,'Y','Y'),'YES_NO',0) enabled,
x.*,
esr.security_handler_name
from
ecc.ecc_source_system ess,
ecc.ecc_dataset_b edb,
ecc.ecc_dataset_tl edt,
(
select
edlr.dataset_id,
xxen_util.meaning(edlr.load_type,'ECC_LOAD_TYPE_LKUP',0) load_type,
edlr.package_name||nvl2(edlr.procedure_name,'.'||edlr.procedure_name,null) procedure_name
from
ecc.ecc_dataset_load_rules edlr
)
pivot (
max(procedure_name) proc
for
load_type in (
'Incremental data load' incremental_load,
'Full data load' full_load,
'Metadata load' metadata_load
)
) x,
ecc.ecc_security_rules esr
where
1=1 and
ess.system_id=edb.system_id and
edb.dataset_id=edt.dataset_id and
edt.language=xxen_util.bcp47_language(userenv('lang')) and
edt.dataset_id=x.dataset_id(+) and
edb.dataset_id=esr.applies_to_entity(+) and
esr.applies_to_type(+)='DATASET'
order by
ess.system_name,
edb.dataset_key
Parameter Name SQL text Validation
Application
edb.dataset_id in (
select
eadr.dataset_id
from
ecc.ecc_application_tl eat,
ecc.ecc_app_ds_relationships eadr
where
eat.application_name=:application_name and
eat.language='en' and
eat.application_id=eadr.application_id
)
LOV
Data Set
edt.display_name=:data_set
LOV