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
| USE [UTILITAIRES]
GO
/****** Object: StoredProcedure [dbo].[CONVERT_PDS_PIECE_ART_TOSTRING] Script Date: 08/10/2020 12:57:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[CONVERT_PDS_PIECE_ART_TOSTRING]
-- Add the parameters for the stored procedure here
@ARTCODE VARCHAR(15),
@OUTPUTVAL VARCHAR(5) OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE @ENTIER INT,
@DECIMAL INT,
@ROUND VARCHAR(2),
@PIECE_KILO VARCHAR(1),
@DECIMAL_TO_LETTER VARCHAR(1);
SET @ENTIER = (SELECT(CONVERT(INT, SUBSTRING(CONVERT(VARCHAR,ART_PDS_NET), 0, CHARINDEX('.',CONVERT(VARCHAR,ART_PDS_NET)))))
FROM ERP_CRETS..AG1.GCARTICLE
WHERE ART_CODE = @ARTCODE);
SET @DECIMAL = (SELECT CHARINDEX('.',ART_PDS_NET)
FROM ERP_CRETS..AG1.GCARTICLE
WHERE ART_CODE = @ARTCODE) +1 ;
SET @ROUND = (SELECT RIGHT(SUBSTRING(CONVERT(VARCHAR,ART_PDS_NET),@DECIMAL,1),1)
FROM ERP_CRETS..AG1.GCARTICLE
WHERE ART_CODE = @ARTCODE);
SET @PIECE_KILO = (SELECT CASE
WHEN ART_UNIT_CDE = 'P' THEN 'P'
ELSE 'K' END AS PK
FROM ERP_CRETS..AG1.GCARTICLE
WHERE ART_CODE = @ARTCODE);
SET @DECIMAL_TO_LETTER = (SELECT LETTRE FROM CORRES_LETTRE_DECIMAL
WHERE CHIFFRE = @ROUND);
SET @OUTPUTVAL =
(SELECT CASE
WHEN ART_UNIT_CDE = 'P' THEN CONCAT(@PIECE_KILO,ART_PIECE_COL)
ELSE CONCAT(@PIECE_KILO,@ENTIER,@DECIMAL_TO_LETTER) END AS U
FROM ERP_CRETS..AG1.GCARTICLE
WHERE ART_CODE = @ARTCODE);
SELECT @OUTPUTVAL;
END |
