Calculo do custo médio

*CRIAÇÃO DE TABELA TEMPORARIA

CREATE TABLE #ZB9CUSTOM

( ZB9_FILIAL VARCHAR(6),

ZB9_LOCAL VARCHAR(2),

ZB9_COD VARCHAR(15),

ZB9_DTMOV VARCHAR(8),

ZB9_QTDE FLOAT,

ZB9_SALDO FLOAT,

ZB9_CUSTOMED FLOAT,

ZB9_RECNO INT IDENTITY(1,1) PRIMARY KEY)

-------------------------------------------------------------------------------------------------------------------------

*CRIAÇÃO DA PROCEDURE

CREATE PROCEDURE #SPC_CUSTOMEDIO

AS

BEGIN

DECLARE

@ZB9_FILIAL VARCHAR(6),

@ZB9_LOCAL VARCHAR(2),

@ZB9_COD VARCHAR(15),

@ZB9_DTMOV VARCHAR(8),

@ZB9_QTDE FLOAT,

@ZB9_SALDO FLOAT,

@ZB9_CUSTOMED FLOAT,

@ZB9_TEMP FLOAT

DELETE

FROM #ZB9CUSTOM

DECLARE cCURSOR

CURSOR FOR

SELECT TMP.FILIAL, TMP.PRODUTO, TMP.ARM, TMP.DTMOV, SUM(TMP.QTDESTQ) AS QTDESTQ, SUM(TMP.QTDESTQ) AS QTDEREG, AVG(CUSTOMED) AS CUSTOMED

FROM

( SELECT D1_FILIAL AS FILIAL, D1_COD AS PRODUTO, D1_LOCAL AS ARM, D1_EMISSAO AS DTMOV, (D1_QUANT) * (1) AS QTDESTQ, D1_CUSTO AS CUSTOMED

FROM SD1010 SD1, SF4010 SF4

WHERE D1_FILIAL <> ' '

AND SD1.D_E_L_E_T_ = ' '

AND F4_FILIAL = SUBSTRING(D1_FILIAL,1,2)

AND F4_CODIGO = D1_TES

AND F4_ESTOQUE = 'S'

AND SF4.D_E_L_E_T_ = ' '

AND D1_EMISSAO > ( SELECT MIN(SB9.B9_DATA)

FROM SB9010 SB9

WHERE SB9.D_E_L_E_T_ <> '*'

AND LEN(SB9.B9_DATA) > 0

AND SB9.B9_FILIAL = SD1.D1_FILIAL

AND SB9.B9_LOCAL = SD1.D1_LOCAL

AND SB9.B9_COD = SD1.D1_COD )

UNION ALL

SELECT D2_FILIAL AS FILIAL, D2_COD AS PRODUTO, D2_LOCAL AS ARM, D2_EMISSAO AS DTMOV, (D2_QUANT) * (-1) AS QTDESTQ, D2_CUSTO1 AS CUSTOMED

FROM SD2010 SD2, SF4010 SF4

WHERE D2_FILIAL <> ' '

AND SD2.D_E_L_E_T_ = ' '

AND F4_FILIAL = SUBSTRING(D2_FILIAL,1,2)

AND F4_CODIGO = D2_TES

AND F4_ESTOQUE = 'S'

AND SF4.D_E_L_E_T_ = ' '

AND D2_EMISSAO > ( SELECT MIN(SB9.B9_DATA)

FROM SB9010 SB9

WHERE SB9.D_E_L_E_T_ <> '*'

AND LEN(SB9.B9_DATA) > 0

AND SB9.B9_FILIAL = SD2.D2_FILIAL

AND SB9.B9_LOCAL = SD2.D2_LOCAL

AND SB9.B9_COD = SD2.D2_COD )

UNION ALL

SELECT D3_FILIAL AS FILIAL, D3_COD AS PRODUTO, D3_LOCAL AS ARM, D3_EMISSAO AS DTMOV, (D3_QUANT) * (-1) AS QTDESTQ, D3_CUSTO1 AS CUSTOMED

FROM SD3010 SD3

WHERE SD3.D3_QUANT > 0

AND SD3.D3_CF = 'RE4'

AND SD3.D_E_L_E_T_ <> '*'

AND D3_EMISSAO > ( SELECT MIN(SB9.B9_DATA)

FROM SB9010 SB9

WHERE SB9.D_E_L_E_T_ <> '*'

AND LEN(SB9.B9_DATA) > 0

AND SB9.B9_FILIAL = SD3.D3_FILIAL

AND SB9.B9_LOCAL = SD3.D3_LOCAL

AND SB9.B9_COD = SD3.D3_COD )

UNION ALL

SELECT D3_FILIAL AS FILIAL, D3_COD AS PRODUTO, D3_LOCAL AS ARM, D3_EMISSAO AS DTMOV, (D3_QUANT) * (1) AS QTDESTQ, D3_CUSTO1 AS CUSTOMED

FROM SD3010 SD3

WHERE SD3.D3_QUANT > 0

AND SD3.D3_CF = 'DE4'

AND SD3.D_E_L_E_T_ <> '*'

AND D3_EMISSAO > ( SELECT MIN(SB9.B9_DATA)

FROM SB9010 SB9

WHERE SB9.D_E_L_E_T_ <> '*'

AND LEN(SB9.B9_DATA) > 0