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 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97
| Procedure P_GET_DONNEE2(
in_DONNEE_ID in DONNEE.DONNEE_ID%type,
in_EXE_CODE in ventilation.exe_code%type,
out_DONNEE_LIBELLE OUT SYS_REFCURSOR) is
Str_chaine varchar2(10000);
BEGIN
Str_chaine :=
'WITH param1 AS (SELECT :in_DONNEE_ID AS in_DONNEE_ID , :in_EXE_CODE AS in_EXE_CODE FROM DUAL)
SELECT *
FROM VALEUR_DONNEE vdon
JOIN
(SELECT vd1.VALEUR_DIMENSION_ID AS VALEUR_DIMENSION_ID_1,
vd1.VAL_DIM_VALEUR AS VAL_DIM_VALEUR_1,
vd1.VAL_DIM_CODE AS VAL_DIM_CODE_1,
vdon.VALEUR_DONNEE_ID
FROM VALEUR_DONNEE vdon
JOIN VENTILATION ven
ON ven.VEN_ID = vdon.VEN_ID
JOIN VAL_DON_VAL_DIM vdvdtem
ON vdvdtem.VALEUR_DONNEE_ID = vdon.VALEUR_DONNEE_ID
JOIN VALEUR_DIMENSION vdtem
ON vdtem.VALEUR_DIMENSION_ID = vdvdtem.VALEUR_DIMENSION_ID
AND vdtem.VALEUR_DIMENSION_ID = 320
JOIN VAL_DON_VAL_DIM vdvdorg
ON vdvdorg.VALEUR_DONNEE_ID = vdon.VALEUR_DONNEE_ID
JOIN VALEUR_DIMENSION vdorg
ON vdorg.VALEUR_DIMENSION_ID = vdvdorg.VALEUR_DIMENSION_ID
AND vdorg.VALEUR_DIMENSION_ID =7604
JOIN VAL_DON_VAL_DIM vdvd1
ON vdvd1.VALEUR_DONNEE_ID = vdon.VALEUR_DONNEE_ID
JOIN VALEUR_DIMENSION vd1
ON vd1.VALEUR_DIMENSION_ID = vdvd1.VALEUR_DIMENSION_ID
JOIN DON_DIM ddi1
ON ddi1.DIMENSION_ID = vd1.DIMENSION_ID
AND ddi1.DIMENSION_ID =1014
AND ddi1.VEN_ID = ven.VEN_ID
AND ddi1.DON_DIM_IS_ACTIVE = 1
AND ddi1.DON_DIM_ORDRE = 1
JOIN FAVORI_LISTE fli1
ON fli1.DDI_ID = ddi1.DDI_ID
AND fli1.VALEUR_DIMENSION_ID = vd1.VALEUR_DIMENSION_ID
JOIN FAVORI fav1
ON fav1.FLI_ID = fli1.FLI_ID
AND fav1.VALEUR_GROUPE_ID =7604
Where ven.DONNEE_ID =(select in_DONNEE_ID from param1)
AND ven.EXE_CODE =(select in_EXE_CODE from param1)
)val ON val.VALEUR_DONNEE_ID = vdon.VALEUR_DONNEE_ID
RIGHT JOIN
(SELECT vd1.VALEUR_DIMENSION_ID AS VALEUR_DIMENSION_ID_1,
axe1.AXE_LIBELLE AS AXE_LIBELLE_1,
dim1.DIM_LIBELLE AS DIM_LIBELLE_1,
vd1.VAL_DIM_CODE AS VAL_DIM_CODE_1,
vd1.VAL_DIM_VALEUR AS VAL_DIM_VALEUR_1
FROM
(SELECT vd.VALEUR_DIMENSION_ID,
vd.DIMENSION_ID,
vd.VAL_DIM_VALEUR,
vd.VAL_DIM_CODE
FROM VALEUR_DIMENSION vd
JOIN DON_DIM ddi
ON ddi.DIMENSION_ID = vd.DIMENSION_ID
AND ddi.DIMENSION_ID =1014
And ddi.VEN_ID=(SELECT v.VEN_ID FROM VENTILATION v WHERE v.DONNEE_ID =(select in_DONNEE_ID from param1) AND v.EXE_CODE =(select in_EXE_CODE from param1) )
AND ddi.DON_DIM_ORDRE =1
Join FAVORI_LISTE fli
ON fli.DDI_ID = ddi.DDI_ID
AND fli.VALEUR_DIMENSION_ID = vd.VALEUR_DIMENSION_ID
JOIN FAVORI fav
ON fav.FLI_ID = fli.FLI_ID
AND fav.VALEUR_GROUPE_ID =7604
)vd1
JOIN DIMENSION dim1
ON dim1.DIMENSION_ID = vd1.DIMENSION_ID
JOIN AXE axe1
ON axe1.AXE_ID = dim1.AXE_ID
) vdim ON vdim.VALEUR_DIMENSION_ID_1 = val.VALEUR_DIMENSION_ID_1
JOIN DONNEE don ON don.DONNEE_ID =(select in_DONNEE_ID from param1)
JOIN VENTILATION ven ON ven.DONNEE_ID = don.DONNEE_ID AND ven.EXE_CODE =(select in_EXE_CODE from param1)
JOIN VALEUR_DIMENSION vdtem ON vdtem.VALEUR_DIMENSION_ID =320
JOIN DIMENSION dimtem ON dimtem.DIMENSION_ID = vdtem.DIMENSION_ID
JOIN AXE axetem ON axetem.AXE_ID = dimtem.AXE_ID
JOIN VALEUR_DIMENSION vdorg ON vdorg.VALEUR_DIMENSION_ID =7604
JOIN DIMENSION dimorg ON dimorg.DIMENSION_ID = vdorg.DIMENSION_ID
JOIN AXE axeorg ON axeorg.AXE_ID = dimorg.AXE_ID
WHERE 1 =1
AND ROWNUM <= 10001';
OPEN out_DONNEE_LIBELLE FOR str_chaine using in_DONNEE_ID,in_EXE_CODE;
END; |
Partager