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
|
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE INS_RECDOSSIER
(
@ADVNUMBER VARCHAR(10)
)
AS
BEGIN
Insert into Dossiers.dbo.RECDOSSIER
select
replicate('0', 10- (len(RIG.ItemSetName))) + RIG.ItemSetName as ADVNUMBER,
RIG.ItemSetName as ADVNUMBER,
case RIG.LEVEL3 when '' then RIG.LEVEL2 else RIG.LEVEL3 END as TYPOLOGIE,
case DosOrEtat.IdStatutORA when 'AD' then '' else DosOrEtat.IdStatutORA END as Etat,
RIG.addDtime as DateIndexation
ISNULL(ReqItemGlobal.LEVEL3,ReqItemGlobal.LEVEL2) as Typo,
FROM
base1.dbo.REITEMGLOBAL RIG,Dossiers.dbo.EtatDossier DosOrEtat
WHERE
RIG.ItemSetName = @ADVNUMBER
replicate('0', 10- (len(RIG.ItemSetName))) + RIG.ItemSetName = replicate('0', 10- (len(@ADVNUMBER))) + @ADVNUMBER
ReqItemGlobal.ItemSetName ='0008685848'
AND
RIG.ItemStatusId = DosOrEtat.IdStatutRec
AND
RIG.Level1 + RIG.Level2 + RIG.LEvel3 NOT IN (SELECT E.LEVEL1 + E.LEVEL2 + E.LEVEL3
FROM EXCLUSION E)
UNION
select
replicate('0', 10- (len(@ADVNUMBER))) + @ADVNUMBER as ADVNUMBER,
@ADVNUMBER as ADVNUMBER,
null as TYPOLOGIE,
'AD' as ETAT,
null as DateIndexation
FROM
base1.dbo.REITEMGLOBAL RIG
WHERE
NOT EXISTS (SELECT ItemGlobal.ItemSetName
FROM base1.dbo.REITEMGLOBAL ItemGlobal
WHERE replicate('0', 10- (len(ItemGlobal.ItemSetName))) + ItemGlobal.ItemSetName = replicate('0', 10- (len(@ADVNUMBER))) + @ADVNUMBER )
NOT EXISTS (SELECT ItemGlobal.ItemSetName
FROM base1.dbo.REITEMGLOBAL ItemGlobal
WHERE ItemGlobal.ItemSetName = @ADVNUMBER )
AND
RIG.Level1 + RIG.Level2 + RIG.LEvel3 NOT IN (SELECT E.LEVEL1 + E.LEVEL2 + E.LEVEL3
FROM EXCLUSION E)
END;
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO |
Partager