/// USE [SCHNEIDER] GO /****** Object: StoredProcedure [dbo].[EXT_CREATION_OFFRES_MANQUANTES] Script Date: 30/08/2016 23:03:37 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO — ============================================= — Author: — Create date: <30/08/2016> — Description: < -- ============================================= --POUR MEMOIRE LA REQUETE PERMETTANT DE RETROUVER LES OFFRES MANQUANTES : --select [PCVNUM],[PCVID] from [dbo].[PIECEVENTES] where [PINCODE] = 'OFFRES' and [PCVID] not in (select pcid from [dbo].[PIECEOFFRE]) CREATE PROCEDURE [dbo].[EXT_CREATION_OFFRES_MANQUANTES] @PCVNUM varchar(50) AS BEGIN SET NOCOUNT ON; declare @PCVID int declare @NBOFFRE int declare @NBADR int declare @DATEPREVU date ---RECENCEMENT DES ID select @PCVID = I.[PCVID], @DATEPREVU = [PCVDATEEFFET] from [dbo].[PIECEVENTES] I where I.[PINCODE] = 'OFFRES' and i.[PCVNUM] = @PCVNUM and I.[PCVID] not in (select J.pcid from [dbo].[PIECEOFFRE] J); --LECTURE DES COMPTEURS D ENREGISTREMENTS SELECT @NBOFFRE = CTNLASTID FROM [dbo].[IDENTIFIANTTABLE] WHERE CTNTABLE = 'pieceoffre'; SELECT @NBADR = CTNLASTID FROM [dbo].[IDENTIFIANTTABLE] WHERE CTNTABLE = 'adresses'; -- CREATION DE L'ENREGISTREMENT MANQUANT OFFRE insert into [dbo].[PIECEOFFRE] ([POFID] ,[PCID] ,[PCCLASSE] ,[ADRID] ,[POFETAT] ,[POFSTATUT] ,[POFTAUXCOMMERCIAL] ,[POFTAUXDIRECTION] ,[POFDATEPREVU] ,[POFISSYNCHRONISABLE] ,[POFISPRINCIPALE] ,[PCID_PRINCIPALE] ,[POFDECLINAISON]) select @NBOFFRE + 1 ,@PCVID ,'PCV' ,@NBADR + 1 ,'D' ,'C' ,'0.5000' ,'0.5000' ,@DATEPREVU ,'N' ,'O' ,@PCVID ,'0' ; -- CREATION DE L'ENREGISTREMENT MANQUANT ADRESSE insert into [dbo].[ADRESSES] ([ADRID],[ADRSOCIETE]) select @NBADR + 1,'ADRESSE RECUPEREE' --MISE A JOUR DES NOUVEAUX COMPTEURS UPDATE [dbo].[IDENTIFIANTTABLE] set CTNLASTID = @NBOFFRE + 1 WHERE CTNTABLE = 'pieceoffre' UPDATE [dbo].[IDENTIFIANTTABLE] set CTNLASTID = @NBADR + 1 WHERE CTNTABLE = 'adresses' END GO ///