Hi @ward_scott ,
We have a custom report bases on the table PA_QUAL_CPNT, where are all item to curricula historical changes are stored. It excludes also the "M" - action, which means modify like change effectivedates etc. So it should fit your requirements. How could I share it with you? Here I can't find an option to add an attachment 😞
This is the SQL below. You can create a new custom CSV report, just add the parameters CurriculumSearch & ItemSearch.
/* Items (alle Revisionen) */
with cpnt as
(select cp.CPNT_TYP_ID, cp.CPNT_ID, cp.REV_DTE, cp.REV_NUM, cp.CPNT_TITLE
from ph_cpnt cp
group by cp.CPNT_TYP_ID, cp.CPNT_ID, cp.REV_DTE, cp.REV_NUM, cp.CPNT_TITLE
),
qual as
(select pq.QUAL_ID, pq.QUAL_TITLE
from PH_QUAL pq
group by pq.QUAL_ID, pq.QUAL_TITLE
),
stud as
(select st.STUD_ID
from PA_STUDENT st
where 1 = 1
/** and [security:pa_student st] */
)
select
ph.CPNT_TYP_ID
, PH.CPNT_ID
, cpnt.CPNT_TITLE
, ph.REV_DTE
, cpnt.REV_NUM
, ph.QUAL_ID as curr_id
, qual.QUAL_TITLE as CURR_TITLE
, cast(null as varchar(90)) as sub_curr
, ph.ACTION
, ph.LST_UPD_TSTMP as up_date
, ph.STAT_EFFECT_DTE as ass_date
, ph.LST_UPD_USR as add_user
from
ph_qual_cpnt ph
left join cpnt on cpnt.CPNT_TYP_ID = ph.CPNT_TYP_ID
and cpnt.CPNT_ID = ph.CPNT_ID and cpnt.REV_DTE = ph.REV_DTE
left join qual on qual.QUAL_ID = ph.QUAL_ID
where 1 = 1
and ph.ACTION <> 'M'
/** and ph.QUAL_ID in [CurriculumSearch] */
/** and (ph.CPNT_TYP_ID, ph.CPNT_ID, ph.REV_DTE) in [ItemSearch] */
union
/* Sub-Curriculas ohne Items */
select
cast(null as varchar(90)) as CPNT_TYP_ID
, cast(null as varchar(90)) as CPNT_ID
, cast(null as varchar(90)) as CPNT_TITLE
, cast(null as date) as REV_DTE
, cast(null as varchar(90)) as REV_NUM
, sq.QUAL_ID as curr_id
, qual.QUAL_TITLE as curr_Title
, sq.QUAL_ID_SUB as sub_curr
, sq.ACTION
, sq.LST_UPD_TSTMP as up_date
, sq.CREATE_TSTMP as ass_date
, sq.LST_UPD_USR as add_user
from
PH_QUAL_SUBQUAL sq
left join qual
on qual.QUAL_ID = sq.QUAL_ID
where 1 = 1
and sq.ACTION <> 'M'
/** and sq.QUAL_ID in [CurriculumSearch] */
and 2=2
union
/* Sub-Curriculas ohne Items */
select
cast(null as varchar(90)) as CPNT_TYP_ID
, cast(null as varchar(90)) as CPNT_ID
, cast(null as varchar(90)) as CPNT_TITLE
, cast(null as date) as REV_DTE
, cast(null as varchar(90)) as REV_NUM
, sq.QUAL_ID as curr_id
, qual.QUAL_TITLE as curr_Title
, sq.QUAL_ID_SUB as sub_curr
, sq.ACTION
, sq.LST_UPD_TSTMP as up_date
, sq.CREATE_TSTMP as ass_date
, sq.LST_UPD_USR as add_user
from
PH_QUAL_SUBQUAL sq
left join qual
on qual.QUAL_ID = sq.QUAL_ID
where 1 = 1
and sq.ACTION <> 'M'
/** and sq.QUAL_ID_SUB in [CurriculumSearch] */
and 2=2
order by CPNT_ID, CURR_ID, SUB_CURR, UP_DATE
Kind regards
Marcus
