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
|
ALTER PROCEDURE [dbo].[ps_EDI_LAUCH_INVOIC]
@p_sENV varchar(30)
AS
DECLARE @iENTFAC_NUMFAC int
--traitement edi
DECLARE @sRESULT char(16)
DECLARE @sREP varchar(150)
DECLARE @sCMD varchar(2000)
DECLARE @sNOMFIC varchar(50)
--initialisation de la commande BCP
SET @sCMD = 'master..xp_cmdshell ''BCP "SELECT FLIGNE FROM KLAUS.dbo.EDIJDE_FICHIERS WHERE DOCNUM = #document#" queryout "#fichier#" -c -t; -T -Usa -SWIPRD065'''
SELECT @sREP = PARAM.ZONEPAR1+'\' FROM PARAM WHERE PARAM.TYPEPAR = 'EDIREP' AND PARAM.CODEPAR = @p_sENV
SET NOCOUNT ON
DECLARE curseur CURSOR FOR
SELECT TOP 1 ENTFAC.NUMFAC
FROM ENTFAC WITH (NOLOCK)
INNER JOIN EDIJDE_PARTN WITH (NOLOCK) ON EDIJDE_PARTN.CLIENT = ENTFAC.CLIFAC
AND EDIJDE_PARTN.DIVISION = ENTFAC.DIVISION AND EDIJDE_PARTN.MESCOD = 'INVOIC01'
AND EDIJDE_PARTN.ACTIVE = '1'
WHERE ENTFAC.TOPEDI <> '2' AND ENTFAC.TOPCOMPTA = '1' ORDER BY NUMFAC
OPEN curseur
FETCH NEXT FROM curseur INTO @iENTFAC_NUMFAC
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
EXECUTE dbo.ps_EDIJDE_INVOIC @iENTFAC_NUMFAC, @sRESULT OUTPUT
IF (@sRESULT IS NOT NULL)
BEGIN
PRINT @sRESULT
--préparer le nom du fichier
SELECT @sNOMFIC = 'INVOIC01_'+CAST(@iENTFAC_NUMFAC AS varchar)+'_'+dbo.fn_UserDateTime('DATETIME',GETDATE())+'.txt'
--le chemin de collecte
SET @sREP = @sREP+@sNOMFIC
SET @sCMD = REPLACE(@sCMD, '#document#', @sRESULT)
SET @sCMD = REPLACE(@sCMD, '#fichier#', @sREP)
EXECUTE (@sCMD) -- SELECT @sCMD
--mettre à jour les fichiers log
UPDATE EDIJDE_FICHIERS SET NOMFIC = @sNOMFIC, STATUS = '50' WHERE DOCNUM = @sRESULT
UPDATE EDIJDE_DOC SET STATUS = '50' WHERE DOCNUM = @sRESULT
--mettre à jour la facture
UPDATE ENTFAC SET ENTFAC.TOPEDI = '2', ENTFAC.DOCNUM = @sRESULT WHERE ENTFAC.NUMFAC = @iENTFAC_NUMFAC
END ELSE
BEGIN
UPDATE EDIJDE_FICHIERS SET NOMFIC = @sNOMFIC, STATUS = '59' WHERE DOCNUM = @sRESULT
UPDATE EDIJDE_DOC SET STATUS = '59' WHERE DOCNUM = @sRESULT
END
END
FETCH NEXT FROM curseur INTO @iENTFAC_NUMFAC
END
DEALLOCATE curseur
SET NOCOUNT OFF |
