/* Correction planning DA DA_PLANN*/
declare @NumDA as int, @NumLig as int, @DteLig as char(8)
declare cur_lig cursor
for
select p.NO_DA,p.NO_PO,max(l.DTELIVSOU) dtelivligne 
from <PREFIXE_HA>DA_PLANN p with(nolock)
left outer join <PREFIXE_HA>DA_LIGNE l with(nolock) on p.NO_DA=l.NO_DA and p.NO_PO=l.NO_PO 
group by p.NO_DA,p.NO_PO
having abs(sum(p.QTE_DEMAN)-max(l.QTE_DEM))>1E-4

open cur_lig
fetch next from cur_lig
INTO @NumDA, @NumLig, @DteLig

while @@FETCH_STATUS = 0
BEGIN
delete from <PREFIXE_HA>DA_PLANN where NO_DA=@NumDA and NO_PO=@NumLig and ((@DteLig='00000000' and DTE_LIV='00000000') OR (@DteLig<>'00000000' and DTE_LIV<>@DteLig))
fetch next from cur_lig
INTO @NumDA, @NumLig, @DteLig
END
CLOSE cur_lig
DEALLOCATE cur_lig
<SEPARATEUR>
/* Correction planning AO */
declare @NumAO as int, @NumLig as int, @DteLig as char(8)
declare cur_lig cursor
for
select p.NO_AO,p.NO_PO,max(l.DELAI) dtelivligne 
from <PREFIXE_HA>AO_PLANN p with(nolock)
left outer join <PREFIXE_HA>AO_LIGNE l with(nolock) on p.NO_AO=l.NO_AO and p.NO_PO=l.NO_PO
group by p.NO_AO,p.NO_PO
having abs(sum(p.QTE)-max(l.QTE))>1E-4

open cur_lig
fetch next from cur_lig
INTO @NumAO, @NumLig, @DteLig

while @@FETCH_STATUS = 0
BEGIN
delete from <PREFIXE_HA>AO_PLANN where NO_AO=@NumAO and NO_PO=@NumLig and ((@DteLig='00000000' and DTE='00000000') OR (@DteLig<>'00000000' and DTE<>@DteLig))
fetch next from cur_lig
INTO @NumAO, @NumLig, @DteLig
END
CLOSE cur_lig
DEALLOCATE cur_lig
<SEPARATEUR>
/* Correction planning contractuel Cde */
declare @NumCde as int, @NumLig as int, @DteLig as char(8)
declare cur_lig cursor
for
select p.NO_CA,p.NO_PO_CA,max(l.DTELIVCON) dtelivligne
from <PREFIXE_HA>CA_PL_CT p with(nolock)
left outer join <PREFIXE_HA>CA_LIGNE l with(nolock) on p.NO_CA=l.NO_CDE and p.NO_PO_CA=l.NO_PO
where l.RECEP_AUTO=0x46
group by p.NO_CA,p.NO_PO_CA
having abs(sum(p.QTE_COMM)-max(l.QTE_COMM))>1E-4

open cur_lig
fetch next from cur_lig
INTO @NumCde, @NumLig, @DteLig

while @@FETCH_STATUS = 0
BEGIN
delete from <PREFIXE_HA>CA_PL_CT where NO_CA=@NumCde and NO_PO_CA=@NumLig and ((@DteLig='00000000' and DTE_LIV='00000000') OR (@DteLig<>'00000000' and DTE_LIV<>@DteLig))
fetch next from cur_lig
INTO @NumCde, @NumLig, @DteLig
END
CLOSE cur_lig
DEALLOCATE cur_lig
<SEPARATEUR>
/* Correction planning prévisionnel Cde */
declare @NumCde as int, @NumLig as int, @DteLig as char(8)
declare cur_lig cursor
for
select p.NO_CA,p.NO_PO_CA,max(l.DTELIVCON) dtelivligne
from <PREFIXE_HA>CA_PL_PR p with(nolock)
left outer join <PREFIXE_HA>CA_LIGNE l  with(nolock) on p.NO_CA=l.NO_CDE and p.NO_PO_CA=l.NO_PO
where l.RECEP_AUTO=0x46
group by p.NO_CA,p.NO_PO_CA
having abs(sum(p.QTE_COMM)-max(l.QTE_COMM))>1E-4

open cur_lig
fetch next from cur_lig
INTO @NumCde, @NumLig, @DteLig

while @@FETCH_STATUS = 0
BEGIN
delete from <PREFIXE_HA>CA_PL_PR where NO_CA=@NumCde and NO_PO_CA=@NumLig and ((@DteLig='00000000' and DTE_LIV='00000000') OR (@DteLig<>'00000000' and DTE_LIV<>@DteLig))
fetch next from cur_lig
INTO @NumCde, @NumLig, @DteLig
END
CLOSE cur_lig
DEALLOCATE cur_lig

<SEPARATEUR>
/*Correction eclatement de 1 sur 3 lignes 0.333 CA_PL_CT*/
update T set T.QTE_COMM=T.QTE_COMM+res.diff from <PREFIXE_HA>CA_PL_CT T ,(select p.NO_CA as CA, p.NO_PO_CA as LCA, sum(p.QTE_COMM*1.000) as total,LigneCde.QTE_COMM,(round(LigneCde.QTE_COMM,3)-round(sum(p.QTE_COMM*1.000),3)) as diff,max(p.DTE_LIV) as dte  from <PREFIXE_HA>CA_PL_CT p, (select l.NO_CDE, l.NO_PO, l.QTE_COMM from <PREFIXE_HA>CA_LIGNE l) as LigneCde 
where LigneCde.NO_CDE=p.NO_CA and LigneCde.NO_PO=p.NO_PO_CA group by p.NO_CA, p.NO_PO_CA,LigneCde.QTE_COMM having round(sum(p.QTE_COMM*1.000),3)<>round(LigneCde.QTE_COMM*1.000,3)) as res 
where T.NO_CA=res.CA and T.NO_PO_CA=res.LCA and T.DTE_LIV=res.dte
<SEPARATEUR>
/*Correction eclatement de 1 sur 3 lignes 0.333 CA_PL_PR*/
update T set T.QTE_COMM=T.QTE_COMM+res.diff from <PREFIXE_HA>CA_PL_PR T ,(select p.NO_CA CA, p.NO_PO_CA LCA, sum(p.QTE_COMM*1.000) as total,LigneCde.QTE_COMM,(round(LigneCde.QTE_COMM,3)-round(sum(p.QTE_COMM*1.000),3)) as diff,max(p.DTE_LIV) as dte  from <PREFIXE_HA>CA_PL_PR p, (select l.NO_CDE, l.NO_PO, l.QTE_COMM from <PREFIXE_HA>CA_LIGNE l) as LigneCde 
where LigneCde.NO_CDE=p.NO_CA and LigneCde.NO_PO=p.NO_PO_CA group by p.NO_CA, p.NO_PO_CA,LigneCde.QTE_COMM having round(sum(p.QTE_COMM*1.000),3)<>round(LigneCde.QTE_COMM*1.000,3)) as res 
where T.NO_CA=res.CA and T.NO_PO_CA=res.LCA and T.DTE_LIV=res.dte
