1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
| DEFINE my_period = 202101
SELECT DISTINCT
&&my_period AS mois_réf
,chi.file_number AS dossier
,per.inss AS niss
,CASE WHEN fam.type LIKE 'D%' THEN per4.inss ELSE NULL END AS cohab
FROM children chi
INNER JOIN legal_situation leg ON leg.child_id = chi.actor_id
AND (&&my_period BETWEEN (leg.start_year * 100 + leg.start_month) AND NVL(leg.end_year * 100 + leg.end_month,999999))
AND leg.status = 'ACTIVE'
INNER JOIN childbeneficiarylinks cbl ON cbl.child_id = chi.actor_id
AND (&&my_period BETWEEN (cbl.start_year * 100 + cbl.start_month) AND NVL(cbl.end_year * 100 + cbl.end_month,999999))
INNER JOIN actors ac ON ac.actor_id = cbl.beneficiary_id
INNER JOIN persons per ON per.person_id = ac.person_id
INNER JOIN historical_family_situations his_fam ON his_fam.concerned_natural_person_id = ac.person_id
INNER JOIN family_situations fam ON his_fam.historical_fam_situation_id = fam.historical_fam_situation_id
AND TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(&&my_period, 'yyyymm'), -1)),'yyyymmdd')) BETWEEN TO_NUMBER(TO_CHAR(fam.start_date,'yyyymmdd')) AND NVL(TO_NUMBER(TO_CHAR(fam.end_date,'yyyymmdd')),99991231)
LEFT JOIN persons per4 ON per4.person_id = fam.second_natural_person_id
ORDER BY 3, 4
; |
Partager