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 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272
|
create or replace
PROCEDURE FACTURATION_POST_BG_FINAL(V_DATE_CALC VARCHAR2, V_DATE_DEB VARCHAR2, V_DATE_FIN VARCHAR2, V_ENTETE_FACT VARCHAR2) AS
V_NLig number:=0;
V_remise number:=0;
V_prixTTC number;
V_prixUHt number;
V_TauxTVA number;
V_prixUPublic number;
V_PTVA number;
V_PUHT number;
V_LITR number;
V_MR number;
V_totalHT number;
V_totalTva number;
V_quantite number;
V_delais NUMBER;
V_datedebuexo DATE;
V_dateFINexo DATE;
V_exo number;
V_timbre number:=0.400;
V_NUM_FACT VARCHAR2(20);
CURSOR SEL_OS IS
SELECT DISTINCT CLIENT.CODE_CLIENT,substr(bon.num_seri,7,6)as CODE_RUBR_BUDG,substr(bon.num_seri,1,6) as ancien_code,1
FROM BON,CLIENT
WHERE (BON.NUM_SERI IN (SELECT NUM_SERI FROM BONDET_BGV where BONDET_BGV.DATE_BORD BETWEEN V_DATE_DEB AND V_DATE_FIN))
AND (BON.NOM_PROCESSUS='CB')
AND (CLIENT.ancien_code in (2,4,9))
AND (BON.NUM_SERI NOT IN (SELECT BON_FACTURE_OS_PF.NUM_SERI FROM BON_FACTURE_OS_PF))
UNION
SELECT DISTINCT CLIENT.CODE_CLIENT,substr(bon.num_seri,7,6) as CODE_RUBR_BUDG,substr(bon.num_seri,1,6) as ancien_code,2
FROM BON,CLIENT
WHERE (BON.NUM_SERI IN (SELECT NUM_SERI FROM BONDET_BGV where BONDET_BGV.DATE_BORD BETWEEN V_DATE_DEB AND V_DATE_FIN))
AND (BON.NOM_PROCESSUS='CB')
AND (CLIENT.ANCIEN_CODE =substr(bon.num_seri,1,6))
AND (CLIENT.ancien_code not in (2,4,9))
AND (BON.CODE_TYPE_BON IN (SELECT TYPE_BON.CODE_TYPE_BON FROM TYPE_BON
WHERE TYPE_BON.CODE_NATU_CONS IN(SELECT NATURE_CONSOMMATION.CODE_NATU_CONS FROM NATURE_CONSOMMATION
WHERE NATURE_CONSOMMATION.TYPE_FACT='POST')))
AND (BON.NUM_SERI NOT IN (SELECT BON_FACTURE_OS_PF.NUM_SERI FROM BON_FACTURE_OS_PF))
ORDER BY ANCIEN_CODE,CODE_RUBR_BUDG,1,2;
ENR_OS SEL_OS%ROWTYPE;
begin
open SEL_OS;
loop
Fetch SEL_OS Into ENR_OS;
Exit When SEL_OS %notfound;
V_totalHT:=0;
V_totalTva:=0;
V_NUM_FACT := NUM_AUTO_FACTURE(V_ENTETE_FACT);
begin
SELECT CONDITION_COMMERCIALE.DELAIS_PAY, CONDITION_COMMERCIALE.EXOTIMB_FISC, CONDITION_COMMERCIALE.DATE_DEB_EXOTIMBRE,
CONDITION_COMMERCIALE.DATE_FIN_EXOTIMBRE into V_delais, V_exo, V_datedebuexo, V_dateFINexo
FROM CONDITION_COMMERCIALE
where (CONDITION_COMMERCIALE.CODE_CLIENT=ENR_OS.CODE_CLIENT)
and (CONDITION_COMMERCIALE.CODE_TYPE_CLIE IN ('4', '5','6'))
and (CONDITION_COMMERCIALE.NOM_PROCESSUS='CB');
EXCEPTION WHEN NO_DATA_FOUND THEN V_delais:=0; V_exo:=0; V_datedebuexo:=null; V_dateFINexo:=null;
End;
V_timbre:=0;
if( V_exo=1)then
if(V_datedebuexo is not NULL AND V_dateFINexo is not NULL )THEN
if(V_datedebuexo>=TO_DATE(SYSDATE, 'DD/MM/RRRR') AND V_dateFINexo<=TO_DATE(SYSDATE, 'DD/MM/RRRR') )THEN
SELECT TO_NUMBER(substr(val_param,3))/10 into V_timbre
FROM PARAMETRE
where PARAMETRE.CODE_PARAM='TIMB_FISC';
end if;
if(V_datedebuexo is NULL )THEN
SELECT TO_NUMBER(substr(val_param,3))/10 into V_timbre
FROM PARAMETRE
where PARAMETRE.CODE_PARAM='TIMB_FISC';
end if;
end if;
end if;
insert into FACTURE_OS_PF(FACTURE_OS_PF.NUM_FACT,FACTURE_OS_PF.CODE_RUBR_BUDG,FACTURE_OS_PF.ANNE_FACT, FACTURE_OS_PF.CODE_CLIENT,
FACTURE_OS_PF.MOIS_FACT,FACTURE_OS_PF.OBJET_FACT,FACTURE_OS_PF.DATE_FACT,FACTURE_OS_PF.DATE_CREA
,FACTURE_OS_PF.DATE_CAL_ECH,FACTURE_OS_PF.TIMB_FISC)
values ( V_NUM_FACT,ENR_OS.CODE_RUBR_BUDG,TO_NUMBER(TO_CHAR(TO_DATE(V_DATE_CALC),'YYYY')),ENR_OS.CODE_CLIENT, TO_NUMBER(TO_CHAR(TO_DATE(V_DATE_CALC),'MM')),'facture post-facturée',TO_DATE(V_DATE_CALC, 'DD/MM/RRRR'),TO_DATE(SYSDATE, 'DD/MM/RRRR'),TO_DATE(V_DATE_CALC, 'DD/MM/RRRR')+V_delais, V_timbre);
if(ENR_OS.ANCIEN_CODE<=9) then
BEGIN
declare
CURSOR SEL_NB_BON is select DISTINCT BON.CODE_PROD,CARTE_BON.CODE_LITR
FROM BON,CARTE_BON
WHERE (BON.NUM_SERI IN (SELECT NUM_SERI FROM BONDET_BGV where BONDET_BGV.DATE_BORD BETWEEN V_DATE_DEB AND V_DATE_FIN))
AND (BON.NOM_PROCESSUS='CB')
AND (BON.NUM_SERI=CARTE_BON.NUM_SERI)
AND (to_number(substr(bon.num_seri,1,6))=ENR_OS.ANCIEN_CODE)
AND (substr(bon.num_seri,7,6) = ENR_OS.CODE_RUBR_BUDG)
ORDER BY BON.CODE_PROD,CARTE_BON.CODE_LITR;
ENR_NB_BON SEL_NB_BON%ROWTYPE;
BEGIN
open SEL_NB_BON;
loop
V_NLig:=V_NLig+1;
Fetch SEL_NB_BON Into ENR_NB_BON;
Exit When SEL_NB_BON %notfound;
insert into DETAIL_FACTURE_OS_PF (DETAIL_FACTURE_OS_PF.NUM_FACT,DETAIL_FACTURE_OS_PF.CODE_PROD,DETAIL_FACTURE_OS_PF.CODE_LITR,DETAIL_FACTURE_OS_PF.NUM_LIGN) values(V_NUM_FACT,ENR_NB_BON.CODE_PROD,ENR_NB_BON.CODE_LITR,V_NLig);
begin
declare
CURSOR SEL_BON is select DISTINCT BON.NUM_SERI
FROM BON,CARTE_BON
WHERE (BON.NUM_SERI IN (SELECT NUM_SERI FROM BONDET_BGV where BONDET_BGV.DATE_BORD BETWEEN V_DATE_DEB AND V_DATE_FIN))
AND (BON.NOM_PROCESSUS='CB')
AND (BON.NUM_SERI=CARTE_BON.NUM_SERI)
AND (BON.CODE_PROD=ENR_NB_BON.CODE_PROD)
AND (CARTE_BON.CODE_LITR=ENR_NB_BON.CODE_LITR)
AND (to_number(substr(bon.num_seri,1,6))=ENR_OS.ANCIEN_CODE)
AND (substr(bon.num_seri,7,6) = ENR_OS.CODE_RUBR_BUDG)
AND (BON.NUM_SERI NOT IN (SELECT BON_FACTURE_OS_PF.NUM_SERI FROM BON_FACTURE_OS_PF));
ENR_BON SEL_BON%ROWTYPE;
BEGIN
open SEL_BON;
V_quantite :=0;
V_prixTTC:=0;
loop
Fetch SEL_BON Into ENR_BON;
Exit When SEL_BON %notfound;
begin
select STRUCTURE_PRIX_PRD.MARGE_REVEND ,STRUCTURE_PRIX_PRD.PRIX_TVA ,STRUCTURE_PRIX_PRD.PRIX_UNIT_HT
INTO V_MR , V_PTVA , V_PUHT
from STRUCTURE_PRIX_PRD
where
STRUCTURE_PRIX_PRD.CODE_PROD=ENR_NB_BON.CODE_PROD
and V_DATE_CALC BETWEEN STRUCTURE_PRIX_PRD.DATE_DEB AND STRUCTURE_PRIX_PRD.DATE_FIN;
end;
begin
SELECT LITRAGE.QUAN_LITR INTO V_LITR FROM LITRAGE WHERE LITRAGE.CODE_LITR=ENR_NB_BON.CODE_LITR;
select remise into V_remise from remise where code_client = ENR_OS.CODE_CLIENT and code_prd = ENR_NB_BON.CODE_PROD;
EXCEPTION WHEN NO_DATA_FOUND THEN V_remise:=0;
end;
V_quantite:=V_quantite +1;
V_prixTTC := V_prixTTC +( V_LITR * ((V_MR+V_PTVA+V_PUHT)/1000));
V_prixUHt := V_LITR * ((V_MR+V_PUHT)/1000);
V_prixUPublic := (V_MR+V_PTVA+V_PUHT)/1000;
V_totalHT:=V_totalHT+( V_LITR * ((V_MR+V_PUHT)/1000));
V_totalTva:=V_totalTva +( V_LITR*(V_PTVA/1000));
insert into BON_FACTURE_OS_PF(BON_FACTURE_OS_PF.NUM_FACT,BON_FACTURE_OS_PF.NUM_LIGN,BON_FACTURE_OS_PF.NUM_SERI)
values(V_NUM_FACT,V_NLig,ENR_BON.NUM_SERI);
end loop;
close SEL_BON;
END;
end;
update DETAIL_FACTURE_OS_PF set DETAIL_FACTURE_OS_PF.PRIX_UNIT_HT=V_prixUHt ,
DETAIL_FACTURE_OS_PF.PRIX_TOTA_TTC=V_prixTTC,
DETAIL_FACTURE_OS_PF.PRIX_UNIT_PUB=V_prixUPublic,
DETAIL_FACTURE_OS_PF.PRIX_TVA = V_PTVA/1000 ,
DETAIL_FACTURE_OS_PF.QUANTITE= V_quantite * V_LITR,
DETAIL_FACTURE_OS_PF.NBR_BONS= V_quantite,
detail_facture_os_pf.taux_tva = V_remise
where (DETAIL_FACTURE_OS_PF.NUM_FACT=V_NUM_FACT)
and( DETAIL_FACTURE_OS_PF.NUM_LIGN=V_NLig);
end loop;
close SEL_NB_BON;
END;
end;
end if;
if(ENR_OS.ANCIEN_CODE > 9) then
BEGIN
declare
CURSOR SEL_NB_BON is select DISTINCT BON.CODE_PROD,CARTE_BON.CODE_LITR
FROM BON,CARTE_BON
WHERE (BON.NUM_SERI IN (SELECT NUM_SERI FROM BONDET_BGV where BONDET_BGV.DATE_BORD BETWEEN V_DATE_DEB AND V_DATE_FIN))
AND (BON.NOM_PROCESSUS='CB')
AND (BON.NUM_SERI=CARTE_BON.NUM_SERI)
AND (to_number(substr(bon.num_seri,1,6))=ENR_OS.ANCIEN_CODE)
AND (substr(bon.num_seri,7,6) = ENR_OS.CODE_RUBR_BUDG)
AND (BON.CODE_TYPE_BON IN (SELECT TYPE_BON.CODE_TYPE_BON FROM TYPE_BON
WHERE TYPE_BON.CODE_NATU_CONS IN(SELECT NATURE_CONSOMMATION.CODE_NATU_CONS FROM NATURE_CONSOMMATION
WHERE NATURE_CONSOMMATION.TYPE_FACT='POST')))
AND (BON.NUM_SERI NOT IN (SELECT BON_FACTURE_OS_PF.NUM_SERI FROM BON_FACTURE_OS_PF))
ORDER BY BON.CODE_PROD,CARTE_BON.CODE_LITR;
ENR_NB_BON SEL_NB_BON%ROWTYPE;
BEGIN
open SEL_NB_BON;
loop
V_NLig:=V_NLig+1;
Fetch SEL_NB_BON Into ENR_NB_BON;
Exit When SEL_NB_BON %notfound;
insert into DETAIL_FACTURE_OS_PF (DETAIL_FACTURE_OS_PF.NUM_FACT,DETAIL_FACTURE_OS_PF.CODE_PROD,DETAIL_FACTURE_OS_PF.CODE_LITR,DETAIL_FACTURE_OS_PF.NUM_LIGN) values(V_NUM_FACT,ENR_NB_BON.CODE_PROD,ENR_NB_BON.CODE_LITR,V_NLig);
begin
declare
CURSOR SEL_BON is select DISTINCT BON.NUM_SERI
FROM BON,CARTE_BON
WHERE (BON.NUM_SERI IN (SELECT NUM_SERI FROM BONDET_BGV where BONDET_BGV.DATE_BORD BETWEEN V_DATE_DEB AND V_DATE_FIN))
AND (BON.NOM_PROCESSUS='CB')
AND (BON.NUM_SERI=CARTE_BON.NUM_SERI)
AND (BON.CODE_PROD=ENR_NB_BON.CODE_PROD)
AND (CARTE_BON.CODE_LITR=ENR_NB_BON.CODE_LITR)
AND (to_number(substr(bon.num_seri,1,6))=ENR_OS.ANCIEN_CODE)
AND (substr(bon.num_seri,7,6) = ENR_OS.CODE_RUBR_BUDG)
AND (BON.CODE_TYPE_BON IN (SELECT TYPE_BON.CODE_TYPE_BON FROM TYPE_BON
WHERE TYPE_BON.CODE_NATU_CONS IN(SELECT NATURE_CONSOMMATION.CODE_NATU_CONS FROM NATURE_CONSOMMATION
WHERE NATURE_CONSOMMATION.TYPE_FACT='POST')))
AND (BON.NUM_SERI NOT IN (SELECT BON_FACTURE_OS_PF.NUM_SERI FROM BON_FACTURE_OS_PF));
ENR_BON SEL_BON%ROWTYPE;
BEGIN
open SEL_BON;
V_quantite :=0;
V_prixTTC:=0;
loop
Fetch SEL_BON Into ENR_BON;
Exit When SEL_BON %notfound;
begin
select STRUCTURE_PRIX_PRD.MARGE_REVEND ,STRUCTURE_PRIX_PRD.PRIX_TVA ,STRUCTURE_PRIX_PRD.PRIX_UNIT_HT
INTO V_MR , V_PTVA , V_PUHT
from STRUCTURE_PRIX_PRD
where
STRUCTURE_PRIX_PRD.CODE_PROD=ENR_NB_BON.CODE_PROD
and V_DATE_CALC BETWEEN STRUCTURE_PRIX_PRD.DATE_DEB AND STRUCTURE_PRIX_PRD.DATE_FIN;
end;
begin
SELECT LITRAGE.QUAN_LITR INTO V_LITR FROM LITRAGE WHERE LITRAGE.CODE_LITR=ENR_NB_BON.CODE_LITR;
select remise into V_remise from remise where code_client = ENR_OS.CODE_CLIENT and code_prd = ENR_NB_BON.CODE_PROD;
EXCEPTION WHEN NO_DATA_FOUND THEN V_remise:=0;
end;
V_quantite:=V_quantite +1;
V_prixTTC := V_prixTTC +( V_LITR * ((V_MR+V_PTVA+V_PUHT)/1000));
V_prixUHt := V_LITR * ((V_MR+V_PUHT)/1000);
V_prixUPublic := (V_MR+V_PTVA+V_PUHT)/1000;
V_totalHT:=V_totalHT+( V_LITR * ((V_MR+V_PUHT)/1000));
V_totalTva:=V_totalTva +( V_LITR*(V_PTVA/1000));
insert into BON_FACTURE_OS_PF(BON_FACTURE_OS_PF.NUM_FACT,BON_FACTURE_OS_PF.NUM_LIGN,BON_FACTURE_OS_PF.NUM_SERI)
values(V_NUM_FACT,V_NLig,ENR_BON.NUM_SERI);
end loop;
close SEL_BON;
END;
end;
update DETAIL_FACTURE_OS_PF set DETAIL_FACTURE_OS_PF.PRIX_UNIT_HT=V_prixUHt ,
DETAIL_FACTURE_OS_PF.PRIX_TOTA_TTC=V_prixTTC,
DETAIL_FACTURE_OS_PF.PRIX_UNIT_PUB=V_prixUPublic,
DETAIL_FACTURE_OS_PF.PRIX_TVA = V_PTVA/1000 ,
DETAIL_FACTURE_OS_PF.QUANTITE= V_quantite * V_LITR,
DETAIL_FACTURE_OS_PF.NBR_BONS= V_quantite,
detail_facture_os_pf.taux_tva = V_remise
where (DETAIL_FACTURE_OS_PF.NUM_FACT=V_NUM_FACT)
and( DETAIL_FACTURE_OS_PF.NUM_LIGN=V_NLig);
end loop;
close SEL_NB_BON;
END;
end;
end if;
update FACTURE_OS_PF set FACTURE_OS_PF.MONT_HT= V_totalHT,
FACTURE_OS_PF.MONT_TVA= V_totalTva,
FACTURE_OS_PF.MONT_TTC= V_totalHT+V_totalTva
where
FACTURE_OS_PF.NUM_FACT=V_NUM_FACT;
begin
declare
CURSOR SEL_LIV is SELECT DISTINCT LIVRAISON.NUM_BL from
LIVRAISON, BON,BON_FACTURE_OS_PF,FACTURE_OS_PF
where (LIVRAISON.NUM_OS=BON.NUM_OS)
and (BON.NUM_SERI = BON_FACTURE_OS_PF.NUM_SERI)
and (BON_FACTURE_OS_PF.NUM_FACT=FACTURE_OS_PF.NUM_FACT)
and (FACTURE_OS_PF.NUM_FACT=V_NUM_FACT)
;
LIVFAC SEL_LIV%ROWTYPE;
BEGIN
open SEL_LIV;
loop
Fetch SEL_LIV Into LIVFAC ;
Exit When SEL_LIV %notfound;
insert into FACT_OSPF_LIVRAISON(FACT_OSPF_LIVRAISON.NUM_BL,FACT_OSPF_LIVRAISON.NUM_FACT)values(LIVFAC.NUM_BL,V_NUM_FACT);
END LOOP;
CLOSE SEL_LIV;
end;
end;
end loop;
close SEL_OS;
commit;
/*MAJ_ETAT_DET_OS_PF ;
MAJ_ETAT_OS_PF;*/
end; |
Partager