10 --Ne pas afficher les utilisateurs techniques (1 à 8 pour l'instant) UNION ALL --Extraire les ressources "inactives" de type "Centre de Service" /" Prestataire" / "Salarié" ayant une date de départ, si elle est renseignée, strictement postérieure à la date du jour SELECT 'RES01-03' numo_ref, resource_link || rsc.id AS lnk, 'La ressource inactive ' || rsc.unique_name || ' de type ' || DECODE(rsc.ma_prj_typ_res,4,'Centre de Service',2,'Prestataire',1,'Salarié') || ' a une date de départ supérieure à la date du jour' AS msg_erreur, 'Action corrective' AS msg_action FROM mcf_resources rsc WHERE rsc.is_active = 0 AND rsc.ma_prj_typ_res IN (4, 2, 1) AND rsc.date_of_termination IS NOT NULL AND rsc.date_of_termination-1 > SYSDATE UNION ALL --Extraire les ressources "inactives" de type "Centre de Service" /" Prestataire" / "Salarié" dont le Prénom ou "Nom utilisateur" ne sont pas suffixés par "-AASS" (AA = Année de désactivation et SS=Semaine de désactivation) SELECT 'RES01-04' numo_ref, resource_link || rsc.id AS lnk, 'La ressource inactive ' || rsc.unique_name || ' de type ' || DECODE(rsc.ma_prj_typ_res,4,'Centre de Service',2,'Prestataire',1,'Salarié') || ' n''est pas suffixée par sa date de départ (format -AASS)' AS msg_erreur, 'Action corrective' AS msg_action FROM mcf_resources rsc WHERE rsc.is_active = 0 AND rsc.ma_prj_typ_res IN (4, 2, 1) AND rsc.obj_last_anonymized IS NULL AND NOT( SUBSTR(rsc.first_name, -5, 1) = '-' AND SUBSTR(rsc.first_name, -4, 2) BETWEEN '00' AND '99' AND SUBSTR(rsc.first_name, -2, 2) BETWEEN '01' AND '53' AND SUBSTR(rsc.user_name, -5, 1) = '-' AND SUBSTR(rsc.user_name, -4, 2) BETWEEN '00' AND '99' AND SUBSTR(rsc.user_name, -2, 2) BETWEEN '01' AND '53' --AND SUBSTR(rsc.unique_name, -5, 1) = '-' --AND SUBSTR(rsc.unique_name, -4, 2) BETWEEN '00' AND '99' --AND SUBSTR(rsc.unique_name, -2, 2) BETWEEN '01' AND '53' ) UNION ALL SELECT 'RES01-05' numo_ref, resource_link || rsc.id AS lnk, 'La ressource inactive ' || rsc.unique_name || ' de type ' || DECODE(rsc.ma_prj_typ_res,4,'Centre de Service',2,'Prestataire',1,'Salarié') || ' a le droit de gérer son calendrier' AS msg_erreur, 'Action corrective' AS msg_action FROM mcf_resources rsc INNER JOIN CMN_SEC_ASSGND_OBJ_PERM rgt ON rgt.principal_id = rsc.user_id INNER JOIN cmn_sec_objects obj ON obj.id = rgt.object_id WHERE rsc.is_active = 0 AND rsc.ma_prj_typ_res IN (4, 2, 1) AND rgt.RIGHT_ID = 51500 AND rgt.principal_type = 'USER' AND rgt.permission_id = '5' AND obj.object_code = 'ResourceCalendar' AND rgt.object_instance_id = rsc.id UNION ALL SELECT 'RES01-05' numo_ref, resource_link || rsc.id AS lnk, 'La ressource inactive ' || rsc.unique_name || ' de type ' || DECODE(rsc.ma_prj_typ_res,4,'Centre de Service',2,'Prestataire',1,'Salarié') || ' a la case à cocher gérer son calendrier' AS msg_erreur, 'Action corrective' AS msg_action FROM mcf_resources rsc WHERE rsc.is_active = 0 AND rsc.ma_prj_typ_res IN (4, 2, 1) AND rsc.ma_mgt_calend = 1 UNION ALL SELECT 'RES02-01' numo_ref, resource_link || rsc.id AS lnk, 'Le rôle inactif ' || rsc.unique_name || ' n''est pas suffixé par AASS' AS msg_erreur, 'Action corrective' AS msg_action FROM srm_resources rsc INNER JOIN prj_resources prj ON prj.prid = rsc.id INNER JOIN odf_ca_resource ocr ON ocr.id =rsc.id CROSS JOIN param WHERE rsc.is_active = 0 AND ocr.ma_prj_typ_res IN (5, 7, 6) AND prj.prisrole = 1 AND NOT( (SUBSTR(rsc.unique_name, -5, 1) = '-') AND (SUBSTR(rsc.unique_name, -4, 2) BETWEEN '00' AND '99') AND (SUBSTR(rsc.unique_name, -2, 2) BETWEEN '01' AND '53') ) UNION ALL SELECT 'RES02-01' numo_ref, resource_link || rsc.id AS lnk, 'Le rôle inactif ' || rsc.unique_name || ' n''est pas suffixé par AASS' AS msg_erreur, 'Action corrective' AS msg_action FROM mcf_resources rsc INNER JOIN prj_resources prj ON prj.prid = rsc.id INNER JOIN odf_ca_resource ocr ON ocr.id =rsc.id WHERE rsc.is_active = 0 --AND prj.prisrole = 1 AND ocr.ma_prj_typ_res IN (5, 7, 6) AND NOT( SUBSTR(rsc.user_name, -5, 1) = '-' AND (SUBSTR(rsc.user_name, -4, 2) BETWEEN '00' AND '99') AND (SUBSTR(rsc.user_name, -2, 2) BETWEEN '01' AND '53') AND (SUBSTR(rsc.unique_name, -5, 1) = '-') AND (SUBSTR(rsc.unique_name, -4, 2) BETWEEN '00' AND '99') AND (SUBSTR(rsc.unique_name, -2, 2) BETWEEN '01' AND '53') ) UNION ALL SELECT 'RES03-01' numo_ref, resource_link || rsc.id AS lnk, 'La ressource active ' || rsc.unique_name || ' de type ' || DECODE(rsc.ma_prj_typ_res,5,'Rôle',7,'Charge en instance',6,'Charge non allouée',8,'Contingence',0,'Pas de type') || ' est ouverte à la saisie des temps' AS msg_erreur, 'Action corrective' AS msg_action FROM mcf_resources rsc WHERE rsc.is_active = 1 AND rsc.ma_prj_typ_res IN (5, 7, 6, 8, 0) AND rsc.prisopen = 1 UNION ALL SELECT 'RES03-01' numo_ref, resource_link || rsc.id AS lnk, 'La ressource active ' || rsc.unique_name || ' de type ' || DECODE(rsc.ma_prj_typ_res,1,'Salarié',2,'Prestataire',4,'Centre de service') || ' n''est pas ouverte à la saisie des temps' AS msg_erreur, 'Action corrective' AS msg_action FROM mcf_resources rsc WHERE rsc.is_active = 1 AND rsc.ma_prj_typ_res IN (1, 2, 4) AND rsc.prisopen = 0 UNION ALL SELECT 'RES03-02' numo_ref, group_link || rsc.user_id AS lnk, 'La ressource active ' || rsc.unique_name || ' ne possède pas le groupe "Groupe de base pour accès utilisateur" ou l''un des deux groupes suivants: "Utilisateur Clarity" ou "Utilisateur Clarity hors approbation feuille de temps"' AS msg_erreur, 'Action corrective' AS msg_action FROM mcf_resources rsc WHERE rsc.is_active = 1 AND rsc.ma_prj_typ_res IN (1, 2, 4) AND (rsc.user_id NOT IN ( SELECT grp.user_id FROM cmn_sec_user_groups grp INNER JOIN cmn_sec_groups_v grv ON grv.id = grp.group_id WHERE grv.group_code = 'baseUserGroup' ) OR rsc.user_id NOT IN ( SELECT grp.user_id FROM cmn_sec_user_groups grp INNER JOIN cmn_sec_groups_v grv ON grv.id = grp.group_id WHERE grv.group_code IN ('ma_users_clarity', 'ma_users_clarity_hors_fdt') ) ) UNION ALL SELECT 'RES03-03' numo_ref, resource_link || rsc.id AS lnk, 'La ressource ' || rsc.unique_name || ' ne possède pas les 2 groupes de base' AS msg_erreur, 'Action corrective' AS msg_action FROM mcf_resources rsc WHERE rsc.is_active = 1 AND rsc.ma_prj_typ_res IN (2, 4) AND rsc.date_of_hire IS NULL UNION ALL SELECT 'RES03-04' numo_ref, resource_link || rsc.id AS lnk, 'La ressource ' || rsc.unique_name || ' est paramétrée de façon incorrecte. Date de départ '||rsc.date_of_termination||DECODE(rsc.date_of_termination,NULL,'nulle') AS msg_erreur, 'Action corrective' AS msg_action FROM mcf_resources rsc WHERE rsc.is_active = 1 AND rsc.ma_prj_typ_res IN (2, 4) AND (rsc.date_of_termination IS NULL OR rsc.date_of_termination < SYSDATE) UNION ALL /*--Regle à remettre? SELECT 'RES03-05' numo_ref, resource_link || rsc.id AS lnk, 'La ressource ' || rsc.unique_name || ' n''a pas le droit de gérer son calendrier' AS msg_erreur, 'Action corrective' AS msg_action FROM mcf_resources rsc WHERE rsc.is_active = 1 AND rsc.ma_prj_typ_res IN (1, 2, 4) AND rsc.id NOT IN ( SELECT rsc.id FROM mcf_resources rsc INNER JOIN CMN_SEC_ASSGND_OBJ_PERM rgt ON rgt.principal_id = rsc.user_id INNER JOIN cmn_sec_objects obj ON obj.id = rgt.object_id WHERE rsc.is_active = 1 AND rsc.ma_prj_typ_res IN (1, 2, 4) AND rgt.RIGHT_ID = 51500 AND rgt.principal_type = 'USER' AND rgt.permission_id = '5' AND obj.object_code = 'ResourceCalendar' AND rgt.object_instance_id = rsc.id ) UNION ALL*/ SELECT 'RES03-05' numo_ref, resource_link || rsc.id AS lnk, 'La ressource ' || rsc.unique_name || ' n''a pas la case "Autoriser à gérer son calendrier" cochée' AS msg_erreur, 'Action corrective' AS msg_action FROM mcf_resources rsc WHERE rsc.is_active = 1 AND rsc.ma_prj_typ_res IN (1, 2, 4) AND rsc.ma_mgt_calend != 1 UNION ALL SELECT 'RES03-06' numo_ref, rsc.resource_link || rsc.id AS lnk, 'La ressource ' || rsc.unique_name || ' est paramétrée de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM mcf_resources rsc INNER JOIN mcf_resources pil ON pil.user_id = rsc.manager_id WHERE rsc.is_active = 1 AND pil.is_active = 0 UNION ALL SELECT 'RES03-07' numo_ref, rsc.resource_link || rsc.id AS lnk, 'La ressource ' || rsc.unique_name || ' est paramétrée de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM mcf_resources rsc INNER JOIN mcf_resources rol ON rol.id = rsc.prprimaryroleid WHERE rsc.is_active = 1 AND rsc.prisrole = 0 AND rol.is_active = 0 UNION ALL SELECT 'RES03-08' numo_ref, resource_link || rsc.id AS lnk, 'La ressource ' || rsc.unique_name || ' est paramétrée de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM mcf_resources rsc WHERE rsc.is_active = 1 AND (rsc.ma_prj_typ_res IN (1, 2) OR (rsc.ma_prj_typ_res = 4 /*AND UPPER(rsc.prcategory) LIKE 'MOA%'*/)) AND rsc.date_of_hire IS NULL AND rsc.user_status_id NOT IN (200, 202) UNION ALL SELECT 'RES03-09' numo_ref, resource_link || rsc.id AS lnk, 'La ressource ' || rsc.last_name || ', ' || rsc.first_name || ' est paramétrée de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM mcf_resources rsc WHERE rsc.prisrole = 0 AND UPPER(rsc.last_name) || UPPER(rsc.first_name) IN ( SELECT UPPER(last_name) || UPPER(first_name) FROM mcf_resources GROUP BY UPPER(last_name) || UPPER(first_name) HAVING COUNT(*) > 1 ) UNION ALL SELECT 'RES04-01' numo_ref, resource_link || rsc.id AS lnk, 'La ressource ' || rsc.unique_name || ' est activée financièrement' AS msg_erreur, 'Action corrective' AS msg_action FROM mcf_resources rsc WHERE NOT(rsc.ma_prj_typ_res IN (1, 4) OR (rsc.ma_prj_typ_res = 2 AND rsc.type_prestation_aff = '2a')) AND rsc.active = 1 UNION ALL SELECT 'RES04-02' numo_ref, resource_link || rsc.id AS lnk, 'La ressource ' || rsc.unique_name || ' n''est pas activée financièrement ou l''est avec un code de valorisation nul' AS msg_erreur, 'Action corrective' AS msg_action FROM mcf_resources rsc -- ressource salariée ou CDS ou prestataire mutualisée -- ET pas activée financièrement ou activée financièrement avec code d'imputation nul -- ET ressource active WHERE (rsc.ma_prj_typ_res IN (1, 4) OR (rsc.ma_prj_typ_res = 2 AND rsc.type_prestation_aff = '2a')) AND (rsc.active = 0 OR rsc.active IS NULL OR (rsc.active = 1 AND rsc.prtypecodeid IS NULL)) AND rsc.is_active = 1 UNION ALL SELECT 'RES04-03' numo_ref, resource_link || rsc.id AS lnk, 'La ressource ' || rsc.unique_name || ' ne respecte pas les règles de cohérence de la gestion financière' AS msg_erreur, 'Action corrective' AS msg_action FROM mcf_resources rsc WHERE (rsc.ma_prj_typ_res IN (1, 4) OR (rsc.ma_prj_typ_res = 2 AND rsc.type_prestation_aff = '2a')) AND rsc.active = 1 AND rsc.is_active = 1 AND ( (rsc.prcategory = 'DDSIG' AND (rsc.resource_class != 'DIT' OR rsc.transclass != '07_DDSIG')) OR (rsc.prcategory LIKE 'MOA%' AND rsc.ma_prj_typ_res = 1 AND (rsc.resource_class != 'MOA' OR rsc.transclass != '01_Inter')) OR (rsc.prcategory LIKE 'MOA%' AND rsc.ma_prj_typ_res = 4 AND (rsc.resource_class != 'MOA' OR rsc.transclass != '02_Prest')) OR (rsc.prcategory = 'GERAP' AND rsc.ma_prj_typ_res = 1 AND (rsc.resource_class != 'GERAP' OR rsc.transclass != '10_Part' )) ) UNION ALL SELECT 'RES05-01' numo_ref, resource_link || rsc.id AS lnk, 'La ressource ' || rsc.unique_name || ' a un code catégorie différent de "MOE*", "MOA*"' AS msg_erreur, 'Action corrective' AS msg_action FROM mcf_resources rsc WHERE rsc.is_active = 1 --AND (rsc.prcategory IS NULL OR rsc.prcategory NOT IN ('DDSIG','MOA','MOA-MUT','MOA-MMU','MOA-PIF','MOA-PNO','MOA-PNE','MOA-PSE','MOA-PSO','GERAP','SBA_SOCRAM_MOA','SBE_SOCRAM_MOE')) AND (rsc.prcategory IS NULL OR SUBSTR(rsc.prcategory,0,3) NOT IN ('MOE', 'MOA')) UNION ALL SELECT 'RES06-01' numo_ref, resource_link || rsc.id AS lnk, 'La ressource ' || rsc.unique_name || ' est paramétrée de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM mcf_resources rsc WHERE rsc.is_active = 1 AND ( (rsc.prcategory != 'MOE-SAM' AND (rsc.prusertext2 LIKE 'D7%' OR rsc.prusertext2 = 'D600')) OR (rsc.prcategory = 'MOE-SAM' AND (rsc.prusertext2 NOT LIKE 'D7%' AND rsc.prusertext2 != 'D600')) OR (rsc.prcategory = 'MOE-SAM' AND rsc.prusertext2 IS NULL) ) UNION ALL SELECT 'RES06-02' numo_ref, rsc.resource_link || rsc.id AS lnk, 'La ressource ' || rsc.unique_name || ' est paramétrée de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM mcf_resources rsc LEFT JOIN srm_resources rol ON rol.id = rsc.prprimaryroleid LEFT JOIN srm_resources pil ON pil.user_id = rsc.manager_id WHERE rsc.is_active = 1 AND rsc.prcategory = 'MOE-SAM' AND (rsc.prusertext2 = 'D600' OR rsc.prusertext2 LIKE 'D7%') AND (pil.is_active = 0 OR pil.id IS NULL OR rol.is_active = 0 OR rol.id IS NULL) UNION ALL SELECT 'RES06-03' numo_ref, resource_link || rsc.id AS lnk, 'La ressource ' || rsc.unique_name || ' est paramétrée de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM prj_obs_units pou INNER JOIN prj_obs_types pot ON pot.id = pou.type_id AND pot.unique_name = 'ma_groupe_macif' INNER JOIN prj_obs_associations poa ON poa.unit_id = pou.id AND poa.table_name = 'SRM_RESOURCES' INNER JOIN mcf_resources rsc ON rsc.id = poa.record_id INNER JOIN nbi_dim_obs ndo ON ndo.obs_unit_id = pou.id WHERE UPPER(ndo.path) NOT LIKE '%DDSIG%' AND (rsc.prcategory = 'MOE-SAM' OR (rsc.prusertext2 LIKE 'D7%' OR rsc.prusertext2 = 'D600')) AND rsc.is_active = 1 UNION ALL SELECT 'RES07-01' numo_ref, resource_link || rsc.id AS lnk, 'La ressource ' || rsc.unique_name || ' est paramétrée de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM mcf_resources rsc WHERE rsc.is_active = 1 AND rsc.ma_prj_typ_res IN (1, 5) AND (rsc.person_type NOT IN (300, 5000160) OR rsc.is_external = 1) UNION ALL SELECT 'RES07-01' numo_ref, resource_link || rsc.id AS lnk, 'La ressource ' || rsc.unique_name || ' est paramétrée de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM mcf_resources rsc WHERE rsc.is_active = 1 AND rsc.ma_prj_typ_res IN (5, 7, 6, 8, 0) AND (rsc.person_type != 0 OR rsc.person_type IS NOT NULL OR rsc.is_external = 1) UNION ALL SELECT 'RES07-01' numo_ref, resource_link || rsc.id AS lnk, 'La ressource ' || rsc.unique_name || ' est paramétrée de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM mcf_resources rsc WHERE rsc.is_active = 1 AND rsc.ma_prj_typ_res IN (2, 4) AND (rsc.person_type NOT IN 301 OR rsc.is_external = 0) UNION ALL SELECT 'RES07-02' numo_ref, resource_link || rsc.id AS lnk, 'La ressource ' || rsc.unique_name || ' est paramétrée de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM mcf_resources rsc WHERE rsc.ma_prj_typ_res NOT IN (2, 7, 6) AND rsc.ma_typ_prest != 'non_app' AND rsc.is_active = 1 UNION ALL SELECT 'RES07-02' numo_ref, resource_link || rsc.id AS lnk, 'La ressource ' || rsc.unique_name || ' est paramétrée de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM mcf_resources rsc WHERE rsc.ma_prj_typ_res IN (2, 7, 6) AND rsc.ma_typ_prest = 'non_app' AND rsc.is_active = 1 UNION ALL /*SELECT 'RES07-02' numo_ref, resource_link || rsc.id AS lnk, 'La ressource ' || rsc.unique_name || ' est paramétrée de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM mcf_resources rsc WHERE rsc.ma_prj_typ_res = 2 AND (rsc.prcategory = 'MOE-MUT' OR rsc.prcategory LIKE 'MOA%') AND rsc.ma_typ_prest = '2a' AND rsc.is_active = 1 UNION ALL*/ SELECT 'RES07-03' numo_ref, resource_link || rsc.id AS lnk, 'La ressource ' || rsc.unique_name || ' est une ressource active de type "Rôle" et catégorie "MOE-SAM" dont l''ID ne débute pas par "COMP_"' AS msg_erreur, 'Action corrective' AS msg_action FROM mcf_resources rsc WHERE rsc.is_active = 1 AND rsc.ma_prj_typ_res = 5 AND rsc.prcategory = 'MOE-SAM' AND rsc.unique_name NOT LIKE 'COMP_%' UNION ALL SELECT 'RES07-03' numo_ref, resource_link || rsc.id AS lnk, 'La ressource ' || rsc.unique_name || ' est une ressource active de type "Rôle" et catégorie "MOA" ou "MOE-M" dont l''ID ne débute pas par "ROLE_"' AS msg_erreur, 'Action corrective' AS msg_action FROM mcf_resources rsc WHERE rsc.is_active = 1 AND rsc.ma_prj_typ_res = 5 AND (rsc.prcategory LIKE 'MOA%' OR rsc.prcategory LIKE 'MOE-M%') AND rsc.unique_name NOT LIKE 'ROLE_%' UNION ALL SELECT 'RES07-03' numo_ref, resource_link || rsc.id AS lnk, 'La ressource ' || rsc.unique_name || ' est une ressource active de type "Centre de services" dont l''ID ne débute pas par "E_"' AS msg_erreur, 'Action corrective' AS msg_action FROM mcf_resources rsc WHERE rsc.is_active = 1 AND rsc.ma_prj_typ_res = 4 AND rsc.unique_name NOT LIKE 'E_%' /* UNION ALL SELECT 'RES07-03' numo_ref, resource_link || rsc.id AS lnk, 'La ressource ' || rsc.unique_name || ' est une ressource active de type "Prestataire régie" dont l''ID ne débute pas par "H" ou "ATGR" ou par "G" si catégorie =MOA-MMU' AS msg_erreur, 'Action corrective' AS msg_action FROM mcf_resources rsc WHERE rsc.is_active = 1 AND rsc.ma_prj_typ_res = 2 AND rsc.prcategory = 'MOA-MMU' AND NOT(rsc.unique_name LIKE 'H%' OR rsc.unique_name LIKE 'ATGR%' OR rsc.unique_name LIKE 'G%') */ UNION ALL SELECT 'RES07-03' numo_ref, resource_link || rsc.id AS lnk, 'La ressource ' || rsc.unique_name || ' est une ressource active de type "Charges en instance" dont l''ID ne débute pas par "ICOMP_"' AS msg_erreur, 'Action corrective' AS msg_action FROM mcf_resources rsc WHERE rsc.is_active = 1 AND rsc.ma_prj_typ_res = 7 AND rsc.unique_name NOT LIKE 'ICOMP_%' UNION ALL -- Charges en instance ou charges non allouée SELECT 'RES07-03' numo_ref, resource_link || rsc.id AS lnk, 'La ressource ' || rsc.unique_name || ' est une ressource active de type "Charges en instance" dont l''ID ne débute pas par "NCOMP_"' AS msg_erreur, 'Action corrective' AS msg_action FROM mcf_resources rsc WHERE rsc.is_active = 1 AND rsc.ma_prj_typ_res = 7 AND rsc.unique_name NOT LIKE 'NCOMP_%' UNION ALL SELECT 'RES07-03' numo_ref, resource_link || rsc.id AS lnk, 'La ressource ' || rsc.unique_name || ' est une ressource active de type "Charges non allouée" dont l''ID ne débute pas par "QCOMP_"' AS msg_erreur, 'Action corrective' AS msg_action FROM mcf_resources rsc WHERE rsc.is_active = 1 AND rsc.ma_prj_typ_res = 6 AND rsc.unique_name NOT LIKE 'QCOMP_%' UNION ALL SELECT 'RES07-03' numo_ref, resource_link || rsc.id AS lnk, 'La ressource ' || rsc.unique_name || ' est une ressource active de type "Salarié" - catégorie "MOE-SAM" dont le matricule ne débute pas par "S" ou - catégorie "MOA-MSP" ou "MOE-MSP" dont le matricule ne débute pas par "U" ou - catégorie "MOE-MFE" dont le matricule ne débute pas par "C" ou - catégorie "MOA-MFE" dont le matricule ne débute pas par "C"' AS msg_erreur, 'Action corrective' AS msg_action FROM mcf_resources rsc WHERE rsc.is_active = 1 AND rsc.ma_prj_typ_res = 1 AND ( (rsc.prcategory = 'MOE-SAM' AND rsc.unique_name NOT LIKE 'S%') OR (rsc.prcategory IN ('MOA-MSP','MOE-MSP') AND rsc.unique_name NOT LIKE 'U%') OR (rsc.prcategory = 'MOE-MFE' AND rsc.unique_name NOT LIKE 'C%') OR (rsc.prcategory = 'MOA-MFE' AND rsc.unique_name NOT LIKE 'C%') ) UNION ALL SELECT 'RES08-01' numo_ref, resource_link || rsc.id AS lnk, 'La ressource ' || rsc.unique_name || ' est paramétrée de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM mcf_resources rsc WHERE rsc.is_active = 1 AND rsc.person_type = 301 AND rsc.prusertext1 IS NULL UNION ALL SELECT 'RES08-02' numo_ref, resource_link || rsc.id AS lnk, 'La ressource ' || rsc.unique_name || ' est paramétrée de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM mcf_resources rsc WHERE rsc.is_active = 1 AND rsc.ma_prj_typ_res = 2 AND rsc.unique_name NOT LIKE 'ATGR%' AND rsc.last_name NOT LIKE '%.ext' UNION ALL SELECT 'RES09-01' numo_ref, resource_link || rsc.id AS lnk, 'La ressource ' || rsc.unique_name || ' est paramétrée de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM mcf_resources rsc WHERE rsc.is_active = 1 AND rsc.ma_prj_typ_res = 5 AND (rsc.prprimaryroleid IS NULL OR rsc.prprimaryroleid NOT IN (SELECT id FROM mcf_resources WHERE is_active = 1)) UNION ALL SELECT 'FEU01-01' numo_ref, new_ui_timesheet_link || rsc.id || new_ui_period_id_link || ptp.prid AS lnk, 'La feuille de temps de la ressource ' || rsc.unique_name || ' est au statut soumise sur la période ouverte "' || TO_CHAR(ptp.prstart,'YYYY-MM-DD') || '"' AS msg_erreur, 'Action corrective' AS msg_action FROM prtimesheet pts INNER JOIN prtimeperiod ptp ON ptp.prid = pts.prtimeperiodid INNER JOIN mcf_resources rsc ON rsc.id = pts.prresourceid WHERE pts.prstatus = 1 AND ptp.prisopen = 1 UNION ALL SELECT 'FEU01-02' numo_ref, new_ui_timesheet_link || rsc.id || new_ui_period_id_link || ptp.prid AS lnk, 'La feuille de temps de la ressource ' || rsc.unique_name || ' est au statut renvoyée avec une charge sur la période ouverte "' || TO_CHAR(ptp.prstart,'YYYY-MM-DD') || '"' AS msg_erreur, 'Action corrective' AS msg_action FROM prtimesheet pts INNER JOIN prtimeperiod ptp ON ptp.prid = pts.prtimeperiodid INNER JOIN mcf_resources rsc ON rsc.id = pts.prresourceid INNER JOIN prtimeentry pte ON pte.prtimesheetid = pts.prid WHERE pts.prstatus = 2 AND ptp.prisopen = 1 GROUP BY new_ui_timesheet_link, rsc.id, new_ui_period_id_link, ptp.prid, rsc.unique_name, TO_CHAR(ptp.prstart, 'YYYY-MM-DD') HAVING SUM(pte.practsum) > 0 UNION ALL SELECT numo_ref, (SELECT user_link FROM param) || id AS lnk, erreur AS msg_erreur, '' AS msg_action FROM ( SELECT 'Utilisateur '|| user_name ||' dans le cas 1, date de dernière connexion '||last_logged_in_date||DECODE(last_logged_in_date,NULL,'nulle') AS erreur, 'LIC01-01' numo_ref, id, user_name, user_status_id, last_logged_in_date, lic_right_type, MAX(licence_priority) licence_priority FROM LIC01 WHERE ma_prj_typ_res IN (2, 1) AND lic_right_type IN ('full', 'restricted') AND (last_logged_in_date IS NOT NULL AND last_logged_in_date < SYSDATE - 365) AND user_status_id = 202 AND id NOT IN ( SELECT csu.id FROM cmn_sec_users csu INNER JOIN srm_resources rsc ON rsc.user_id = csu.id INNER JOIN prassignment asg ON asg.prresourceid = rsc.id INNER JOIN prtask tsk ON tsk.prid = asg.prtaskid INNER JOIN inv_investments inv ON inv.id = tsk.prprojectid WHERE inv.is_active = 1 ) AND id IN ( SELECT csu.id FROM cmn_sec_users csu INNER JOIN srm_resources rsc ON rsc.user_id = csu.id INNER JOIN prassignment asg ON asg.prresourceid = rsc.id INNER JOIN prtask tsk ON tsk.prid = asg.prtaskid INNER JOIN inv_investments inv ON inv.id = tsk.prprojectid INNER JOIN prj_blb_slices slc ON slc.prj_object_id = asg.prid INNER JOIN prj_blb_slicerequests slr ON slr.id = slc.slice_request_id AND slr.request_name = 'DAILYRESOURCEACTCURVE' -- WHERE inv.is_active = 1 GROUP BY csu.id HAVING MAX(slc.slice_date) <= SYSDATE - 183 ) GROUP BY id, user_name, user_status_id, last_logged_in_date, lic_right_type /*UNION ALL SELECT 'Utilisateur '|| user_name ||' dans le cas 2 avec dernière date de connexion '||last_logged_in_date||DECODE(last_logged_in_date,NULL,'nulle') AS erreur, 'LIC01-01' numo_ref, id, user_name, user_status_id, last_logged_in_date, lic_right_type, MAX(licence_priority) licence_priority FROM LIC01 WHERE ma_prj_typ_res IN (2, 1) AND lic_right_type = 'restricted' AND last_logged_in_date < SYSDATE - 365 GROUP BY id, user_name, user_status_id, last_logged_in_date, lic_right_type UNION ALL SELECT 'Utilisateur '|| user_name ||' dans le cas 3 ne s''est jamais connecté (date de création : '||created_date||')' AS erreur, 'LIC01-01' numo_ref, id, user_name, user_status_id, last_logged_in_date, lic_right_type, MAX(licence_priority) licence_priority FROM LIC01 WHERE lic_right_type = 'restricted' AND last_logged_in_date IS NULL AND created_date < ADD_MONTHS(SYSDATE, -6) GROUP BY id, user_name, user_status_id, last_logged_in_date, lic_right_type, created_date*/ UNION ALL SELECT 'Utilisateur '|| user_name ||' dans le cas 2, aucune connexion depuis sa création le '||created_date AS erreur, 'LIC01-01' numo_ref, id, user_name, user_status_id, last_logged_in_date, lic_right_type, MAX(licence_priority) licence_priority FROM LIC01 WHERE ma_prj_typ_res IN (2, 1) AND lic_right_type IN ('full', 'restricted') AND last_logged_in_date IS NULL AND created_date < SYSDATE - 183 --AND user_status_id = 202 AND id NOT IN ( SELECT csu.id FROM cmn_sec_users csu INNER JOIN srm_resources rsc ON rsc.user_id = csu.id INNER JOIN prassignment asg ON asg.prresourceid = rsc.id INNER JOIN prtask tsk ON tsk.prid = asg.prtaskid INNER JOIN inv_investments inv ON inv.id = tsk.prprojectid WHERE inv.is_active = 1 ) AND id IN ( SELECT csu.id FROM cmn_sec_users csu INNER JOIN srm_resources rsc ON rsc.user_id = csu.id INNER JOIN prassignment asg ON asg.prresourceid = rsc.id INNER JOIN prtask tsk ON tsk.prid = asg.prtaskid INNER JOIN inv_investments inv ON inv.id = tsk.prprojectid INNER JOIN prj_blb_slices slc ON slc.prj_object_id = asg.prid INNER JOIN prj_blb_slicerequests slr ON slr.id = slc.slice_request_id AND slr.request_name = 'DAILYRESOURCEACTCURVE' WHERE inv.is_active = 1 GROUP BY csu.id HAVING MAX(slc.slice_date) <= SYSDATE - 183 ) GROUP BY id, user_name, user_status_id, last_logged_in_date, created_date, lic_right_type ) /* Resources - Fin */ ) QRY INNER JOIN odf_ca_ma_reg_gest octr ON octr.code = QRY.numo_ref CROSS JOIN param_filter filt WHERE (filt.objet IS NULL OR octr.ma_objet = filt.objet) AND (filt.controle IS NULL OR octr.ma_controle = filt.controle) AND (filt.acteur IS NULL OR octr.ma_acteur = filt.acteur) AND (filt.proprio IS NULL OR octr.ma_proprio = filt.proprio) AND (filt.criticite IS NULL OR octr.ma_criticite = filt.criticite) AND octr.ma_stat = 1 ) CTRL WHERE @FILTER@]]>