Skip to content

Instantly share code, notes, and snippets.

@Djourdain

Djourdain/gist:e1c3360cd41f6a56dc2f

Created December 7, 2015 10:56
Show Gist options
  • Save Djourdain/e1c3360cd41f6a56dc2f to your computer and use it in GitHub Desktop.
Save Djourdain/e1c3360cd41f6a56dc2f to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
/* *************************************************************** *\
* Date de modif : 11/08/2011 Auteur : LB *
* Sujet : Si produit soldé, on récupère son cdtypvente si possible*
=====================================================================
* Date de modif : 27/01/2012 Auteur : DJ *
* Sujet : Récupération de l'ecotaxe pour chaque ligne produit *
=====================================================================
* Date de modif : 11/04/2012 Auteur : GB *
* Sujet : cdcompta passe en varchar 8 *
=====================================================================
* Date de modif : 27/04/2012 Auteur : JD *
* Sujet : insert echange adelya pour envoi (traitement C#) *
=====================================================================
* Date de modif : 08/08/2012 Auteur : LB *
* Sujet : On vérifie que la carte ne soit pas pérmiés. <--(faute) *
=====================================================================
* Date de modif : 04/09/2012 Auteur : JD *
* Sujet : pmp pour les livres numériques *
=====================================================================
* Date de modif : 30/04/2013 Auteur : GP *
* Sujet : ajout idwebsite dans facture_client *
=====================================================================
* Date de modif : 03/04/2014 Auteur : LB *
* Sujet : Ajout financement participatif. *
=====================================================================
* Date de modif : 04/06/2014 Auteur : DJ *
* Sujet : libpdt occasion/ histo_ventes occasions *
\* *************************************************************** */
if exists (select 1
from sysobjects
where name = 'b_fac_cli_web'
and type = 'P')
Drop Proc b_fac_cli_web
go
Create Proc b_fac_cli_web
@O_RET integer output,
@O_ERR varchar(255) output
as
Declare @err int ,
@CU_NUMCDEBES int ,
@CU_DTCRCDEBES smalldatetime ,
@CU_CDREFEXPEML varchar(20) ,
@NUMFACTCLI int ,
@NUMCDEWEB varchar(10) ,
@NUMADRFACT int ,
@NUMADRLIV int ,
@CDMODREGLT varchar(2) ,
@LIBMODREGLT varchar(60) ,
@CDSITE varchar(5) ,
@NUMCLIENT varchar(8) ,
@NOMCLIENT varchar(50) ,
@NUMCONTPAY int ,
@NBFACT int ,
@CDDEVDEC int ,
@CDDEVDEC2 int ,
@CDEDHT int ,
@CDEXPORT int ,
@CDFCPO int ,
@CDDEVISE varchar(3) ,
@CDPARTDECITRE varchar(8) ,
@NUMLGNFACTCLI int ,
@NOMEDITR varchar(30) ,
@NUMLGNEMPCDE decimal(6,2) ,
@CU_QTEEXPE int ,
@CU_TXDEVEXPORT money ,
@TXDEVEXPORTEURO money ,
@CU_NUMSUIVIDDEEXPE int ,
@CU_CDPDT varchar(21) ,
@CU_CDREFEXPEML2 varchar(20) ,
@NUMLGNBES int ,
@PVHT money ,
@PVNETHT money ,
@PVTTC money ,
@PVNETTTC money ,
@PVDEVPDT money ,
@CDDEVISE2 varchar(3) ,
@MTACPTEAREGLER money ,
@MTACPTEREGLE money ,
@MTACPTERADED money ,
@pvhtfrf money ,
@pvnethtfrf money ,
@pvttcfrf money ,
@pvnetttcfrf money ,
@LIBPDT varchar(225) ,
@auteur_princ varchar(60) ,
@auteurs_princ varchar(255) ,
@NOMCOLLEC varchar(255) ,
@TXREMCLI money ,
@CDCOMPTA varchar(8) ,
@CU_CDCOMPTA varchar(8) ,
@CU_CDSERV varchar(2) ,
@CU_LIBSERV varchar(40) ,
@CU_QTECDE int ,
@CU_CDTVA char(1) ,
@CU_TXTVA money ,
@CU_TREMCLI money ,
@CU_NUMLGNBES int ,
@CU_DTEEXPE smalldatetime ,
@CU_NUMLGNEMPCDE decimal(7,2) ,
@CDREPCLI varchar(3) ,
@LIBREP varchar(255) ,
@CU_CDSTATLGNARTWEB varchar(3) ,
@QTECDE int ,
@QTELIV int ,
@CDFAM varchar(3) ,
@PMP money ,
@CDTYPCARTE varchar(2) ,
@NUMCARTEREDUC varchar(13) ,
@QTERECPT int ,
@O_RETOUR int ,
@CU_DTVALCDEWEB smalldatetime ,
@PDT_SOLDE int ,
@CU_CDPARTDECITRE VarChar(10) ,
@CDTYPVENTE VarChar(5), -- Ajout LB le 11/08/2011.
@CU_CDFAM varchar(3), -- Ajout EMA me 01/08/2012
@DTEMISFACT smalldatetime,
@CU_CDEDECITREFR Int,
@MTREDUC money
-- Ajout LB le 04/12/09.
Declare @QTETMP Integer
Declare @CU_NUMLGNBES2 Integer
Declare @CU_QTEMAX Integer
Declare @QTEI Integer
Declare @CU_IDRELAIS VarChar(15)
Declare @CU_CDMODLIVWEB VarChar(5)
Declare @DATEJ SmallDateTime
declare @MTECOTAXE money -- Ajout DJ le 27/01/2012
declare @CDSOC varchar(5) -- Ajout DJ le 27/01/2012
DECLARE @PMP_TXTVA decimal(12,2), -- JD le 03/09/2012
@PMP_PVTTC money,
@PMP_TXRMS decimal(12,2),
@CDEDITEUR varchar(10),
@CDPLATEFORMELN int
DECLARE @IDWEBSITE varchar(100)
DECLARE @LIBOCCAS VARCHAR(255) -- DJ le 04/06/2014
DECLARE @OCCASION TINYINT -- DJ le 04/06/2014
DECLARE @TYPSTOOCCAS VARCHAR(05) -- DJ le 05/06/2014
Select @TXDEVEXPORTEURO=txdevexport
from tauxdevise
where cddevise = 'EUR'
and getdate() between dtdebvali1 and dtfinval
Select @O_RET = 0
Select @DATEJ = Convert(SmallDateTime, Convert(VarChar(8), GetDate(), 112))
Update DECDEPOT..expe_morin_a_facturer
set cdenregtraite = 1
select @err = @@error
if @err != 0
begin
SELECT @O_ERR = 'Erreur update DECDEPOT..expe_morin_a_facturer'
Select @O_RET = -1
return
end
Create table #pied_fact_ht
(
cdtva char(1) ,
cdcompta varchar(8) , -- Modif GB le 11/04/2012
mttva decimal(11,2) null ,
mtht decimal(11,2) null ,
txtva decimal(9,4) ,
mttvafrf decimal(11,2) null , -- Modif 04/10/2001
mthtfrf decimal(11,2) null -- Modif 04/10/2001
)
Create table #pied_fact_export
(
cdtva char(1) ,
cdcompta varchar(8) , -- Modif GB le 11/04/2012
mtttc decimal(11,2) null,
mtht decimal(11,2) null,
txtva decimal(9,4) ,
mtttcfrf decimal(11,2) null, -- Modif 04/10/2001
mthtfrf decimal(11,2) null -- Modif 04/10/2001
)
Create table #pied_fact
(
cdtva char(1) ,
cdcompta varchar(8) , -- Modif GB le 11/04/2012
mtttc decimal(11,2) null,
mtht decimal(11,2) null,
txtva decimal(9,4) ,
mtttcfrf decimal(11,2) null, -- Modif 04/10/2001
mthtfrf decimal(11,2) null -- Modif 04/10/2001
)
/* Par sécurité, si insertion manuelle par exemple, on supprime au préalables les commandes partenaire qui sont exclues de la génération des factures */
delete DECDEPOT..expe_morin_a_facturer
from RESADEC..cde_bes_client cbc,
CATALOGLIST..boutique_part bp
where cbc.cdtyppricde = "PM"
and isnull(bp.cd_gen_facture,0) = 0
and DECDEPOT..expe_morin_a_facturer.numcdebes = cbc.numcdebes
and cbc.cdpartdecitre = bp.cdpartdecitre
/* Déclaration du curseur pour création facture */
DECLARE c_list_cde CURSOR FOR
SELECT c.numcdebes,
c.dtcrcdebes,
c.txdevexport,
p.cdrefexpeml,
IsNull(c.idrelais, ''),
c.cdmodlivweb,
(Case When c.cdtyppricde = 'PM' Then c.dtcrcdepm Else c.dtvalcdeweb End),
(Case When c.cdtyppricde = 'PM' Then c.cdpartdecitre Else 'I' End),
convert(smalldatetime, convert(varchar, dteexpe, 112) ),
(Case When cdtyppricde = 'I' And idwebsite = '1' And cdorigcde = 'CLP' Then 1 Else 0 End)
FROM DECDEPOT..expe_morin_a_facturer f,
DECDEPOT..lgn_expe_entrepot_depot_pdt p,
cde_bes_client c
WHERE f.cdenregtraite = 1
and f.numcdebes = c.numcdebes
and p.numfactcli = NULL
and c.cdorigcde = 'CLP'
and isnull(c.cdmodfact,'1') <> '9'
and ((c.cdtyppricde = 'I' or c.cdtyppricde = 'PM')
or (c.cdemagexpebvs = 1 and p.numfactcli = null))
and p.cdrefexpeml = f.cdrefexpeml -- Modif GB le 11/11/2009 --and p.numexpe = f.numexpe
and c.numcdebes = p.numcdebes
/*and (c.idwebsite=null
or (c.idwebsite <> null and
(select cdrgptexpe from lst_site_web where idwebsite = c.idwebsite)=null))*/
GROUP BY c.numcdebes,
c.dtcrcdebes,
c.txdevexport,
p.cdrefexpeml,
IsNull(c.idrelais, ''),
c.cdmodlivweb,
(Case When c.cdtyppricde = 'PM' Then c.dtcrcdepm Else c.dtvalcdeweb End),
(Case When c.cdtyppricde = 'PM' Then c.cdpartdecitre Else 'I' End),
convert(smalldatetime, convert(varchar, dteexpe, 112) ),
(Case When cdtyppricde = 'I' And idwebsite = '1' And cdorigcde = 'CLP' Then 1 Else 0 End)
order by c.numcdebes
FOR READ ONLY
OPEN c_list_cde
FETCH c_list_cde into @CU_NUMCDEBES,@CU_DTCRCDEBES,@CU_TXDEVEXPORT,@CU_CDREFEXPEML, @CU_IDRELAIS, @CU_CDMODLIVWEB, @CU_DTVALCDEWEB, @CU_CDPARTDECITRE,@CU_DTEEXPE, @CU_CDEDECITREFR
while (@@sqlstatus != 2)
begin
if @@sqlstatus=1
begin
SELECT @O_ERR = 'Erreur curseur c_list_cde'
SELECT @O_RET = -1
return
end
--print 'numcdebes :%1!', @CU_NUMCDEBES
-- Modif GB le 14/04/2010 : ré-initialisation des variables
Select @NUMCDEWEB=NULL,
@NUMADRFACT=NULL,
@NUMADRLIV=NULL,
@CDMODREGLT=NULL,
@CDSITE=NULL,
@CDEDHT=NULL,
@CDEXPORT=NULL,
@CDFCPO=NULL,
@NUMCLIENT=NULL,
@CDDEVISE=NULL,
@CDPARTDECITRE=NULL,
@CDTYPCARTE=NULL,
@NUMCARTEREDUC=NULL,
@LIBMODREGLT=NULL,
@NOMCLIENT=NULL,
@CDDEVDEC=NULL,
@NUMFACTCLI=NULL,
@IDWEBSITE = NULL
Select @NUMCDEWEB = numcdeweb,
@NUMADRFACT = numadrfact,
@NUMADRLIV = numadr,
@CDMODREGLT = cdmodreglt,
@CDSITE = cdsite,
@CDEDHT = cdedht,
@CDEXPORT = isnull(cdexport,0),
@CDFCPO = cdfcpo,
@NUMCLIENT = numclient,
@CDDEVISE = cddevise,
@CDPARTDECITRE = cdpartdecitre,
@IDWEBSITE = idwebsite
from cde_bes_client
where numcdebes = @CU_NUMCDEBES
Select @CDTYPCARTE = r.cdtypcarte,
@NUMCARTEREDUC = r.numcartereduc
from carte_reduc r,
client c
where c.numclient = @NUMCLIENT
and c.numcartereduc = r.numcartereduc
And r.dtanncarte = Null -- Ajout LB le 08/08/2012
And GetDate() Between dtdebval And dtfinval -- Ajout LB le 08/08/2012
Select @LIBMODREGLT = libmodreglt from mode_reglt where cdmodreglt = @CDMODREGLT
Select @NOMCLIENT = nomcli from client where numclient = @NUMCLIENT
Select @CDDEVDEC = cddevdec from devise where cddevise = @CDDEVISE
-- print 'insert facture_client %1! pour cde %2!',@NUMFACTCLI,@CU_NUMCDEBES
IF EXISTS(SELECT 1 FROM cde_bes_client c WHERE c.numcdebes = @CU_NUMCDEBES AND cdemagexpebvs = 1) BEGIN
SELECT @CDSOC = 'DEC',
@CDMODREGLT = "00",
@LIBMODREGLT = libmodreglt
FROM mode_reglt
WHERE cdmodreglt = "00"
exec rc_gen_getid 'facture_client','TOUS',@NUMFACTCLI output
END
ELSE BEGIN
SELECT @CDSOC = 'DI' -- Modif JD le 1/06/2012
exec rc_gen_getid 'facture_di','TOUS',@NUMFACTCLI output
END
BEGIN TRANSACTION facture
If @CU_IDRELAIS = ''
Begin
insert facture_client
(
numfactcli,
numcdeweb,
dtemisfact,
cdmodreglt,
libmodreglt,
cdsite,
cdedht,
cdexport,
cdfcpo,
numadrfact,
numadrliv,
numclient,
nomcli,
cddevise,
cddevdec,
hd_cdoper,
hd_cdsite,
hd_dtheure,
cdpartdecitre,
nbfact,
idrelais,
cdmodlivweb,
cdsoc,
idwebsite
)
Values(
@NUMFACTCLI,
@NUMCDEWEB,
@CU_DTEEXPE , -- convert(varchar(8), getdate(), 112), --getdate(),
@CDMODREGLT,
@LIBMODREGLT,
@CDSITE,
@CDEDHT,
@CDEXPORT,
@CDFCPO,
@NUMADRFACT,
@NUMADRLIV,
@NUMCLIENT,
@NOMCLIENT,
@CDDEVISE,
@CDDEVDEC,
'MORB3',
'INFO',
getdate(),
@CDPARTDECITRE,
1,
@CU_IDRELAIS,
@CU_CDMODLIVWEB,
@CDSOC,
@IDWEBSITE
)
End
Else
Begin
insert facture_client
(
numfactcli,
numcdeweb,
dtemisfact,
cdmodreglt,
libmodreglt,
cdsite,
cdedht,
cdexport,
cdfcpo,
numadrfact,
numclient,
nomcli,
cddevise,
cddevdec,
hd_cdoper,
hd_cdsite,
hd_dtheure,
cdpartdecitre,
nbfact,
idrelais,
cdmodlivweb,
cdsoc,
idwebsite
)
Values(
@NUMFACTCLI,
@NUMCDEWEB,
@CU_DTEEXPE , --convert(varchar(8), getdate(), 112), --getdate(),
@CDMODREGLT,
@LIBMODREGLT,
@CDSITE,
@CDEDHT,
@CDEXPORT,
@CDFCPO,
@NUMADRFACT,
@NUMCLIENT,
@NOMCLIENT,
@CDDEVISE,
@CDDEVDEC,
'MORB3',
'INFO',
getdate(),
@CDPARTDECITRE,
1,
@CU_IDRELAIS,
@CU_CDMODLIVWEB,
@CDSOC,
@IDWEBSITE
)
End
select @err = @@error
if @err != 0
begin
ROLLBACK TRANSACTION facture
SELECT @O_ERR = "Erreur Insert facture_client pour la cde "+convert(varchar,@CU_NUMCDEBES)
Select @O_RET = -1
Return
end
Select @NUMLGNFACTCLI = 1
/*Déclaration du cursor pour création des lignes de fact pdt */
-- Modif LB le 04/12/09 : On regroupe par code produit.
DECLARE c_lgn_pdt CURSOR FOR
SELECT l.cdpdt,
Max((Right(l.cdrefexpeml, (char_length (l.cdrefexpeml)-2)))),
Sum(l.qteexpe)
FROM DECDEPOT..lgn_expe_entrepot_depot_pdt l
WHERE l.numcdebes = @CU_NUMCDEBES
and l.cdrefexpeml = @CU_CDREFEXPEML
and l.numfactcli = Null
GROUP BY cdpdt
FOR READ ONLY
OPEN c_lgn_pdt
FETCH c_lgn_pdt into @CU_CDPDT,@CU_CDREFEXPEML2,@CU_QTEEXPE
while (@@sqlstatus != 2)
begin
if @@sqlstatus=1
begin
ROLLBACK TRANSACTION facture
SELECT @O_ERR = 'Erreur curseur c_lgn_pdt'
SELECT @O_RET = -1
return
end
-- Modif GB le 14/04/2010 : ré-initialisation des variables
Select @NOMEDITR=NULL,
@NUMLGNEMPCDE=NULL,
@NUMLGNBES=NULL,
@TXREMCLI=NULL,
@QTECDE=NULL,
@PVHT=NULL,
@PVNETHT=NULL,
@PVTTC=NULL,
@PVNETTTC=NULL,
@PVDEVPDT=NULL,
@CDDEVISE2=NULL,
@MTACPTEAREGLER=NULL,
@MTACPTEREGLE=NULL,
@MTACPTERADED=NULL,
@pvhtfrf=NULL,
@pvnethtfrf=NULL,
@pvttcfrf=NULL,
@pvnetttcfrf=NULL,
@CDDEVDEC2=NULL,
@CDFAM=NULL,
@PMP=NULL,
@NOMCOLLEC=NULL,
@PDT_SOLDE=NULL,
@LIBOCCAS = NULL,
@TYPSTOOCCAS = NULL,
@MTREDUC = NULL
Select @NOMEDITR = nomeditr
from editeur e,
produit p
where p.cdpdt = @CU_CDPDT
and e.cdeditr = p.cdeditr
Select @NUMLGNEMPCDE = min(p.numlgnempcde),
@NUMLGNBES = min(p.numlgnbes)
from lgn_cde_bes_client_pdt p
where p.numcdebes = @CU_NUMCDEBES
and p.cdpdt = @CU_CDPDT
and p.cdstatlgnbescli = 'V'
and exists(select 1 from DECDEPOT..suivi_dde_expe_morin s,
DECDEPOT..lgn_expe_entrepot_depot_pdt l,
lgn_cde_bes_client_pdt p
where Right(l.cdrefexpeml, (char_length (l.cdrefexpeml)-2)) = convert(varchar,s.numsuividdeexpe)
and l.numcdebes = s.numcdebes
and p.numlgnbes = s.numlgnbes
and l.numcdebes = p.numcdebes
and p.numcdebes = @CU_NUMCDEBES
and p.cdpdt = @CU_CDPDT
)
if @NUMLGNBES = NULL
begin
ROLLBACK TRANSACTION facture
SELECT @O_ERR = 'Erreur lors de la recherche du numlgnbes pour la cde '+convert(varchar,@CU_NUMCDEBES)+' et le pdt '+@CU_CDPDT
SELECT @O_RET = -1
return
end
Select @TXREMCLI = txremcli,
@QTECDE = qtecde,
@MTREDUC = mtreduc
from lgn_cde_bes_client_pdt
where numcdebes = @CU_NUMCDEBES
and numlgnbes = @NUMLGNBES
/* Appel de la proc calculant les prix de vente */
EXECUTE rc_module_montants
@CU_NUMCDEBES,
@NUMLGNBES,
'P',
@PVHT output,
@PVNETHT output,
@PVTTC output,
@PVNETTTC output,
@PVDEVPDT output,
@CDDEVISE2 output,
@MTACPTEAREGLER output,
@MTACPTEREGLE output,
@MTACPTERADED output,
@pvhtfrf output,
@pvnethtfrf output ,
@pvttcfrf output ,
@pvnetttcfrf output
Select @CDDEVDEC2 = cddevdec from devise where cddevise = @CDDEVISE2
Select @CDFAM = cdfam, @LIBPDT = libpdt from produit where cdpdt = @CU_CDPDT
select @OCCASION = 0
SELECT @LIBOCCAS = ISNULL(bpe.libfacture,""),
@OCCASION = 1,
@TYPSTOOCCAS = bpe.cdtypsto
FROM CATALOGLIST..boutique_part_etat bpe,
lgn_cde_bes_client_pdt l,
cde_bes_client cbc
WHERE cbc.numcdebes = @CU_NUMCDEBES
AND l.numcdebes = cbc.numcdebes
AND l.numlgnbes= @NUMLGNBES
AND l.cdtypaffecml = bpe.cdetat
AND bpe.cdpartdecitre = (case
when cbc.cdtyppricde="I" then "I"
else cbc.cdpartdecitre
end )
AND bpe.cdetat <> "N"
IF ISNULL(@LIBOCCAS,"")<>""
BEGIN
SELECT @LIBPDT = @LIBPDT +"-"+@LIBOCCAS
END
IF isnull(@OCCASION,0) = 0
BEGIN
Select @PMP = pmp from pdt_site where cdpdt = @CU_CDPDT and cdsite = (select cdsite from site_expedition) -- @CDSITE modif EMA le 01/08/2012 (on prend tjours le pmp du site d'expédition)
END
IF @OCCASION = 1
BEGIN
SELECT @PMP = pmp
FROM pdt_site_typsto
WHERE cdpdt = @CU_CDPDT
--AND cdsite = (SELECT cdsite FROM cde_bes_client WHERE numcdebes = @CU_NUMCDEBES)
AND cdtypsto = @TYPSTOOCCAS
AND EXISTS (SELECT 1 FROM site_expedition WHERE cdsite = pdt_site_typsto.cdsite)
SELECT @PMP = ISNULL(@PMP,0.00)
END
IF (SELECT cdtyppdt FROM produit WHERE cdpdt = @CU_CDPDT) = "LN" BEGIN -- Modif JD le 07/08/2012
SELECT @CDPLATEFORMELN = cdplateformeln FROM pdt_plateforme_ln WHERE cdpdt = @CU_CDPDT AND getdate() between dtdebvali1 and dtfinval
select @PMP_TXTVA = td.txtva
from pdt_tva pt,
tva_date td
where pt.cdtva = td.cdtva
and pt.cdpdt = @CU_CDPDT
and @DATEJ between pt.dtdebvali1 and pt.dtfinval
and @DATEJ between td.dtdebvali1 and td.dtfinval
select @PMP_PVTTC = pvttc
from lgn_fact_client_pdt
where cdpdt = @CU_CDPDT
and numfactcli = @NUMFACTCLI
select @PMP_TXRMS = isnull(pe.txrms,0)
from plateformeln_editr pe,
produit p,
pdt_plateforme_ln ppl
where pe.cdplateformeln = ppl.cdplateformeln
and p.cdeditr = pe.cdeditr
and ppl.cdpdt = p.cdpdt
and pe.cdplateformeln = @CDPLATEFORMELN
and p.cdpdt = @CU_CDPDT
and @DATEJ between pe.dtdebvali1 and pe.dtfinval
and @DATEJ between ppl.dtdebvali1 and ppl.dtfinval
if( @PMP_TXRMS <= 0 )
begin
select @PMP_TXRMS = isnull(pr.txrms,0)
from plateformeln_rms pr
where pr.cdplateformeln = @CDPLATEFORMELN
and @DATEJ between pr.dtdebvali1 and pr.dtfinval
end
--calcul du pmp
select @PMP = round ( round((@PMP_PVTTC * 100) / (100 + isnull(@PMP_TXTVA,0)),2) * convert(money, (100 - isnull(@PMP_TXRMS, 0)))/100,2)
END
declare curs_aut cursor
for select LTrim(RTrim(IsNull(a.prenomauteur,"") + " " + IsNull(a.nomauteur,"")))
from auteur a,
pdt_auteur pa
where pa.cdpdt = @CU_CDPDT
and pa.cdauteurprinc = 1
and a.numaut = pa.numaut
order by pa.numordre
for read only
select @auteurs_princ = NULL -- Init liste auteurs
open curs_aut
fetch curs_aut into @auteur_princ
while (@@sqlstatus = 0)
begin
if @auteur_princ <> NULL
begin
if @auteurs_princ = NULL
begin
select @auteurs_princ = @auteur_princ
end
else
begin
select @auteurs_princ = substring(@auteurs_princ + " / " + @auteur_princ, 1, 255)
end
end
-- Auteur suivant...
fetch curs_aut
into @auteur_princ
end
close curs_aut
deallocate cursor curs_aut
select @NOMCOLLEC = substring(@auteurs_princ,1,255)
-- Ajout DJ le 27/01/2012
select @MTECOTAXE = isNull(mtecotaxe,0.00) from pdt_dateprix p where p.cdpdt = @CU_CDPDT and getdate() between p.dtdebvali1 and p.dtfinval
-- print ' insert lgn_fact_client_pdt pr ligne= %1! et produit %2! ',@NUMLGNFACTCLI,@CU_CDPDT
if @CDEXPORT = 0
begin
insert lgn_fact_client_pdt
(
numfactcli ,
numlgnfactcli,
cdpdt ,
nomeditr ,
numlgnempcde,
numbl ,
qtefact ,
pvht ,
pvnetht ,
pvttc ,
pveuro ,
pvnetttc ,
pvdevpdt ,
cddevise ,
cddevdec ,
libpdt ,
nomcollec ,
numcdeclient,
dtcrcdebes ,
txdevexport ,
txremcli ,
pvhtfrf ,
pvttcfrf ,
pvnethtfrf ,
pvnetttcfrf ,
hd_cdoper ,
hd_cdsite ,
hd_dtheure ,
mtecotaxe ,
mtreduc
)
values(
@NUMFACTCLI,
@NUMLGNFACTCLI,
@CU_CDPDT,
@NOMEDITR,
@NUMLGNEMPCDE,
convert(int,@CU_CDREFEXPEML2),
@CU_QTEEXPE,
@PVHT,
@PVNETHT,
@PVTTC,
@PVTTC/@TXDEVEXPORTEURO,
@PVNETTTC,
@PVDEVPDT,
@CDDEVISE2,
@CDDEVDEC2,
@LIBPDT,
@NOMCOLLEC,
@CU_NUMCDEBES,
@CU_DTCRCDEBES,
isnull(@CU_TXDEVEXPORT,@TXDEVEXPORTEURO),
@TXREMCLI,
@pvhtfrf,
@pvttcfrf,
@pvnethtfrf,
@pvnetttcfrf,
'MORB3',
'INFO',
getdate(),
@MTECOTAXE,
@MTREDUC
)
select @err = @@error
if @err != 0
begin
ROLLBACK TRANSACTION facture
SELECT @O_ERR = "Erreur Insert lgn_fact_client_pdt pour la cde "+convert(varchar,@CU_NUMCDEBES)+" et la ligne "+convert(varchar,@NUMLGNBES)
Select @O_RET = -1
return
end
end
else
begin
insert lgn_fact_client_pdt
(
numfactcli ,
numlgnfactcli,
cdpdt ,
nomeditr ,
numlgnempcde,
numbl ,
qtefact ,
pvht ,
pvnetht ,
pvttc ,
pveuro ,
pvnetttc ,
pvdevpdt ,
cddevise ,
cddevdec ,
libpdt ,
nomcollec ,
numcdeclient,
dtcrcdebes ,
txdevexport ,
txremcli ,
pvhtfrf ,
pvttcfrf ,
pvnethtfrf ,
pvnetttcfrf ,
hd_cdoper ,
hd_cdsite ,
hd_dtheure ,
mtecotaxe ,
mtreduc
)
values(
@NUMFACTCLI,
@NUMLGNFACTCLI,
@CU_CDPDT,
@NOMEDITR,
@NUMLGNEMPCDE,
convert(int,@CU_CDREFEXPEML2),
@CU_QTEEXPE,
@PVHT,
@PVNETHT,
@PVHT,
@PVTTC/@TXDEVEXPORTEURO,
@PVNETHT,
@PVDEVPDT,
@CDDEVISE2,
@CDDEVDEC2,
@LIBPDT,
@NOMCOLLEC,
@CU_NUMCDEBES,
@CU_DTCRCDEBES,
isnull(@CU_TXDEVEXPORT,@TXDEVEXPORTEURO),
@TXREMCLI,
@pvhtfrf,
@pvttcfrf,
@pvnethtfrf,
@pvnetttcfrf,
'MORB3',
'INFO',
getdate(),
@MTECOTAXE,
@MTREDUC
)
select @err = @@error
if @err != 0
begin
ROLLBACK TRANSACTION facture
SELECT @O_ERR = "Erreur Insert lgn_fact_client_pdt pour la cde "+convert(varchar,@CU_NUMCDEBES)+" et la ligne "+convert(varchar,@NUMLGNBES)
Select @O_RET = -1
return
end
end
-- Modif LB le 04/12/09 : On met à jour les qté facturée pour toutes les lignes de ce produit.
Select @QTETMP = @CU_QTEEXPE
Declare c_lgn_cde Cursor For
Select numlgnbes,
IsNull(qtecde, 0) - IsNull(qtefact, 0)
From lgn_cde_bes_client_pdt
Where numcdebes = @CU_NUMCDEBES
And cdpdt = @CU_CDPDT
And cdstatlgnbescli Not In ('A', 'S')
Open c_lgn_cde
Fetch c_lgn_cde Into @CU_NUMLGNBES2,
@CU_QTEMAX
While (@@sqlstatus != 2) And @QTETMP > 0
Begin
if @@sqlstatus=1
Begin
ROLLBACK TRANSACTION facture
SELECT @O_ERR = "Erreur parcours c_lgn_cde"
Select @O_RET = -1
Close c_lgn_cde
Deallocate c_lgn_cde
return
End
Select @QTEI = Case When @QTETMP > @CU_QTEMAX Then @CU_QTEMAX
Else @QTETMP
End
/* On met a jour la qte facturee de la ligne de commande */
Update lgn_cde_bes_client_pdt
set qtefact = IsNull(qtefact, 0) + @QTEI
where numcdebes = @CU_NUMCDEBES
and numlgnbes = @CU_NUMLGNBES2
select @err = @@error
if @err != 0
begin
ROLLBACK TRANSACTION facture
SELECT @O_ERR = "Erreur Update lgn_cde_bes_client_pdt pour la cde "+convert(varchar,@CU_NUMCDEBES)+" et la ligne "+convert(varchar,@NUMLGNBES)
Select @O_RET = -1
Close c_lgn_cde
Deallocate c_lgn_cde
return
end
Select @QTETMP = @QTETMP - @QTEI
Fetch c_lgn_cde Into @CU_NUMLGNBES2,
@CU_QTEMAX
End
Close c_lgn_cde
Deallocate c_lgn_cde
-- print ' insert lgnfactclipdt_tva pr ligne= %1!',@NUMLGNFACTCLI
-- Modif LB le 11/08/2011.
select
@PDT_SOLDE = 1,
@CDTYPVENTE = cdtypvente
from liste_pdt_soldes_web
where cdpdt = @CU_CDPDT
and cdpartdecitre = @CU_CDPARTDECITRE
and @CU_DTVALCDEWEB between dtdebvali1 and dtfinval
/*SELECT @OCCASION = 0
SELECT @OCCASION = 1
FROM CATALOGLIST..boutique_part_etat bpe,
lgn_cde_bes_client_pdt l,
cde_bes_client cbc
WHERE cbc.numcdebes = @CU_NUMCDEBES
AND l.numcdebes = cbc.numcdebes
AND l.numlgnbes= @NUMLGNBES
AND l.cdtypaffecml = bpe.cdetat
AND bpe.cdpartdecitre = cbc.cdpartdecitre
AND bpe.cdetat <> "N"
*/
/*if exists (select 1 from liste_pdt_soldes_web
where cdpdt = @CU_CDPDT
and cdpartdecitre = @CU_CDPARTDECITRE
and @CU_DTVALCDEWEB between dtdebvali1 and dtfinval)
begin
Select @PDT_SOLDE = 1
end */
-- Insertion dans lgnfactclipdt_tva
if @CDEXPORT = 0
begin
Insert lgnfactclipdt_tva(
numfactcli ,
numlgnfactcli,
cdtva ,
txpvtva ,
txtva
)
Select @NUMFACTCLI,
@NUMLGNFACTCLI,
p.cdtva,
p.txpvtva,
d.txtva
from pdt_tva p,
tva_date d
where p.cdpdt = @CU_CDPDT
and @DATEJ between p.dtdebvali1 and p.dtfinval
and @DATEJ between d.dtdebvali1 and d.dtfinval
and d.cdtva = p.cdtva
select @err = @@error
if @err != 0
begin
ROLLBACK TRANSACTION facture
SELECT @O_ERR = 'Erreur Insert lgnfactclipdt_tva'
Select @O_RET = -1
return
end
-- print ' insert histo_ventes pr ligne= %1!',@NUMLGNFACTCLI
select @DTEMISFACT = dtemisfact from facture_client where numfactcli = @NUMFACTCLI
insert histo_ventes
(
dtmvt ,
typmvt ,
cdtypvente ,
cdfam ,
cdpdt ,
mtttc ,
mtrms ,
mttva ,
pmp ,
numclient ,
cdtypcarte ,
numcarte ,
nummoypai ,
qtevendu ,
cdsite ,
dtvaleur ,
cdreforigmvt ,
numcdebes
)
Values(
getdate(),
'03',
(case when @OCCASION = 1 THEN "08"
when @PDT_SOLDE = 1 and @CDTYPVENTE <> Null and @TXREMCLI > 5.00 then @CDTYPVENTE
when @PDT_SOLDE = 1 and @CDTYPVENTE = Null and @TXREMCLI > 5.00 then '07'
else '02'
end), -- Modif GB le 10/01/2011
@CDFAM,
@CU_CDPDT,
@PVTTC * @CU_QTEEXPE,
(@PVTTC - @PVNETTTC)* @CU_QTEEXPE,
(@PVNETTTC - @PVNETHT)* @CU_QTEEXPE,
@PMP,
@NUMCLIENT,
@CDTYPCARTE,
@NUMCARTEREDUC,
NULL,
@CU_QTEEXPE,
@CDSITE,
@DTEMISFACT , --@DTEMISFACT , --getdate(),
convert(varchar,@NUMFACTCLI),
@CU_NUMCDEBES
)
select @err = @@error
if @err != 0
begin
ROLLBACK TRANSACTION facture
SELECT @O_ERR = "Erreur Insert histo_ventes"
Select @O_RET = -1
return
end
end
else
begin
-- Modif LB le 11/08/2011
select @DTEMISFACT = dtemisfact from facture_client where numfactcli = @NUMFACTCLI
insert histo_ventes
(
dtmvt ,
typmvt ,
cdtypvente ,
cdfam ,
cdpdt ,
mtttc ,
mtrms ,
mttva ,
pmp ,
numclient ,
cdtypcarte ,
numcarte ,
nummoypai ,
qtevendu ,
cdsite ,
dtvaleur ,
cdreforigmvt ,
numcdebes
)
Values(
getdate(),
'03',
(case when @OCCASION = 1 THEN "08"
when @PDT_SOLDE = 1 and @CDTYPVENTE <> Null and @TXREMCLI > 5.00 then @CDTYPVENTE
when @PDT_SOLDE = 1 and @CDTYPVENTE = Null and @TXREMCLI > 5.00 then '07'
else '02'
end), -- Modif GB le 10/01/2011
@CDFAM,
@CU_CDPDT,
@PVHT * @CU_QTEEXPE,
(@PVHT - @PVNETHT)* @CU_QTEEXPE,
0,
@PMP,
@NUMCLIENT,
@CDTYPCARTE,
@NUMCARTEREDUC,
NULL,
@CU_QTEEXPE,
@CDSITE,
getdate(),-- @DTEMISFACT ,
convert(varchar,@NUMFACTCLI),
@CU_NUMCDEBES
)
select @err = @@error
if @err != 0
begin
ROLLBACK TRANSACTION facture
SELECT @O_ERR = "Erreur Insert histo_ventes"
Select @O_RET = -1
return
end
end
-- Modif LB le 04/12/09.
-- on Solde la ligne pdt
update lgn_cde_bes_client_pdt
set cdstatlgnbescli = 'S',
hd_cdoper = 'MORB3',
hd_cdsite = 'INFO',
hd_dtheure = getdate(),
dtsoldlgn = GetDate()
where numcdebes = @CU_NUMCDEBES
And cdpdt = @CU_CDPDT
And qtecde = qtefact
select @err = @@error
if @err != 0
begin
ROLLBACK TRANSACTION facture
SELECT @O_ERR = "Erreur lors du solde de lgn_cde_bes_client_pdt pour la cde "+convert(varchar,@CU_NUMCDEBES)+" et la ligne "+convert(varchar,@NUMLGNBES)
Select @O_RET = -1
return
end
/*Select @QTECDE = qtecde
from lgn_cde_bes_client_pdt
where numcdebes = @CU_NUMCDEBES
and numlgnbes = @NUMLGNBES
Select @QTERECPT = IsNull(sum(qterecptmag),0)
from lgn_appro
where numcdebes = @CU_NUMCDEBES
and numlgnbes = @NUMLGNBES
if (@CU_QTEEXPE = @QTECDE)
or (@CU_QTEEXPE = @QTERECPT and not exists (select 1
from lgn_appro
where numcdebes = @CU_NUMCDEBES
and numlgnbes = @NUMLGNBES
and cdstatappro <> 'T'))
begin
-- on Solde la ligne pdt
update lgn_cde_bes_client_pdt
set cdstatlgnbescli = 'S',
hd_cdoper = 'MORB3',
hd_cdsite = 'VILL',
hd_dtheure = getdate(),
dtsoldlgn = GetDate()
where numcdebes = @CU_NUMCDEBES
and numlgnbes = @NUMLGNBES
select @err = @@error
if @err != 0
begin
ROLLBACK TRANSACTION facture
SELECT @O_ERR = "Erreur lors du solde de lgn_cde_bes_client_pdt pour la cde "+convert(varchar,@CU_NUMCDEBES)+" et la ligne "+convert(varchar,@NUMLGNBES)
Select @O_RET = -1
return
end
end*/
Select @NUMLGNFACTCLI = @NUMLGNFACTCLI + 1
FETCH c_lgn_pdt into @CU_CDPDT,@CU_CDREFEXPEML2,@CU_QTEEXPE
end -- end while fetch
close c_lgn_pdt
deallocate cursor c_lgn_pdt
/*Déclaration du cursor pour création des lignes reponse four */
DECLARE c_lgn_rep CURSOR FOR
SELECT numlgnbes,
cdstatlgnweb,
cdpdt,
qtecde
FROM lgn_cde_bes_client_pdt
where numcdebes = @CU_NUMCDEBES
and cdstatlgnbescli = 'V'
and isnull(cdstatlgnweb,'') <> '10'
FOR READ ONLY
OPEN c_lgn_rep
FETCH c_lgn_rep into @CU_NUMLGNBES,@CU_CDSTATLGNARTWEB,@CU_CDPDT,@CU_QTECDE
while (@@sqlstatus != 2)
begin
if @@sqlstatus=1
begin
ROLLBACK TRANSACTION facture
SELECT @O_ERR = 'Erreur curseur c_lgn_rep'
SELECT @O_RET = -1
return
end
--print 'numlgnbes=%1!,cdpdt=%2!',@CU_NUMLGNBES,@CU_CDPDT
-- Modif GB le 14/04/2010 ré-intitialisation
Select @CDREPCLI=NULL,
@LIBREP=NULL,
@LIBPDT=NULL,
@NOMEDITR=NULL,
@QTELIV=NULL,
@NOMCOLLEC=NULL
select @CDREPCLI = cdrepcli
from lgn_appro
where numcdebes = @CU_NUMCDEBES
and numlgnbes = @CU_NUMLGNBES
and dtsairepcli = (select max(dtsairepcli)
from lgn_appro
where numcdebes = @CU_NUMCDEBES
and numlgnbes = @CU_NUMLGNBES)
and cdrepcli <> NULL
--print 'cdrepcli=%1!',@CDREPCLI
if isnull(@CDREPCLI,'') = ''
begin
--print '1'
Select @LIBREP = libstatlgnpartweb
from stat_lgn_part_web
where cdstatlgnpartweb = @CU_CDSTATLGNARTWEB
end
else
begin
--print '2'
Select @LIBREP = libstatlgnpartweb
from stat_lgn_part_web s,
repcli r
where s.cdstatlgnpartweb = r.cdstatlgnpartweb
and r.cdrepcli = @CDREPCLI
end
Select @LIBPDT = libpdt from produit where cdpdt = @CU_CDPDT
Select @NOMEDITR = nomeditr
from editeur e,
produit p
where p.cdpdt = @CU_CDPDT
and e.cdeditr = p.cdeditr
declare curs_aut cursor
for select LTrim(RTrim(IsNull(a.prenomauteur,"") + " " + IsNull(a.nomauteur,"")))
from auteur a,
pdt_auteur pa
where pa.cdpdt = @CU_CDPDT
and pa.cdauteurprinc = 1
and a.numaut = pa.numaut
order by pa.numordre
for read only
select @auteurs_princ = NULL -- Init liste auteurs
open curs_aut
fetch curs_aut into @auteur_princ
while (@@sqlstatus = 0)
begin
if @auteur_princ <> NULL
begin
if @auteurs_princ = NULL
begin
select @auteurs_princ = @auteur_princ
end
else
begin
select @auteurs_princ = substring(@auteurs_princ + " / " + @auteur_princ, 1, 255)
end
end
-- Auteur suivant...
fetch curs_aut
into @auteur_princ
end
close curs_aut
deallocate cursor curs_aut
select @NOMCOLLEC = substring(@auteurs_princ,1,255)
-- Modif LB le 05/02/2010.
/*select @QTELIV = sum (IsNull(qtelivree,0))
from lgn_liv_client_pdt
where numcdebes = @CU_NUMCDEBES
and numlgnbes = @CU_NUMLGNBES*/
Select @QTELIV = IsNull(Sum(IsNull(qteexpe, 0)), 0)
From DECDEPOT..lgn_expe_entrepot_depot_pdt
Where numcdebes = @CU_NUMCDEBES
And cdpdt = @CU_CDPDT
--print ' insert lgn_fact_client_repfour pr ligne= %1! et librep = %2!',@NUMLGNFACTCLI,@LIBREP
Insert into lgn_fact_client_repfour(
numfactcli,
numlgnfactcli,
librepfourcdecli,
cdpdt,
libpdt,
nomeditr,
nomcollec,
qtenonservie,
numcdeclient,
dtcrcdebes,
hd_cdoper,
hd_cdsite,
hd_dtheure
)
values(
@NUMFACTCLI,
@NUMLGNFACTCLI,
@LIBREP,
@CU_CDPDT,
@LIBPDT,
@NOMEDITR,
@NOMCOLLEC,
isnull(@CU_QTECDE,0) - isnull(@QTELIV,0),
@CU_NUMCDEBES,
@CU_DTCRCDEBES,
'MORB3',
'INFO',
getdate()
)
select @err = @@error
if @err != 0
begin
ROLLBACK TRANSACTION facture
SELECT @O_ERR = "Erreur Insert lgn_fact_client_repfour pour la cde "+convert(varchar,@CU_NUMCDEBES)+" et la ligne "+convert(varchar,@CU_NUMLGNBES)
Select @O_RET = -1
return
end
Select @NUMLGNFACTCLI = @NUMLGNFACTCLI + 1
FETCH c_lgn_rep into @CU_NUMLGNBES,@CU_CDSTATLGNARTWEB,@CU_CDPDT,@CU_QTECDE
end -- end while fetch
close c_lgn_rep
deallocate cursor c_lgn_rep
/*Déclaration du cursor pour création des lignes de fact service */
DECLARE c_lgn_serv CURSOR FOR
SELECT ls.numlgnbes,
ls.libserv,
su.numsuividdeexpe,
ls.qtecde,
ls.cdtva,
ls.cdcompta,
ls.cdserv,
ls.txtva,
isnull(ls.txremcli,0),
ls.numlgnempcde,
s.cdfam
FROM DECDEPOT..suivi_dde_expe_morin su,
lgn_cde_bes_client_service ls,
service s
WHERE ls.numlgnbes = su.numlgnbes
and ls.numcdebes = su.numcdebes
and s.cdserv = ls.cdserv
and su.numsuividdeexpe = convert(int,Right(@CU_CDREFEXPEML, (char_length (@CU_CDREFEXPEML)-2)))
and su.qteexpe > 0
and su.cdlgnserv = 1
and su.numcdebes = @CU_NUMCDEBES
FOR READ ONLY
OPEN c_lgn_serv
FETCH c_lgn_serv into @CU_NUMLGNBES,@CU_LIBSERV,@CU_NUMSUIVIDDEEXPE,@CU_QTECDE,@CU_CDTVA,@CU_CDCOMPTA,@CU_CDSERV,@CU_TXTVA,@CU_TREMCLI,@CU_NUMLGNEMPCDE,@CU_CDFAM
while (@@sqlstatus != 2)
begin
if @@sqlstatus=1
begin
ROLLBACK TRANSACTION facture
SELECT @O_ERR = 'Erreur curseur c_lgn_serv'
SELECT @O_RET = -1
return
end
-- Modif GB le 14/04/2010 : ré-initialisation
Select @PVHT=NULL,
@PVNETHT=NULL,
@PVTTC=NULL,
@PVNETTTC=NULL,
@PVDEVPDT=NULL,
@CDDEVISE2=NULL,
@MTACPTEAREGLER=NULL,
@MTACPTEREGLE=NULL,
@MTACPTERADED=NULL,
@pvhtfrf=NULL,
@pvnethtfrf=NULL,
@pvttcfrf=NULL,
@pvnetttcfrf=NULL
/* Appel de la proc calculant les prix de vente */
EXECUTE rc_module_montants
@CU_NUMCDEBES,
@CU_NUMLGNBES,
'S',
@PVHT output,
@PVNETHT output,
@PVTTC output,
@PVNETTTC output,
@PVDEVPDT output,
@CDDEVISE2 output,
@MTACPTEAREGLER output,
@MTACPTEREGLE output,
@MTACPTERADED output,
@pvhtfrf output,
@pvnethtfrf output ,
@pvttcfrf output ,
@pvnetttcfrf output
-- print ' insert lgn_fact_client_serv pr ligne= %1!',@NUMLGNFACTCLI
-- print '%1!,%2!,%3!,%4!,%5!,%6!,%7!',@CU_NUMLGNBES,@CU_LIBSERV,@CU_CDREFEXPEML2,@CU_QTECDE,@CU_CDTVA,@CU_CDCOMPTA,@CU_CDSERV
insert lgn_fact_client_serv
(
numfactcli ,
numlgnfactcli,
libserv ,
numbl ,
qtefact ,
pvht ,
pvnetht ,
pvttc ,
pvnetttc ,
pveuro ,
pvhtfrf ,
pvttcfrf ,
pvnethtfrf ,
pvnetttcfrf ,
cdtva ,
numcdeclient,
dtcrcdebes ,
txdevexport ,
cdcompta ,
cdserv ,
hd_cdoper ,
hd_cdsite ,
hd_dtheure ,
txtva ,
txremcli ,
numlgnempcde
)
values(
@NUMFACTCLI,
@NUMLGNFACTCLI,
@CU_LIBSERV,
@CU_NUMSUIVIDDEEXPE,
@CU_QTECDE,
@PVHT,
@PVNETHT,
@PVTTC,
@PVNETTTC,
@PVTTC/@TXDEVEXPORTEURO,
@pvhtfrf,
@pvttcfrf,
@pvnethtfrf,
@pvnetttcfrf,
(case when @CDEXPORT = 1 then 'E' -- Modif GB le 25/01/2010
else @CU_CDTVA
end),
@CU_NUMCDEBES,
@CU_DTCRCDEBES,
isnull(@CU_TXDEVEXPORT,@TXDEVEXPORTEURO),
@CU_CDCOMPTA,
@CU_CDSERV,
'MORB3',
'INFO',
getdate(),
@CU_TXTVA,
@CU_TREMCLI,
@CU_NUMLGNEMPCDE
)
select @err = @@error
if @err != 0
begin
ROLLBACK TRANSACTION facture
SELECT @O_ERR = "Erreur Insert lgn_fact_client_serv pour la cde "+convert(varchar,@CU_NUMCDEBES)+" et cdserv="+@CU_CDSERV
Select @O_RET = -1
return
end
-- Financement participatif
If @CU_CDSERV = 'FP'
Begin
Insert Into arrondi_solidaire (
cdsite,
numticket,
dtticket,
numclient,
mtarrondi,
cdannul
)
Select
@CDSITE,
Convert(VarChar(8), @NUMFACTCLI),
@CU_DTEEXPE,
@NUMCLIENT,
@PVTTC * @CU_QTECDE,
0
select @err = @@error
if @err != 0
begin
ROLLBACK TRANSACTION facture
SELECT @O_ERR = "Erreur Insert arrondi_solidaire pour la cde "+convert(varchar,@CU_NUMCDEBES)+" et cdserv="+@CU_CDSERV
Select @O_RET = -1
return
end
End
-- Autres
Else
Begin
-- print ' insert histo_ventes pr ligne= %1!',@NUMLGNFACTCLI
select @DTEMISFACT = dtemisfact from facture_client where numfactcli = @NUMFACTCLI
insert histo_ventes
(
dtmvt ,
typmvt ,
cdtypvente ,
cdfam ,
cdpdt ,
mtttc ,
mtrms ,
mttva ,
pmp ,
numclient ,
cdtypcarte ,
numcarte ,
nummoypai ,
qtevendu ,
cdsite ,
dtvaleur ,
cdreforigmvt ,
numcdebes
)
Values(
getdate(),
'03',
'05',
@CU_CDFAM,
@CU_CDSERV,
@PVTTC * @CU_QTECDE,
(@PVTTC - @PVNETTTC)* @CU_QTECDE,
(@PVNETTTC - @PVNETHT)* @CU_QTECDE,
NULL,
@NUMCLIENT,
@CDTYPCARTE,
@NUMCARTEREDUC,
NULL,
@CU_QTECDE,
@CDSITE,
getdate() , -- @DTEMISFACT , -- getdate(),
convert(varchar,@NUMFACTCLI),
@CU_NUMCDEBES
)
select @err = @@error
if @err != 0
begin
ROLLBACK TRANSACTION facture
SELECT @O_ERR = "Erreur Insert histo_ventes"
Select @O_RET = -1
return
end
End
-- on Solde la ligne cde service
update lgn_cde_bes_client_service
set cdstatlgnbescli = 'S',
hd_cdoper = 'MORB3',
hd_cdsite = 'INFO',
hd_dtheure = getdate(),
qtefact = @CU_QTECDE,
dtsoldlgn = GetDate()
where numcdebes = @CU_NUMCDEBES
and numlgnbes = @CU_NUMLGNBES
select @err = @@error
if @err != 0
begin
ROLLBACK TRANSACTION facture
SELECT @O_ERR = "Erreur lors du solde de lgn_cde_bes_client_pdt pour la cde "+convert(varchar,@CU_NUMCDEBES)+" et la ligne "+convert(varchar,@NUMLGNBES)
Select @O_RET = -1
return
end
Select @NUMLGNFACTCLI = @NUMLGNFACTCLI + 1
FETCH c_lgn_serv into @CU_NUMLGNBES,@CU_LIBSERV,@CU_NUMSUIVIDDEEXPE,@CU_QTECDE,@CU_CDTVA,@CU_CDCOMPTA,@CU_CDSERV,@CU_TXTVA,@CU_TREMCLI,@CU_NUMLGNEMPCDE,@CU_CDFAM
end -- end while fetch
close c_lgn_serv
deallocate cursor c_lgn_serv
delete from #pied_fact
exec rc_fac_constit_pied_fact_i @NUMFACTCLI, @CU_NUMCDEBES, @O_RETOUR output
if @O_RETOUR != 0
begin
ROLLBACK TRANSACTION facture
SELECT @O_ERR = "Erreur lors de la creation du pied de la facture "+convert(varchar,@CU_NUMCDEBES)
Select @O_RET = -1
return
end
-- Si toutes les lignes sont soldées ou anulées
if (not exists (select 1 from lgn_cde_bes_client_pdt
where numcdebes = @CU_NUMCDEBES
and cdstatlgnbescli not in ('S','A'))
and not exists (select 1 from lgn_cde_bes_client_service
where numcdebes = @CU_NUMCDEBES
and cdstatlgnbescli not in ('S','A')))
begin
Update cde_bes_client
set cdstatcdebescli = 'S',
dtsoldcde = getdate(),
hd_cdoper = 'MORB3',
hd_cdsite = 'INFO',
hd_dtheure = getdate()
where numcdebes = @CU_NUMCDEBES
select @err = @@error
if @err != 0
begin
ROLLBACK TRANSACTION facture
SELECT @O_ERR = "Erreur update cde_bes_client pour la cde "+convert(varchar,@CU_NUMCDEBES)
select @O_RET = -1
return
end
end
/* On met a jour les lignes expe */
Update DECDEPOT..lgn_expe_entrepot_depot_pdt
set numfactcli = @NUMFACTCLI,
dtfactcli = getdate(),
hd_cdoper = 'MORB3',
hd_cdsite = 'INFO',
hd_dtheure = getdate()
where numcdebes = @CU_NUMCDEBES
and cdrefexpeml = @CU_CDREFEXPEML
select @err = @@error
if @err != 0
begin
ROLLBACK TRANSACTION facture
SELECT @O_ERR = "Erreur update DECDEPOT..lgn_expe_entrepot_depot_pdt pour la cde "+convert(varchar,@CU_NUMCDEBES)
select @O_RET = -1
return
end
----------------------------------
-- JD le 27/04/2012 INSERT pour envoi adelya (traitement C#) mise à jour de la cagnote
If @CU_CDEDECITREFR = 1
Begin
INSERT EXPORT_FLUX..echange_adelya(cdtypenvoi,cdenregtraite,numclient,numfactcli,dtinsert)
SELECT
"FACT",
0,
@NUMCLIENT,
@NUMFACTCLI,
getdate()
-- Gestion des erreurs
select @err = @@error
if @err != 0
begin
ROLLBACK TRANSACTION facture
SELECT @O_ERR = "Erreur insert EXPORT_FLUX..echange_adelya pour fact num: "+convert(varchar,@NUMFACTCLI)
select @O_RET = -1
return
end
End
COMMIT TRANSACTION facture
FETCH c_list_cde into @CU_NUMCDEBES,@CU_DTCRCDEBES,@CU_TXDEVEXPORT,@CU_CDREFEXPEML, @CU_IDRELAIS, @CU_CDMODLIVWEB, @CU_DTVALCDEWEB, @CU_CDPARTDECITRE,@CU_DTEEXPE, @CU_CDEDECITREFR
end -- end while fetch,
close c_list_cde
deallocate cursor c_list_cde
/* On supprime les factures traitées */
/*Delete from DECDEPOT..expe_morin_a_facturer
where cdenregtraite = 1*/
select @err = @@error
if @err != 0
begin
SELECT @O_ERR = "Erreur delete DECDEPOT..expe_morin_a_facturer pour la cde "+convert(varchar,@CU_NUMCDEBES)
select @O_RET = -1
return
end
go
grant execute on b_fac_cli_web to UTIL_PB
go
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment