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; |
Procédure stockée dure trois jours pour se terminer
Répondre avec citation
Partager