1: /*
2: Limpa a tabela para o Insert Total baseado na Classificacao
3: */
4:
5: TRUNCATE TABLE SZD030
6:
7: /*
8: Popula a Tabela SZD de acordo com a Linha de Produto e Classificacao do Cliente
9: */
10: INSERT INTO
11: SZD030
12: (
13: ZD_FILIAL,
14: ZD_CLIENTE,
15: ZD_LOJA,
16: ZD_CODTMRG,
17: ZD_LINHAP,
18: D_E_L_E_T_,
19: R_E_C_N_O_,
20: R_E_C_D_E_L_
21: )
22: SELECT
23: SZD.ZD_FILIAL AS ZD_FILIAL,
24: SZD.ZD_CLIENTE AS ZD_CLIENTE,
25: SZD.ZD_LOJA AS ZD_LOJA,
26: SZD.ZD_CODTMRG AS ZD_CODTMRG,
27: ZD_LINHAP AS ZD_LINHAP,
28: ' ' AS D_E_L_E_T_,
29: ROW_NUMBER() OVER ( ORDER BY SZD.R_E_C_N_O_ ) AS R_E_C_N_O_,
30: 0 AS R_E_C_D_E_L_
31: FROM
32: (
33: --Linha de Produto 01
34: SELECT
35: SA1030.A1_FILIAL AS ZD_FILIAL,
36: SA1030.A1_COD AS ZD_CLIENTE,
37: SA1030.A1_LOJA AS ZD_LOJA,
38: CODTMRG.ZD_CODTMRG AS ZD_CODTMRG,
39: '01' AS ZD_LINHAP,
40: 0 AS R_E_C_N_O_
41: FROM
42: SA1030,
43: (
44: SELECT
45: SE1.E1_FILIAL,
46: SE1.E1_CLIENTE,
47: SE1.E1_LOJA,
48: SE1.E1_VALOR,
49: (
50: CASE
51: WHEN
52: SE1.E1_VALOR < 15000
53: THEN
54: '001'
55: ELSE
56: CASE
57: WHEN
58: SE1.E1_VALOR BETWEEN 15000 AND 24999.99
59: THEN
60: '002'
61: ELSE
62: CASE
63: WHEN
64: SE1.E1_VALOR >= 25000
65: THEN '003'
66: END
67: END
68: END
69: ) AS ZD_CODTMRG
70: FROM
71: (
72: SELECT
73: SE1_S.E1_FILIAL,
74: SE1_S.E1_CLIENTE,
75: SE1_S.E1_LOJA,
76: SUM(SE1_S.E1_VALOR) E1_VALOR
77: FROM
78: SE1030 SE1_S
79: WHERE
80: SE1_S.D_E_L_E_T_ <> '*'
81: AND
82: SE1_S.E1_FILIAL = '01'
83: AND
84: SE1_S.E1_EMISSAO BETWEEN '20110101' AND '20111231'
85: GROUP BY
86: SE1_S.E1_FILIAL,
87: SE1_S.E1_CLIENTE,
88: SE1_S.E1_LOJA
89: ) AS SE1
90: ) AS CODTMRG
91: WHERE
92: SA1030.D_E_L_E_T_ <> '*'
93: AND
94: SA1030.A1_FILIAL = ' '
95: AND
96: SA1030.A1_MSBLQL <> '1'
97: AND
98: CODTMRG.E1_FILIAL = '01'
99: AND
100: CODTMRG.E1_CLIENTE = SA1030.A1_COD
101: AND
102: CODTMRG.E1_LOJA = SA1030.A1_LOJA
103: AND
104: NOT EXISTS
105: (
106: SELECT
107: 1
108: FROM
109: SZD030
110: WHERE
111: SZD030.ZD_FILIAL = SA1030.A1_FILIAL
112: AND
113: SZD030.ZD_CLIENTE = SA1030.A1_COD
114: AND
115: SZD030.ZD_LOJA = SA1030.A1_LOJA
116: AND
117: SZD030.ZD_LINHAP = '01'
118: )
119: UNION ALL
120: --Linha de Produto 02
121: SELECT
122: SA1030.A1_FILIAL AS ZD_FILIAL,
123: SA1030.A1_COD AS ZD_CLIENTE,
124: SA1030.A1_LOJA AS ZD_LOJA,
125: CODTMRG.ZD_CODTMRG AS ZD_CODTMRG,
126: '02' AS ZD_LINHAP,
127: 0 AS R_E_C_N_O_
128: FROM
129: SA1030,
130: (
131: SELECT
132: (
133: CASE
134: WHEN
135: SE1.E1_VALOR < 15000
136: THEN
137: '001'
138: ELSE
139: CASE
140: WHEN
141: SE1.E1_VALOR BETWEEN 15000 AND 24999.99
142: THEN
143: '002'
144: ELSE
145: CASE
146: WHEN
147: SE1.E1_VALOR >= 25000
148: THEN '005'
149: END
150: END
151: END
152: ) AS ZD_CODTMRG,
153: SE1.E1_CLIENTE,
154: SE1.E1_LOJA,
155: SE1.E1_VALOR
156: FROM
157: (
158: SELECT
159: SE1_S.E1_CLIENTE,
160: SE1_S.E1_LOJA,
161: SUM(SE1_S.E1_VALOR) E1_VALOR
162: FROM
163: SE1030 SE1_S
164: WHERE
165: SE1_S.D_E_L_E_T_ <> '*'
166: AND
167: SE1_S.E1_EMISSAO BETWEEN '20110101' AND '20111231'
168: GROUP BY
169: SE1_S.E1_CLIENTE,
170: SE1_S.E1_LOJA
171: ) AS SE1
172: ) AS CODTMRG
173: WHERE
174: SA1030.D_E_L_E_T_ <> '*'
175: AND
176: SA1030.A1_MSBLQL <> '1'
177: AND
178: CODTMRG.E1_CLIENTE = SA1030.A1_COD
179: AND
180: CODTMRG.E1_LOJA = SA1030.A1_LOJA
181: AND
182: NOT EXISTS
183: (
184: SELECT
185: 1
186: FROM
187: SZD030
188: WHERE
189: SZD030.ZD_FILIAL = SA1030.A1_FILIAL
190: AND
191: SZD030.ZD_CLIENTE = SA1030.A1_COD
192: AND
193: SZD030.ZD_LOJA = SA1030.A1_LOJA
194: AND
195: SZD030.ZD_LINHAP = '02'
196: )
197: UNION ALL
198: --Linha de Produto 03
199: SELECT
200: SA1030.A1_FILIAL AS ZD_FILIAL,
201: SA1030.A1_COD AS ZD_CLIENTE,
202: SA1030.A1_LOJA AS ZD_LOJA,
203: CODTMRG.ZD_CODTMRG AS ZD_CODTMRG,
204: '03' AS ZD_LINHAP,
205: 0 AS R_E_C_N_O_
206: FROM
207: SA1030,
208: (
209: SELECT
210: (
211: CASE
212: WHEN
213: SE1.E1_VALOR < 15000
214: THEN
215: '001'
216: ELSE
217: CASE
218: WHEN
219: SE1.E1_VALOR BETWEEN 15000 AND 24999.99
220: THEN
221: '002'
222: ELSE
223: CASE
224: WHEN
225: SE1.E1_VALOR >= 25000
226: THEN '003'
227: END
228: END
229: END
230: ) AS ZD_CODTMRG,
231: SE1.E1_CLIENTE,
232: SE1.E1_LOJA,
233: SE1.E1_VALOR
234: FROM
235: (
236: SELECT
237: SE1_S.E1_CLIENTE,
238: SE1_S.E1_LOJA,
239: SUM(SE1_S.E1_VALOR) E1_VALOR
240: FROM
241: SE1030 SE1_S
242: WHERE
243: SE1_S.D_E_L_E_T_ <> '*'
244: AND
245: SE1_S.E1_EMISSAO BETWEEN '20110101' AND '20111231'
246: GROUP BY
247: SE1_S.E1_CLIENTE,
248: SE1_S.E1_LOJA
249: ) AS SE1
250: ) AS CODTMRG
251: WHERE
252: SA1030.D_E_L_E_T_ <> '*'
253: AND
254: SA1030.A1_MSBLQL <> '1'
255: AND
256: CODTMRG.E1_CLIENTE = SA1030.A1_COD
257: AND
258: CODTMRG.E1_LOJA = SA1030.A1_LOJA
259: AND
260: NOT EXISTS
261: (
262: SELECT
263: 1
264: FROM
265: SZD030
266: WHERE
267: SZD030.ZD_FILIAL = SA1030.A1_FILIAL
268: AND
269: SZD030.ZD_CLIENTE = SA1030.A1_COD
270: AND
271: SZD030.ZD_LOJA = SA1030.A1_LOJA
272: AND
273: SZD030.ZD_LINHAP = '03'
274: )
275: ) AS SZD
276:
277: /*
278: Totaliza os Clientes Processados
279: */
280:
281: SELECT
282: ( COUNT(1) / 3 ) AS TOTAL_CLIENTES
283: FROM
284: ( SELECT * FROM SZD030 ) SZD
285:
286: /*
287: Lista os Clientes Processados Classificados por Filial,Codigo, Loja, Linha
288: */
289: SELECT
290: SZD.ZD_FILIAL,
291: SZD.ZD_CLIENTE,
292: SZD.ZD_LOJA,
293: SZD.ZD_LINHAP,
294: SZD.E1_VALOR,
295: SZD030.ZD_CODTMRG
296: FROM
297: SZD030,
298: (
299: SELECT
300: SZD.ZD_FILIAL,
301: SZD.ZD_CLIENTE,
302: SZD.ZD_LOJA,
303: SZD.ZD_LINHAP,
304: SUM(SE1.E1_VALOR) E1_VALOR
305: FROM
306: SZD030 SZD,
307: SE1030 SE1
308: WHERE
309: SZD.D_E_L_E_T_ <> '*'
310: AND
311: SZD.ZD_FILIAL = ' '
312: AND
313: SE1.D_E_L_E_T_ <> '*'
314: AND
315: SE1.E1_FILIAL = '01'
316: AND
317: SZD.ZD_CLIENTE = SE1.E1_CLIENTE
318: AND
319: SZD.ZD_LOJA = SE1.E1_LOJA
320: AND
321: SZD.ZD_LINHAP = '01'
322: AND
323: SE1.E1_EMISSAO BETWEEN '20110101' AND '20111231'
324: GROUP BY
325: SZD.ZD_FILIAL,
326: SZD.ZD_CLIENTE,
327: SZD.ZD_LOJA,
328: SZD.ZD_LINHAP
329: UNION ALL
330: SELECT
331: SZD.ZD_FILIAL,
332: SZD.ZD_CLIENTE,
333: SZD.ZD_LOJA,
334: SZD.ZD_LINHAP,
335: SUM(SE1.E1_VALOR) E1_VALOR
336: FROM
337: SZD030 SZD,
338: SE1030 SE1
339: WHERE
340: SZD.D_E_L_E_T_ <> '*'
341: AND
342: SZD.ZD_FILIAL = ' '
343: AND
344: SE1.D_E_L_E_T_ <> '*'
345: AND
346: SE1.E1_FILIAL = '01'
347: AND
348: SZD.ZD_CLIENTE = SE1.E1_CLIENTE
349: AND
350: SZD.ZD_LOJA = SE1.E1_LOJA
351: AND
352: SZD.ZD_LINHAP = '02'
353: AND
354: SE1.E1_EMISSAO BETWEEN '20110101' AND '20111231'
355: GROUP BY
356: SZD.ZD_FILIAL,
357: SZD.ZD_CLIENTE,
358: SZD.ZD_LOJA,
359: SZD.ZD_LINHAP
360: UNION ALL
361: SELECT
362: SZD.ZD_FILIAL,
363: SZD.ZD_CLIENTE,
364: SZD.ZD_LOJA,
365: SZD.ZD_LINHAP,
366: SUM(SE1.E1_VALOR) E1_VALOR
367: FROM
368: SZD030 SZD,
369: SE1030 SE1
370: WHERE
371: SZD.D_E_L_E_T_ <> '*'
372: AND
373: SZD.ZD_FILIAL = ' '
374: AND
375: SE1.D_E_L_E_T_ <> '*'
376: AND
377: SE1.E1_FILIAL = '01'
378: AND
379: SZD.ZD_CLIENTE = SE1.E1_CLIENTE
380: AND
381: SZD.ZD_LOJA = SE1.E1_LOJA
382: AND
383: SZD.ZD_LINHAP = '03'
384: AND
385: SE1.E1_EMISSAO BETWEEN '20110101' AND '20111231'
386: GROUP BY
387: SZD.ZD_FILIAL,
388: SZD.ZD_CLIENTE,
389: SZD.ZD_LOJA,
390: SZD.ZD_LINHAP
391: ) SZD
392: WHERE
393: SZD030.D_E_L_E_T_ <> '*'
394: AND
395: SZD030.ZD_FILIAL = SZD.ZD_FILIAL
396: AND
397: SZD030.ZD_CLIENTE = SZD.ZD_CLIENTE
398: AND
399: SZD030.ZD_LOJA = SZD.ZD_LOJA
400: AND
401: SZD030.ZD_LINHAP = SZD.ZD_LINHAP
402: ORDER BY
403: SZD.ZD_FILIAL,
404: SZD.ZD_CLIENTE,
405: SZD.ZD_LOJA,
406: SZD.ZD_LINHAP
Comentários
Postar um comentário