0 UNION ALL SELECT 'PLA02-01' numo_ref, (SELECT portfolio_link FROM param) || inv.id AS lnk, 'Projet inactif ' || inv.code || ' lié au plan Version de Travail' AS msg_erreur, 'Action corrective' AS msg_action FROM inv_plan_vtravail inv WHERE inv.is_active = 0 UNION ALL SELECT 'PLA02-02' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' avec plan métier à "pas de valeur"' AS msg_erreur, 'Action corrective' AS msg_action FROM inv_plan_vtravail inv WHERE ma_perimetre_metier = 'ma_no_perim' UNION ALL SELECT 'PLA02-03' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet "' || inv.code || '" lié au Plan version Travail avec ID ne débutant pas par "' || (SELECT x_string FROM PLA0203_listagg) || '"' AS msg_erreur, 'Action corrective' AS msg_action FROM inv_plan_vtravail inv WHERE SUBSTR(inv.code,1,1) NOT IN (SELECT x_string FROM PLA0203_list) UNION ALL SELECT 'PLA02-04' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' du plan Version de Travail avec catégorie différent de planning unique' AS msg_erreur, 'Action corrective' AS msg_action FROM inv_plan_vtravail inv WHERE categorie != 'ma_categ_unique' UNION ALL SELECT 'PLA02-05' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' avec date de fin > 2035' AS msg_erreur, 'Action corrective' AS msg_action FROM inv_plan_vtravail inv WHERE EXTRACT(YEAR FROM inv.schedule_finish) > 2035 UNION ALL SELECT 'PLA02-06' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM inv_plan_vtravail inv LEFT JOIN srm_resources mgr ON mgr.user_id = inv.manager_id LEFT JOIN srm_resources moe ON moe.id = inv.ma_cpmoa WHERE (inv.manager_id IS NULL OR mgr.is_active = 0 OR inv.ma_cpmoa IS NULL OR moe.is_active = 0) AND inv.ma_statut_prj IN ('pre_planif', 'actif') UNION ALL SELECT 'PLA02-07' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' du plan version de travail a un classement stratégique renseigné à pas de valeur' AS msg_erreur, 'Action corrective' AS msg_action FROM inv_plan_vtravail inv WHERE classement_strategiq = 'ma_no_clst' UNION ALL SELECT 'PLA03-01' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet du Plan Version de Travail ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM inv_plan_vtravail inv WHERE inv.code LIKE 'P%_Prog' UNION ALL SELECT 'PLA03-02' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM p_prog inv WHERE inv.is_active = 1 AND inv.code NOT IN (SELECT inv.code || '_Prog' FROM pfm_portfolios ptf INNER JOIN odf_ca_pfm_portfolio ocp ON ocp.id = ptf.id INNER JOIN pfm_investments pfi ON pfi.portfolio_id = ptf.id INNER JOIN inv_investments inv ON inv.id = pfi.investment_id WHERE ocp.ma_indice_revision = '2012-P0' AND inv.code LIKE 'P%' UNION ALL SELECT inv.code FROM inv_investments inv WHERE inv.is_active = 1 ) UNION ALL SELECT 'PLA03-03' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM P_Prog inv INNER JOIN odf_ca_project odi ON odi.id = inv.id INNER JOIN inv_investments prj ON prj.code || '_Prog' = inv.code INNER JOIN odf_ca_project odp ON odp.id = prj.id WHERE inv.is_active = 1 AND odp.ma_statut_prj != odi.ma_statut_prj UNION ALL SELECT 'PLA03-04' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM P_Prog inv INNER JOIN odf_ca_project odp ON odp.id = inv.id WHERE inv.is_active = 1 AND odp.categorie != 'Autre' UNION ALL SELECT 'PLA03-05' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM P_Prog inv LEFT JOIN odf_blueprints blp ON blp.id = inv.odf_blueprint_id LEFT JOIN odf_abstract_blueprints blpa ON blp.id = blpa.id WHERE inv.is_active = 1 AND (inv.odf_blueprint_id IS NULL OR blpa.code != 'BP1005') UNION ALL SELECT 'PLA03-06' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM P_Prog inv WHERE inv.is_active = 1 AND inv.id NOT IN ( SELECT inv.id FROM P_Prog inv INNER JOIN prj_obs_associations poa ON record_id = inv.id AND poa.table_name = 'SRM_PROJECTS' INNER JOIN prj_obs_units pou ON pou.id = poa.unit_id INNER JOIN prj_obs_types pot ON pot.id = pou.type_id AND pot.unique_name = 'ma_obs_projets' INNER JOIN nbi_dim_obs ndo ON ndo.obs_unit_id = poa.unit_id WHERE inv.is_active = 1 ) UNION ALL SELECT DISTINCT 'PRG01-01' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM inv_investments inv INNER JOIN odf_ca_project odp ON odp.id = inv.id INNER JOIN inv_hierarchies hch ON hch.parent_id = inv.id INNER JOIN inv_investments cld ON cld.id = hch.child_id INNER JOIN odf_ca_project odc ON odc.id = cld.id WHERE inv.code LIKE 'P%_Prog' AND inv.is_active = 1 AND odp.ma_statut_prj IN ('termine', 'abandonne') AND odc.ma_statut_prj NOT IN ('termine', 'abandonne', 'suspendu') UNION ALL SELECT DISTINCT 'PRG02-01' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM inv_investments inv INNER JOIN inv_hierarchies hch ON hch.parent_id = inv.id INNER JOIN inv_investments cld ON cld.id = hch.child_id WHERE inv.code LIKE 'P%_Prog' AND inv.is_active = 0 AND cld.is_active = 1 UNION ALL SELECT 'PRG02-02' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM inv_investments inv WHERE inv.code LIKE 'P%_Prog' AND inv.is_active = 1 AND ( SELECT COUNT(inv_subquery.id) FROM inv_investments inv_subquery INNER JOIN inv_hierarchies hch ON hch.parent_id = inv_subquery.id INNER JOIN inv_investments cld ON cld.id = hch.child_id WHERE hch.parent_id = inv.id ) = ( SELECT COUNT(inv_subquery.id) FROM inv_investments inv_subquery INNER JOIN inv_hierarchies hch ON hch.parent_id = inv_subquery.id INNER JOIN inv_investments cld ON cld.id = hch.child_id WHERE hch.parent_id = inv.id AND cld.is_active = 0 ) UNION ALL SELECT 'PRG02-03' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM inv_investments inv INNER JOIN inv_investments prj ON prj.code || '_Prog' = inv.code WHERE inv.code LIKE 'P%_Prog' AND inv.is_active = 0 AND prj.is_active = 1 UNION ALL SELECT 'PRJ01-01' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM PRJ01 inv WHERE inv.is_open_for_te = 1 UNION ALL SELECT 'PRJ01-02' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM PRJ01 inv WHERE ma_statut_prj NOT IN ('termine', 'abandonne', 'suspendu') UNION ALL SELECT 'PRJ01-03' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte. RAF = ' || inv.labor_etcsum AS msg_erreur, 'Action corrective' AS msg_action FROM PRJ01 inv WHERE inv.labor_etcsum > 0 UNION ALL SELECT 'PRJ02-01' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM PRJ02 inv WHERE inv.labor_etcsum > 0 UNION ALL SELECT 'PRJ02-02' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM PRJ02 inv WHERE inv.is_open_for_te = 1 UNION ALL SELECT 'PRJ02-03' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM PRJ02 inv WHERE ma_date_livraison IS NULL UNION ALL SELECT 'PRJ02-04' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM PRJ02 inv WHERE (inv.schedule_start > SYSDATE OR inv.schedule_finish > SYSDATE) UNION ALL SELECT 'PRJ02-05' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM PRJ02 inv WHERE inv.id NOT IN (SELECT id FROM inv_plan_vtravail) UNION ALL SELECT 'PRJ03-01' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM inv_plan_vtravail inv WHERE inv.is_open_for_te = 1 AND ma_statut_prj IN ('nd', 'pre_planif') UNION ALL SELECT 'PRJ03-01' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM inv_plan_vtravail inv WHERE ma_statut_prj = 'actif' AND inv.is_open_for_te = 0 UNION ALL SELECT 'PRJ03-02' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM inv_plan_vtravail inv WHERE ma_statut_prj = 'actif' AND ma_date_debut IS NULL UNION ALL SELECT DISTINCT 'PRJ03-03' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM inv_investments inv INNER JOIN odf_ca_project odp ON odp.id = inv.id INNER JOIN inv_projects prj ON prj.prid = inv.id WHERE inv.is_active = 1 AND odp.categorie = 'ma_categ_unique' AND odp.ma_statut_prj IN ('nd', 'pre_planif', 'actif') AND odp.ma_domaine_metier != 'HDO' AND inv.id NOT IN (SELECT id FROM inv_plan_vtravail) AND prj.is_template = 0 UNION ALL SELECT 'PRJ04-01' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM inv_investments inv INNER JOIN inv_projects prj ON prj.prid = inv.id INNER JOIN odf_ca_project odp ON odp.id = inv.id WHERE inv.is_active = 1 AND odp.categorie = 'ma_categ_unique' AND prj.is_template = 0 AND inv.id NOT IN ( SELECT poa.record_id FROM prj_obs_associations poa INNER JOIN nbi_dim_obs ndo ON ndo.obs_unit_id = poa.unit_id AND ndo.obs_type_name = 'OBS Projets' INNER JOIN cmn_lookups_v lkp ON lkp.lookup_code = odp.ma_domaine_metier AND lookup_type = 'MA_LKP_DOMMET' AND language_code = 'fr' WHERE poa.table_name = 'SRM_PROJECTS' AND ndo.path LIKE ('%PPM/' || lkp.name ||'%') ) UNION ALL SELECT 'PRJ04-02' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM inv_investments inv INNER JOIN inv_projects prj ON prj.prid = inv.id INNER JOIN odf_ca_project odp ON odp.id = inv.id WHERE inv.is_active = 1 AND odp.categorie = 'ma_categ_unique' AND prj.is_program = 0 AND prj.is_template = 0 AND ( SELECT COUNT(inv_subquery.id) FROM inv_investments inv_subquery INNER JOIN inv_hierarchies hch ON hch.parent_id = inv_subquery.id INNER JOIN inv_investments cld ON cld.id = hch.child_id INNER JOIN inv_projects prj ON prj.prid = inv_subquery.id WHERE hch.child_id = inv.id AND prj.is_program = 1 ) > 1 UNION ALL SELECT DISTINCT 'PRJ04-03' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM pfm_portfolios ptf INNER JOIN odf_ca_pfm_portfolio ocp ON ocp.id = ptf.id INNER JOIN pfm_investments pfi ON pfi.portfolio_id = ptf.id INNER JOIN inv_investments inv ON inv.id = pfi.investment_id INNER JOIN odf_ca_project odp ON odp.id = inv.id INNER JOIN inv_projects prj ON prj.prid = inv.id WHERE inv.is_active = 1 AND odp.categorie = 'ma_categ_unique' AND odp.ma_domaine_metier = 'HDO' AND ocp.ma_indice_revision = '2012-P0' AND ptf.is_active = 1 AND prj.is_template = 0 UNION ALL SELECT 'PRJ04-04' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM inv_investments inv INNER JOIN inv_projects prj ON prj.prid = inv.id AND prj.is_template = 0 INNER JOIN odf_ca_project ocp ON ocp.id = inv.id LEFT JOIN odf_blueprints blp ON blp.id = inv.odf_blueprint_id LEFT JOIN odf_abstract_blueprints blpa ON blp.id = blpa.id WHERE inv.is_active = 1 AND ocp.categorie = 'ma_categ_unique' AND ocp.ma_statut_prj = 'nd' AND blpa.code != 'BP1007' AND prj.is_template = 0 UNION ALL SELECT 'PRJ04-04' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM inv_investments inv INNER JOIN inv_projects prj ON prj.prid = inv.id AND prj.is_template = 0 INNER JOIN odf_ca_project ocp ON ocp.id = inv.id LEFT JOIN odf_blueprints blp ON blp.id = inv.odf_blueprint_id LEFT JOIN odf_abstract_blueprints blpa ON blp.id = blpa.id WHERE inv.is_active = 1 AND ocp.categorie = 'ma_categ_unique' AND ocp.ma_statut_prj IN ('actif', 'pre_planif') AND blpa.code != 'BP1006' AND prj.is_template = 0 UNION ALL SELECT 'PRJ04-04' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM inv_investments inv INNER JOIN inv_projects prj ON prj.prid = inv.id AND prj.is_template = 0 INNER JOIN odf_ca_project ocp ON ocp.id = inv.id LEFT JOIN odf_blueprints blp ON blp.id = inv.odf_blueprint_id LEFT JOIN odf_abstract_blueprints blpa ON blp.id = blpa.id WHERE inv.is_active = 1 AND ocp.categorie = 'ma_categ_unique' AND ocp.ma_statut_prj NOT IN ('nd', 'actif', 'pre_planif') AND blpa.code IS NULL AND prj.is_template = 0 UNION ALL SELECT 'PRJ05-01' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM inv_investments inv INNER JOIN inv_projects prj ON prj.prid = inv.id AND prj.is_template = 0 INNER JOIN odf_ca_project ocp ON ocp.id = inv.id WHERE ocp.ma_statut_prj = 'nd' AND (ocp.ma_insc_plprj IS NULL OR ocp.ma_insc_plprj != 'ma_non_valid') AND prj.is_template = 0 AND prj.is_program = 0 AND inv.is_active = 1 AND ocp.categorie = 'ma_categ_unique' AND (ocp.ma_type_projet != 'avenant' OR ocp.ma_type_projet IS NULL) UNION ALL SELECT 'PRJ05-01' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM inv_investments inv INNER JOIN inv_projects prj ON prj.prid = inv.id AND prj.is_template = 0 INNER JOIN odf_ca_project ocp ON ocp.id = inv.id WHERE ocp.ma_statut_prj = 'actif' AND inv.code LIKE '%0' AND inv.code NOT LIKE 'P%0' AND (ocp.ma_insc_plprj IS NULL OR ocp.ma_insc_plprj != 'ma_insc_en_cours') AND prj.is_template = 0 AND prj.is_program = 0 AND inv.is_active = 1 AND ocp.categorie = 'ma_categ_unique' AND (ocp.ma_type_projet != 'avenant' OR ocp.ma_type_projet IS NULL) UNION ALL SELECT 'PRJ05-01' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM inv_investments inv INNER JOIN inv_projects prj ON prj.prid = inv.id AND prj.is_template = 0 INNER JOIN odf_ca_project ocp ON ocp.id = inv.id WHERE ocp.ma_statut_prj IN ('pre_planif', 'actif') AND (inv.code NOT LIKE '%0' OR inv.code LIKE 'P%0') AND (ocp.ma_insc_plprj IS NULL OR ocp.ma_insc_plprj != 'ma_valid') AND prj.is_template = 0 AND prj.is_program = 0 AND inv.is_active = 1 AND ocp.categorie = 'ma_categ_unique' AND (ocp.ma_type_projet != 'avenant' OR ocp.ma_type_projet IS NULL) UNION ALL SELECT 'PRJ06-01' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM inv_investments inv INNER JOIN inv_projects prj ON prj.prid = inv.id AND prj.is_template = 0 WHERE inv.id IN ( SELECT inv.id FROM inv_investments inv INNER JOIN odf_ca_project ocp ON ocp.id = inv.id INNER JOIN prtask tsk ON tsk.prprojectid = inv.id INNER JOIN prassignment asg ON asg.prtaskid = tsk.prid INNER JOIN srm_resources rsc ON rsc.id = asg.prresourceid 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 = 'DAILYRESOURCEESTCURVE' WHERE slc.slice > 0 AND categorie = 'ma_categ_unique' AND slc.slice_date < (SELECT MIN(prstart) FROM prtimeperiod WHERE prisopen = 1)-- reste a faire avant 1ère periode de temps ouverte ) UNION ALL SELECT 'PRJ06-02' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM inv_investments inv INNER JOIN inv_projects prj ON prj.prid = inv.id AND prj.is_template = 0 WHERE inv.id IN ( SELECT tsk.prprojectid FROM prtask tsk INNER JOIN prassignment asg ON asg.prtaskid = tsk.prid INNER JOIN srm_resources rsc ON rsc.id = asg.prresourceid INNER JOIN odf_ca_project ocp ON ocp.id = tsk.prprojectid WHERE ocp.categorie = 'ma_categ_unique' AND rsc.is_active = 0 AND asg.prestsum > 0 ) UNION ALL SELECT 'PRJ06-03' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM inv_investments inv INNER JOIN inv_projects prj ON prj.prid = inv.id AND prj.is_template = 0 WHERE inv.id IN ( SELECT inv.id FROM inv_investments inv INNER JOIN odf_ca_project ocp ON ocp.id = inv.id INNER JOIN prtask tsk ON tsk.prprojectid = inv.id WHERE inv.is_active = 1 AND ocp.categorie = 'ma_categ_unique' AND SUBSTR(tsk.prcategory, 1, 4) NOT IN ('MOA-', 'PRJ-', 'PRG-', 'AVT-') --AND tsk.wbs_parseq != (SELECT prwbssequence FROM prtask WHERE prprojectid = inv.id and prexternalid = '~dtp') --AND tsk.prexternalid != '~dtp' AND (tsk.prexternalid IS NULL OR tsk.prexternalid NOT LIKE '~dtp%') ) UNION ALL SELECT 'PRJ06-04' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM inv_investments inv INNER JOIN inv_projects prj ON prj.prid = inv.id AND prj.is_template = 0 WHERE inv.id IN ( SELECT inv.id FROM inv_investments inv INNER JOIN odf_ca_project ocp ON ocp.id = inv.id INNER JOIN prtask tsk ON tsk.prprojectid = inv.id WHERE ocp.categorie = 'ma_categ_unique' AND tsk.pristask = 1 AND (SUBSTR(tsk.prcategory, 4, 1) != '-' OR LENGTH(tsk.prcategory) < 7) AND inv.is_active = 1 ) UNION ALL SELECT 'PRJ06-05' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM inv_investments inv INNER JOIN inv_projects prj ON prj.prid = inv.id AND prj.is_template = 0 WHERE inv.id IN ( SELECT inv.id FROM inv_investments inv INNER JOIN prtask tsk ON tsk.prprojectid = inv.id INNER JOIN prassignment asg ON asg.prtaskid = tsk.prid INNER JOIN srm_resources rsc ON rsc.id = asg.prresourceid INNER JOIN odf_ca_resource ocr ON ocr.id = rsc.id WHERE inv.is_active = 1 AND inv.id IN (SELECT id FROM inv_plan_vtravail) AND tsk.prfinish < SYSDATE + 30 AND ocr.ma_prj_typ_res IN (5, 6, 8, 7) AND asg.practsum + asg.prestsum > 0 ) UNION ALL SELECT 'PRJ06-06' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM inv_plan_vtravail inv WHERE inv.id IN ( SELECT inv.id FROM inv_plan_vtravail inv INNER JOIN odf_ca_project ocp ON ocp.id = inv.id INNER JOIN prtask tsk ON tsk.prprojectid = inv.id INNER JOIN prassignment asg ON asg.prtaskid = tsk.prid INNER JOIN srm_resources rsc ON rsc.id = asg.prresourceid 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 = 'MONTHLYRESOURCEESTCURVE' WHERE slc.slice > 0 AND TO_CHAR(slc.slice_date, 'YYYY') = TO_CHAR(SYSDATE, 'YYYY') AND rsc.resource_type = 0 --Labor AND inv.is_active = 1 AND ocp.categorie = 'ma_categ_unique' AND inv.id NOT IN ( SELECT inv.id FROM odf_ssl_cst_dtl_cost ssl INNER JOIN fin_cost_plan_details dtl ON dtl.id = ssl.prj_object_id INNER JOIN fin_plans pln ON pln.id = dtl.plan_id INNER JOIN odf_ca_costplan ocp ON ocp.id = pln.id INNER JOIN inv_plan_vtravail inv ON inv.id = pln.object_id INNER JOIN transclass trx ON trx.id = dtl.transaction_class_id WHERE inv.odf_object_code = 'project' AND TO_CHAR(ssl.start_date, 'YYYY') = TO_CHAR(SYSDATE, 'YYYY') AND trx.transclass IN ('01_Inter', '02_Prest', '07_DDSIG', '09_Filia', '10_Part') AND ocp.ma_indice_revision = '2012-P0' GROUP BY inv.id, trx.transclass, pln.name, TO_CHAR(ssl.start_date, 'YYYY') HAVING ROUND(SUM((ssl.finish_date-ssl.start_date)*ssl.slice),2) > 0 ) ) UNION ALL SELECT 'PRJ07-01' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM inv_investments inv INNER JOIN inv_projects prj ON prj.prid = inv.id AND prj.is_template = 0 INNER JOIN odf_ca_project ocp ON ocp.id = inv.id WHERE ocp.categorie = 'ma_categ_unique' AND ocp.ma_statut_prj = 'suspendu' AND inv.is_open_for_te = 1 UNION ALL SELECT 'PRJ07-02' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM inv_investments inv INNER JOIN inv_projects prj ON prj.prid = inv.id AND prj.is_template = 0 INNER JOIN odf_ca_project ocp ON ocp.id = inv.id WHERE inv.is_active = 1 AND ocp.categorie = 'ma_categ_unique' AND ocp.ma_statut_prj = 'suspendu' AND (ocp.ma_domaine_metier != 'HDO' OR ocp.ma_domaine_metier IS NULL) AND inv.id NOT IN (SELECT id FROM inv_plan_vtravail) UNION ALL SELECT 'PRJ08-01' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM inv_investments inv INNER JOIN inv_projects prj ON prj.prid = inv.id AND prj.is_template = 0 INNER JOIN odf_ca_project ocp ON ocp.id = inv.id WHERE inv.is_active = 1 AND inv.id IN (SELECT id FROM inv_plan_vtravail) AND ocp.classement_strategiq = 'ma_strategique' AND NOT(SUBSTR(ocp.ma_perimetre_metier, 1, 3) = 'ma_' AND SUBSTR(ocp.ma_perimetre_metier, 4, 2) BETWEEN '01' AND '11' AND SUBSTR(ocp.ma_perimetre_metier, 6, 1) = '_') /* AND inv.id NOT IN ( SELECT inv.id FROM inv_investments inv INNER JOIN inv_hierarchies hch ON hch.child_id = inv.id INNER JOIN inv_investments prt ON prt.id = hch.parent_id INNER JOIN odf_ca_project ocp ON ocp.id = prt.id WHERE SUBSTR(ocp.ma_perimetre_metier, 1, 3) = 'ma_' AND SUBSTR(ocp.ma_perimetre_metier, 4, 2) BETWEEN '01' AND '11' AND SUBSTR(ocp.ma_perimetre_metier, 6, 1) = '_' )*/ UNION ALL SELECT 'PRJ08-02' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM inv_investments inv INNER JOIN inv_projects prj ON prj.prid = inv.id AND prj.is_template = 0 INNER JOIN odf_ca_project ocp ON ocp.id = inv.id WHERE inv.is_active = 1 AND inv.id IN (SELECT id FROM inv_plan_vtravail) AND ocp.classement_strategiq = 'ma_strategique' AND ocp.ma_obj_metier IS NULL UNION ALL SELECT 'PRJ08-03' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM inv_investments inv INNER JOIN odf_ca_project ocp ON ocp.id = inv.id INNER JOIN inv_projects prj ON prj.prid = inv.id AND prj.is_template = 0 WHERE inv.is_active = 1 AND inv.id IN (SELECT id FROM inv_plan_vtravail) AND SUBSTR(ocp.ma_perimetre_metier, 1, 3) = 'ma_' AND SUBSTR(ocp.ma_perimetre_metier, 4, 2) BETWEEN '01' AND '11' AND ocp.classement_strategiq != 'ma_strategique' UNION ALL SELECT 'PRJ08-04' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM inv_investments inv INNER JOIN inv_projects prj ON prj.prid = inv.id AND prj.is_template = 0 INNER JOIN odf_ca_project ocp ON ocp.id = inv.id WHERE inv.is_active = 1 AND inv.id IN (SELECT id FROM inv_plan_vtravail) AND ocp.ma_obj_metier IS NOT NULL AND ocp.classement_strategiq != 'ma_strategique' UNION ALL SELECT 'PRJ08-05' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM inv_investments inv INNER JOIN inv_projects prj ON prj.prid = inv.id AND prj.is_template = 0 INNER JOIN odf_ca_project ocp ON ocp.id = inv.id WHERE inv.is_active = 1 AND inv.id IN (SELECT id FROM inv_plan_vtravail) AND ocp.ma_cle_tri_2 = 'Trajectoire SI' AND ocp.classement_strategiq != 'ma_strategique' UNION ALL SELECT 'FCT01-01' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM FCT inv WHERE inv.is_active = 0 AND is_open_for_te = 1 UNION ALL SELECT 'FCT01-02' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM FCT inv WHERE inv.is_active = 0 AND inv.ma_statut_prj NOT IN ('termine', 'abandonne') UNION ALL SELECT 'FCT01-03' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM FCT inv WHERE inv.is_active = 0 AND inv.labor_etcsum != 0 UNION ALL SELECT 'FCT02-01' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM FCT inv WHERE inv.is_active = 1 AND inv.ma_statut_prj IN ('termine', 'abandonne') AND inv.labor_etcsum > 0 UNION ALL SELECT 'FCT02-02' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM FCT inv WHERE inv.is_active = 1 AND inv.ma_statut_prj IN ('termine', 'abandonne') AND inv.is_open_for_te = 1 UNION ALL SELECT 'FCT02-03' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM FCT inv WHERE inv.is_active = 1 AND inv.ma_statut_prj IN ('termine', 'abandonne') UNION ALL SELECT 'FCT03-01' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM FCT inv WHERE inv.is_active = 1 AND inv.is_open_for_te = 1 AND inv.ma_statut_prj = 'nd' UNION ALL SELECT 'FCT03-01' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM FCT inv WHERE inv.is_active = 1 AND inv.ma_statut_prj = 'actif' AND inv.is_open_for_te = 0 UNION ALL SELECT 'FCT03-02' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM FCT inv LEFT JOIN srm_resources mgr ON mgr.user_id = inv.manager_id --LEFT JOIN srm_resources moe ON moe.id = inv.ma_cpmoa WHERE inv.is_active = 1 AND inv.ma_statut_prj = 'actif' AND (inv.manager_id IS NULL OR mgr.is_active = 0)--OR inv.ma_cpmoa IS NULL) UNION ALL SELECT 'FCT03-03' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM FCT inv LEFT JOIN odf_blueprints blp ON blp.id = inv.odf_blueprint_id LEFT JOIN odf_abstract_blueprints blpa ON blp.id = blpa.id WHERE inv.is_active = 1 AND (inv.odf_blueprint_id IS NULL OR blpa.code != 'BP1005') UNION ALL SELECT 'FCT03-04' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM FCT inv INNER JOIN prj_obs_associations poa ON record_id = inv.id AND poa.table_name = 'SRM_PROJECTS' INNER JOIN nbi_dim_obs ndo ON ndo.obs_unit_id = poa.unit_id WHERE inv.is_active = 1 AND ndo.obs_type_name = 'OBS Projets' AND ndo.path NOT LIKE '%/Fonctionnement%' UNION ALL SELECT 'FCT03-05' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM FCT inv INNER JOIN pac_mnt_projects pmp ON pmp.id = inv.id WHERE inv.is_active = 1 AND pmp.status = 'O' UNION ALL SELECT 'FCT04-01' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM FCT inv WHERE inv.is_active = 1 AND inv.id IN ( SELECT inv.id FROM FCT inv INNER JOIN prtask tsk ON tsk.prprojectid = inv.id INNER JOIN prassignment asg ON asg.prtaskid = tsk.prid 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 = 'DAILYRESOURCEESTCURVE' WHERE inv.ma_statut_prj = 'actif' AND inv.is_active = 1 AND slc.slice > 0 AND slc.slice_date < (SELECT MIN(prstart) FROM prtimeperiod WHERE prisopen = 1)-- reste a faire avant 1ère periode de temps ouverte ) UNION ALL SELECT 'FCT04-02' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM FCT inv WHERE inv.is_active = 1 AND inv.id IN ( SELECT inv.id FROM FCT inv INNER JOIN prtask tsk ON tsk.prprojectid = inv.id INNER JOIN prassignment asg ON asg.prtaskid = tsk.prid INNER JOIN srm_resources rsc ON rsc.id = asg.prresourceid 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 = 'DAILYRESOURCEESTCURVE' WHERE inv.ma_statut_prj = 'actif' AND inv.is_active = 1 AND slc.slice > 0 AND rsc.is_active = 0 ) UNION ALL SELECT 'FCT04-03' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM FCT inv WHERE inv.is_active = 1 AND inv.id IN ( SELECT inv.id FROM FCT inv INNER JOIN inv_projects prj ON prj.prid = inv.id INNER JOIN prtask tsk ON tsk.prprojectid = inv.id WHERE inv.ma_statut_prj = 'actif' AND inv.is_active = 1 AND (LENGTH(tsk.prcategory) < 3 OR tsk.prcategory IS NULL) AND tsk.pristask = 1 ) UNION ALL SELECT 'FCT04-04' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM FCT inv WHERE inv.id IN ( SELECT inv.id FROM FCT inv INNER JOIN inv_projects prj ON prj.prid = inv.id INNER JOIN prtask tsk ON tsk.prprojectid = inv.id INNER JOIN prassignment asg ON asg.prtaskid = tsk.prid INNER JOIN srm_resources rsc ON rsc.id = asg.prresourceid INNER JOIN odf_ca_resource ocr ON ocr.id = rsc.id WHERE inv.ma_statut_prj = 'actif' AND tsk.prfinish <= SYSDATE + 30 AND ocr.ma_prj_typ_res IN (5, 6, 8, 7) AND asg.prestsum > 0 ) UNION ALL SELECT 'FCT05-01' numo_ref, (SELECT project_link FROM param) || inv.id AS lnk, 'Projet ' || inv.code || ' est paramétré de façon incorrecte' AS msg_erreur, 'Action corrective' AS msg_action FROM FCT inv WHERE inv.is_active = 1 AND inv.id IN ( SELECT inv.id FROM FCT inv INNER JOIN inv_projects prj ON prj.prid = inv.id INNER JOIN prtask tsk ON tsk.prprojectid = inv.id WHERE inv.ma_statut_prj = 'actif' AND inv.is_active = 1 AND (SUBSTR(SUBSTR(prcategory,INSTR(prcategory,'-')+1),INSTR(SUBSTR(prcategory,INSTR(prcategory,'-')+1),'-')+1) NOT IN ('AEF','AEM','AES','BPS','BQE','ECA','MAS','MCV','MFE','MNF','MPB','MPP','MSP','nim','NUO','SAM') OR SUBSTR(SUBSTR(prcategory,INSTR(prcategory,'-')+1),INSTR(SUBSTR(prcategory,INSTR(prcategory,'-')+1),'-')+1) IS NULL) AND tsk.pristask = 1 AND (inv.code LIKE 'D7%' OR inv.code LIKE 'D600%') ) /* Projets - Fin */ UNION ALL /* Resources - Début */ --Extraire les ressources "inactives" de type "Centre de Service" /" Prestataire" / "Salarié" / ouvertes à la saisie des temps SELECT 'RES01-01' 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é"') || ' est ouverte à la saisie des temps' 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.prisopen = 1 UNION ALL --Extraire les ressources "inactives" de tpe "Centre de Service" /" Prestataire" / "Salarié" / ayant un compte clarity " actif" ou "verrouillé" SELECT 'RES01-02' numo_ref, user_link || rsc.user_id || user_rsc_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 un compte clarity ' || DECODE(rsc.user_status_id,200,'actif',202,'verrouillé') 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.user_status_id IN (200, 202) AND rsc.id > 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-SAM')) 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@]]>