Solved the problem with the unsolved hint but the problm in the big query remains
Alessandra, May 29, 2024 - 7:28 pm UTC
set timing on
set heading off;
set sqlblanklines on;
SET FEEDBACK ON SQL_ID;
set feedback only;
with
t_ctis_error
AS
( SELECT ctxd.ctxd_ctrs_id ctrs_id, MAX (1) error
FROM ct_xml_data ctxd
INNER JOIN ct_staging_xml_data csxd
ON ctxd.ctxd_id = csxd.csxd_ctxd_id
AND csxd.csxd_logging_info IS NOT NULL
WHERE ctxd.ctxd_valid_to IS NULL
GROUP BY ctxd.ctxd_ctrs_id),
t_t_deatline
AS
(
SELECT DISTINCT
FIRST_VALUE (tide.tide_ctis_due_date)
OVER (PARTITION BY proc.tita_prcd_id
ORDER BY tide.tide_day) AS ctis_due_date,
proc.tita_prcd_id prcd_id
FROM (
SELECT FIRST_VALUE (tide_day)
OVER (PARTITION BY prcd_id
ORDER BY tide_day NULLS LAST)
AS min_day,
prcd_id,
tide_tita_id,
tide_ddln_step_compl_date,
tide_day,
tide_ctis_due_date,
max_tide_ddln_step_compl_date
FROM (
SELECT FIRST_VALUE (tide_ddln_step_compl_date)
OVER (
PARTITION BY prcd_id
ORDER BY
tide_ddln_step_compl_date DESC NULLS LAST)
AS max_tide_ddln_step_compl_date,
proc.prcd_id,
deadline.tide_tita_id,
deadline.tide_ddln_step_compl_date,
deadline.tide_day,
deadline.tide_ctis_due_date
FROM timetable_deadline deadline,
proc_timetable timetable,
procedure proc
WHERE
timetable.tita_prcd_id = proc.prcd_id
AND deadline.tide_tita_id =
timetable.tita_id)
WHERE NVL (tide_ddln_step_compl_date, SYSDATE) =
NVL (max_tide_ddln_step_compl_date, SYSDATE)
)
base,
proc_timetable proc
INNER JOIN timetable_deadline tide
ON tide.tide_tita_id = proc.tita_id
WHERE proc.tita_prcd_id = base.prcd_id
AND tide.tide_ddln_step_compl_date IS NULL
AND tide.tide_day >= base.min_day
),
role_base
AS
(SELECT role_prty_id, role_prcd_id
FROM pty_role
WHERE role_roltc_id =
HEXTORAW ('A443E226873F0087E0540010E0683AC2')),
meldein
AS
(
SELECT role_prcd_id,
prty.prty_id,
prty.prty_name,
addr.addr_street,
addr.addr_street_number,
addr.addr_street_number_addition,
addr.addr_post_code,
addr.addr_city,
ctrc.ctrc_name
FROM role_base role
INNER JOIN pty_party prty
ON role.role_prty_id = prty.prty_id
/*LEFT JOIN pty_person pers
ON prty.prty_id = pers.pers_prty_id*/
INNER JOIN pty_contact_details codt
ON codt.codt_prty_id = prty.prty_id
INNER JOIN pty_address addr
ON addr.addr_codt_id = codt.codt_id
INNER JOIN ctl_country ctrc
ON addr.addr_crtc_id = ctrc.ctrc_crtc_id
INNER JOIN ctl_language lngc
ON ctrc.ctrc_lngc_id = lngc.lngc_id
WHERE lngc.LNGC_ISO_639_1 = 'DE')
SELECT /*+ index(ct_xml_data CTXD_IDX_ID_CTRS_VALID) */
t.prcd_ref_nr
prcd_ref_nr,
TO_CHAR (u.prfa_ref_num_folder)
prfa_ref_num_folder,
NVL2 (
meldein.prty_id,
meldein.prty_name
|| ', '
|| meldein.addr_street
|| ' '
|| meldein.addr_street_number
|| ' '
|| meldein.addr_street_number_addition
|| ', '
|| meldein.addr_post_code
|| ' '
|| meldein.addr_city
|| ', '
|| meldein.ctrc_name,
u.prfa_name_folder)
AS prfa_name_folder,
t.prcd_proc_id
prcd_proc_id,
t.prcd_pttc_id
prcd_pttc_id,
t.prcd_prsc_id
prcd_prsc_id,
t.prcd_comment_prcd_state
prcd_comment_prcd_state,
t.prcd_priority
prcd_priority,
t.prcd_submission_date
prcd_submission_date,
CASE
WHEN s.ctst_scope = 'AMG' AND ctrs.ctrs_id IS NULL
THEN
s.ctst_eudract_nr
WHEN ctrs.ctrs_id IS NULL
THEN
s.ctst_eudamed_civ_id
ELSE
NULL -- nur wenn es sich um keine CTIS handelt
END
ctst_eudract_nr,
DECODE (ctrs.ctrs_id,
NULL, ctl.cssc_name,
ctrs.ctrs_sponsorkategorie)
AS cssc_name,
ctl.cssc_name
orig,
DECODE (
ctrs.ctrs_id,
NULL, -- wenn nicht neues CT dann
(SELECT LISTAGG (
(SELECT tphc_name
FROM ctl_ct_phase
WHERE ctl_ct_phase.tphc_id =
ctr_phase_asgn.ctpa_tphc_id),
';')
WITHIN GROUP (ORDER BY 1)
FROM ctr_phase_asgn
WHERE ctr_phase_asgn.ctpa_ctst_id = s.ctst_id),
ctrs.ctrs_part1phase)
AS tphc_name,
(SELECT LISTAGG (
(SELECT tphc_name
FROM ctl_ct_phase
WHERE ctl_ct_phase.tphc_id =
ctr_phase_asgn.ctpa_tphc_id),
';')
WITHIN GROUP (ORDER BY 1)
FROM ctr_phase_asgn
WHERE ctr_phase_asgn.ctpa_ctst_id = s.ctst_id)
AS phase_orig,
(CASE
WHEN EXISTS
(SELECT ctob_scope
FROM ctr_object_ivd_md
WHERE ctr_object_ivd_md.ctob_ctst_id =
s.ctst_id
AND ctob_scope = 'IVD')
AND ctrs.ctrs_id IS NULL
THEN
1
WHEN NOT EXISTS
(SELECT ctob_scope
FROM ctr_object_ivd_md
WHERE ctr_object_ivd_md.ctob_ctst_id =
s.ctst_id
AND ctob_scope = 'IVD')
AND ctrs.ctrs_id IS NULL
THEN
0
ELSE
NULL
END)
AS ctob_scope,
(CASE
WHEN EXISTS
(SELECT ctob_has_ce_mark
FROM ctr_object_ivd_md
WHERE ctr_object_ivd_md.ctob_ctst_id =
s.ctst_id
AND ctob_has_ce_mark = '1')
AND ctrs.ctrs_id IS NULL
THEN
1
WHEN NOT EXISTS
(SELECT ctob_has_ce_mark
FROM ctr_object_ivd_md
WHERE ctr_object_ivd_md.ctob_ctst_id =
s.ctst_id
AND ctob_has_ce_mark = '1')
AND ctrs.ctrs_id IS NULL
THEN
0
ELSE
NULL
END)
AS ctob_has_ce_mark,
CASE WHEN mafo.mafo_ref_num_folder IS NULL THEN 0 ELSE 1 END
AS has_mafo,
DECODE (ctrs.ctrs_id, NULL, m.eqpf_gectc_id, NULL)
AS eqpf_gectc_id -- Kategorie
,
DECODE (ctrs.ctrs_id, NULL, m.eqpf_subject, NULL)
AS eqpf_subject -- Betreff
,
DECODE (ctrs.ctrs_id, NULL, s.ctst_crptc_id, NULL)
AS ctst_crptc_id -- Meldeverfahren
,
(CASE
WHEN ctst_scope = 'AMG'
THEN
ctim.ctim_display_name
WHEN ctst_scope = 'MPG'
THEN
(CASE
WHEN ctob.ctob_ext_seq_number IS NOT NULL
THEN
'PR' || ctob.ctob_ext_seq_number || ': '
ELSE
''
END)
|| COALESCE (ctob.ctob_product_name,
ctob.ctob_product_name_lay,
ctob.ctob_product_name_en)
WHEN ctrs.ctrs_id IS NOT NULL
THEN
ctmd.ctmd_main_product
END)
AS main_product -- Pruefprodukt
,
DECODE (ctrs.ctrs_id,
NULL, ctob.ctob_is_aimd_dir_90_385_eec,
NULL)
AS ctob_is_aimd_dir_90_385_eec -- Klassifizierung
,
DECODE (ctrs.ctrs_id, NULL, ctob.ctob_is_mdrc_dir_93_42_eec, NULL)
AS ctob_is_mdrc_dir_93_42_eec,
DECODE (ctrs.ctrs_id, NULL, ctob.ctob_ctmtc_id, NULL)
AS ctob_ctmtc_id,
DECODE (ctrs.ctrs_id, NULL, ctob.ctob_ctitc_id, NULL)
AS ctob_ctitc_id,
DECODE (ctrs.ctrs_id, NULL, ctob.ctob_is_intended_use, NULL)
AS ctob_is_intended_use -- Anw. in ZB
,
DECODE (ctrs.ctrs_id, NULL, s.ctst_has_paed_subj, NULL)
AS ctst_has_paed_subj -- <18
,
DECODE (ctrs.ctrs_id,
NULL, (SELECT SIGN (COUNT (tsgc.term_id))
FROM ctr_subj_group_asgn ctsa
JOIN all_tsgc_valid tsgc
ON tsgc.term_id = ctsa.ctsa_tsgtc_id
AND tsgc.term_code IN
('TSGTC_PREGNANT_WOMEN',
'TSGTC_NURS_WOMEN',
'TSGTC_EMERG_SITUATION',
'TSGTC_SUBJ_NOT_CONSENT')
WHERE ctsa.ctsa_ctst_id = s.ctst_id),
NULL)
AS risk_factor -- Risikofaktor
,
CASE
WHEN ctrs.ctrs_id IS NULL THEN s.ctst_is_combined_study
ELSE ctmd.ctmd_amg_mpg_comb
END
AS ctst_is_combined_study -- Kombistudie
,
DECODE (ctrs.ctrs_id, NULL, ctim.ctim_is_somatic_thrpy_mp, NULL)
AS ctim_is_somatic_thrpy_mp -- ATMP
,
DECODE (ctrs.ctrs_id, NULL, ctim.ctim_is_gene_therapy_mp, NULL)
AS ctim_is_gene_therapy_mp,
DECODE (ctrs.ctrs_id, NULL, ctim.ctim_is_tissue_eng_mp, NULL)
AS ctim_is_tissue_eng_mp,
DECODE (ctrs.ctrs_id, NULL, ctim.ctim_has_chem_origin, NULL)
AS ctim_has_chem_origin -- Chemical/biological origin
,
DECODE (ctrs.ctrs_id, NULL, ctim.ctim_has_bio_origin, NULL)
AS ctim_has_bio_origin,
DECODE (
ctrs.ctrs_id,
NULL, (SELECT CASE
WHEN ctam_has_chg_app_form = 1
AND ctst_scope = 'AMG'
THEN
'CTA, '
END
|| CASE
WHEN ctam_has_chg_prot = 1
AND ctst_scope = 'AMG'
THEN
'PROT, '
END
|| CASE
WHEN ctam_has_chg_app_form = 1
AND ctst_scope = 'MPG'
THEN
'CIA, '
END
|| CASE
WHEN ctam_has_chg_prot = 1
AND ctst_scope = 'MPG'
THEN
'CIP, '
END
|| CASE ctam_has_chg_ib WHEN 1 THEN 'IB, ' END
|| CASE
WHEN ctam_has_chg_impd = 1
AND ctst_scope = 'AMG'
THEN
'IMPD, '
END
|| CASE
WHEN ctam_has_chg_invstg_prod = 1
AND ctst_scope = 'MPG'
THEN
'MD/IVD, '
END
|| CASE
WHEN ctam_has_chg_pl_nr_sub_glob = 1
AND ctst_scope = 'MPG'
THEN
'NUMB, '
END
|| CASE ctam_has_chg_other_doc
WHEN 1 THEN 'SONST, '
END
|| CASE ctam_has_chg_add_sites
WHEN 1 THEN 'ZENTRUM, '
END
|| CASE ctam_is_ntf_tmp_halt
WHEN 1 THEN 'HALT, '
END
|| CASE ctam_is_req_restart
WHEN 1 THEN 'RESTART, '
END AS content_amd
FROM ctr_amendment ctam
WHERE ctam.ctam_ctst_id = s.ctst_id),
NULL
)
AS content_amendment -- Inhalt AMD
,
ctim.ctim_imp_has_ma
AS ctim_imp_has_ma,
t.prcd_pros_status_sum,
tide.ctis_due_date
AS ctis_due_date,
CASE
WHEN NVL (ctob.ctob_is_aimd_dir_90_385_eec, 0) = 1
THEN
'AIMD'
WHEN NVL (ctob.ctob_is_mdrc_dir_93_42_eec, 0) = 1
THEN
(SELECT name
FROM all_ctmc_valid
WHERE id = ctob.ctob_ctmc_id)
WHEN NVL (ctob.ctob_in_house_ivd, 0) = 1
THEN
'In-house IVD'
WHEN NVL (ctob.ctob_dir_98_79_ec_ivd, 0) = 1
THEN
(SELECT name
FROM all_ctic_valid
WHERE id = ctob.ctob_ctic_id)
WHEN NVL (ctob.ctob_dir_2017_746_ivdr, 0) = 1
THEN
(SELECT name
FROM all_ccac_valid
WHERE id = ctob.ctob_ccac_id)
END
klassifizierung,
s.ctst_xml_raw
xml_data,
s.ctst_scope
scope,
ctpf.ctpf_ctst_id
ctst_id,
crcc.name
AS risikokategorie,
DECODE (ctrs.ctrs_part,
0, 'Part I ' || CHR (38) || ' Part II',
1, 'Part I',
2, 'Part II',
NULL)
AS applicationscope,
ctrs.ctrs_reportingmemberstate
AS rms,
t.prcd_grouping_pooling
AS europ_verfahrensnummer,
t_ctis_error.error
is_ctis_error
FROM
(
select * from procedure,
(
select * from
(
SELECT /*+ dynamic_sampling (nrs 3) */distinct(column_value) a from TABLE(NUMBER_TBL( 102798692,102799603)) nrs
)) prcdnrs
where
prcdnrs.a = PRCD_REF_NR
)t
-- procedure t
INNER JOIN proc_folder_asgn u ON t.prcd_id = u.prfa_prcd_id
INNER JOIN proc_folder v ON u.prfa_prof_id = v.prof_id
LEFT JOIN general_enquiry_proc_folder m
ON m.eqpf_prof_id = u.prfa_prof_id
LEFT JOIN ctr_proc_folder ctpf ON v.prof_id = ctpf.ctpf_prof_id
LEFT JOIN ctr_study s ON s.ctst_id = ctpf_ctst_id
LEFT JOIN ctl_ct_spons_stat ctl ON ctl.cssc_id = s.ctst_cssc_id
LEFT JOIN master_folder mafo
ON mafo.mafo_ref_num_folder = u.prfa_ref_num_folder
AND mafo.mafo_folder_state_term_code = 'FOSTC_GA'
LEFT JOIN ctr_object_ivd_md ctob
ON s.ctst_ctob_main_id = ctob.ctob_id
LEFT JOIN ctr_study_imp ctim ON ctim.ctim_id = s.ctst_ctim_main_id
LEFT JOIN t_t_deatline tide ON tide.prcd_id = t.prcd_id
LEFT JOIN ct_procedure_proc_folder ctrf
ON ctrf.ctrf_prof_id = v.prof_id
LEFT JOIN ct_regulation_study ctrs
ON ctrs.ctrs_ctrf_id = ctrf.ctrf_id
LEFT JOIN t_ctis_error ON t_ctis_error.ctrs_id = ctrs.ctrs_id
LEFT JOIN ct_main_data ctmd ON ctmd.ctmd_ctrs_id = ctrs.ctrs_id
LEFT JOIN all_crcc_valid crcc ON crcc.id = ctmd.ctmd_crcc_id
LEFT JOIN meldein ON meldein.role_prcd_id = t.prcd_id;
--end;