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
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
| ALTER PROCEDURE [dbo].[xsp_lbsa_getArtInfo4CC]
@ar_code nvarchar(30)
,@cl_code nvarchar(20)
AS
BEGIN
DECLARE @last2 nvarchar(2)
DECLARE @ar_code_brut nvarchar(30)
DECLARE @ar_code_base nvarchar(30)
SET NOCOUNT ON;
IF (@ar_code !='')
BEGIN
--SELECT @ar_code='1520d1.55TB', @cl_code='0296'
SELECT @last2=right(@ar_code,2), @ar_code_brut= @ar_code
IF EXISTS(SELECT nom FROM [07_traitements] WHERE nom=@last2)
BEGIN
SELECT @ar_code_base=@ar_code, @ar_code=left(@ar_code,len(@ar_code)-2), @ar_code_brut= @ar_code
END
SELECT @ar_code=@ar_code + '%'
SET NOCOUNT OFF;
SELECT * FROM (
SELECT
articles.ar_code
--,lbsa_reap_OFEnCours as cim_ofnum
--,isnull(QTECDEE,0) + isnull(QTESTOC,0) as cim_ofqte
,listof as cim_ofnum
,isnull(tqte,0) as cim_ofqte
,nom as cim_ofnextop
,isnull(stklbsa.stk_lbsa,0) as stk_lbsa
,isnull(articles.ar_stockmin,0) as min_lbsa
,isnull(stkcli.stk_cli,0) as stk_cli
,isnull(smincli.min_cli,0) as min_cli
,isnull(stkothers.stk_oth,0) as stk_others
,isnull(cc.cc_solde,0) as cc_solde,
as_conso_yc, as_consob_yc,
as_conso_y1, as_consob_y1
FROM
(SELECT ar_code, lbsa_reap_ofencours, ar_stockmin FROM articles WITH (NOLOCK)
WHERE
ar_code not in ('cf','6000') and
ar_code like @ar_code
and ar_code not like @ar_code_brut + '[0-9]%'
) as articles
-- LEFT JOIN cimproductimport..[OF] cimof WITH (NOLOCK)
-- ON cimof.numof = articles.lbsa_reap_OFEnCours
-- LEFT JOIN vlbsa_cimproduct_prochaineOpParOF WITH (NOLOCK)
-- ON cimof.numof = vlbsa_cimproduct_prochaineOpParOF.numof
LEFT JOIN
(SELECT
li_article
,sum(li_qte * CASE WHEN li_stock=1 THEN 1 ELSE -1 END) as stk_lbsa
FROM
lignes with (NOLOCK)
WHERE li_article LIKE @ar_code
AND li_date>'01.01.2007'
AND li_stock !=0
AND li_nostock =0
GROUP BY li_article
) stklbsa ON stklbsa.li_article = articles.ar_code
LEFT JOIN
(SELECT
li_article
,sum(li_qte * CASE WHEN li_stock=1 THEN 1 ELSE -1 END) as stk_cli
FROM
lignes with (NOLOCK)
WHERE li_article LIKE @ar_code
AND li_date>'01.01.2007'
AND li_stock !=0
AND li_nostock IN (SELECT ts_no FROM stocksclients WHERE cl_code=@cl_code)
GROUP BY li_article
) stkcli ON stkcli.li_article = articles.ar_code
LEFT JOIN
(SELECT
ar_code
,cast(sum(sta_stockmin) as int) as min_cli
FROM
stocksMinArticles WITH (NOLOCK)
WHERE
ar_code like @ar_code
AND ts_no IN (SELECT ts_no FROM stocksclients WHERE cl_code=@cl_code)
GROUP BY ar_code
) smincli ON smincli.ar_code = articles.ar_code
LEFT JOIN
(
SELECT
li_article
,sum(li_qte * CASE WHEN li_stock=1 THEN 1 ELSE -1 END) stk_oth
FROM
lignes with (NOLOCK)
WHERE li_article LIKE @ar_code
AND li_date>'01.01.2007'
AND li_stock !=0
AND li_nostock NOT IN (SELECT ts_no FROM stocksclients WHERE cl_code=@cl_code)
AND li_nostock !=0
GROUP BY li_article
) stkothers ON stkothers.li_article = articles.ar_code
LEFT JOIN
(SELECT
li_article
,sum(li_solde) as cc_solde
FROM
lignes with (NOLOCK)
WHERE li_article LIKE @ar_code
AND li_date>'01.01.2007'
AND li_typedoc='CC'
AND li_posteouvert!=0
GROUP BY li_article
) cc ON cc.li_article = articles.ar_code
LEFT JOIN vlbsa_cimproduct_articles_of_encours cimof ON cimof.numdossier = articles.ar_code
LEFT JOIN vlbsa_cimproduct_prochaineOpParOF WITH (NOLOCK)
ON cimof.numof = vlbsa_cimproduct_prochaineOpParOF.numof
INNER JOIN articles_statistiques WITH (NOLOCK)
ON articles_statistiques.ar_code = articles.ar_code
WHERE
articles.ar_code like @ar_code
) as subsel
WHERE
((cim_ofqte+stk_lbsa+stk_cli+stk_others+min_lbsa+min_cli) !=0) or ar_code=@ar_code_base
ORDER by ar_code
END
END |
requêtes éxécutant une procédure stockée
Répondre avec citation
, les résultat sont là mais le hic est que je ne peux pas copier l'ensemble des résultats en une fois!! Je vais donc tenter de mettre tout ça dans une table temporaire.
Partager