1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
| WITH FLAG_ANO AS (
SELECT
CASE WHEN TDER_RM_BILLABLE_AREA.DP_ID <> DM_PAT_EDG.DP_ID THEN 'ANOMALIE' ELSE '' END AS FLAG_ANO_EDG,
CASE WHEN EM.SIRET <> SITE_SIRET.SIRET THEN 'ANOMALIE' ELSE '' END AS FLAG_ANO_SIRET,
EM.CDR AS EM_CDR
FROM
DM_PAT_EDG EDG_BILLAREA RIGHT OUTER JOIN (
WITH fl_has_edg AS (
-- dit s'il existe au moins une piece affectee a un EDG sur l'etage
SELECT bl_id, fl_id, CASE WHEN nb_edg > 0 THEN 1 ELSE 0 END has_edg
FROM (
SELECT aff.bl_id, aff.fl_id , SUM(NVL2(aff.dp_id,1,0)) AS nb_edg
FROM rm_billable_area aff
GROUP BY aff.bl_id, aff.fl_id
)
)
SELECT affect.bl_id, affect.fl_id, affect.rm_id, affect.rm_name, affect.area,
affect.rm_cat, affect.rm_std, affect.dv_id, affect.dp_id, affect.prorate, affect.avail_stat_id,
affect.date_last_transfer, affect.user_last_transfer, affect.area_chargable,
date_last_calcul, affect.area_parking, affect.area_comn, affect.volumes, rm.vtc
FROM rm_billable_area affect
INNER JOIN property ON (property.pr_id = affect.bl_id)
INNER JOIN fl ON (fl.bl_id = property.pr_id AND fl.fl_id = affect.fl_id)
INNER JOIN rm ON (rm.bl_id = fl.bl_id AND rm.fl_id = fl.fl_id AND rm.rm_id = affect.rm_id)
-- On prend que les affections sur les batiments/etages actifs
WHERE (fl.date_valid_to IS NULL OR fl.date_valid_to >SYSDATE)
AND (property.date_valid_to IS NULL OR property.date_valid_to >SYSDATE)
UNION ALL
-- repartition des murs et cloisons sur batiments/etage actifs
SELECT fl_has_edg.bl_id, fl_has_edg.fl_id,
NULL AS rm_id, NULL AS rm_name, fl.area_remain AS area, NULL AS rm_cat,
'WALL' AS rm_std, NULL AS dv_id, NULL AS dp_id,
CASE WHEN fl_has_edg.has_edg = 1 THEN 'FLOOR' ELSE 'BUILDING' END AS prorate,
NULL AS avail_stat_id, NULL AS date_last_transfer, NULL AS user_last_transfer,
0 AS area_chargable, NULL AS date_last_calcul, 0 AS area_parking, 0 AS area_comn, '-1' AS volumes, -1 AS vtc
FROM fl_has_edg
INNER JOIN property ON (property.pr_id = fl_has_edg.bl_id)
INNER JOIN fl ON (fl.bl_id = property.pr_id AND fl.fl_id = fl_has_edg.fl_id)
WHERE (fl.date_valid_to IS NULL OR fl.date_valid_to >SYSDATE)
AND (property.date_valid_to IS NULL OR property.date_valid_to >SYSDATE)
AND property.has_dwgs = 1
) TDER_RM_BILLABLE_AREA ON (EDG_BILLAREA.DV_ID=TDER_RM_BILLABLE_AREA.DV_ID AND EDG_BILLAREA.DP_ID=TDER_RM_BILLABLE_AREA.DP_ID)
INNER JOIN DM_PAT_BL ON (TDER_RM_BILLABLE_AREA.BL_ID=DM_PAT_BL.BL_ID)
INNER JOIN EM ON (DM_PAT_BL.BL_ID=EM.BL_ID)
LEFT JOIN RC ON (EM.CDR=RC.RC_ID)
LEFT JOIN DM_PAT_EDG ON (RC.DV_ID=DM_PAT_EDG.DV_ID and RC.DP_ID = DM_PAT_EDG.DP_ID)
)
SELECT EMP_CDR,
FLAG_ANO_EDG,
FLAG_ANO_SIRET
FROM FLAG_ANO; |
