drop table IEC_CMM_CHECK_ALL_IDEAS ;

-- BASIC Table, no need to update it!
CREATE TABLE IEC_CMM_CHECK_ALL_IDEAS AS
SELECT /*+ PARALLEL(8) */ DISTINCT i.pitem_id, rev.pitem_revision_id, rev.puid
FROM pitem i
JOIN pitemrevision rev
ON i.puid = rev.ritems_tagu
JOIN pimanrelation rel2ds
ON rel2ds.rprimary_objectu = rev.puid
JOIN pdataset ds
ON rel2ds.rsecondary_objectu =  ds.puid
JOIN pworkspaceobject wso
on ds.puid = wso.puid
where wso.POBJECT_TYPE IN ('IdeasPart','IdeasAssembly','IdeasDrawing');



-- CRETE REPORT TABLE ==> update it in the morning!
-- UNION is used because drawing files keep same name, but UGMASTER will be renamed by the name of the Revision
-- Could be the case that we need to fix the name of the Ideas Parts and Assemblys 
drop table IEC_CMM_CHECK_IDEAS_RPRT; 

CREATE TABLE IEC_CMM_CHECK_IDEAS_RPRT AS
SELECT * FROM 
(

SELECT 
  /*+ LEADING(iec) */
  iec.*, 
  
    (SELECT LISTAGG(st.pname,',')  WITHIN GROUP (ORDER BY pname) 
  FROM prelease_status_list lst
  JOIN preleasestatus st
  ON lst.pvalu_0 = st.puid
  WHERE lst.puid = iec.puid
  )
  AS
    rev_status,
    
  
  wso_ideas.pobject_type as ideas_ds_type, 
  wso_ideas.pobject_name as ideas_ds_name,
  wso_ideas.puid         as ideas_ds_puid, 

  (SELECT LISTAGG(st.pname,',')  WITHIN GROUP (ORDER BY pname) 
  FROM prelease_status_list lst
  JOIN preleasestatus st
  ON lst.pvalu_0 = st.puid
  WHERE lst.puid = wso_ideas.puid
  )
  AS
    wso_ideas_status, 
    
  
  wso_nx.pobject_type as nx_ds_type, 
  wso_nx.pobject_name as nx_ds_name,
  wso_nx.puid         as nx_ds_puid, 
  pao_nx.pcreation_date, 
  pao_nx.plast_mod_date, 
  
  (select puser_id from ppom_user u where pao_nx.rowning_useru = u.puid) as last_mod_user,

  (SELECT LISTAGG(st.pname,',')  WITHIN GROUP (ORDER BY pname) 
  FROM prelease_status_list lst
  JOIN preleasestatus st
  ON lst.pvalu_0 = st.puid
  WHERE lst.puid = wso_nx.puid
  )
  AS
    wso_nx_status 
    
FROM pitemrevision rev
JOIN pimanrelation rel2ideas
ON rev.puid = rel2ideas.rprimary_objectu
JOIN pworkspaceobject wso_ideas
ON rel2ideas.rsecondary_objectu = wso_ideas.puid
AND wso_ideas.pobject_type     IN ('IdeasDrawing')
JOIN pimanrelation rel2nx
ON rev.puid = rel2nx.rprimary_objectu
JOIN pworkspaceobject wso_nx
ON rel2nx.rsecondary_objectu                                = wso_nx.puid
AND wso_nx.pobject_type                                    IN ('UGPART')
--AND REGEXP_REPLACE(wso_ideas.pobject_name,'([\-\/])', '') = REGEXP_REPLACE(wso_nx.pobject_name, '([\-\/])', '')
AND wso_ideas.pobject_name = wso_nx.pobject_name
JOIN ppom_application_object pao_nx 
ON wso_nx.puid = pao_nx.puid
JOIN IEC_CMM_CHECK_ALL_IDEAS iec
ON rev.puid = iec.puid
JOIN ppom_application_object pao
ON iec.puid                = pao.puid
--WHERE pao.rlast_mod_useru IN
--  ( SELECT puid FROM ppom_user WHERE puser_id LIKE 'cmm%'
--  )


UNION 

SELECT 
  /*+ LEADING(iec) */
  iec.*, 
  
    (SELECT LISTAGG(st.pname,',')  WITHIN GROUP (ORDER BY pname) 
  FROM prelease_status_list lst
  JOIN preleasestatus st
  ON lst.pvalu_0 = st.puid
  WHERE lst.puid = iec.puid
  )
  AS
    rev_status,
    
  
  wso_ideas.pobject_type, 
  wso_ideas.pobject_name,
  wso_ideas.puid, 

  (SELECT LISTAGG(st.pname,',')  WITHIN GROUP (ORDER BY pname) 
  FROM prelease_status_list lst
  JOIN preleasestatus st
  ON lst.pvalu_0 = st.puid
  WHERE lst.puid = wso_ideas.puid
  )
  AS
    wso_ideas_status, 
    
  
  wso_nx.pobject_type, 
  wso_nx.pobject_name,
  wso_nx.puid,
  pao_nx.pcreation_date, 
  pao_nx.plast_mod_date, 
  
  (select puser_id from ppom_user u where  pao_nx.rowning_useru = u.puid) as last_mod_user,
  
  (SELECT LISTAGG(st.pname,',')  WITHIN GROUP (ORDER BY pname) 
  FROM prelease_status_list lst
  JOIN preleasestatus st
  ON lst.pvalu_0 = st.puid
  WHERE lst.puid = wso_nx.puid
  )
  AS
    wso_nx_status 
    
FROM pitemrevision rev
JOIN pimanrelation rel2ideas
ON rev.puid = rel2ideas.rprimary_objectu
JOIN pworkspaceobject wso_ideas
ON rel2ideas.rsecondary_objectu = wso_ideas.puid
AND wso_ideas.pobject_type     IN ('IdeasPart','IdeasAssembly')
JOIN pimanrelation rel2nx
ON rev.puid = rel2nx.rprimary_objectu
JOIN pworkspaceobject wso_nx
ON rel2nx.rsecondary_objectu                                = wso_nx.puid
AND wso_nx.pobject_type                                    IN ('UGMASTER')
--AND REGEXP_REPLACE(wso_ideas.pobject_name,'([\-\/])', '') = REGEXP_REPLACE(wso_nx.pobject_name, '([\-\/])', '')
---AND wso_ideas.pobject_name = wso_nx.pobject_name
JOIN ppom_application_object pao_nx 
ON wso_nx.puid = pao_nx.puid
JOIN IEC_CMM_CHECK_ALL_IDEAS iec
ON rev.puid = iec.puid
JOIN ppom_application_object pao
ON iec.puid                = pao.puid
--WHERE pao.rlast_mod_useru IN
--  ( SELECT puid FROM ppom_user WHERE puser_id LIKE 'cmm%'
--  )

)
ORDER BY 1, 2;


-- THIS is the report that needs to be given to Dieter Klinke 
-- CMM is only done for LD :)
select * from (
SELECT iec.*,
  (SELECT SUBSTR( MAX(SYS_CONNECT_BY_PATH(pname, '.')), 2) full_group
  FROM ppom_group pg
    CONNECT BY nocycle prior pg.rparentu = pg.puid
    START WITH pg.puid                   = pao.rowning_groupu
  ) AS full_group
FROM IEC_CMM_CHECK_IDEAS_RPRT6 iec
JOIN ppom_application_object pao
ON iec.puid     = pao.puid
WHERE nx_ds_name  IS NOT NULL

) where full_group like '%.LD_Project.%' or full_group like '%.LD.%';

