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 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210
|
CREATE OR REPLACE PACKAGE PKG_TB IS
-- Record --
TYPE T_REC_ECO IS RECORD (
libelleCTC Varchar2(20),
resteCoupure INTEGER,
stock INTEGER,
traficTrie INTEGER,
tauxReste number);
-- Table de records --
TYPE TAB_T_REC_ECO IS TABLE OF T_REC_ECO index by binary_integer ;
PROCEDURE GetResteCoupureStock ( PCI_CODE_CATEGORIE IN CHAR,
PNI_CODE_NIVEAU_TRI IN NUMBER,
PTO_RES OUT TAB_T_REC_ECO);
END PKG_TB;
/
CREATE OR REPLACE PACKAGE BODY PKG_TB AS
PROCEDURE GetResteCoupureStock ( PCI_CODE_CATEGORIE IN CHAR,
PNI_CODE_NIVEAU_TRI IN NUMBER,
PTO_RES OUT TAB_T_REC_ECO) IS
codeCTC INTEGER;
resteCoupure INTEGER;
stock INTEGER;
traficTrie INTEGER;
fileHandler UTL_FILE.FILE_TYPE;
indice INTEGER;
codeZT INTEGER;
traficTraite INTEGER;
traficDefinitif INTEGER;
-- Record --
TYPE T_REC_ECO IS RECORD (
libelleCTC Varchar2(20),
resteCoupure INTEGER,
stock INTEGER,
traficTrie INTEGER,
tauxReste number);
-- Table de records --
TYPE TAB_T_REC_ECO IS TABLE OF T_REC_ECO index by binary_integer ;
t_rec TAB_T_REC_ECO ; -- variable tableau d'enregistrements
BEGIN
-- Ouverture du fichier de log
fileHandler := UTL_FILE.FOPEN('TRC_DIR', 'eco_depart.log', 'w');
stock := 0 ;
resteCoupure := 0 ;
traficTrie := 0;
indice := 1;
FOR r IN (
-- recuperation de la liste des etablissement
select distinct(ctc.code_ctc ),
ctc.libelle_ctc,
ctc.id_ctc
from ctc, stock_etablissement se, stock_macro_pt smp
where ctc.id_ctc = se.id_ctc
and (smp.stock != 0 or smp.reste_en_coupure != 0)
and smp.id_stock_etablissemen = se.id_stock_etablissemen
and se.journee_postale = '15/11/2005'
and ctc.date_suppression is null
and se.date_suppression is null
)
LOOP
UTL_FILE.PUT_LINE(fileHandler, 'code : ' || r.libelle_ctc);
t_rec(indice).libelleCTC := r.libelle_ctc;
for s in (
select smp.reste_en_coupure,
smp.stock,
mp.id_macro_pt,
zt.code_zt
from ctc, stock_etablissement se, stock_macro_pt smp, macro_pt mp, categorie cat, niveau_tri nt, zt
where ctc.id_ctc = se.id_ctc
and (smp.stock != 0 or smp.reste_en_coupure != 0)
and smp.id_stock_etablissemen = se.id_stock_etablissemen
and se.journee_postale = '15/11/2005'
and mp.id_macro_pt = smp.id_macro_pt
and mp.id_categorie = cat.id_categorie
and mp.id_niveau_tri = nt.id_niveau_tri
and cat.code_categorie = PCI_CODE_CATEGORIE
and nt.code_nt = PNI_CODE_NIVEAU_TRI
and ctc.date_suppression is null
and se.date_suppression is null
and mp.date_suppression is null
and cat.date_suppression is null
and nt.date_suppression is null
and ctc.id_ctc = r.id_ctc
and zt.id_zt = mp.id_zt
)
LOOP
resteCoupure := resteCoupure + s.reste_en_coupure;
stock := stock + s.stock;
UTL_FILE.PUT_LINE(fileHandler, 'macrpt : ' || s.id_macro_pt);
UTL_FILE.PUT_LINE(fileHandler, 'code zt : ' || s.code_zt);
-- recuperation trafic trié
if (s.code_zt = '2') then
select sum(t.trafic_traite) into traficTraite
from macro_pt mp, categorie cat, niveau_tri nt, chaine ch, trafic t, zt
where cat.date_suppression is null
and nt.date_suppression is null
and zt.date_suppression is null
and t.date_suppression is null
and mp.id_macro_pt = s.id_macro_pt
and mp.id_categorie = cat.id_categorie
and mp.id_niveau_tri = nt.id_niveau_tri
and cat.code_categorie = PCI_CODE_CATEGORIE
and nt.code_nt = PNI_CODE_NIVEAU_TRI
and ch.id_macro_pt = s.id_macro_pt
and t.id_chaine = ch.id_chaine
and t.journee_postale = '15/11/2005'
and zt.id_zt = mp.id_zt
and zt.code_zt = 2;
if traficTraite > 0 then
traficTrie := traficTrie + traficTraite;
end if;
UTL_FILE.PUT_LINE(fileHandler, 'traficTraite : ' || traficTraite);
end if;
if (s.code_zt = 1) then
select sum(trafic_definitif) into traficDefinitif
from ctc, stock_etablissement se, stock_macro_pt smp, macro_pt mp, categorie cat, niveau_tri nt, chaine ch, trafic t, zt
where cat.date_suppression is null
and nt.date_suppression is null
and zt.date_suppression is null
and t.date_suppression is null
and mp.id_macro_pt = s.id_macro_pt
and mp.id_categorie = cat.id_categorie
and mp.id_niveau_tri = nt.id_niveau_tri
and cat.code_categorie = PCI_CODE_CATEGORIE
and nt.code_nt = PNI_CODE_NIVEAU_TRI
and ch.id_macro_pt = s.id_macro_pt
and t.id_chaine = ch.id_chaine
and t.journee_postale = '15/11/2005'
and zt.id_zt = mp.id_zt
and zt.code_zt = 1;
UTL_FILE.PUT_LINE(fileHandler, 'traficDefinitif : ' || traficDefinitif);
if traficDefinitif > 0 then
traficTrie := traficTrie + traficDefinitif;
end if;
end if;
END LOOP;
t_rec(indice).resteCoupure := resteCoupure;
t_rec(indice).stock := stock;
t_rec(indice).traficTrie := traficTrie;
if traficTrie != 0 then
t_rec(indice).tauxReste := stock / traficTrie;
end if;
UTL_FILE.PUT_LINE(fileHandler, 'reste : ' || resteCoupure);
UTL_FILE.PUT_LINE(fileHandler, 'stock : ' || stock);
UTL_FILE.PUT_LINE(fileHandler, 'traficTrie : ' || traficTrie);
UTL_FILE.PUT_LINE(fileHandler, 'tauxReste : ' || t_rec(indice).tauxReste);
indice := indice + 1;
END LOOP;
-- PTO_RES :=t_rec;
-- Fermeture du fichier de log
UTL_FILE.FCLOSE(fileHandler);
EXCEPTION
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR(-20000, 'Erreur: répertoire / nom de fichier invalide');
WHEN OTHERS THEN
RAISE;
END GetResteCoupureStock;
END PKG_TB;
/ |
Partager