Notes

SELECT
PS_PERS_NID.NATIONAL_ID,
PS_RF_EMPLOYEES_C.RF_ESTABID_LBL,
PS_RF_EMPLOYEES_C.ESTABID,
PS_RF_EMPLOYEES_C.ESTABID,
PS_RF_EMPLOYEES_C.RF_LIEU_TRAVAIL,
PS_PERS_NID.SSN_KEY_FRA,
PS_ADDRESSES.ADDRESS1,
PS_ADDRESSES.ADDRESS2,
PS_ADDRESSES.CITY,
PS_ADDRESSES.COUNTY,
PS_ADDRESSES.POSTAL,
PS_PERSONAL_DATA.BIRTHDATE,
PS_RF_EMPLOYEES_C.EMPLID,
PS_RF_EMPLOYEES_C.RF_SERVICE_LBL,
PS_RF_EMPLOYEES_C.RF_CLE_ANA3_2,
BO_ARSF_MV_DIRC.DESCR50,
SUBSTR(PS_RF_EMPLOYEES_C.RF_CLE_ANA3_2,1,2),
PS_RF_ENGAGEMENT.RF_NUMERO_OBJET,
PS_RF_EMPLOYEES_C.URSSAF_CD_FRA,
PS_RF_EMPLOYEES_C.RF_CHOMAGE_LBL,
PS_RF_EMPLOYEES_C.RF_TX_CHOMAGE,
PS_RF_EMPLOYEES_C.GXP_ASSURANCE,
PS_RF_EMPLOYEES_C.RF_CD_IMPOT_LBL,
PS_RF_EMPLOYEES_C.RF_CD_IMPOT,
PS_RF_EMPLOYEES_C.RF_CD_STATUT_LBL,
PS_RF_EMPLOYEES_C.RF_CD_STATUT,
decode(PS_RF_EMPLOYEES_C.RFI_CONTRAT_LIQUID_2,'Y','Oui','Non'),
PS_RF_EMPLOYEES_C.RF_SERVICE_LBL,
PS_RF_ENGAGEMENT.RF_MONTANT_CESSION,
decode(PS_RF_ENGAGEMENT.RF_ACCORD_CESSION,'Y','O','N')
,
PS_RF_EMPLOYEES_C.RF_CORRECT_CONTRAT,
PS_RF_EMPLOYEES_C.RF_REGIME_SS_LBL,
PS_RF_EMPLOYEES_C.RF_REGIME_SS,
PS_RF_EMPLOYEES_C.RF_CATEGORIE,
PS_RF_EMPLOYEES_C.RF_CATEGORIE_LBL,
PS_PERSONAL_DATA.NAME_PREFIX,
PS_PERSONAL_DATA.LAST_NAME || ', ' || PS_PERSONAL_DATA.FIRST_NAME,
PS_RF_EMPLOYEES_C.RF_SEC_CACHET,
to_char(PS_RF_EMPLOYEES_C.RF_CONTRACT_NUM),
PS_RF_EMPLOYEES_C.BEGIN_DT,
PS_RF_EMPLOYEES_C.END_DATE,
PS_RF_EMPLOYEES_C.JOBCODE,
PS_RF_EMPLOYEES_C.RF_JOBCODE_LBL,
decode(PS_RF_EMPLOYEES_C.RF_SUP_CACHET,'Y','O','N'),
decode(PS_RF_EMPLOYEES_C.RF_CONTRAT_PARTIC,'Y','O','N'),
PS_RF_EMPLOYEES_C.RF_CLE_ANA4,
PS_RF_EMPLOYEES_C.RF_PRODUIT_LBL,
PS_RF_EMPLOYEES_C.RF_DEBUT_PER_PAIE,
PS_RF_EMPLOYEES_C.RF_FIN_PER_PAIE,
PS_RF_EMPLOYEES_C.RF_TOTAL_CACHET,
PS_RF_EMPLOYEES_C.RF_TOTAL_PIGE,
PS_RF_EMPLOYEES_C.RF_TOTAL_HONORAIRE,
PS_RF_EMPLOYEES_C.RF_NB_JOURS_2,
PS_RF_EMPLOYEES_C.RF_NB_HEURES_2,
PS_RF_EMPLOYEES_C.RF_NB_SERVICES_2,
decode(PS_RF_EMPLOYEES_C.RF_PAYER,'Y','O','N'),
decode(PS_RF_EMPLOYEES_C.RF_PAYE,'Y','O','N'),
PS_RF_EMPLOYEES_C.RF_BULLETIN_NUM,
decode(PS_RF_EMPLOYEES_C.RF_NEUTRALISER,'Y','O','N'),
decode(PS_RF_EMPLOYEES_C.RF_ANNULER,'Y','O','N'),
decode(PS_RF_EMPLOYEES_C.RF_CONTRAT_GRILLE,'Y','O','N'),
PS_RF_EMPLOYEES_C.RF_CACHET_ACTIVITE,
PS_RF_EMPLOYEES_C.RF_BUDG_ACTIVITE,
PS_RF_EMPLOYEES_C.RF_ENTITE,
PS_RF_EMPLOYEES_C.RF_ENTITE_DESCR,
PS_RF_EMPLOYEES_C.RF_NODE_LVL5,
PS_RF_EMPLOYEES_C.RF_NODE_LVL5_DESCR5,
PS_RF_EMPLOYEES_C.RF_NODE_LVL4,
PS_RF_EMPLOYEES_C.RF_NODE_LVL4_DESCR4,
PS_RF_EMPLOYEES_C.RF_EOTP,
PS_RF_EMPLOYEES_C.RF_OPE_LD,
PS_RF_EMPLOYEES_C.RF_NUMERO_OBJET,
PS_RF_EMPLOYEES_C.RF_PHASE,
PS_RF_EMPLOYEES_C.RF_OPE_PROJET,
PS_RF_EMPLOYEES_C.RF_OI
FROM
PS_RF_ENGAGEMENT RIGHT OUTER JOIN PS_RF_EMPLOYEES_C ON (PS_RF_ENGAGEMENT.EMPLID=PS_RF_EMPLOYEES_C.EMPLID and PS_RF_ENGAGEMENT.RF_CONTRACT_NUM=PS_RF_EMPLOYEES_C.RF_CONTRACT_NUM)
INNER JOIN PS_PERSONAL_DATA ON (PS_RF_EMPLOYEES_C.EMPLID=PS_PERSONAL_DATA.EMPLID)
INNER JOIN PS_PERS_NID ON (PS_PERS_NID.EMPLID=PS_PERSONAL_DATA.EMPLID)
INNER JOIN PS_PERS_DATA_EFFDT ON (PS_PERSONAL_DATA.EMPLID=PS_PERS_DATA_EFFDT.EMPLID)
INNER JOIN PS_ADDRESSES ON (PS_ADDRESSES.EMPLID=PS_PERSONAL_DATA.EMPLID)
INNER JOIN BO_ARSF_MV BO_ARSF_MV_DIRC ON (BO_ARSF_MV_DIRC.DEPTID=substr(PS_RF_EMPLOYEES_C.RF_CLE_ANA3_2,1,2) and BO_ARSF_MV_DIRC.EFFDT=(Select max(a.effdt) from BO_ARSF_MV a where a.DEPTID=BO_ARSF_MV_DIRC.DEPTID and a.effdt<=sysdate))

WHERE
(
decode(PS_RF_EMPLOYEES_C.RF_PAYE,'Y','O','N') IN ( 'O' )
AND
PS_RF_EMPLOYEES_C.RF_BULLETIN_NUM >= '202005999999'
AND
( PS_ADDRESSES.EFFDT=(Select max(a.EFFDT) from PS_ADDRESSES a where a.EMPLID=PS_ADDRESSES.EMPLID and a.EFFDT<=sysdate ) )
AND
( PS_PERS_DATA_EFFDT.EFFDT=(Select max(a.EFFDT) from PS_PERS_DATA_EFFDT a where a.EMPLID=PS_PERS_DATA_EFFDT.EMPLID and a.EFFDT<=sysdate ) )
AND
to_char(PS_RF_EMPLOYEES_C.RF_CONTRACT_NUM) NOT IN ( '572251' )
)