ajuda Oracle

ricardos1000 · Abril 6, 2018

Boa tarde a todos, os script abaixo me trás de dentro da tabela de pedidos, todos os produtos vendidos em determinando período, não pode haver duplicação de código do produto, mais esta gerando.

Colunas de retorno da consulta : Código, Produto, volume, Vlr Negociado, Vlr de Tabela, Total da Venda, Custo, Custo x Qtde, Vlr Desconto.

Preciso remover a duplicidade, alguém pode ajudar.

SELECT
X.CODPROD
,X.DESCRPROD
,X.CODVOL
,SUM(X.QTDNEG) AS QTDNEG
,ROUND(SUM(X.VLRTOT_ITEM),2) AS VLRTOT_NEGOCIADO
,ROUND(X.PRECOBASE,4) AS VLR_TABELA
,ROUND(SUM(X.TOTAL_VENDA),2) AS TOTAL_VENDA

SELECT
--X.NUNOTA
X.CODPROD
,X.DESCRPROD
,X.CODVOL
,SUM(X.QTDNEG) AS QTDNEG
,ROUND(SUM(X.VLRTOT_ITEM),2) AS VLRTOT_NEGOCIADO
,ROUND(X.PRECOBASE,4) AS VLR_TABELA
,ROUND(SUM(X.TOTAL_VENDA),2) AS TOTAL_VENDA
,X.CUSTO
,SUM(X.CUSTO * X.QTDNEG) AS CUSTO_X_QTDE
,ROUND(SUM(X.VALOR_DESCONTO),2) AS VALOR_DESCONTO

FROM (SELECT
ITE.CODPROD,
PRO.DESCRPROD,
ITE.CODVOL,
ITE.PRECOBASE,
--CAB.NUNOTA,
EXC.VLRVENDA,
ITE.QTDNEG,

CASE WHEN CAB.CODTIPOPER = '51' THEN
SUM((ITE.VLRTOT - ITE.VLRDESC) * (1 + (CAB.VLRFRETE / (CAB.VLRNOTA-CAB.VLRFRETE))))
ELSE
SUM((ITE.VLRTOT - ITE.VLRDESC + ITE.VLRIPI) * (1 + (CAB.VLRFRETE / (CAB.VLRNOTA-CAB.VLRFRETE))))
END AS VLRTOT_ITEM,

CASE WHEN CAB.CODTIPOPER = '51' THEN
SUM((ITE.PRECOBASE * ITE.QTDNEG) * (1 + (CAB.VLRFRETE / (CAB.VLRNOTA-CAB.VLRFRETE))))
ELSE
SUM((ITE.PRECOBASE * ITE.QTDNEG + ITE.VLRIPI) * (1 + (CAB.VLRFRETE / (CAB.VLRNOTA-CAB.VLRFRETE))))
END AS TOTAL_VENDA,

CASE WHEN CAB.CODTIPOPER = '51' THEN
SUM((ITE.VLRDESC) * (1 + (CAB.VLRFRETE / (CAB.VLRNOTA-CAB.VLRFRETE))))
ELSE
SUM((ITE.VLRDESC + ITE.VLRIPI) * (1 + (CAB.VLRFRETE / (CAB.VLRNOTA-CAB.VLRFRETE))))
END AS VALOR_DESCONTO,

CASE WHEN CAB.CODTIPOPER = '51' THEN
ROUND(SUM((ITE.VLRTOT - ITE.VLRDESC) * (1 + (CAB.VLRFRETE / (CAB.VLRNOTA-CAB.VLRFRETE)))) / (SELECT SUM((ITE.VLRTOT - ITE.VLRDESC) * (1 + (CAB.VLRFRETE / (CAB.VLRNOTA-CAB.VLRFRETE))))
FROM TGFCAB CAB, TGFITE ITE, TGFPRO PRO
WHERE CAB.NUNOTA = ITE.NUNOTA
AND CAB.DTNEG BETWEEN '01-01-2018' AND '06-03-2018'
AND ITE.CODPROD = PRO.CODPROD
AND CAB.TIPMOV = 'P'
AND CAB.CODPARC NOT IN(62,63,64,65,66,67,1043)
AND CAB.CODTIPOPER IN (4,17,49,51,52,53,54,63,66,89,133,135,136,142,143,145,147,165)
AND PRO.USOPROD <> 'M') *100,2)
ELSE
ROUND(SUM((ITE.VLRTOT - ITE.VLRDESC + ITE.VLRIPI) * (1 + (CAB.VLRFRETE / (CAB.VLRNOTA-CAB.VLRFRETE)))) / (SELECT SUM((ITE.VLRTOT - ITE.VLRDESC + ITE.VLRIPI) * (1 + (CAB.VLRFRETE / (CAB.VLRNOTA-CAB.VLRFRETE)))) FROM TGFCAB CAB, TGFITE ITE, TGFPRO PRO
WHERE CAB.NUNOTA = ITE.NUNOTA
AND CAB.DTNEG BETWEEN '01-01-2018' AND '14-03-2018'
AND ITE.CODPROD = PRO.CODPROD
AND CAB.TIPMOV = 'P'
AND PRO.CODGRUPOPROD NOT IN(01050000,01050101)
AND CAB.CODPARC NOT IN(62,63,64,65,66,67,1043)
AND CAB.CODTIPOPER IN (4,17,49,51,52,53,54,63,66,89,133,135,136,142,143,145,147,165)
AND PRO.USOPROD <> 'M' ) *100,2)
END AS PORCENTAGEM,
(SELECT
EXC.VLRVENDA
FROM TGFEXC EXC
WHERE EXC.NUTAB = 36 AND EXC.CODPROD = ITE.CODPROD
)CUSTO

FROM TGFCAB CAB INNER JOIN TGFITE ITE ON (CAB.NUNOTA = ITE.NUNOTA)
INNER JOIN TGFPRO PRO ON (ITE.CODPROD = PRO.CODPROD)
INNER JOIN TGFEXC EXC ON (EXC.CODPROD = ITE.CODPROD)

WHERE CAB.TIPMOV = 'P'
AND CAB.DTNEG BETWEEN '01-01-2018' AND '06-03-2018'
AND CAB.CODPARC NOT IN(62,63,64,65,66,67,1043)
AND PRO.CODGRUPOPROD NOT IN(01050101,01050102,01050103)
AND CAB.CODTIPOPER IN (4,17,49,51,52,53,54,63,66,89,133,135,136,142,143,145,147,165)
AND PRO.USOPROD <> 'M'

GROUP BY ITE.CODPROD,PRO.DESCRPROD,ITE.CODVOL,ITE.PRECOBASE,ITE.VLRDESC,CAB.CODTIPOPER,EXC.VLRVENDA,CAB.NUNOTA,ITE.QTDNEG

) X
GROUP BY X.CODPROD,X.DESCRPROD,X.CODVOL,X.QTDNEG,X.TOTAL_VENDA,X.PRECOBASE,X.VALOR_DESCONTO,X.CUSTO--,X.NUNOTA

ORDER BY X.QTDNEG DESC;

,X.CUSTO
,SUM(X.CUSTO * X.QTDNEG) AS CUSTO_X_QTDE
,ROUND(SUM(X.VALOR_DESCONTO),2) AS VALOR_DESCONTO

FROM (SELECT
ITE.CODPROD,
PRO.DESCRPROD,
ITE.CODVOL,
ITE.PRECOBASE,
EXC.VLRVENDA,
ITE.QTDNEG,