Le Forum des Utilisateurs Sage

de **JFT** » Jeu 16 Oct 2008 16:57

Bonjour tout le monde,

Je ne suis pas le premier à poser la question, mais cela fait presque une heure que je cherche sur le Net sans réponse...

Donc pour le 1000ème message dans cette partie du forum, la question est : comment qu'on transforme un document en SQL (bordel) (siouplé) ? Ou en Objets Métiers v3 au pire ? Ou même avec ODBC si vous êtes courageux ?

Il paraît qu'il faut recopier le document à la mimine et supprimer l'original, mais j'aurais voulu avoir qq exemples en SQL pour me guider un peu ! Comment mettre en place la référence vers le doc original ? Toussa quoi !

Merci !

de **ricil78** » Lun 24 Nov 2008 13:58

SQL
pour moi en sql à part un développeur chez sage je pense que personne ne voudrait tenter l'aventure.
car en sql pure aucun control de la base donc tu risques qu'une seul chose s'est de flinguer la base (et le pire c'est que tu risques de ne pas t'en appercevoir tout de suite).

ODBC (le plus sur que SQL car le driver effectue des controles de cohérence)
il faut créer ton doc (récup les infos et les remettres dans l'autre) et supprimer l'autre, à chaque fois les tables suivantes sont impactées (F_DOCENTETE ET F_FOCLIGNE)

en Objet V3 je sais que c'est possible mais pas encore réalisé.
regarde la doc ça doit être indiqué

de **AuBoutDuRouleau** » Mer 26 Nov 2008 18:56

Bonjour,

Effectivement, en SQL direct c'est très délicat : il faut faire manuellement tous les contrôles et modifications effectués par la Gestion Commerciale... Ce qui revient à redévelopper une partie de la Gestion Commerciale, ce qui me semble idiot (mais cet avis n'engage que moi !).
Le strict minimum est de modifier la valeur du champ DO_Type, d'abord sur l'en-tête de document (table F_DocEntete) puis dans toutes les lignes du document (table F_DocLigne). Par exemple, pour les documents des ventes, 1 correspond à un bon de commande et 3 à un bon de livraison.
Mais pour transformer un BC en BL, il faut aussi, au minimum :

- changer le numéro de pièce en allant récupérer le prochain numéro de bon de commande pour la souche concernée (stocké dans F_DocCurrentPiece)

- calculer le numéro suivant et mettre à jour F_DocCurrentPiece

- passer l'ancienne valeur de DO_Piece dans DO_PieceBC (ce qui permet de regénérer le BC si on supprime le BL)

- modifier les valeurs des stocks (dans F_ArtStock et F_GamStock) pour les articles suivis en stock

- ...Mais cela n'est valable que si l'intégralité du BC est transformable en BL (pas de reliquat...). Sinon, cela se complique grandement...

Franchement, il vaut mieux utiliser l'ODBC ou les Objets Métier qui vont se charger de faire tous les contrôles et modifications nécessaires.

Cordialement.

de **ricil78** » Mer 26 Nov 2008 19:04

Erratum :

d'après un autre poste http://www.zoom.fr/phpBB2/post16167.html#p16167

il semble que la transformation de doc na pas encore était mis en V3.
je confirmerai cela dès que j'aurai eu sage au téléphone.

mais c'est toujours plus simple et plus sure de faire ça en objet métier qu'en ODBC

de **stephane3381** » Jeu 27 Nov 2008 10:08

bonjour,
et vous oubliez aussi F_artstock si vous passez par sql....

de **ricil78** » Jeu 27 Nov 2008 11:09

j'ai eu sage est effectivement pas de transformation de document en V3 et il semble qu'il ne faut pas compter dessus pour la V4. :cry:

de **stephane3381** » Jeu 27 Nov 2008 15:13

- la transformation de doc peut se faire via sql à la seule et unique condition de "taper" toutes les tables nécessaire (pas infaisable mais implique de la rigeur et de la connaissance des tables)
- par l'odbc c'est lui meme qui fait les contrôles de cohérence des informations donc beaucoup moins de risque.

même des prestataires très connus et réputés utiliseront tjs l'odbc.

de **Andry** » Mer 4 Fév 2009 09:04

Lorsque j'ai créer mon appli pour manipuler la base SAGE SQL, voici ce que j'ai fait :
- Lancer une trace au niveau du Serveur SQL
- Faire la même manipulation au niveau de SAGE
- Récupérer le trace obtenu
- Analyser les requêtes dans le trace.
J'ai mis a peu près un an pour étudier la structure des tables SAGE en se basant sur le fichier pdf fourni.
Mais le constat est que c'est vraiment le bordel. (Ceci n'engage que moi même).

de **Cloonies** » Lun 4 Avr 2011 11:30

Voila une procédure que j'utilise pour creer deux facture par rapport à une commande

Je passe par des tables temporaire que je supprimer à la fin.

Code: Tout sélectionner: CREATE PROCEDURE [dbo].[SPE_LOGE_GAC_TRAI_VTE_FAC_Creation_Facture_a_partir_commande] (@sNumeroDeb VARCHAR(40),@sNumeroFin VARCHAR(40)) AS BEGIN DECLARE @DO_PIECE_DEB VARCHAR(40) DECLARE @DO_PIECE_FIN VARCHAR(40) SET @DO_PIECE_DEB = @sNumeroDeb SET @DO_PIECE_FIN = @sNumeroFin ---ETAPE 1 ------------------------------------------------------------------------------- -----ETAPE 1.1----------------------------------------------------------------------------- --------- CREATION DE LA TABLE TEMPORAIRE SPE_DOC_ENTETE ------------------------------------------------------------------------------- ------------------------------------------------------------------------------- /****** Object: Table [dbo].[F_DOCENTETE] Script Date: 01/25/2011 10:48:20 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SPE_F_DOCENTETE]') AND type in (N'U')) DROP TABLE [dbo].[SPE_F_DOCENTETE] /****** Object: Table [dbo].[SPE_F_DOCENTETE] Script Date: 01/25/2011 10:48:20 ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON SET ARITHABORT ON CREATE TABLE [dbo].[SPE_F_DOCENTETE]( [ID] [int] identity (1,1) NOT NULL, [DO_Domaine] [smallint] NULL, [DO_Type] [smallint] NULL, [DO_Piece] [varchar](9) NULL, [cbDO_Piece] AS (CONVERT([varbinary](10),space((10)-(len(isnull([DO_Piece],''))+(1)))+isnull([DO_Piece],''),0)), [DO_Date] [smalldatetime] NULL, [DO_Ref] [varchar](17) NULL, [DO_Tiers] [varchar](17) NULL, [cbDO_Tiers] AS (CONVERT([varbinary](18),isnull([DO_Tiers],''),0)), [CO_No] [int] NULL, [cbCO_No] [int] NULL, [DO_Period] [smallint] NULL, [DO_Devise] [smallint] NULL, [DO_Cours] [numeric](24, 6) NULL, [DE_No] [int] NULL, [cbDE_No] [int] NULL, [LI_No] [int] NULL, [cbLI_No] [int] NULL, [CT_NumPayeur] [varchar](17) NULL, [cbCT_NumPayeur] AS (CONVERT([varbinary](18),isnull([CT_NumPayeur],''),0)), [DO_Expedit] [smallint] NULL, [DO_NbFacture] [smallint] NULL, [DO_BLFact] [smallint] NULL, [DO_TxEscompte] [numeric](24, 6) NULL, [DO_Reliquat] [smallint] NULL, [DO_Imprim] [smallint] NULL, [CA_Num] [varchar](13) NULL, [cbCA_Num] AS (CONVERT([varbinary](14),isnull([CA_Num],''),0)), [DO_Coord01] [varchar](25) NULL, [DO_Coord02] [varchar](25) NULL, [DO_Coord03] [varchar](25) NULL, [DO_Coord04] [varchar](25) NULL, [DO_Souche] [smallint] NULL, [DO_DateLivr] [smalldatetime] NULL, [DO_Condition] [smallint] NULL, [DO_Tarif] [smallint] NULL, [DO_Colisage] [smallint] NULL, [DO_TypeColis] [smallint] NULL, [DO_Transaction] [smallint] NULL, [DO_Langue] [smallint] NULL, [DO_Ecart] [numeric](24, 6) NULL, [DO_Regime] [smallint] NULL, [N_CatCompta] [smallint] NULL, [DO_Ventile] [smallint] NULL, [AB_No] [int] NULL, [DO_DebutAbo] [smalldatetime] NULL, [DO_FinAbo] [smalldatetime] NULL, [DO_DebutPeriod] [smalldatetime] NULL, [DO_FinPeriod] [smalldatetime] NULL, [CG_Num] [varchar](13) NULL, [cbCG_Num] AS (CONVERT([varbinary](14),isnull([CG_Num],''),0)), [DO_Statut] [smallint] NULL, [DO_Heure] [char](9) NULL, [CA_No] [int] NULL, [cbCA_No] [int] NULL, [CO_NoCaissier] [int] NULL, [cbCO_NoCaissier] [int] NULL, [DO_Transfere] [smallint] NULL, [DO_Cloture] [smallint] NULL, [DO_NoWeb] [varchar](17) NULL, [DO_Attente] [smallint] NULL, [DO_Provenance] [smallint] NULL, [CA_NumIFRS] [varchar](13) NULL, [MR_No] [int] NULL, [DO_TypeFrais] [smallint] NULL, [DO_ValFrais] [numeric](24, 6) NULL, [DO_TypeLigneFrais] [smallint] NULL, [DO_TypeFranco] [smallint] NULL, [DO_ValFranco] [numeric](24, 6) NULL, [DO_TypeLigneFranco] [smallint] NULL, [DO_Taxe1] [numeric](24, 6) NULL, [DO_TypeTaux1] [smallint] NULL, [DO_TypeTaxe1] [smallint] NULL, [DO_Taxe2] [numeric](24, 6) NULL, [DO_TypeTaux2] [smallint] NULL, [DO_TypeTaxe2] [smallint] NULL, [DO_Taxe3] [numeric](24, 6) NULL, [DO_TypeTaux3] [smallint] NULL, [DO_TypeTaxe3] [smallint] NULL, [DO_MajCpta] [smallint] NULL, [DO_Motif] [varchar](69) NULL, [CT_NumCentrale] [varchar](17) NULL, [cbCT_NumCentrale] AS (CONVERT([varbinary](18),isnull([CT_NumCentrale],''),0)), [DO_Contact] [varchar](35) NULL, [DO_FactureElec] [smallint] NULL, [DO_TypeTransac] [smallint] NULL, [cbProt] [smallint] NULL, [cbMarq] [int] NOT NULL, [cbCreateur] [char](4) NULL, [cbModification] [smalldatetime] NULL, [cbReplication] [int] NULL, [cbFlag] [smallint] NULL ) ------------------------------------------------------------------------------- ------ETAPE 1.2---------------------------------------------------------------------------- --------- CREATION DE LA TABLE TEMPORAIRE SPE_DOC_LIGNE ------------------------------------------------------------------------------- ------------------------------------------------------------------------------- /****** Object: Table [dbo].[F_DOCLIGNE] Script Date: 01/25/2011 11:02:16 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SPE_F_DOCLIGNE]') AND type in (N'U')) DROP TABLE [dbo].[SPE_F_DOCLIGNE] /****** Object: Table [dbo].[F_DOCLIGNE] Script Date: 01/25/2011 11:02:16 ******/ CREATE TABLE [dbo].[SPE_F_DOCLIGNE]( [ID] [int] identity (1,1) NOT NULL, [DO_Domaine] [smallint] NULL, [DO_Type] [smallint] NOT NULL, [CT_Num] [varchar](17) NULL, [cbCT_Num] AS (CONVERT([varbinary](18),isnull([CT_Num],''),0)), [DO_Piece] [varchar](9) NOT NULL, [cbDO_Piece] AS (CONVERT([varbinary](10),space((10)-(len([DO_Piece])+(1)))+[DO_Piece],0)), [DL_PieceBC] [varchar](9) NULL, [cbDL_PieceBC] AS (CONVERT([varbinary](10),space((10)-(len(isnull([DL_PieceBC],''))+(1)))+isnull([DL_PieceBC],''),0)), [DL_PieceBL] [varchar](9) NULL, [cbDL_PieceBL] AS (CONVERT([varbinary](10),space((10)-(len(isnull([DL_PieceBL],''))+(1)))+isnull([DL_PieceBL],''),0)), [DO_Date] [smalldatetime] NULL, [DL_DateBC] [smalldatetime] NULL, [DL_DateBL] [smalldatetime] NULL, [DL_Ligne] [int] NULL, [DO_Ref] [varchar](17) NULL, [DL_TNomencl] [smallint] NULL, [DL_TRemPied] [smallint] NULL, [DL_TRemExep] [smallint] NULL, [AR_Ref] [varchar](19) NULL, [cbAR_Ref] AS (CONVERT([varbinary](20),isnull([AR_Ref],''),0)), [DL_Design] [varchar](69) NULL, [DL_Qte] [numeric](24, 6) NULL, [DL_QteBC] [numeric](24, 6) NULL, [DL_QteBL] [numeric](24, 6) NULL, [DL_PoidsNet] [numeric](24, 6) NULL, [DL_PoidsBrut] [numeric](24, 6) NULL, [DL_Remise01REM_Valeur] [numeric](24, 6) NULL, [DL_Remise01REM_Type] [smallint] NULL, [DL_Remise02REM_Valeur] [numeric](24, 6) NULL, [DL_Remise02REM_Type] [smallint] NULL, [DL_Remise03REM_Valeur] [numeric](24, 6) NULL, [DL_Remise03REM_Type] [smallint] NULL, [DL_PrixUnitaire] [numeric](24, 6) NULL, [DL_PUBC] [numeric](24, 6) NULL, [DL_Taxe1] [numeric](24, 6) NULL, [DL_TypeTaux1] [smallint] NULL, [DL_TypeTaxe1] [smallint] NULL, [DL_Taxe2] [numeric](24, 6) NULL, [DL_TypeTaux2] [smallint] NULL, [DL_TypeTaxe2] [smallint] NULL, [CO_No] [int] NULL, [cbCO_No] [int] NULL, [AG_No1] [int] NULL, [AG_No2] [int] NULL, [DL_PrixRU] [numeric](24, 6) NULL, [DL_CMUP] [numeric](24, 6) NULL, [DL_MvtStock] [smallint] NULL, [DT_No] [int] NULL, [cbDT_No] [int] NULL, [AF_RefFourniss] [varchar](19) NULL, [cbAF_RefFourniss] AS (CONVERT([varbinary](20),isnull([AF_RefFourniss],''),0)), [EU_Enumere] [varchar](21) NULL, [EU_Qte] [numeric](24, 6) NULL, [DL_TTC] [smallint] NULL, [DE_No] [int] NULL, [cbDE_No] [int] NULL, [DL_NoRef] [smallint] NULL, [DL_TypePL] [smallint] NULL, [DL_PUDevise] [numeric](24, 6) NULL, [DL_PUTTC] [numeric](24, 6) NULL, [DL_No] [int] NULL, [DO_DateLivr] [smalldatetime] NULL, [CA_Num] [varchar](13) NULL, [cbCA_Num] AS (CONVERT([varbinary](14),isnull([CA_Num],''),0)), [DL_Taxe3] [numeric](24, 6) NULL, [DL_TypeTaux3] [smallint] NULL, [DL_TypeTaxe3] [smallint] NULL, [DL_Frais] [numeric](24, 6) NULL, [DL_Valorise] [smallint] NULL, [AR_RefCompose] [varchar](19) NULL, [DL_NonLivre] [smallint] NULL, [AC_RefClient] [varchar](19) NULL, [DL_MontantHT] [numeric](24, 6) NULL, [DL_MontantTTC] [numeric](24, 6) NULL, [DL_FactPoids] [smallint] NULL, [DL_Escompte] [smallint] NULL, [DL_PiecePL] [varchar](9) NULL, [cbDL_PiecePL] AS (CONVERT([varbinary](10),space((10)-(len(isnull([DL_PiecePL],''))+(1)))+isnull([DL_PiecePL],''),0)), [DL_DatePL] [smalldatetime] NULL, [DL_QtePL] [numeric](24, 6) NULL, [DL_NoColis] [varchar](19) NULL, [DL_NoLink] [int] NULL, [cbDL_NoLink] [int] NULL, [RP_Code] [varchar](11) NULL, [cbRP_Code] AS (CONVERT([varbinary](12),isnull([RP_Code],''),0)), [DL_QteRessource] [int] NULL, [DL_DateAvancement] [smalldatetime] NULL, [cbProt] [smallint] NULL, [cbMarq] [int], [cbCreateur] [char](4) NULL, [cbModification] [smalldatetime] NULL, [cbReplication] [int] NULL, [cbFlag] [smallint] NULL ) ------------------------------------------------------------------------------- -------ETAPE 1.3--------------------------------------------------------------------------- --------- CREATION DE LA TABLE TEMPORAIRE SPE_DOC_REGLEMENT ------------------------------------------------------------------------------- ------------------------------------------------------------------------------- /****** Object: Table [dbo].[F_DOCREGL] Script Date: 01/25/2011 13:42:54 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SPE_F_DOCREGL]') AND type in (N'U')) DROP TABLE [dbo].[SPE_F_DOCREGL] /****** Object: Table [dbo].[SPE_F_DOCREGL] Script Date: 01/25/2011 13:42:54 ******/ CREATE TABLE [dbo].[SPE_F_DOCREGL]( [ID] [int] identity (1,1) NOT NULL, [DR_No] [int] NULL, [DO_Domaine] [smallint] NULL, [DO_Type] [smallint] NULL, [DO_Piece] [varchar](9) NULL, [cbDO_Piece] AS (CONVERT([varbinary](10),space((10)-(len(isnull([DO_Piece],''))+(1)))+isnull([DO_Piece],''),0)), [DR_TypeRegl] [smallint] NULL, [DR_Date] [smalldatetime] NULL, [DR_Libelle] [varchar](35) NULL, [DR_Pourcent] [numeric](24, 6) NULL, [DR_Montant] [numeric](24, 6) NULL, [DR_MontantDev] [numeric](24, 6) NULL, [DR_Equil] [smallint] NULL, [EC_No] [int] NULL, [cbEC_No] [int] NULL, [DR_Regle] [smallint] NULL, [N_Reglement] [smallint] NULL, [cbProt] [smallint] NULL, [cbMarq] [int] NOT NULL, [cbCreateur] [char](4) NULL, [cbModification] [smalldatetime] NULL, [cbReplication] [int] NULL, [cbFlag] [smallint] NULL ) ---ETAPE 2 ------ETAPE 2.1---------------------------------------------------------------------------- --------- Insertion des valeurs ---------SPE_DOC_ENTETE ------------------------------------------------------------------------------- Insert into SPE_F_DOCENTETE (DO_Domaine, DO_Type, DO_Piece, DO_Date, DO_Ref, DO_Tiers, CO_No, cbCO_No, DO_Period, DO_Devise, DO_Cours, DE_No, cbDE_No, LI_No, cbLI_No, CT_NumPayeur, DO_Expedit, DO_NbFacture, DO_BLFact, DO_TxEscompte, DO_Reliquat, DO_Imprim, CA_Num, DO_Coord01, DO_Coord02, DO_Coord03, DO_Coord04, DO_Souche, DO_DateLivr, DO_Condition, DO_Tarif, DO_Colisage, DO_TypeColis, DO_Transaction, DO_Langue, DO_Ecart, DO_Regime, N_CatCompta, DO_Ventile, AB_No, DO_DebutAbo, DO_FinAbo, DO_DebutPeriod, DO_FinPeriod, CG_Num, DO_Statut, DO_Heure, CA_No, cbCA_No, CO_NoCaissier, cbCO_NoCaissier, DO_Transfere, DO_Cloture, DO_NoWeb, DO_Attente, DO_Provenance, CA_NumIFRS, MR_No, DO_TypeFrais, DO_ValFrais, DO_TypeLigneFrais, DO_TypeFranco, DO_ValFranco, DO_TypeLigneFranco, DO_Taxe1, DO_TypeTaux1, DO_TypeTaxe1, DO_Taxe2, DO_TypeTaux2, DO_TypeTaxe2, DO_Taxe3, DO_TypeTaux3, DO_TypeTaxe3, DO_MajCpta, DO_Motif, CT_NumCentrale, DO_Contact, DO_FactureElec, DO_TypeTransac, cbProt, cbMarq, cbCreateur, cbModification, cbReplication, cbFlag ) Select DO_Domaine, DO_Type, DO_Piece, DO_Date, DO_Ref, DO_Tiers, CO_No, cbCO_No, DO_Period, DO_Devise, DO_Cours, DE_No, cbDE_No, LI_No, cbLI_No, CT_NumPayeur, DO_Expedit, DO_NbFacture, DO_BLFact, DO_TxEscompte, DO_Reliquat, DO_Imprim, CA_Num, DO_Coord01, DO_Coord02, DO_Coord03, DO_Coord04, DO_Souche, DO_DateLivr, DO_Condition, DO_Tarif, DO_Colisage, DO_TypeColis, DO_Transaction, DO_Langue, DO_Ecart, DO_Regime, N_CatCompta, DO_Ventile, AB_No, DO_DebutAbo, DO_FinAbo, DO_DebutPeriod, DO_FinPeriod, CG_Num, DO_Statut, DO_Heure, CA_No, cbCA_No, CO_NoCaissier, cbCO_NoCaissier, DO_Transfere, DO_Cloture, DO_NoWeb, DO_Attente, DO_Provenance, CA_NumIFRS, MR_No, DO_TypeFrais, DO_ValFrais, DO_TypeLigneFrais, DO_TypeFranco, DO_ValFranco, DO_TypeLigneFranco, DO_Taxe1, DO_TypeTaux1, DO_TypeTaxe1, DO_Taxe2, DO_TypeTaux2, DO_TypeTaxe2, DO_Taxe3, DO_TypeTaux3, DO_TypeTaxe3, DO_MajCpta, DO_Motif, CT_NumCentrale, DO_Contact, DO_FactureElec, DO_TypeTransac, cbProt, cbMarq, cbCreateur, cbModification, cbReplication, cbFlag from F_DOCENTETE E Where E.DO_Type = 1 and E.Do_Domaine = 0 and E.DO_piece BETWEEN @DO_PIECE_DEB and @DO_PIECE_FIN ------ETAPE 2.2---------------------------------------------------------------------------- --------- Insertion des valeurs ---------SPE_DOC_LIGNE ------------------------------------------------------------------------------- Insert into SPE_F_DOCLIGNE (DO_Domaine, DO_Type, CT_Num, DO_Piece, DL_PieceBC, DL_PieceBL, DO_Date, DL_DateBC, DL_DateBL, DL_Ligne, DO_Ref, DL_TNomencl, DL_TRemPied, DL_TRemExep, AR_Ref, DL_Design, DL_Qte, DL_QteBC, DL_QteBL, DL_PoidsNet, DL_PoidsBrut, DL_Remise01REM_Valeur, DL_Remise01REM_Type, DL_Remise02REM_Valeur, DL_Remise02REM_Type, DL_Remise03REM_Valeur, DL_Remise03REM_Type, DL_PrixUnitaire, DL_PUBC, DL_Taxe1, DL_TypeTaux1, DL_TypeTaxe1, DL_Taxe2, DL_TypeTaux2, DL_TypeTaxe2, CO_No, cbCO_No, AG_No1, AG_No2, DL_PrixRU, DL_CMUP, DL_MvtStock, DT_No, cbDT_No, AF_RefFourniss, EU_Enumere, EU_Qte, DL_TTC, DE_No, cbDE_No, DL_NoRef, DL_TypePL, DL_PUDevise, DL_PUTTC, DL_No, DO_DateLivr, CA_Num, DL_Taxe3, DL_TypeTaux3, DL_TypeTaxe3, DL_Frais, DL_Valorise, AR_RefCompose, DL_NonLivre, AC_RefClient, DL_MontantHT, DL_MontantTTC, DL_FactPoids, DL_Escompte, DL_PiecePL, DL_DatePL, DL_QtePL, DL_NoColis, DL_NoLink, cbDL_NoLink, RP_Code, DL_QteRessource, DL_DateAvancement, cbProt, cbMarq, cbCreateur, cbModification, cbReplication, cbFlag ) Select DO_Domaine, DO_Type, CT_Num, DO_Piece, DL_PieceBC, DL_PieceBL, DO_Date, DL_DateBC, DL_DateBL, DL_Ligne, DO_Ref, DL_TNomencl, DL_TRemPied, DL_TRemExep, AR_Ref, DL_Design, DL_Qte, DL_QteBC, DL_QteBL, DL_PoidsNet, DL_PoidsBrut, DL_Remise01REM_Valeur, DL_Remise01REM_Type, DL_Remise02REM_Valeur, DL_Remise02REM_Type, DL_Remise03REM_Valeur, DL_Remise03REM_Type, DL_PrixUnitaire, DL_PUBC, DL_Taxe1, DL_TypeTaux1, DL_TypeTaxe1, DL_Taxe2, DL_TypeTaux2, DL_TypeTaxe2, CO_No, cbCO_No, AG_No1, AG_No2, DL_PrixRU, DL_CMUP, 3 as DL_MvtStock, DT_No, cbDT_No, AF_RefFourniss, EU_Enumere, EU_Qte, DL_TTC, DE_No, cbDE_No, DL_NoRef, DL_TypePL, DL_PUDevise, DL_PUTTC, DL_No, DO_DateLivr, CA_Num, DL_Taxe3, DL_TypeTaux3, DL_TypeTaxe3, DL_Frais, DL_Valorise, AR_RefCompose, DL_NonLivre, AC_RefClient, DL_MontantHT, DL_MontantTTC, DL_FactPoids, DL_Escompte, DL_PiecePL, DL_DatePL, DL_QtePL, DL_NoColis, DL_NoLink, cbDL_NoLink, RP_Code, DL_QteRessource, DL_DateAvancement, cbProt, cbMarq, cbCreateur, cbModification, cbReplication, cbFlag From F_DOCLIGNE as L Where L.DO_Type = 1 and L.Do_Domaine = 0 --and L.DO_piece in ('CBC32329','CBC32330') and L.DO_piece BETWEEN @DO_PIECE_DEB and @DO_PIECE_FIN ---------ETAPE 2.3------------------------------------------------------------------------- --------- Insertion des valeurs ---------SPE_DOC_REGELEMENT ------------------------------------------------------------------------------- INSERT INTO SPE_F_DOCREGL ( DR_No, DO_Domaine, DO_Type, DO_Piece, DR_TypeRegl, DR_Date, DR_Libelle, DR_Pourcent, DR_Montant, DR_MontantDev, DR_Equil, EC_No, cbEC_No, DR_Regle, N_Reglement, cbProt, cbMarq, cbCreateur, cbModification, cbReplication, cbFlag ) Select DR_No, DO_Domaine, DO_Type, DO_Piece, DR_TypeRegl, DR_Date, DR_Libelle, DR_Pourcent, DR_Montant, DR_MontantDev, DR_Equil, EC_No, cbEC_No, DR_Regle, N_Reglement, cbProt, cbMarq, cbCreateur, cbModification, cbReplication, cbFlag From F_DOCREGL R Where R.DO_Type = 1 and R.Do_Domaine = 0 and R.DO_piece BETWEEN @DO_PIECE_DEB and @DO_PIECE_FIN ---ETAPE 3 --------ETAPE 3.1-------------------------------------------------------------------------- --------- Insertion des valeurs ---------SPE_DOC_Reglement --------- Creation Facture CONSOMABLE ---------INDICE FACTUREC ------------------------------------------------------------------------------- ------ETAPE 3.1.1------------------------------------------------------------------------------- ---------------------------------------------------------------------------------- -----Insertion des entetes de type consomables et autres ---------------------------------------------------------------------------------- ---------------------------------------------------------------------------------- INSERT INTO F_DOCENTETE (AB_NO,CA_NO,CA_NUM,CG_NUM,CT_NUMPAYEUR, DE_NO,DO_ATTENTE,DO_BLFACT,DO_CLOTURE,DO_COLISAGE, DO_CONDITION,DO_COORD01,DO_COORD02,DO_COORD03,DO_COORD04, DO_COURS,DO_DATE,DO_DATELIVR,DO_DEBUTABO,DO_DEBUTPERIOD, DO_DEVISE,DO_DOMAINE,DO_ECART,DO_EXPEDIT,DO_FINABO, DO_FINPERIOD,DO_HEURE,DO_IMPRIM,DO_LANGUE,DO_NBFACTURE, DO_NOWEB,DO_PERIOD,DO_PIECE,DO_REF,DO_REGIME, DO_RELIQUAT,DO_SOUCHE,DO_STATUT,DO_TARIF,DO_TIERS, DO_TRANSACTION,DO_TRANSFERE,DO_TXESCOMPTE,DO_TYPE,DO_TYPECOLIS, DO_VENTILE,LI_NO,N_CATCOMPTA,CO_NO,CO_NOCAISSIER, DO_Provenance,CA_NumIFRS,MR_No,DO_TypeFrais,DO_ValFrais,DO_TypeLigneFrais, DO_TypeFranco,DO_ValFranco,DO_TypeLigneFranco,DO_Taxe1,DO_TypeTaux1,DO_TypeTaxe1, DO_Taxe2,DO_TypeTaux2,DO_TypeTaxe2,DO_Taxe3,DO_TypeTaux3,DO_TypeTaxe3,DO_MajCpta,DO_Motif,CT_NumCentrale) select distinct 0,0,'',CG_Num,CT_NumPayeur, 1,0,0,0,1, 1,E.DO_PIECE as DO_COORD01,DO_COORD02,DO_COORD03,DO_COORD04, 0,cast(Floor(cast(getdate()as Float)) as smallDateTime ),cast(Floor(cast(getdate()as Float)) as smallDateTime ),Null,Null, 0,0, 0,1,Null, Null,Null,0,0,1, '',1,(select left(DC_Piece,3)+convert(varchar,(right(DC_Piece,5)-1+E.ID)) from [F_DOCCURRENTPIECE] where DC_Domaine = 0 and DC_IdCol = 6 and DC_Souche = 0),E.DO_REF,21, 0,0,2,2,DO_Tiers, 11,0, DO_TXESCOMPTE,6,1, 0,14,1,4,0, DO_Provenance,CA_NumIFRS,MR_No,DO_TypeFrais,DO_ValFrais,DO_TypeLigneFrais, DO_TypeFranco,DO_ValFranco,DO_TypeLigneFranco,DO_Taxe1,DO_TypeTaux1,DO_TypeTaxe1, DO_Taxe2,DO_TypeTaux2,DO_TypeTaxe2,DO_Taxe3,DO_TypeTaux3,DO_TypeTaxe3,DO_MajCpta,DO_Motif,CT_NumCentrale from SPE_F_DOCentete as E Left outer join SPE_F_DOCligne as L On E.Do_Piece = L.Do_Piece and E.DO_Type=L.DO_Type and E.Do_Domaine=L.Do_Domaine LEFT OUTER JOIN F_ARTICLE as A ON A.AR_REF=L.AR_REF where E.DO_Type = 1 and E.Do_Domaine = 0 AND FA_CODEFAMILLE Not like 'M%' ---------------------------------------------------------------------------------- -------ETAPE 3.1.2------------------------------------------------------------------------------ -----Insertion des lignes de type consomables et autres ---------------------------------------------------------------------------------- ---------------------------------------------------------------------------------- Insert Into F_DOCLIGNE (AC_REFCLIENT,AF_REFFOURNISS,AG_NO1,AG_NO2,AR_REF, AR_REFCOMPOSE,CA_NUM,CT_NUM,DE_NO,DL_CMUP, DL_DATEBC,DL_DATEBL,DL_DESIGN,DL_FRAIS,DL_LIGNE, DL_MVTSTOCK,DL_NO,DL_NONLIVRE,DL_NOREF,DL_PIECEBC, DL_PIECEBL,DL_POIDSBRUT,DL_POIDSNET,DL_PRIXRU,DL_PRIXUNITAIRE, DL_PUBC,DL_PUDEVISE,DL_PUTTC,DL_QTE,DL_QTEBC, DL_QTEBL,DL_REMISE01REM_TYPE,DL_REMISE01REM_VALEUR,DL_REMISE02REM_TYPE,DL_REMISE02REM_VALEUR, DL_REMISE03REM_TYPE,DL_REMISE03REM_VALEUR,DL_TAXE1,DL_TAXE2,DL_TAXE3, DL_TNOMENCL,DL_TREMEXEP,DL_TREMPIED,DL_TTC,DL_TYPEPL, DL_TYPETAUX1,DL_TYPETAUX2,DL_TYPETAUX3,DL_TYPETAXE1,DL_TYPETAXE2, DL_TYPETAXE3,DL_VALORISE,DO_DATE,DO_DATELIVR,DO_DOMAINE, DO_PIECE,DO_REF,DO_TYPE,EU_ENUMERE,EU_QTE, DT_NO,CO_NO,[DL_QtePL], DL_MONTANTHT,DL_MONTANTTTC,DL_FactPoids,DL_Escompte,DL_PiecePL,DL_DatePL,DL_NoColis,DL_NoLink,DL_QteRessource,DL_DateAvancement) Select AC_REFCLIENT,AF_REFFOURNISS,0,0,L.AR_REF, AR_REFCOMPOSE,'',CT_NUM,1, 0, cast(Floor(cast(getdate()as Float)) as smallDateTime ),cast(Floor(cast(getdate()as Float)) as smallDateTime ),DL_Design, 0,DL_LIGNE,---VKVKVKV 0,(select max(DL_NO)+L.ID from F_DOCLIGNE LN ),0,0,E.DO_PIECE, '', DL_POIDSBRUT,DL_POIDSNET,DL_PRIXUNITAIRE,DL_PRIXUNITAIRE, DL_PRIXUNITAIRE,DL_PUDEVISE,DL_PUTTC,DL_QTE,DL_QTEBC, DL_QTEBL,DL_REMISE01REM_TYPE,DL_REMISE01REM_VALEUR,DL_REMISE02REM_TYPE,DL_REMISE02REM_VALEUR, DL_REMISE03REM_TYPE,DL_REMISE03REM_VALEUR,DL_TAXE1,DL_TAXE2,DL_TAXE3, DL_TNOMENCL,DL_TREMEXEP,DL_TREMPIED,DL_TTC,DL_TYPEPL, DL_TYPETAUX1,DL_TYPETAUX2,DL_TYPETAUX3,DL_TYPETAXE1,DL_TYPETAXE2, DL_TYPETAXE3,DL_VALORISE,cast(Floor(cast(getdate()as Float)) as smallDateTime ),cast(Floor(cast(0 as Float)) as smallDateTime ),0, (select left(DC_Piece,3)+convert(varchar,(right(DC_Piece,5)-1+E.ID)) from [F_DOCCURRENTPIECE] where DC_Domaine = 0 and DC_IdCol = 6 and DC_Souche = 0),L.DO_REF,6,EU_ENUMERE,EU_QTE, DT_NO,L.CO_NO,DL_QTE, (DL_PRIXUNITAIRE*DL_Qte) as DL_MONTANTHT,(DL_PRIXUNITAIRE*DL_Qte*(1+DL_Taxe1/100)) as DL_MONTANTTTC, 0 as DL_FactPoids,0 as DL_Escompte,'' as DL_PiecePL, cast(Floor(cast(getdate()as Float)) as smallDateTime ) as DL_DatePL,'' as DL_NoColis,0 as DL_NoLink,0 as DL_QteRessource, cast(Floor(cast(0 as Float)) as smallDateTime ) as DL_DateAvancement from SPE_F_DOCentete as E Left outer join SPE_F_DOCligne as L On E.Do_Piece = L.Do_Piece and E.DO_Type=L.DO_Type and E.Do_Domaine=L.Do_Domaine LEFT OUTER JOIN F_ARTICLE as A ON A.AR_REF=L.AR_REF where L.DO_Type = 1 and L.Do_Domaine = 0 AND FA_CODEFAMILLE Not like 'M%' --------ETAPE 3.1.3.1----------------------------------------------------------------------------- ---------------------------------------------------------------------------------- -----Insertion des regelements de type consomables ---------------------------------------------------------------------------------- ---------------------------------------------------------------------------------- ---------------------------------------------------------------------------------- ---------------------------------------------------------------------------------- -----DR_TypeRegl 2 les regelement ---------------------------------------------------------------------------------- ---------------------------------------------------------------------------------- INSERT INTO F_DOCREGL (DR_No, DO_Domaine, DO_Type, DO_Piece, DR_TypeRegl, DR_Date, DR_Libelle, DR_Pourcent, DR_Montant, DR_MontantDev, DR_Equil, EC_No, cbEC_No, DR_Regle, N_Reglement, cbProt, cbCreateur, cbModification, cbReplication, cbFlag ) Select (select max(DR_No) from F_DOCREGL)+E.id, DO_Domaine, 6, (select left(DC_Piece,3)+convert(varchar,(right(DC_Piece,5)-1+E.ID)) from [F_DOCCURRENTPIECE] where DC_Domaine = 0 and DC_IdCol = 6 and DC_Souche = 0), DR_TypeRegl, DR_Date, DR_Libelle, DR_Pourcent, DR_Montant, DR_MontantDev, DR_Equil, EC_No, cbEC_No, DR_Regle, N_Reglement, cbProt, cbCreateur, cbModification, cbReplication, cbFlag From SPE_F_DOCREGL E Where (select left(DC_Piece,3)+convert(varchar,(right(DC_Piece,5)-1+E.ID)) from [F_DOCCURRENTPIECE] where DC_Domaine = 0 and DC_IdCol = 6 and DC_Souche = 0) in (Select DO_PIECE from F_DOCENTETE where DO_Domaine = 0 and DO_Type = 6 and DO_Souche = 0) and DR_TypeRegl=2 ---------------------------------------------------------------------------------- ------ETAPE 3.1.3.2------------------------------------------------------------------------------ -----DR_TypeRegl 1 les Bon d'achats ---------------------------------------------------------------------------------- ---------------------------------------------------------------------------------- INSERT INTO F_DOCREGL (DR_No, DO_Domaine, DO_Type, DO_Piece, DR_TypeRegl, DR_Date, DR_Libelle, DR_Pourcent, DR_Montant, DR_MontantDev, DR_Equil, EC_No, cbEC_No, DR_Regle, N_Reglement, cbProt, cbCreateur, cbModification, cbReplication, cbFlag ) Select (select max(DR_No) from F_DOCREGL)+E.id, DO_Domaine, 6, (select left(DC_Piece,3)+convert(varchar,(right(DC_Piece,5)-1+E.ID)) from [F_DOCCURRENTPIECE] where DC_Domaine = 0 and DC_IdCol = 6 and DC_Souche = 0), DR_TypeRegl, DR_Date, DR_Libelle, DR_Pourcent, DR_Montant, DR_MontantDev, DR_Equil, EC_No, cbEC_No, DR_Regle, N_Reglement, cbProt, cbCreateur, cbModification, cbReplication, cbFlag From SPE_F_DOCREGL E Where (select left(DC_Piece,3)+convert(varchar,(right(DC_Piece,5)-1+E.ID)) from [F_DOCCURRENTPIECE] where DC_Domaine = 0 and DC_IdCol = 6 and DC_Souche = 0) in (Select DO_PIECE from F_DOCENTETE where DO_Domaine = 0 and DO_Type = 6 and DO_Souche = 0) and DR_TypeRegl=1 ---------------------------------------------------------------------------------- -------ETAPE 3.1.3.3----------------------------------------------------------------------------- -----DR_TypeRegl 0 les acomptes ---------------------------------------------------------------------------------- ---------------------------------------------------------------------------------- INSERT INTO F_DOCREGL (DR_No, DO_Domaine, DO_Type, DO_Piece, DR_TypeRegl, DR_Date, DR_Libelle, DR_Pourcent, DR_Montant, DR_MontantDev, DR_Equil, EC_No, cbEC_No, DR_Regle, N_Reglement, cbProt, cbCreateur, cbModification, cbReplication, cbFlag ) Select (select max(DR_No) from F_DOCREGL)+E.id, DO_Domaine, 6, (select left(DC_Piece,3)+convert(varchar,(right(DC_Piece,5)-1+E.ID)) from [F_DOCCURRENTPIECE] where DC_Domaine = 0 and DC_IdCol = 6 and DC_Souche = 0), DR_TypeRegl, DR_Date, DR_Libelle, DR_Pourcent, DR_Montant, DR_MontantDev, DR_Equil, EC_No, cbEC_No, DR_Regle, N_Reglement, cbProt, cbCreateur, cbModification, cbReplication, cbFlag From SPE_F_DOCREGL E Where (select left(DC_Piece,3)+convert(varchar,(right(DC_Piece,5)-1+E.ID)) from [F_DOCCURRENTPIECE] where DC_Domaine = 0 and DC_IdCol = 6 and DC_Souche = 0) in (Select DO_PIECE from F_DOCENTETE where DO_Domaine = 0 and DO_Type = 6 and DO_Souche = 0) and DR_TypeRegl=0 ------------------------------------------------------------------------------- --------- Fin Insertion des valeurs --------- Fin Creation Facture CONSOMABLE ---------FIN INDICE FACTUREC ------------------------------------------------------------------------------- --ETAPE 4 ------------------------------------------------------------------------------- --------- Insertion des valeurs ---------SPE_DOC_Reglement --------- Creation Facture MARCHANDISES ---------INDICE FACTUREM ------------------------------------------------------------------------------- ---------------------------------------------------------------------------------- -----ETAPE 4.1------------------------------------------------------------------------------- -----Insertion des entête de type Marchandise ---------------------------------------------------------------------------------- ---------------------------------------------------------------------------------- INSERT INTO F_DOCENTETE (AB_NO,CA_NO,CA_NUM,CG_NUM,CT_NUMPAYEUR, DE_NO,DO_ATTENTE,DO_BLFACT,DO_CLOTURE,DO_COLISAGE, DO_CONDITION,DO_COORD01,DO_COORD02,DO_COORD03,DO_COORD04, DO_COURS,DO_DATE,DO_DATELIVR,DO_DEBUTABO,DO_DEBUTPERIOD, DO_DEVISE,DO_DOMAINE,DO_ECART,DO_EXPEDIT,DO_FINABO, DO_FINPERIOD,DO_HEURE,DO_IMPRIM,DO_LANGUE,DO_NBFACTURE, DO_NOWEB,DO_PERIOD,DO_PIECE,DO_REF,DO_REGIME, DO_RELIQUAT,DO_SOUCHE,DO_STATUT,DO_TARIF,DO_TIERS, DO_TRANSACTION,DO_TRANSFERE,DO_TXESCOMPTE,DO_TYPE,DO_TYPECOLIS, DO_VENTILE,LI_NO,N_CATCOMPTA,CO_NO,CO_NOCAISSIER, DO_Provenance,CA_NumIFRS,MR_No,DO_TypeFrais,DO_ValFrais,DO_TypeLigneFrais, DO_TypeFranco,DO_ValFranco,DO_TypeLigneFranco,DO_Taxe1,DO_TypeTaux1,DO_TypeTaxe1, DO_Taxe2,DO_TypeTaux2,DO_TypeTaxe2,DO_Taxe3,DO_TypeTaux3,DO_TypeTaxe3,DO_MajCpta,DO_Motif,CT_NumCentrale) select distinct 0,0,'',CG_Num,CT_NumPayeur, 1,0,0,0,1, 1,E.DO_PIECE as DO_COORD01,DO_COORD02,DO_COORD03,DO_COORD04, 0,cast(Floor(cast(getdate()as Float)) as smallDateTime ),cast(Floor(cast(getdate()as Float)) as smallDateTime ),Null,Null, 0,0, 0,1,Null, Null,Null,0,0,1, '',1,(select left(DC_Piece,3)+convert(varchar,(right(DC_Piece,5)-1+E.ID)) from [F_DOCCURRENTPIECE] where DC_Domaine = 0 and DC_IdCol = 6 and DC_Souche = 1),E.DO_REF,21, 0,1,2,2,DO_Tiers, 11,0, DO_TXESCOMPTE,6,1, 0,14,1,4,0, DO_Provenance,CA_NumIFRS,MR_No,DO_TypeFrais,DO_ValFrais,DO_TypeLigneFrais, DO_TypeFranco,DO_ValFranco,DO_TypeLigneFranco,DO_Taxe1,DO_TypeTaux1,DO_TypeTaxe1, DO_Taxe2,DO_TypeTaux2,DO_TypeTaxe2,DO_Taxe3,DO_TypeTaux3,DO_TypeTaxe3,DO_MajCpta,DO_Motif,CT_NumCentrale from SPE_F_DOCentete as E Left outer join SPE_F_DOCligne as L On E.Do_Piece = L.Do_Piece and E.DO_Type=L.DO_Type and E.Do_Domaine=L.Do_Domaine LEFT OUTER JOIN F_ARTICLE as A ON A.AR_REF=L.AR_REF where E.DO_Type = 1 and E.Do_Domaine = 0 AND FA_CODEFAMILLE like 'M%' --and E.DO_piece in ('CBC32311','CBC32323') --select * from F_DOCentete where DO_piece = 'CBC32311' ---------------------------------------------------------------------------------- -----ETAPE 4.1------------------------------------------------------------------------------- -----Insertion des lignes de type Marchandise ---------------------------------------------------------------------------------- ---------------------------------------------------------------------------------- Insert Into F_DOCLIGNE (AC_REFCLIENT,AF_REFFOURNISS,AG_NO1,AG_NO2,AR_REF, AR_REFCOMPOSE,CA_NUM,CT_NUM,DE_NO,DL_CMUP, DL_DATEBC,DL_DATEBL,DL_DESIGN,DL_FRAIS,DL_LIGNE, DL_MVTSTOCK,DL_NO,DL_NONLIVRE,DL_NOREF,DL_PIECEBC, DL_PIECEBL,DL_POIDSBRUT,DL_POIDSNET,DL_PRIXRU,DL_PRIXUNITAIRE, DL_PUBC,DL_PUDEVISE,DL_PUTTC,DL_QTE,DL_QTEBC, DL_QTEBL,DL_REMISE01REM_TYPE,DL_REMISE01REM_VALEUR,DL_REMISE02REM_TYPE,DL_REMISE02REM_VALEUR, DL_REMISE03REM_TYPE,DL_REMISE03REM_VALEUR,DL_TAXE1,DL_TAXE2,DL_TAXE3, DL_TNOMENCL,DL_TREMEXEP,DL_TREMPIED,DL_TTC,DL_TYPEPL, DL_TYPETAUX1,DL_TYPETAUX2,DL_TYPETAUX3,DL_TYPETAXE1,DL_TYPETAXE2, DL_TYPETAXE3,DL_VALORISE,DO_DATE,DO_DATELIVR,DO_DOMAINE, DO_PIECE,DO_REF,DO_TYPE,EU_ENUMERE,EU_QTE, DT_NO,CO_NO,[DL_QtePL], DL_MONTANTHT,DL_MONTANTTTC,DL_FactPoids,DL_Escompte,DL_PiecePL,DL_DatePL,DL_NoColis,DL_NoLink,DL_QteRessource,DL_DateAvancement) Select AC_REFCLIENT,AF_REFFOURNISS,0,0,L.AR_REF, AR_REFCOMPOSE,'',CT_NUM,1, 0, cast(Floor(cast(getdate()as Float)) as smallDateTime ),cast(Floor(cast(getdate()as Float)) as smallDateTime ),DL_Design, 0,DL_LIGNE, 0,(select max(DL_NO)+L.ID from F_DOCLIGNE LN ),0,0,E.DO_PIECE, '', DL_POIDSBRUT,DL_POIDSNET,DL_PRIXUNITAIRE,DL_PRIXUNITAIRE, DL_PRIXUNITAIRE,DL_PUDEVISE,DL_PUTTC,DL_QTE,DL_QTEBC, DL_QTEBL,DL_REMISE01REM_TYPE,DL_REMISE01REM_VALEUR,DL_REMISE02REM_TYPE,DL_REMISE02REM_VALEUR, DL_REMISE03REM_TYPE,DL_REMISE03REM_VALEUR,DL_TAXE1,DL_TAXE2,DL_TAXE3, DL_TNOMENCL,DL_TREMEXEP,DL_TREMPIED,DL_TTC,DL_TYPEPL, DL_TYPETAUX1,DL_TYPETAUX2,DL_TYPETAUX3,DL_TYPETAXE1,DL_TYPETAXE2, DL_TYPETAXE3,DL_VALORISE,cast(Floor(cast(getdate()as Float)) as smallDateTime ),cast(Floor(cast(0 as Float)) as smallDateTime ),0, (select left(DC_Piece,3)+convert(varchar,(right(DC_Piece,5)-1+E.ID)) from [F_DOCCURRENTPIECE] where DC_Domaine = 0 and DC_IdCol = 6 and DC_Souche = 1),L.DO_REF,6,EU_ENUMERE,EU_QTE, DT_NO,L.CO_NO,DL_QTE, (DL_PRIXUNITAIRE*DL_Qte) as DL_MONTANTHT,(DL_PRIXUNITAIRE*DL_Qte*(1+DL_Taxe1/100)) as DL_MONTANTTTC, 0 as DL_FactPoids,0 as DL_Escompte,'' as DL_PiecePL, cast(Floor(cast(getdate()as Float)) as smallDateTime ) as DL_DatePL,'' as DL_NoColis,0 as DL_NoLink,0 as DL_QteRessource, cast(Floor(cast(0 as Float)) as smallDateTime ) as DL_DateAvancement from SPE_F_DOCentete as E Left outer join SPE_F_DOCligne as L On E.Do_Piece = L.Do_Piece and E.DO_Type=L.DO_Type and E.Do_Domaine=L.Do_Domaine LEFT OUTER JOIN F_ARTICLE as A ON A.AR_REF=L.AR_REF where L.DO_Type = 1 and L.Do_Domaine = 0 AND FA_CODEFAMILLE like 'M%' --and L.DO_piece in ('CBC32311','CBC32323') --select * from F_DOCligne where DO_piece = 'CBC32311' ---------------------------------------------------------------------------------- ------ETAPE 4.2------------------------------------------------------------------------------ -----Insertion des regelements de type Marchandise ---------------------------------------------------------------------------------- ---------------------------------------------------------------------------------- INSERT INTO F_DOCREGL (DR_No, DO_Domaine, DO_Type, DO_Piece, DR_TypeRegl, DR_Date, DR_Libelle, DR_Pourcent, DR_Montant, DR_MontantDev, DR_Equil, EC_No, cbEC_No, DR_Regle, N_Reglement, cbProt, cbCreateur, cbModification, cbReplication, cbFlag ) Select (select max(DR_No) from F_DOCREGL)+E.id, DO_Domaine, 6, (select left(DC_Piece,3)+convert(varchar,(right(DC_Piece,5)-1+E.ID)) from [F_DOCCURRENTPIECE] where DC_Domaine = 0 and DC_IdCol = 6 and DC_Souche = 1), DR_TypeRegl, DR_Date, DR_Libelle, DR_Pourcent, DR_Montant, DR_MontantDev, DR_Equil, EC_No, cbEC_No, DR_Regle, N_Reglement, cbProt, cbCreateur, cbModification, cbReplication, cbFlag From SPE_F_DOCREGL E Where (select left(DC_Piece,3)+convert(varchar,(right(DC_Piece,5)-1+E.ID)) from [F_DOCCURRENTPIECE] where DC_Domaine = 0 and DC_IdCol = 6 and DC_Souche = 1) in (Select DO_PIECE from F_DOCENTETE where DO_Domaine = 0 and DO_Type = 6 and DO_Souche = 1) and DR_TypeRegl=2 ------------------------------------------------------------------------------- --------- Fin Insertion des valeurs --------- Fin Creation Facture MARCHANDISES ---------FIN INDICE FACTUREM ------------------------------------------------------------------------------- --ETAPE 5 ------------------------------------------------------------------------------- ---------Travail Insertion des sous totaux------------- ------------------------------------------------------------------------------- ---------------------------------------------------------------------------------- ------ETAPE 5.1------------------------------------------------------------------------------ -----Suppresion avant insertion des sous totaux ---------------------------------------------------------------------------------- ---------------------------------------------------------------------------------- Delete from F_DOCLIGNE where DO_Domaine =0 And DO_TYPE = 1 and DO_piece BETWEEN @DO_PIECE_DEB and @DO_PIECE_FIN and DL_TRemExep = 2 ---------------------------------------------------------------------------------- -------ETAPE 5.2----------------------------------------------------------------------------- -----Creation table temporaire sous totaux ---------------------------------------------------------------------------------- ---------------------------------------------------------------------------------- /****** Object: Table [dbo].[F_DOCLIGNE] Script Date: 01/25/2011 11:02:16 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SPE_F_DOCLIGNESOUSTOTAL]') AND type in (N'U')) DROP TABLE [dbo].[SPE_F_DOCLIGNESOUSTOTAL] /****** Object: Table [dbo].[F_DOCLIGNE] Script Date: 01/25/2011 11:02:16 ******/ CREATE TABLE [dbo].[SPE_F_DOCLIGNESOUSTOTAL]( [ID] [int] identity (1,1) NOT NULL, [DO_Domaine] [smallint] NULL, [DO_Type] [smallint] NOT NULL, [CT_Num] [varchar](17) NULL, [cbCT_Num] AS (CONVERT([varbinary](18),isnull([CT_Num],''),0)), [DO_Piece] [varchar](9) NOT NULL, [cbDO_Piece] AS (CONVERT([varbinary](10),space((10)-(len([DO_Piece])+(1)))+[DO_Piece],0)), [DL_PieceBC] [varchar](9) NULL, [cbDL_PieceBC] AS (CONVERT([varbinary](10),space((10)-(len(isnull([DL_PieceBC],''))+(1)))+isnull([DL_PieceBC],''),0)), [DL_PieceBL] [varchar](9) NULL, [cbDL_PieceBL] AS (CONVERT([varbinary](10),space((10)-(len(isnull([DL_PieceBL],''))+(1)))+isnull([DL_PieceBL],''),0)), [DO_Date] [smalldatetime] NULL, [DL_DateBC] [smalldatetime] NULL, [DL_DateBL] [smalldatetime] NULL, [DL_Ligne] [int] NULL, [DO_Ref] [varchar](17) NULL, [DL_TNomencl] [smallint] NULL, [DL_TRemPied] [smallint] NULL, [DL_TRemExep] [smallint] NULL, [AR_Ref] [varchar](19) NULL, [cbAR_Ref] AS (CONVERT([varbinary](20),isnull([AR_Ref],''),0)), [DL_Design] [varchar](69) NULL, [DL_Qte] [numeric](24, 6) NULL, [DL_QteBC] [numeric](24, 6) NULL, [DL_QteBL] [numeric](24, 6) NULL, [DL_PoidsNet] [numeric](24, 6) NULL, [DL_PoidsBrut] [numeric](24, 6) NULL, [DL_Remise01REM_Valeur] [numeric](24, 6) NULL, [DL_Remise01REM_Type] [smallint] NULL, [DL_Remise02REM_Valeur] [numeric](24, 6) NULL, [DL_Remise02REM_Type] [smallint] NULL, [DL_Remise03REM_Valeur] [numeric](24, 6) NULL, [DL_Remise03REM_Type] [smallint] NULL, [DL_PrixUnitaire] [numeric](24, 6) NULL, [DL_PUBC] [numeric](24, 6) NULL, [DL_Taxe1] [numeric](24, 6) NULL, [DL_TypeTaux1] [smallint] NULL, [DL_TypeTaxe1] [smallint] NULL, [DL_Taxe2] [numeric](24, 6) NULL, [DL_TypeTaux2] [smallint] NULL, [DL_TypeTaxe2] [smallint] NULL, [CO_No] [int] NULL, [cbCO_No] [int] NULL, [AG_No1] [int] NULL, [AG_No2] [int] NULL, [DL_PrixRU] [numeric](24, 6) NULL, [DL_CMUP] [numeric](24, 6) NULL, [DL_MvtStock] [smallint] NULL, [DT_No] [int] NULL, [cbDT_No] [int] NULL, [AF_RefFourniss] [varchar](19) NULL, [cbAF_RefFourniss] AS (CONVERT([varbinary](20),isnull([AF_RefFourniss],''),0)), [EU_Enumere] [varchar](21) NULL, [EU_Qte] [numeric](24, 6) NULL, [DL_TTC] [smallint] NULL, [DE_No] [int] NULL, [cbDE_No] [int] NULL, [DL_NoRef] [smallint] NULL, [DL_TypePL] [smallint] NULL, [DL_PUDevise] [numeric](24, 6) NULL, [DL_PUTTC] [numeric](24, 6) NULL, [DL_No] [int] NULL, [DO_DateLivr] [smalldatetime] NULL, [CA_Num] [varchar](13) NULL, [cbCA_Num] AS (CONVERT([varbinary](14),isnull([CA_Num],''),0)), [DL_Taxe3] [numeric](24, 6) NULL, [DL_TypeTaux3] [smallint] NULL, [DL_TypeTaxe3] [smallint] NULL, [DL_Frais] [numeric](24, 6) NULL, [DL_Valorise] [smallint] NULL, [AR_RefCompose] [varchar](19) NULL, [DL_NonLivre] [smallint] NULL, [AC_RefClient] [varchar](19) NULL, [DL_MontantHT] [numeric](24, 6) NULL, [DL_MontantTTC] [numeric](24, 6) NULL, [DL_FactPoids] [smallint] NULL, [DL_Escompte] [smallint] NULL, [DL_PiecePL] [varchar](9) NULL, [cbDL_PiecePL] AS (CONVERT([varbinary](10),space((10)-(len(isnull([DL_PiecePL],''))+(1)))+isnull([DL_PiecePL],''),0)), [DL_DatePL] [smalldatetime] NULL, [DL_QtePL] [numeric](24, 6) NULL, [DL_NoColis] [varchar](19) NULL, [DL_NoLink] [int] NULL, [cbDL_NoLink] [int] NULL, [RP_Code] [varchar](11) NULL, [cbRP_Code] AS (CONVERT([varbinary](12),isnull([RP_Code],''),0)), [DL_QteRessource] [int] NULL, [DL_DateAvancement] [smalldatetime] NULL, [cbProt] [smallint] NULL, [cbMarq] [int], [cbCreateur] [char](4) NULL, [cbModification] [smalldatetime] NULL, [cbReplication] [int] NULL, [cbFlag] [smallint] NULL ) ---------------------------------------------------------------------------------- --------ETAPE 5.3---------------------------------------------------------------------------- -----Insertion valeur dans la table temporaire avec regroupement des montants ----- et union des C et M ---------------------------------------------------------------------------------- ---------------------------------------------------------------------------------- Insert into [SPE_F_DOCLIGNESOUSTOTAL] ( DO_Domaine,DO_Type, CT_Num, DO_Piece, DL_PieceBC, DL_PieceBL, L.DO_Date, DL_DateBC, DL_DateBL,DL_LIGNE, DO_Ref,DL_TNomencl,DL_TRemPied,DL_TRemExep, AR_Ref, DL_Design, DL_Qte,DL_QteBC,DL_QteBL,DL_PoidsNet, DL_PoidsBrut, DL_Remise01REM_Valeur, DL_Remise01REM_Type, DL_Remise02REM_Valeur,DL_Remise02REM_Type, DL_Remise03REM_Valeur, DL_Remise03REM_Type,DL_PrixUnitaire, DL_PUBC, L.CO_No, L.cbCO_No, AG_No1, AG_No2, DL_PrixRU, DL_MvtStock, DT_No, cbDT_No, DL_TypePL, DL_PUDevise, L.DO_DateLivr, L.CA_Num, DL_Taxe3, DL_TypeTaux3, DL_TypeTaxe3, DL_Frais, DL_Valorise, AR_RefCompose, DL_NonLivre, AC_RefClient, DL_MontantHT,DL_MontantTTC, DL_FactPoids, DL_Escompte, DL_PiecePL, DL_DatePL,DL_QtePL, DL_NoColis, DL_NoLink, cbDL_NoLink, RP_Code, DL_QteRessource, DL_DateAvancement, L.cbProt, L.cbCreateur, L.cbModification, L.cbReplication, L.cbFlag) Select distinct 0 AS DO_Domaine,1 AS DO_Type, CT_Num, (select left(DC_Piece,3)+convert(varchar,(right(DC_Piece,5)-1+E.ID)) from [F_DOCCURRENTPIECE] where DC_Domaine = 0 and DC_IdCol = 6 and DC_Souche = 0) , '' DL_PieceBC, '' DL_PieceBL, L.DO_Date, DL_DateBC, DL_DateBL, (select MAX(DL_LIGNE)+500 from F_DOCLIGNE LR LEFT OUTER JOIN F_ARTICLE as AR ON AR.AR_REF=LR.AR_REF where DO_Domaine =0 And DO_TYPE = 1 and DO_Piece=L.DO_Piece and FA_CODEFAMILLE=A.FA_CodeFamille group by DO_Domaine,DO_TYPE) as DL_LIGNE, 21 as DO_Ref,0 AS DL_TNomencl, 0 as DL_TRemPied, 2 AS DL_TRemExep /*Article sous Total*/,NULL AS AR_Ref, 'Sous-Total '+substring(A.FA_CodeFamille,2,2)+' '+FA_Stat01+': '+replace(left((sum(DL_Qte*DL_PrixUnitaire)),CHARINDEX('.',(sum(DL_Qte*DL_PrixUnitaire)))+2),'.',',')+' €' as DL_Design, 1 as DL_Qte,1 as DL_QteBC,1 AS DL_QteBL,0 As DL_PoidsNet, 0 as DL_PoidsBrut, 0 as DL_Remise01REM_Valeur, 0 as DL_Remise01REM_Type, 0 as DL_Remise02REM_Valeur, 0 as DL_Remise02REM_Type, 0 as DL_Remise03REM_Valeur, 0 as DL_Remise03REM_Type, sum(DL_Qte*DL_PrixUnitaire) as DL_PrixUnitaire, 0 as DL_PUBC, L.CO_No, L.cbCO_No, 0 as AG_No1,0 as AG_No2,0 as DL_PrixRU, 3 as DL_MvtStock, 0 as DT_No,Null as cbDT_No, /*DL_NoRef,*/0 as DL_TypePL,0 as DL_PUDevise,cast(Floor(cast(getdate()as Float)) as smallDateTime )as DO_DateLivr,'' as CA_Num,0 as DL_Taxe3,0 as DL_TypeTaux3,0 as DL_TypeTaxe3,0 as DL_Frais,1 as DL_Valorise, Null as AR_RefCompose,0 as DL_NonLivre,'' as AC_RefClient, sum(DL_Qte*DL_PrixUnitaire) as DL_MontantHT, sum(DL_Qte*DL_PrixUnitaire)*0.196 as DL_MontantTTC,0 as DL_FactPoids,0 as DL_Escompte,''as DL_PiecePL, cast(Floor(cast(0 as Float)) as smallDateTime )as DL_DatePL,1 as DL_QtePL, ''as DL_NoColis,0 as DL_NoLink,Null as cbDL_NoLink,Null as RP_Code,0 as DL_QteRessource,cast(Floor(cast(getdate()as Float)) as smallDateTime )as DL_DateAvancement, 0 as cbProt, 'ODBC' as cbCreateur,GETDATE() as CbModification , 0 as cbReplication, 0 as cbFlag from F_DOCLIGNE as L LEFT OUTER JOIN SPE_F_DOCentete as E On E.Do_Piece = L.Do_Piece and E.DO_Type=L.DO_Type and E.Do_Domaine=L.Do_Domaine LEFT OUTER JOIN F_ARTICLE as A ON A.AR_REF=L.AR_REF LEFT OUTER JOIN F_FAMILLE as F ON F.FA_CodeFamille=A.FA_CodeFamille where L.DO_Domaine =0 And L.DO_TYPE = 1 AND A.FA_CodeFamille Not like 'M%' and A.FA_CodeFamille is not NULL and (select left(DC_Piece,3)+convert(varchar,(right(DC_Piece,5)-1+E.ID)) from [F_DOCCURRENTPIECE] where DC_Domaine = 0 and DC_IdCol = 6 and DC_Souche = 0)is Not NULL and L.DO_piece BETWEEN @DO_PIECE_DEB and @DO_PIECE_FIN group by CT_Num,A.FA_CODEFAMILLE,F.FA_Stat01, L.DO_Piece, L.DO_Date, DL_DateBC,DL_DateBL, DL_PUBC, L.CO_No, L.cbCO_No, E.ID Union all Select distinct 0 AS DO_Domaine,1 AS DO_Type, CT_Num, (select left(DC_Piece,3)+convert(varchar,(right(DC_Piece,5)-1+E.ID)) from [F_DOCCURRENTPIECE] where DC_Domaine = 0 and DC_IdCol = 6 and DC_Souche = 1) , '' DL_PieceBC, '' DL_PieceBL, L.DO_Date, DL_DateBC, DL_DateBL, (select MAX(DL_LIGNE)+500 from F_DOCLIGNE LR LEFT OUTER JOIN F_ARTICLE as AR ON AR.AR_REF=LR.AR_REF where DO_Domaine =0 And DO_TYPE = 1 and DO_Piece=L.DO_Piece and FA_CODEFAMILLE=A.FA_CodeFamille group by DO_Domaine,DO_TYPE) as DL_LIGNE, 21 as DO_Ref,0 AS DL_TNomencl, 0 as DL_TRemPied, 2 AS DL_TRemExep /*Article sous Total*/,NULL AS AR_Ref, 'Sous-Total '+substring(A.FA_CodeFamille,2,2)+' '+FA_Stat01+': '+replace(left((sum(DL_Qte*DL_PrixUnitaire)),CHARINDEX('.',(sum(DL_Qte*DL_PrixUnitaire)))+2),'.',',')+' €' as DL_Design, 1 as DL_Qte,1 as DL_QteBC,1 AS DL_QteBL,0 As DL_PoidsNet, 0 as DL_PoidsBrut, 0 as DL_Remise01REM_Valeur, 0 as DL_Remise01REM_Type, 0 as DL_Remise02REM_Valeur, 0 as DL_Remise02REM_Type, 0 as DL_Remise03REM_Valeur, 0 as DL_Remise03REM_Type, sum(DL_Qte*DL_PrixUnitaire) as DL_PrixUnitaire, 0 as DL_PUBC, L.CO_No, L.cbCO_No, 0 as AG_No1,0 as AG_No2,0 as DL_PrixRU, 3 as DL_MvtStock, 0 as DT_No,Null as cbDT_No, /*DL_NoRef,*/0 as DL_TypePL,0 as DL_PUDevise,cast(Floor(cast(getdate()as Float)) as smallDateTime )as DO_DateLivr,'' as CA_Num,0 as DL_Taxe3,0 as DL_TypeTaux3,0 as DL_TypeTaxe3,0 as DL_Frais,1 as DL_Valorise, Null as AR_RefCompose,0 as DL_NonLivre,'' as AC_RefClient, sum(DL_Qte*DL_PrixUnitaire) as DL_MontantHT, sum(DL_Qte*DL_PrixUnitaire)*0.196 as DL_MontantTTC,0 as DL_FactPoids,0 as DL_Escompte,''as DL_PiecePL, cast(Floor(cast(0 as Float)) as smallDateTime )as DL_DatePL,1 as DL_QtePL, ''as DL_NoColis,0 as DL_NoLink,Null as cbDL_NoLink,Null as RP_Code,0 as DL_QteRessource,cast(Floor(cast(getdate()as Float)) as smallDateTime )as DL_DateAvancement, 0 as cbProt, 'ODBC' as cbCreateur,GETDATE() as CbModification , 0 as cbReplication, 0 as cbFlag from F_DOCLIGNE as L LEFT OUTER JOIN SPE_F_DOCentete as E On E.Do_Piece = L.Do_Piece and E.DO_Type=L.DO_Type and E.Do_Domaine=L.Do_Domaine LEFT OUTER JOIN F_ARTICLE as A ON A.AR_REF=L.AR_REF LEFT OUTER JOIN F_FAMILLE as F ON F.FA_CodeFamille=A.FA_CodeFamille where L.DO_Domaine =0 And L.DO_TYPE = 1 AND A.FA_CodeFamille like 'M%' and A.FA_CodeFamille is not NULL and (select left(DC_Piece,3)+convert(varchar,(right(DC_Piece,5)-1+E.ID)) from [F_DOCCURRENTPIECE] where DC_Domaine = 0 and DC_IdCol = 6 and DC_Souche = 0)is Not NULL and L.DO_piece BETWEEN @DO_PIECE_DEB and @DO_PIECE_FIN group by CT_Num,A.FA_CODEFAMILLE,F.FA_Stat01, L.DO_Piece, L.DO_Date, DL_DateBC,DL_DateBL, DL_PUBC, L.CO_No, L.cbCO_No, E.ID ---------------------------------------------------------------------------------- --------ETAPE 5.4---------------------------------------------------------------------------- -----Insertion valeur dans la table réel des sous totaux dans les factures ---------------------------------------------------------------------------------- ---------------------------------------------------------------------------------- Insert Into F_DOCLIGNE (AC_REFCLIENT,AF_REFFOURNISS,AG_NO1,AG_NO2,AR_REF, AR_REFCOMPOSE,CA_NUM,CT_NUM,DE_NO,DL_CMUP, DL_DATEBC,DL_DATEBL,DL_DESIGN,DL_FRAIS,DL_LIGNE, DL_MVTSTOCK,DL_NO,DL_NONLIVRE,DL_NOREF,DL_PIECEBC, DL_PIECEBL,DL_POIDSBRUT,DL_POIDSNET,DL_PRIXRU,DL_PRIXUNITAIRE, DL_PUBC,DL_PUDEVISE,DL_PUTTC,DL_QTE,DL_QTEBC, DL_QTEBL,DL_REMISE01REM_TYPE,DL_REMISE01REM_VALEUR,DL_REMISE02REM_TYPE,DL_REMISE02REM_VALEUR, DL_REMISE03REM_TYPE,DL_REMISE03REM_VALEUR,DL_TAXE1,DL_TAXE2,DL_TAXE3, DL_TNOMENCL,DL_TREMEXEP,DL_TREMPIED,DL_TTC,DL_TYPEPL, DL_TYPETAUX1,DL_TYPETAUX2,DL_TYPETAUX3,DL_TYPETAXE1,DL_TYPETAXE2, DL_TYPETAXE3,DL_VALORISE,DO_DATE,DO_DATELIVR,DO_DOMAINE, DO_PIECE,DO_REF,DO_TYPE,EU_ENUMERE,EU_QTE, DT_NO,CO_NO,[DL_QtePL]) Select '','',0,0,L.AR_REF, '','',CT_NUM,1, 0, cast(Floor(cast(getdate()as Float)) as smallDateTime ),Null,DL_Design, 0,DL_Ligne, 0,(select max(DL_NO)+L.ID from F_DOCLIGNE LN ),0,0,L.DO_PIECE, '', DL_POIDSBRUT,DL_POIDSNET,DL_PRIXUNITAIRE,DL_PRIXUNITAIRE, DL_PRIXUNITAIRE,DL_PUDEVISE,DL_PUTTC,DL_QTE,DL_QTEBC, DL_QTEBL,DL_REMISE01REM_TYPE,DL_REMISE01REM_VALEUR,DL_REMISE02REM_TYPE,DL_REMISE02REM_VALEUR, DL_REMISE03REM_TYPE,DL_REMISE03REM_VALEUR,DL_TAXE1,DL_TAXE2,DL_TAXE3, DL_TNOMENCL,DL_TREMEXEP,DL_TREMPIED,DL_TTC,DL_TYPEPL, DL_TYPETAUX1,DL_TYPETAUX2,DL_TYPETAUX3,DL_TYPETAXE1,DL_TYPETAXE2, DL_TYPETAXE3,DL_VALORISE,cast(Floor(cast(getdate()as Float)) as smallDateTime ),cast(Floor(cast(getdate()as Float)) as smallDateTime ),0, DO_Piece,L.DO_REF,6,EU_ENUMERE,EU_QTE, DT_NO,L.CO_NO,DL_QTE from [SPE_F_DOCLIGNESOUSTOTAL] as L ------------------------------------------------------------------------------- ---------Fin Travail Insertion des sous totaux------------- ------------------------------------------------------------------------------- --ETAPE 6 ------------------------------------------------------------------------------- ---------Sauvegarde des tables en cas de PB------------- ------------------------------------------------------------------------------- ------------------------------------------------------------------------------- ------ETAPE 6.1--------------------------------------------------------------------------- INSERT INTO F_DOCENTETE_SAVE ( DO_Domaine, DO_Type, DO_Piece, DO_Date, DO_Ref, DO_Tiers, CO_No, DO_Period, DO_Devise, DO_Cours, DE_No, cbDE_No, LI_No, cbLI_No, CT_NumPayeur, DO_Expedit, DO_NbFacture, DO_BLFact, DO_TxEscompte, DO_Reliquat, DO_Imprim, CA_Num, DO_Coord01, DO_Coord02, DO_Coord03, DO_Coord04, DO_Souche, DO_DateLivr, DO_Condition, DO_Tarif, DO_Colisage, DO_TypeColis, DO_Transaction, DO_Langue, DO_Ecart, DO_Regime, N_CatCompta, DO_Ventile, AB_No, DO_DebutAbo, DO_FinAbo, DO_DebutPeriod, DO_FinPeriod, CG_Num, DO_Statut, DO_Heure, CA_No, cbCA_No, CO_NoCaissier, cbCO_NoCaissier, DO_Transfere, DO_Cloture, DO_NoWeb, DO_Attente, DO_Provenance, CA_NumIFRS, MR_No, DO_TypeFrais, DO_ValFrais, DO_TypeLigneFrais, DO_TypeFranco, DO_ValFranco, DO_TypeLigneFranco, DO_Taxe1, DO_TypeTaux1, DO_TypeTaxe1, DO_Taxe2, DO_TypeTaux2, DO_TypeTaxe2, DO_Taxe3, DO_TypeTaux3, DO_TypeTaxe3, DO_MajCpta, DO_Motif, CT_NumCentrale, DO_Contact, DO_FactureElec, DO_TypeTransac, cbProt, cbMarq, cbCreateur, cbModification, cbReplication, cbFlag ) Select DO_Domaine, DO_Type, DO_Piece, DO_Date, DO_Ref, DO_Tiers, CO_No, DO_Period, DO_Devise, DO_Cours, DE_No, cbDE_No, LI_No, cbLI_No, CT_NumPayeur, DO_Expedit, DO_NbFacture, DO_BLFact, DO_TxEscompte, DO_Reliquat, DO_Imprim, CA_Num, DO_Coord01, DO_Coord02, DO_Coord03, DO_Coord04, DO_Souche, DO_DateLivr, DO_Condition, DO_Tarif, DO_Colisage, DO_TypeColis, DO_Transaction, DO_Langue, DO_Ecart, DO_Regime, N_CatCompta, DO_Ventile, AB_No, DO_DebutAbo, DO_FinAbo, DO_DebutPeriod, DO_FinPeriod, CG_Num, DO_Statut, DO_Heure, CA_No, cbCA_No, CO_NoCaissier, cbCO_NoCaissier, DO_Transfere, DO_Cloture, DO_NoWeb, DO_Attente, DO_Provenance, CA_NumIFRS, MR_No, DO_TypeFrais, DO_ValFrais, DO_TypeLigneFrais, DO_TypeFranco, DO_ValFranco, DO_TypeLigneFranco, DO_Taxe1, DO_TypeTaux1, DO_TypeTaxe1, DO_Taxe2, DO_TypeTaux2, DO_TypeTaxe2, DO_Taxe3, DO_TypeTaux3, DO_TypeTaxe3, DO_MajCpta, DO_Motif, CT_NumCentrale, DO_Contact, DO_FactureElec, DO_TypeTransac, cbProt, cbMarq, cbCreateur, cbModification, cbReplication, cbFlag from SPE_F_DOCENTETE --ETAPE 6.2 INSERT INTO F_DOCLIGNE_SAVE ( DO_Domaine, DO_Type, CT_Num, DO_Piece, DL_PieceBC, DL_PieceBL, DO_Date, DL_DateBC, DL_DateBL, DL_Ligne, DO_Ref, DL_TNomencl, DL_TRemPied, DL_TRemExep, AR_Ref, DL_Design, DL_Qte, DL_QteBC, DL_QteBL, DL_PoidsNet, DL_PoidsBrut, DL_Remise01REM_Valeur, DL_Remise01REM_Type, DL_Remise02REM_Valeur, DL_Remise02REM_Type, DL_Remise03REM_Valeur, DL_Remise03REM_Type, DL_PrixUnitaire, DL_PUBC, DL_Taxe1, DL_TypeTaux1, DL_TypeTaxe1, DL_Taxe2, DL_TypeTaux2, DL_TypeTaxe2, CO_No, cbCO_No, AG_No1, AG_No2, DL_PrixRU, DL_CMUP, DL_MvtStock, DT_No, cbDT_No, AF_RefFourniss, EU_Enumere, EU_Qte, DL_TTC, DE_No, cbDE_No, DL_NoRef, DL_TypePL, DL_PUDevise, DL_PUTTC, DL_No, DO_DateLivr, CA_Num, DL_Taxe3, DL_TypeTaux3, DL_TypeTaxe3, DL_Frais, DL_Valorise, AR_RefCompose, DL_NonLivre, AC_RefClient, DL_MontantHT, DL_MontantTTC, DL_FactPoids, DL_Escompte, DL_PiecePL, DL_DatePL, DL_QtePL, DL_NoColis, DL_NoLink, cbDL_NoLink, RP_Code, DL_QteRessource, DL_DateAvancement, cbProt, cbMarq, cbCreateur, cbModification, cbReplication, cbFlag ) Select DO_Domaine, DO_Type, CT_Num, DO_Piece, DL_PieceBC, DL_PieceBL, DO_Date, DL_DateBC, DL_DateBL, DL_Ligne, DO_Ref, DL_TNomencl, DL_TRemPied, DL_TRemExep, AR_Ref, DL_Design, DL_Qte, DL_QteBC, DL_QteBL, DL_PoidsNet, DL_PoidsBrut, DL_Remise01REM_Valeur, DL_Remise01REM_Type, DL_Remise02REM_Valeur, DL_Remise02REM_Type, DL_Remise03REM_Valeur, DL_Remise03REM_Type, DL_PrixUnitaire, DL_PUBC, DL_Taxe1, DL_TypeTaux1, DL_TypeTaxe1, DL_Taxe2, DL_TypeTaux2, DL_TypeTaxe2, CO_No, cbCO_No, AG_No1, AG_No2, DL_PrixRU, DL_CMUP, DL_MvtStock, DT_No, cbDT_No, AF_RefFourniss, EU_Enumere, EU_Qte, DL_TTC, DE_No, cbDE_No, DL_NoRef, DL_TypePL, DL_PUDevise, DL_PUTTC, DL_No, DO_DateLivr, CA_Num, DL_Taxe3, DL_TypeTaux3, DL_TypeTaxe3, DL_Frais, DL_Valorise, AR_RefCompose, DL_NonLivre, AC_RefClient, DL_MontantHT, DL_MontantTTC, DL_FactPoids, DL_Escompte, DL_PiecePL, DL_DatePL, DL_QtePL, DL_NoColis, DL_NoLink, cbDL_NoLink, RP_Code, DL_QteRessource, DL_DateAvancement, cbProt, cbMarq, cbCreateur, cbModification, cbReplication, cbFlag from SPE_F_DOCLIGNE --ETAPE 6.3 INSERT INTO F_DOCREGL_SAVE ( DR_No, DO_Domaine, DO_Type, DO_Piece, DR_TypeRegl, DR_Date, DR_Libelle, DR_Pourcent, DR_Montant, DR_MontantDev, DR_Equil, EC_No, cbEC_No, DR_Regle, N_Reglement, cbProt, cbMarq, cbCreateur, cbModification, cbReplication, cbFlag ) Select DR_No, DO_Domaine, DO_Type, DO_Piece, DR_TypeRegl, DR_Date, DR_Libelle, DR_Pourcent, DR_Montant, DR_MontantDev, DR_Equil, EC_No, cbEC_No, DR_Regle, N_Reglement, cbProt, cbMarq, cbCreateur, cbModification, cbReplication, cbFlag from SPE_F_DOCREGL ------------------------------------------------------------------------------- ---------FIN Sauvegarde des tables en cas de PB------------- ------------------------------------------------------------------------------- ------------------------------------------------------------------------------- ------------------------------------------------------------------------------- --ETAPE 7 ------------------------------------------------------------------------------- ---------Mise à Jour du Stock------------- ------------------------------------------------------------------------------- --Update A --Set as_QteSto = case when as_QteSto is Null then 0 else as_QteSto end-(select sum(DL_Qte) from SPE_F_DOCligne as L where A.Ar_REF=L.Ar_REF group by Ar_REF), --AS_QteRes= case when AS_QteRes IS NULL then 0 else AS_QteRes end- (select sum(DL_Qte) from SPE_F_DOCligne as L where A.Ar_REF=L.Ar_REF group by Ar_REF), --AS_MontSto=case when as_QteSto =0 then 0 when as_QteSto is Null then 0 else (AS_MontSto/as_QteSto)*(as_QteSto-(select sum(DL_Qte) from SPE_F_DOCligne as L where A.Ar_REF=L.Ar_REF group by Ar_REF))end --FROM F_ARTSTOCK as A LEFT OUTER JOIN SPE_F_DOCligne as L --ON A.Ar_REF=L.Ar_REF --Where L.ar_Ref is not NUll ---Je teste dans ce sens SPE_Docligne en premier pour ne pas avoir à faire de somme Update A Set as_QteSto = case when as_QteSto is Null then 0 else as_QteSto end-DL_Qte, AS_QteRes= case when AS_QteRes IS NULL then 0 else AS_QteRes end-DL_Qte, AS_MontSto= case when as_QteSto =0 then 0 when as_QteSto is Null then 0 else (AS_MontSto/as_QteSto)*(as_QteSto-DL_Qte)end FROM SPE_F_DOCligne as L LEFT OUTER JOIN F_ARTSTOCK as A ON A.Ar_REF=L.Ar_REF Where L.ar_Ref is not NUll and L.DL_Qte <>0 IF @@ERROR <>0 BEGIN ROLLBACK TRAN TEST RETURN END --ETAPE 8 ------------------------------------------------------------------------------- ----------Update du numero de facture --------- Une pour les commande --DC_Souche = 0 -- 0 Pour Consomable et 1 Pour Marchandises ------------------------------------------------------------------------------- Update F_DOCCURRENTPIECE Set DC_Piece = (select left(DO_Piece,3)+convert(varchar,(right(DO_Piece,5)+1)) from F_DOCentete where DO_Piece like 'CFA%' group by DO_Piece Having right(DO_Piece,5)=(select Max(right(DO_Piece,5))from F_DOCentete where DO_Piece like 'CFA%')) where DC_Domaine = 0 and DC_IdCol = 6 and DC_Souche = 0 --0 Pour Consomable et 1 Pour Marchandises Update F_DOCCURRENTPIECE Set DC_Piece = (select left(DO_Piece,3)+convert(varchar,(right(DO_Piece,5)+1)) from F_DOCentete where DO_Piece like 'MFA%' group by DO_Piece Having right(DO_Piece,5)=(select Max(right(DO_Piece,5))from F_DOCentete where DO_Piece like 'MFA%')) where DC_Domaine = 0 and DC_IdCol = 6 and DC_Souche = 1 --0 Pour Consomable et 1 Pour Marchandises --ETAPE 9 ------------------------------------------------------------------------------- ---------SUPPRESSION DES TABLES TEMPORAIRE------------------------ ---------SUPPRESSION DES DONNEE DANS LES TABLES REELS------------- ------------------------------------------------------------------------------- ------------------------------------------------------------------------------- IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SPE_F_DOCREGL]') AND type in (N'U')) DROP TABLE [dbo].[SPE_F_DOCREGL] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SPE_F_DOCLIGNESOUSTOTAL]') AND type in (N'U')) DROP TABLE [dbo].[SPE_F_DOCLIGNESOUSTOTAL] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SPE_F_DOCREGL]') AND type in (N'U')) DROP TABLE [dbo].[SPE_F_DOCENTETE] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SPE_F_DOCREGL]') AND type in (N'U')) DROP TABLE [dbo].[SPE_F_DOCLIGNE] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SPE_F_REGLECH]') AND type in (N'U')) DROP TABLE [dbo].[SPE_F_REGLECH] DELETE from F_DOCLIGNE Where DO_Type = 1 and Do_Domaine = 0 and DO_piece BETWEEN @DO_PIECE_DEB and @DO_PIECE_FIN DELETE FROM F_REGLECH Where DO_Type = 1 and Do_Domaine = 0 and DO_piece BETWEEN @DO_PIECE_DEB and @DO_PIECE_FIN DELETE from F_DOCREGL Where DR_TypeRegl=2 and DO_Type = 1 and Do_Domaine = 0 and DO_piece BETWEEN @DO_PIECE_DEB and @DO_PIECE_FIN DELETE from F_DOCREGL Where DR_TypeRegl=1 and DO_Type = 1 and Do_Domaine = 0 and DO_piece BETWEEN @DO_PIECE_DEB and @DO_PIECE_FIN DELETE from F_DOCREGL Where DR_TypeRegl=0 and DO_Type = 1 and Do_Domaine = 0 and DO_piece BETWEEN @DO_PIECE_DEB and @DO_PIECE_FIN DELETE from F_DOCENTETE Where DO_Type = 1 and Do_Domaine = 0 and DO_piece BETWEEN @DO_PIECE_DEB and @DO_PIECE_FIN END

Le Forum des Utilisateurs Sage

Transfo de doc en SQL !

Transfo de doc en SQL !

Re: Transfo de doc en SQL !

Re: Transfo de doc en SQL !

Re: Transfo de doc en SQL !

Re: Transfo de doc en SQL !

Re: Transfo de doc en SQL !

Re: Transfo de doc en SQL !

Re: Transfo de doc en SQL !

Re: Transfo de doc en SQL !

Qui est en ligne