INFO: Màj des paramètres financiers des ressources
SELECT
RSC.ID RSC_ID,
RSC.UNIQUE_NAME RSC_CODE,
RSC.LAST_NAME, RSC.FIRST_NAME,
CSU.USER_NAME RSC_LOGIN,
RSC.EMAIL, PRSC.PRCATEGORY RSC_CATEGORIE,
(SELECT CLK.LOOKUP_CODE FROM CMN_LOOKUPS CLK
WHERE CLK.LOOKUP_TYPE = 'SRM_RESOURCE_TYPE'
AND RSC.PERSON_TYPE = CLK.ID) TYPE_EMPLOI,
NVL((SELECT CCN.NAME FROM CMN_LOOKUPS CLK, CMN_CAPTIONS_NLS CCN
WHERE CLK.ID = CCN.PK_ID AND CLK.LOOKUP_TYPE = 'MA_LOOKUP_TYP_RES'
AND CLK.LOOKUP_CODE = ORSC.MA_PRJ_TYP_RES
AND CCN.TABLE_NAME = 'CMN_LOOKUPS' AND CCN.LANGUAGE_CODE = 'fr'),
'???') RSC_TYPE_RESS,
NVL((SELECT CCN.NAME FROM CMN_LOOKUPS CLK, CMN_CAPTIONS_NLS CCN
WHERE CLK.ID = CCN.PK_ID AND CLK.LOOKUP_TYPE = 'TYPE_PRESTATION'
AND CLK.LOOKUP_CODE = ORSC.MA_TYP_PREST
AND CCN.TABLE_NAME = 'CMN_LOOKUPS' AND CCN.LANGUAGE_CODE = 'fr'),
'???') RSC_TYPE_PRESTA,
CASE ORSC.MA_PRJ_TYP_RES
WHEN '1' THEN -- Salarié
CASE
--WHEN PRSC.PRCATEGORY = 'MOA' AND ORSC.MA_TYP_PREST = 'non_app' THEN '01_Inter' -- 01 - MOA Interne (J.H)
WHEN PRSC.PRCATEGORY = 'MOA-SAM' AND ORSC.MA_TYP_PREST = 'non_app' THEN '01_Inter' -- 01 - MOA Interne (J.H)
WHEN PRSC.PRCATEGORY = 'MOA-MSP' AND ORSC.MA_TYP_PREST = 'non_app' THEN '01_Inter' -- 01 - MOA Interne (J.H)
WHEN PRSC.PRCATEGORY ='MOA-MFE' AND ORSC.MA_TYP_PREST = 'non_app' THEN '01_Inter' -- 01 - MOA Interne (J.H)
--WHEN PRSC.PRCATEGORY = 'MOE-DDSI' AND ORSC.MA_TYP_PREST = 'non_app' THEN '07_DDSIG' -- 07 - MOE DDSI Prestation mutualisée J.H
WHEN PRSC.PRCATEGORY = 'MOE-SAM' AND ORSC.MA_TYP_PREST = 'non_app' THEN '07_DDSIG' -- 07 - MOE DDSI Prestation mutualisée J.H
WHEN PRSC.PRCATEGORY = 'MOE-MSP' AND ORSC.MA_TYP_PREST = 'non_app' THEN '18_MSP_I' -- 18 - MOE MSP interne (j.H)
WHEN PRSC.PRCATEGORY = 'MOE-MFE' AND ORSC.MA_TYP_PREST = 'non_app' THEN '16_MFE_P' -- 16 - MOE MFE et Presta mutual (j.H)
ELSE '???'
END
WHEN '4' THEN -- Centre de Service
CASE
--WHEN PRSC.PRCATEGORY = 'MOE-DDSI' AND ORSC.MA_TYP_PREST = 'non_app' THEN '07_DDSIG' -- 07 - MOE DDSI Prestation mutualisée J.H
WHEN PRSC.PRCATEGORY = 'MOE-SAM' AND ORSC.MA_TYP_PREST = 'non_app' THEN '07_DDSIG' -- 07 - MOE DDSI Prestation mutualisée J.H
WHEN PRSC.PRCATEGORY = 'MOE-MSP' AND ORSC.MA_TYP_PREST = 'non_app' THEN '19_MSP_P' -- 19 - MOE MSP Presta mutual (j.H)
WHEN PRSC.PRCATEGORY = 'MOE-MFE' AND ORSC.MA_TYP_PREST = 'non_app' THEN '16_MFE_P' -- 16 - MOE MFE et Presta mutual (j.H)
ELSE '???'
END
WHEN '2' THEN -- Prestation mutualisée ??
CASE
--WHEN PRSC.PRCATEGORY = 'MOA' AND ORSC.MA_TYP_PREST = '2a' THEN '02_Prest' -- 02 - MOA Prestation mutualisée AT (J.H)
WHEN PRSC.PRCATEGORY = 'MOA-SAM' AND ORSC.MA_TYP_PREST = '2a' THEN '02_Prest' -- 02 - MOA Prestation mutualisée AT (J.H)
WHEN PRSC.PRCATEGORY = 'MOA-MSP' AND ORSC.MA_TYP_PREST = '2a' THEN '02_Prest' -- 02 - MOA Prestation mutualisée AT (J.H)
WHEN PRSC.PRCATEGORY ='MOA-MFE' AND ORSC.MA_TYP_PREST = '2a' THEN '02_Prest' -- 02 - MOA Prestation mutualisée AT (J.H)
--WHEN PRSC.PRCATEGORY = 'MOE-DDSI' AND ORSC.MA_TYP_PREST = '2a' THEN '07_DDSIG' -- 07 - MOE DDSI Prestation mutualisée J.H
WHEN PRSC.PRCATEGORY = 'MOE-SAM' AND ORSC.MA_TYP_PREST = '2a' THEN '07_DDSIG' -- 07 - MOE DDSI Prestation mutualisée J.H
WHEN PRSC.PRCATEGORY = 'MOE-MSP' AND ORSC.MA_TYP_PREST = '2a' THEN '19_MSP_P' -- 19 - MOE MSP Presta mutual (j.H)
WHEN PRSC.PRCATEGORY = 'MOE-MFE' AND ORSC.MA_TYP_PREST = '2a' THEN '16_MFE_P' -- 16 - MOE MFE et Presta mutual (j.H)
ELSE '???'
END
END CLASS_TRANS_ID,
CASE ORSC.MA_PRJ_TYP_RES
WHEN '1' THEN -- Salarié
CASE
--WHEN PRSC.PRCATEGORY = 'MOA' AND ORSC.MA_TYP_PREST = 'non_app' THEN 'MOA I' -- MOA interne
WHEN PRSC.PRCATEGORY = 'MOA-SAM' AND ORSC.MA_TYP_PREST = 'non_app' THEN 'MOA I' -- MOA interne
WHEN PRSC.PRCATEGORY = 'MOA-MSP' AND ORSC.MA_TYP_PREST = 'non_app' THEN 'MOASP I' -- MOA MSP interne
WHEN PRSC.PRCATEGORY ='MOA-MFE' AND ORSC.MA_TYP_PREST = 'non_app' THEN 'MOAFE I' -- MOA MFE interne
--WHEN PRSC.PRCATEGORY = 'MOE-DDSI' AND ORSC.MA_TYP_PREST = 'non_app' THEN 'MOEDSI I' -- MOE DDSI interne
WHEN PRSC.PRCATEGORY = 'MOE-SAM' AND ORSC.MA_TYP_PREST = 'non_app' THEN 'MOEDSI I' -- MOE DDSI interne
WHEN PRSC.PRCATEGORY = 'MOE-MSP' AND ORSC.MA_TYP_PREST = 'non_app' THEN 'MOESP I' -- MOE MSP interne
WHEN PRSC.PRCATEGORY = 'MOE-MFE' AND ORSC.MA_TYP_PREST = 'non_app' THEN 'MOEFE I' -- MOE MFE interne
ELSE '???'
END
WHEN '4' THEN -- Centre de Service
CASE
--WHEN PRSC.PRCATEGORY = 'MOE-DDSI' AND ORSC.MA_TYP_PREST = 'non_app' THEN 'CDSDSI' -- MOE DDSI CDS
WHEN PRSC.PRCATEGORY = 'MOE-SAM' AND ORSC.MA_TYP_PREST = 'non_app' THEN 'CDSDSI' -- MOE DDSI CDS
WHEN PRSC.PRCATEGORY = 'MOE-MSP' AND ORSC.MA_TYP_PREST = 'non_app' THEN 'CDSSP' -- MOE MSP CDS
WHEN PRSC.PRCATEGORY = 'MOE-MFE' AND ORSC.MA_TYP_PREST = 'non_app' THEN 'CDSFE' -- MOE MFE CDS
ELSE '???'
END
WHEN '2' THEN -- Prestation
CASE
--WHEN PRSC.PRCATEGORY = 'MOA' AND ORSC.MA_TYP_PREST = '2a' THEN 'MOA E' -- MOA externe
WHEN PRSC.PRCATEGORY = 'MOA-SAM' AND ORSC.MA_TYP_PREST = '2a' THEN 'MOA E' -- MOA externe
WHEN PRSC.PRCATEGORY = 'MOA-MSP' AND ORSC.MA_TYP_PREST = '2a' THEN 'MOASP E' -- MOA MSP externe
WHEN PRSC.PRCATEGORY ='MOA-MFE' AND ORSC.MA_TYP_PREST = '2a' THEN 'MOAFE E' -- MOA MFE externe
--WHEN PRSC.PRCATEGORY = 'MOE-DDSI' AND ORSC.MA_TYP_PREST = '2a' THEN 'MOEDSI E' -- MOE DDSI externe
WHEN PRSC.PRCATEGORY = 'MOE-SAM' AND ORSC.MA_TYP_PREST = '2a' THEN 'MOEDSI E' -- MOE DDSI externe
WHEN PRSC.PRCATEGORY = 'MOE-MSP' AND ORSC.MA_TYP_PREST = '2a' THEN 'MOESP E' -- MOE MSP externe
WHEN PRSC.PRCATEGORY = 'MOE-MFE' AND ORSC.MA_TYP_PREST = '2a' THEN 'MOEFE E' -- MOE MFE externe
ELSE '???'
END
END CLASS_RESS_ID
FROM SRM_RESOURCES RSC, PRJ_RESOURCES PRSC,
ODF_CA_RESOURCE ORSC, PAC_MNT_RESOURCES PMR,
CMN_SEC_USERS CSU
WHERE RSC.ID = PRSC.PRID AND RSC.ID = ORSC.ID
AND RSC.ID = PMR.ID (+) AND RSC.USER_ID = CSU.ID
AND RSC.IS_ACTIVE = 1 AND RSC.RESOURCE_TYPE = 0 -- Ressource MO active
AND (PMR.ID IS NULL OR PMR.ACTIVE = 0)
AND (
(ORSC.MA_PRJ_TYP_RES = '1' AND ORSC.MA_TYP_PREST = 'non_app'
--AND PRSC.PRCATEGORY IN ('MOA', 'MOA-MSP', 'MOA-MFE', 'MOE-DDSI', 'MOE-MSP', 'MOE-MFE')) OR
AND PRSC.PRCATEGORY IN ('MOA-SAM', 'MOA-MSP', 'MOA-MFE', 'MOE-SAM', 'MOE-MSP', 'MOE-MFE')) OR
(ORSC.MA_PRJ_TYP_RES = '4' AND ORSC.MA_TYP_PREST = 'non_app'
--AND PRSC.PRCATEGORY IN ('MOE-DDSI', 'MOE-MSP', 'MOE-MFE')) OR
AND PRSC.PRCATEGORY IN ('MOE-SAM', 'MOE-MSP', 'MOE-MFE')) OR
(ORSC.MA_PRJ_TYP_RES = '2' AND ORSC.MA_TYP_PREST = '2a'
--AND PRSC.PRCATEGORY IN ('MOA', 'MOA-MSP', 'MOA-MFE', 'MOE-DDSI', 'MOE-MSP', 'MOE-MFE'))
AND PRSC.PRCATEGORY IN ('MOA-SAM', 'MOA-MSP', 'MOA-MFE', 'MOE-SAM', 'MOE-MSP', 'MOE-MFE'))
)
AND (RSC.UNIQUE_NAME = '${Ress_ID}' OR '${Ress_ID}' = 'TOUS')
ORDER BY RSC.LAST_NAME
ERROR: Echec de connexion à la base - Exception = ${_Exception.getMessage()}INFO: Aucune ressource à mettre à jourINFO: Il y a ${NbRscToUpdate} ressources à mettre à jourINFO: Traitement de la ressource ${RSC.LAST_NAME}, ${RSC.FIRST_NAME} (${RSC.RSC_CODE})INFO: Catégorie=${RSC.RSC_CATEGORIE}, TypeRess=${RSC.RSC_TYPE_RESS}, TypePresta=${RSC.RSC_TYPE_PRESTA}INFO: Class Trans=${RSC.CLASS_TRANS_ID}, Class Ress=${RSC.CLASS_RESS_ID}ERROR: Pas de matching pour cette ressourceINFO: XmlIn=INFO: Màj de la ressource effectuéeERROR: XmlOut=/!\ Pas de màj demandéeINFO: StatistiquesINFO: Nbr ressources à mettre à jour = ${NbRscToUpdate}INFO: Nbr màj OK / KO : ${NbRscUpdateOK} / ${NbRscUpdateKO}INFO: Nbr pb matching : ${NbRscMatchingKO}