|
-- 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
|
