-- Oracle with object_tree(pcmid, cmid, chemin, niveau, name, version, created, modified, disabled, classid, owner, obj_type_name) as ( -- membre racine select a.pcmid, a.cmid, '>'||b.name as chemin, 1 as niveau, b.name, a.version, a.created, a.modified, a.disabled, a.classid, a.owner, c.name as obj_type_name from CMOBJECTS a, (select cmid, name from CMOBJNAMES where isdefault=1) b, CMCLASSES c WHERE a.cmid=b.cmid AND a.classid=c.classid AND a.cmid=0 UNION ALL -- recursive query select a1.pcmid, a1.cmid, chemin||'>'||b.name as chemin, niveau++1 as niveau, b.name, a1.version, a1.created, a1.modified, a1.disabled, a1.classid, a1.owner, c.name as obj_type_name from CMOBJECTS a1, (select cmid, name from CMOBJNAMES where isdefault=1) b, CMCLASSES c, object_tree d WHERE a1.cmid=b.cmid AND a1.classid=c.classid AND a1.pcmid=d.cmid AND a1.cmid<>0 ), lastExecution(PCMID, LastExecDate) as ( select oh.PCMID, max(ACTEXECTIME) LastExecDate from CMOBJPROPS5 h, CMOBJECTS oh where h.CMID=oh.CMID and oh.CLASSID=111 group by oh.PCMID ), objOutput(reportID, lastReportVersionID, lastReportVersionModified, ouputID, outputFormat, outputLocale, lastReportVersionType) as ( select lastReportVersion.PCMID reportID, output.PCMID reportVersionID, lastReportVersion.lastModified reportVersionModified, outputFmt.CMID outputID, CASE WHEN outputFmt.FORMAT=0 THEN 'CSV' WHEN outputFmt.FORMAT=1 THEN 'HTML' WHEN outputFmt.FORMAT=2 THEN 'PDF' WHEN outputFmt.FORMAT=4 THEN 'XML' WHEN outputFmt.FORMAT=5 THEN 'XHTML' WHEN outputFmt.FORMAT=10 THEN 'HTML bis' WHEN outputFmt.FORMAT=11 THEN 'XLSX' ELSE to_char(outputFmt.FORMAT) end outputFormat, l.LOCALE outputLocale, lastReportVersion.CLASSID from CMOBJPROPS54 outputFmt INNER JOIN CMOBJECTS output ON outputFmt.CMID=output.CMID and output.CLASSID=27 LEFT OUTER JOIN CMLOCALES l ON outputFmt.OLOCALEID=l.LOCALEID INNER JOIN (select o.PCMID, max(o.CMID) CMID, max(o.modified) lastModified, max(o.CLASSID) CLASSID from CMOBJECTS o where o.CLASSID=17 group by o.PCMID) lastReportVersion ON lastReportVersion.CMID=output.PCMID ), scheduling(reportID, schedID, Periodicite) as ( select scheduleObj.PCMID reportID, scheduleObj.CMID schedID, CASE WHEN s.TYPE in (0, 9) THEN 'J' WHEN s.TYPE in (4, 12) THEN 'H' WHEN s.TYPE in (1, 2, 10, 11) THEN 'M' WHEN s.TYPE in (5, 6, 13, 14) THEN 'A' WHEN s.TYPE in (7) THEN 'Trigger' ELSE to_char(s.TYPE) END Periodicite from CMOBJECTS scheduleObj INNER JOIN CMOBJPROPS2 s ON scheduleObj.CMID=s.CMID ), sourcePack(reportID, packName) as ( select r.CMID reportID, n.NAME packName from CMREFNOORD1 r INNER JOIN CMOBJECTS o ON r.CMID=o.CMID INNER JOIN CMOBJNAMES n ON r.REFCMID=n.CMID where r.propid=31 and n.isdefault=1 and o.CLASSID in (10, 234) ), jobReportList(jobID, jobStepID, reportID) as ( select jobStep.PCMID jobID, jobStep.CMID jobStepID, r.REFCMID reportID from CMREFNOORD1 r INNER JOIN CMOBJECTS jobStep ON r.CMID=jobStep.CMID INNER JOIN CMOBJECTS o ON r.REFCMID=o.CMID INNER JOIN CMOBJNAMES n ON r.REFCMID=n.CMID where r.propid=10 and n.isdefault=1 and o.CLASSID in (10, 19, 234) ), destschedule(delivoptID, toEmail, ccEmail) as ( select x.CMID, xtomail.toEmail, xccmail.ccEmail from (select CMID, xmltype( case when INSTR(delivoptions,'toAddress',1)=0 then to_clob('no data') else replace(replace(SUBSTR(delivoptions,INSTR(delivoptions,'',INSTR(delivoptions,'toAddress',1))+8-INSTR(delivoptions,'no data') else replace(replace(SUBSTR(delivoptions,INSTR(delivoptions,'',INSTR(delivoptions,'ccAddress',1))+8-INSTR(delivoptions,'no data') else replace(replace(SUBSTR(delivoptions,INSTR(delivoptions,'',INSTR(delivoptions,'toAddress',1))+8-INSTR(delivoptions,'no data') else replace(replace(SUBSTR(delivoptions,INSTR(delivoptions,'',INSTR(delivoptions,'ccAddress',1))+8-INSTR(delivoptions,'/>Team Content>%' -- SQL Server WITH objTree(pcmid, cmid, chemin, niveau, name, version, created, modified, disabled, classid, owner) as ( -- membre racine select a.pcmid, a.cmid, cast('>'+b.name as nvarchar(1000)) as chemin, 1 as niveau, b.name, a.version, a.created, a.modified, a.disabled, a.classid, a.owner from dbo.CMOBJECTS a WITH (NOLOCK), (select cmid, name from dbo.CMOBJNAMES WITH (NOLOCK) where isdefault=1) b WHERE a.cmid=b.cmid AND a.cmid=0 UNION ALL -- recursive query select a1.pcmid, a1.cmid, cast(chemin+'>'+b.name as nvarchar(1000)) as chemin, niveau++1 as niveau, b.name, a1.version, a1.created, a1.modified, a1.disabled, a1.classid, a1.owner from dbo.CMOBJECTS a1 WITH (NOLOCK), (select cmid, name from dbo.CMOBJNAMES WITH (NOLOCK) where isdefault=1) as b, objTree d WHERE a1.cmid=b.cmid AND a1.pcmid=d.cmid AND (a1.DISABLED<>1 or a1.DISABLED is null) AND a1.cmid<>0 ), objList(cmid, pcmid, objet, objRefName, code_type_obj, type_objet, display_sequence, objet_pere, delivoptions, toAddress, toAddressXML, toGroup, toGroupXML, ccAddress, ccAddressXML, ccGroup, ccGroupXML, bccAddress, bccAddressXML, bccGroup, bccGroupXML, paramName, ORD, paramValue, paramValueXML, objet_pere_active_schedule, LastExecDate) as (select o.cmid, o.pcmid, objname.name objet, reference_objet.objRefName, o.classid code_Type_obj, type_obj.name type_objet, o.DISPSEQ, objpere.name objet_pere, delivopt.delivoptions, case when CHARINDEX('toAddress',cast(delivopt.delivoptions as varchar(max)),1)=0 then null else '"'+SUBSTRING(cast(delivopt.delivoptions as varchar(max)), CHARINDEX('toAddress',cast(delivopt.delivoptions as varchar(max)),1),CHARINDEX('',cast(delivopt.delivoptions as varchar(max)),CHARINDEX('toAddress',cast(delivopt.delivoptions as varchar(max)),1))-CHARINDEX('toAddress',cast(delivopt.delivoptions as varchar(max)),1))+'"' end toAddress, case when CHARINDEX('toAddress',cast(delivopt.delivoptions as varchar(max)),1)=0 then null else cast(replace(replace(SUBSTRING(cast(delivopt.delivoptions as varchar(max)), CHARINDEX('',cast(delivopt.delivoptions as varchar(max)),CHARINDEX('toAddress',cast(delivopt.delivoptions as varchar(max)),1))+8-CHARINDEX('',cast(delivopt.delivoptions as varchar(max)),CHARINDEX('to;',cast(delivopt.delivoptions as varchar(max)),1))+8-CHARINDEX('',cast(delivopt.delivoptions as varchar(max)),CHARINDEX('to;',cast(delivopt.delivoptions as varchar(max)),1))+8-CHARINDEX('',cast(delivopt.delivoptions as varchar(max)),CHARINDEX('ccAddress',cast(delivopt.delivoptions as varchar(max)),1))-CHARINDEX('ccAddress',cast(delivopt.delivoptions as varchar(max)),1))+'"' end ccAddress, case when CHARINDEX('ccAddress',cast(delivopt.delivoptions as varchar(max)),1)=0 then null else cast(replace(replace(SUBSTRING(cast(delivopt.delivoptions as varchar(max)), CHARINDEX('',cast(delivopt.delivoptions as varchar(max)),CHARINDEX('ccAddress',cast(delivopt.delivoptions as varchar(max)),1))+8-CHARINDEX('',cast(delivopt.delivoptions as varchar(max)),CHARINDEX('cc;',cast(delivopt.delivoptions as varchar(max)),1))-CHARINDEX('cc;',cast(delivopt.delivoptions as varchar(max)),1))+'"' end ccGroup, case when CHARINDEX('cc;',cast(delivopt.delivoptions as varchar(max)),1)=0 then null else cast(replace(replace(SUBSTRING(cast(delivopt.delivoptions as varchar(max)), CHARINDEX('',cast(delivopt.delivoptions as varchar(max)),CHARINDEX('cc;',cast(delivopt.delivoptions as varchar(max)),1))+8-CHARINDEX('',cast(delivopt.delivoptions as varchar(max)),CHARINDEX('bccAddress',cast(delivopt.delivoptions as varchar(max)),1))-CHARINDEX('bccAddress',cast(delivopt.delivoptions as varchar(max)),1))+'"' end bccAddress, case when CHARINDEX('bccAddress',cast(delivopt.delivoptions as varchar(max)),1)=0 then null else cast(replace(replace(SUBSTRING(cast(delivopt.delivoptions as varchar(max)), CHARINDEX('',cast(delivopt.delivoptions as varchar(max)),CHARINDEX('bccAddress',cast(delivopt.delivoptions as varchar(max)),1))+8-CHARINDEX('',cast(delivopt.delivoptions as varchar(max)),CHARINDEX('bcc;',cast(delivopt.delivoptions as varchar(max)),1))-CHARINDEX('bcc;',cast(delivopt.delivoptions as varchar(max)),1))+'"' end bccGroup, case when CHARINDEX('bcc;',cast(delivopt.delivoptions as varchar(max)),1)=0 then null else cast(replace(replace(SUBSTRING(cast(delivopt.delivoptions as varchar(max)), CHARINDEX('',cast(delivopt.delivoptions as varchar(max)),CHARINDEX('bcc;',cast(delivopt.delivoptions as varchar(max)),1))+8-CHARINDEX('true%' ), DistribListes(idListe, memberOrd, nomListe, dateCreationListe, dateModificationListe, nomMembre, emailContact) as (select o.CMID idListe, l.ORD memberOrd, n.NAME nomListe, o.CREATED dateCreationListe, o.MODIFIED dateModificationListe, coalesce(nUser.NAME, nContact.NAME) nomMembre, c.EMAIL emailContact from CMREFORD1 l WITH (NOLOCK) LEFT OUTER JOIN CMOBJPROPS33 nUser WITH (NOLOCK) on l.REFCMID=nUser.CMID, CMOBJECTS o WITH (NOLOCK), CMOBJNAMES n WITH (NOLOCK), CMOBJNAMES nContact WITH (NOLOCK), CMOBJPROPS1 c WITH (NOLOCK) where l.CMID=o.CMID and o.CMID=n.CMID and l.REFCMID=nContact.CMID and l.REFCMID=c.CMID and nContact.ISDEFAULT=1 and n.ISDEFAULT=1 and o.CLASSID=15 and l.PROPID=27 ), DistribListesAgg(idListe, nomListe, dateCreationListe, dateModificationListe, members, emails) as (select idListe, nomListe, dateCreationListe, dateModificationListe, stuff( (select ',' + nomMembre from DistribListes l2 where l2.idListe=l1.idListe order by memberOrd for xml path(''), type).value('.', 'varchar(8000)') ,1,1,'') as members, stuff( (select ',' + emailContact from DistribListes l2 where l2.idListe=l1.idListe order by memberOrd for xml path(''), type).value('.', 'varchar(8000)') ,1,1,'') as emails from DistribListes l1 group by idListe, nomListe, dateCreationListe, dateModificationListe ), endListAgg (IdObjet, IdObjet_pere, objet, objRefName, code_type_obj, type_objet, display_sequence, objet_pere, chemin, delivoptions, toAddressXML, toAddress, toGroupXML, toGroup, toGroupClean, ccAddressXML, ccAddress, ccGroupXML, ccGroup, ccGroupClean, bccAddressXML, bccAddress, bccGroupXML, bccGroup, bccGroupClean, paramName, ORD, paramValue, useValue, objet_pere_active_schedule, LastExecDate) as (select l.cmid, l.pcmid, objet, objRefName, code_type_obj, type_objet, display_sequence, objet_pere, t.chemin, delivoptions, toAddressXML, stuff( (select ',' + useRef.value('(.)[1]','varchar(8000)') from objList l2 cross apply l2.toAddressXML.nodes('/value/item') as r(useRef) where l2.cmid=l.cmid and l2.paramName=l.paramName order by ord for xml path(''), type).value('.', 'varchar(8000)') ,1,1,'') toAddress, toGroupXML, stuff( (select ',' + useRef.value('(.)[1]','varchar(8000)') from objList l2 cross apply l2.toGroupXML.nodes('/value/item') as r(useRef) where l2.cmid=l.cmid and l2.paramName=l.paramName order by ord for xml path(''), type).value('.', 'varchar(8000)') ,1,1,'') toGroup, stuff( (select ',' + replace(right(useRef.value('(.)[1]','varchar(8000)'), charindex(':',reverse(useRef.value('(.)[1]','varchar(8000)')))-1),'")','') from objList l2 cross apply l2.toGroupXML.nodes('/value/item') as r(useRef) where l2.cmid=l.cmid and l2.paramName=l.paramName order by ord for xml path(''), type).value('.', 'varchar(8000)') ,1,1,'') toGroupClean, ccAddressXML, stuff( (select ',' + useRef.value('(.)[1]','varchar(8000)') from objList l2 cross apply l2.ccAddressXML.nodes('/value/item') as r(useRef) where l2.cmid=l.cmid and l2.paramName=l.paramName order by ord for xml path(''), type).value('.', 'varchar(8000)') ,1,1,'') ccAddress, ccGroupXML, stuff( (select ',' + useRef.value('(.)[1]','varchar(8000)') from objList l2 cross apply l2.ccGroupXML.nodes('/value/item') as r(useRef) where l2.cmid=l.cmid and l2.paramName=l.paramName order by ord for xml path(''), type).value('.', 'varchar(8000)') ,1,1,'') ccGroup, stuff( (select ',' + replace(right(useRef.value('(.)[1]','varchar(8000)'), charindex(':',reverse(useRef.value('(.)[1]','varchar(8000)')))-1),'")','') from objList l2 cross apply l2.ccGroupXML.nodes('/value/item') as r(useRef) where l2.cmid=l.cmid and l2.paramName=l.paramName order by ord for xml path(''), type).value('.', 'varchar(8000)') ,1,1,'') ccGroupClean, bccAddressXML, stuff( (select ',' + useRef.value('(.)[1]','varchar(8000)') from objList l2 cross apply l2.bccAddressXML.nodes('/value/item') as r(useRef) where l2.cmid=l.cmid and l2.paramName=l.paramName order by ord for xml path(''), type).value('.', 'varchar(8000)') ,1,1,'') bccAddress, bccGroupXML, stuff( (select ',' + useRef.value('(.)[1]','varchar(8000)') from objList l2 cross apply l2.bccGroupXML.nodes('/value/item') as r(useRef) where l2.cmid=l.cmid and l2.paramName=l.paramName order by ord for xml path(''), type).value('.', 'varchar(8000)') ,1,1,'') bccGroup, stuff( (select ',' + replace(right(useRef.value('(.)[1]','varchar(8000)'), charindex(':',reverse(useRef.value('(.)[1]','varchar(8000)')))-1),'")','') from objList l2 cross apply l2.bccGroupXML.nodes('/value/item') as r(useRef) where l2.cmid=l.cmid and l2.paramName=l.paramName order by ord for xml path(''), type).value('.', 'varchar(8000)') ,1,1,'') bccGroupClean, paramName, ORD, paramValue, stuff( (select ',' + useRef.value('use[1]','varchar(8000)') from objList l2 cross apply l2.paramValueXML.nodes('/value/item') as r(useRef) where l2.cmid=l.cmid and l2.paramName=l.paramName order by ord for xml path(''), type).value('.', 'varchar(8000)') ,1,1,'') useValue, objet_pere_active_schedule, LastExecDate from objList l, objTree t where l.cmid = t.cmid ) select IdObjet, IdObjet_pere, objet, objRefName, code_type_obj, type_objet, display_sequence, objet_pere, chemin, delivoptions, toAddressXML, toAddress, toGroupXML, toGroup, toGroupClean, distribTo.dateCreationListe toGroupDateCreationListe, distribTo.dateModificationListe toGroupDateModificationListe, distribTo.members toGroupMembers, distribTo.emails toGroupMembersEmails, ccAddressXML, ccAddress, ccGroupXML, ccGroup, ccGroupClean, distribCc.dateCreationListe ccGroupDateCreationListe, distribCc.dateModificationListe ccGroupDateModificationListe, distribCc.members ccGroupMembers, distribCc.emails ccGroupMembersEmails, bccAddressXML, bccAddress, bccGroupXML, bccGroup, bccGroupClean, distribBcc.dateCreationListe bccGroupDateCreationListe, distribBcc.dateModificationListe bccGroupDateModificationListe, distribBcc.members bccGroupMembers, distribBcc.emails bccGroupMembersEmails, paramName, ORD, paramValue, useValue, objet_pere_active_schedule, LastExecDate from endListAgg a LEFT OUTER JOIN DistribListesAgg distribTo ON a.toGroupClean=distribTo.nomListe LEFT OUTER JOIN DistribListesAgg distribCc ON a.ccGroupClean=distribCc.nomListe LEFT OUTER JOIN DistribListesAgg distribBcc ON a.bccGroupClean=distribBcc.nomListe where chemin like '>/>Team Content>%' order by IdObjet_pere, display_sequence, IdObjet, ORD