-- Determine le scope de project_id 
-- sur lesquels on lance la requete
WITH SCOPE_PROJECT AS
(
    Select 
         INV.ID
        ,INV.CODE
        ,INV.NAME as INV_NAME
        ,PPF.IS_ACTIVE
        ,PPF.NAME as PPF_NAME
        ,PPF.CODE as PPF_CODE
    FROM nikuprd.INV_INVESTMENTS INV
    JOIN nikuprd.PFM_INVESTMENTS PPC
        ON PPC.INVESTMENT_ID = INV.ID
    JOIN nikuprd.PFM_PORTFOLIOS PPF
        on PPF.ID = PPC.PORTFOLIO_ID 
    WHERE   
        PPF.CODE = 'PLAN VTRAVAIL'
        AND PPF.IS_ACTIVE = '1'
)
,CL_CCN AS
(
	SELECT
		CL.lookup_code
		,CL.lookup_type
		,CL.is_active
		,CCN.table_name
		,CCN.name
	FROM nikuprd.CMN_LOOKUPS 		CL
	INNER JOIN nikuprd.CMN_CAPTIONS_NLS	CCN
		ON CCN.pk_id = CL.id
		AND CCN.language_code = 'fr'
		AND CCN.table_name = 'CMN_LOOKUPS'
  WHERE CL.lookup_type in ('MA_LKP_LIGNE_DDSIG')
)
Select
    INV.PPF_CODE
    ,INV.ID
    ,INV.CODE
    ,proj.prname as proj_name
    ,proj.Is_Active as proj_actif
    ,TASK.prname as task_name
    ,TASK.prstart as task_start_date
    ,TASK.PRFINISH as task_end_date
    ,task.PRCATEGORY as code_tache
    ,substr(upper(task.PRCATEGORY),1,3)  as code_majeur
    ,codcat_lvl1.DESCRIPTION             as code_majeur_label
    ,substr(upper(task.PRCATEGORY),5,3)  as code_mineur
    ,codcat_lvl2.DESCRIPTION             as code_mineur_label
    ,substr(upper(task.PRCATEGORY),9,3)  as code_tierce
    ,codcat_lvl3.DESCRIPTION             as code_tierce_label
    ,AF_ASSI.SLICE_DATE
    ,AF_ASSI.AFACTUAL
    ,AF_ASSI.AFESTIMATE
    ,CASE WHEN upper(INV.PPF_CODE) = 'PLAN VTRAVAIL' then 
            CASE WHEN SYSDATE - 1 >= AF_ASSI.SLICE_DATE THEN AF_ASSI.AFESTIMATE ELSE 0 END
          ELSE 
            CASE WHEN to_date(20231231,'yyyymmdd') <= AF_ASSI.SLICE_DATE THEN AF_ASSI.AFESTIMATE ELSE 0 END
          END
     as AFESTIMATE_PAST
    ,TO_NUMBER(TO_CHAR(NVL(AF_ASSI.SLICE_DATE, TASK.prstart),'YYYY')) as TASK_START_YEAR
    ,TO_NUMBER(TO_CHAR(NVL(AF_ASSI.SLICE_DATE, TASK.prstart),'MM')) as TASK_START_MONTH
    ,RESS.PRCATEGORY
    ,RESS.PRISROLE
    ,RESS.PRNAME
    ,RESS.IS_ACTIVE AS RESSOURCE_IS_ACTIVE
    ,UPPER(RESS.PREXTERNALID) AS PREXTERNALID
    ,UPPER(SUBSTR(RESS.PrUserText2, 1,7)) as SA
    ,UPPER(RESS.PRUSERTEXT1) AS SOCIETE
    ,CA_RESS.MA_PRJ_TYP_RES
    ,CA_RESS.TYPE_PRESTATION_AFF
    ,RESS_lvl2.FULL_NAME AS RESSOURCE_PILOTE_NAME
    ,ROLE.prname as ROLE_NAME
    ,CL_CCN_RESS.NAME as DDSI_NAME
    ,CL_CCN_RESS.LOOKUP_CODE AS DDSI_CODE
from SCOPE_PROJECT INV
JOIN nikuprd.PRPROJECT PROJ
ON PROJ.prExternalID = INV.CODE
-- On ajoute les taches a la vue projet
JOIN nikuprd.PRTASK TASK
ON TASK.prProjectId  = PROJ.prID
-- On ajoute les assignements pour les taches
LEFT JOIN NIKUPRD.PRASSIGNMENT PR_ASSI
    ON PR_ASSI.PRTASKID = TASK.PRID
LEFT JOIN adm_bo.AFASSIGNMENT_C AF_ASSI
    ON AF_ASSI.PRJ_OBJECT_ID = PR_ASSI.PRID
-- On ajoute les ressources associees à chacune des taches
JOIN NIKUPRD.PRRESOURCE RESS
    ON RESS.PRID = PR_ASSI.PRRESOURCEID
JOIN nikuprd.ODF_CA_RESOURCE CA_RESS
    ON RESS.PRID = CA_RESS.ID
LEFT JOIN NIKUPRD.SRM_RESOURCES RESS_lvl1
    ON RESS.PREXTERNALID= RESS_lvl1.UNIQUE_NAME
LEFT JOIN NIKUPRD.SRM_RESOURCES RESS_lvl2
    ON RESS_lvl1.MANAGER_ID = RESS_lvl2.USER_ID
LEFT JOIN NIKUPRD.PRRESOURCE ROLE
    ON ROLE.PRID = RESS.PRPRIMARYROLEID
LEFT JOIN CL_CCN CL_CCN_RESS
    ON UPPER(SUBSTR(CL_CCN_RESS.Name,1,4)) = UPPER(RESS.PrUserText2)
    AND CL_CCN_RESS.lookup_type = 'MA_LKP_LIGNE_DDSIG'
    AND CL_CCN_RESS.IS_ACTIVE = 1
-- Ajout des codes de categorie des tâches
left join ADM_BO_PILOTAGE.PIL_CODES_CATEGORIE codcat_lvl1
    on substr(upper(task.PRCATEGORY),1,3)= codcat_lvl1.CODE_CATEGORIE
    and codcat_lvl1.niveau = 1 
left join ADM_BO_PILOTAGE.PIL_CODES_CATEGORIE codcat_lvl2
    on substr(upper(task.PRCATEGORY),5,3)= codcat_lvl2.CODE_CATEGORIE
    and codcat_lvl2.niveau = 2 
left join ADM_BO_PILOTAGE.PIL_CODES_CATEGORIE codcat_lvl3
    on substr(upper(task.PRCATEGORY),9,3)= codcat_lvl3.CODE_CATEGORIE
    and codcat_lvl3.niveau = 3 
WHERE 
    1=1
    AND UPPER(RESS.PrExternalID) not like 'MA_ACC%' 
    -- On ne prend que les Assignement >= Current-year-1
    AND (
        TO_NUMBER(TO_CHAR(NVL(AF_ASSI.SLICE_DATE, TASK.prstart),'SYYYY'))>=  
        DECODE(0, 0, 2026-1,2026-2)
        )
ORDER BY INV.CODE DESC,TASK_NAME,ress.prname