1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82
|
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
DROP PROCEDURE [sysadm].[moSelectionBranche]
GO
-- =============================================
-- Author: <OP>
-- Create date: <27/07/2006>
-- Description:
-- <Permet de selectionner les branches adhérentes définies par les
-- arguments passés en paramètre>
-- =============================================
CREATE PROCEDURE [sysadm].[moSelectionBranche] (@matricule_ent
varchar(50) = null, @raisonsociale varchar(30) = null, @nrid decimal(15,0)
= null, @region varchar(30) = null)
AS
BEGIN
SET NOCOUNT ON;
--- Rajout du pourcentage pour la recherche ---
select @matricule_ent = IsNull(rtrim(@matricule_ent),'')+'%',
@raisonsociale = IsNull(rtrim(@raisonsociale),'') + '%',
@region = IsNull(rtrim(@region), '') + '%'
--- Création de la table temporaire ---
CREATE TABLE #LISTE_BRANCHE (idEntreprise decimal(15,0), idBranche
decimal(15,0), refBranche char(5) COLLATE French_CI_AI, libelleBranche
varchar(50) COLLATE French_CI_AI, nomEntreprise varchar(50) COLLATE
French_CI_AI, matricule_ent varchar(15) COLLATE French_CI_AI)
--- Recherche des branches faisant partis des critères de recherche,
et étant adhérents à ce jour ---
--- Lorsque le nrid est passé en paramètre, on ignore les autres
paramètres
If @nrid is null
BEGIN
INSERT INTO #LISTE_BRANCHE (idEntreprise, idBranche, refBranche,
libelleBranche, nomEntreprise, matricule_ent)
SELECT DISTINCT so0entreprise.nrid, xbr00.nrid, pj0.ref, xbr00.libelle,
so0entreprise.societe, so0entreprise.depart
FROM sysadm.so0 so0, sysadm.pj0 pj0, sysadm.xbr0 xbr0,
sysadm.lnk0 lnk0, sysadm.so0 so0entreprise, sysadm.xbr00 xbr00
WHERE xbr0.pj0_nrid = pj0.nrid and so0.nrid = pj0.so0_nrid
and xbr0.rad != 1 and xbr0.adh = 1 and so0.nrid = lnk0.lnk2_nrid
and lnk0.template is null
and so0.template is null and pj0.template is null and lnk0.lnk1_nrid =
so0entreprise.nrid
and xbr00.ref = pj0.ref and so0entreprise.societe like
@raisonsociale
and so0entreprise.depart like @matricule_ent and so0.reg_code_2
like @region
END
Else
BEGIN
INSERT INTO #LISTE_BRANCHE (idEntreprise, idBranche, refBranche,
libelleBranche, nomEntreprise, matricule_ent)
SELECT DISTINCT so0entreprise.nrid, xbr00.nrid, pj0.ref, xbr00.libelle,
so0entreprise.societe, so0entreprise.depart
FROM sysadm.so0 so0, sysadm.pj0 pj0, sysadm.xbr0 xbr0,
sysadm.lnk0 lnk0, sysadm.so0 so0entreprise, sysadm.xbr00 xbr00
WHERE xbr0.pj0_nrid = pj0.nrid and so0.nrid = pj0.so0_nrid
and xbr0.rad != 1 and xbr0.adh = 1 and so0.nrid = lnk0.lnk2_nrid
and lnk0.template is null
and so0.template is null and pj0.template is null and lnk0.lnk1_nrid =
so0entreprise.nrid
and xbr00.ref = pj0.ref
and so0entreprise.nrid = @nrid and so0.reg_code_2 like @region
END
--- Retourne les valeurs de la table temporaire ---
SELECT idEntreprise, idBranche, refBranche, libelleBranche,
nomEntreprise, matricule_ent
FROM #LISTE_BRANCHE
ORDER BY idEntreprise, refBranche
RETURN 1
END |
Partager