CREATE OR REPLACE VIEW VIEW_QLIKSENSE_DEVOLUCAO AS

(SELECT SUBSTR(( [Link]), 1, 4) AS CODIGOFILIAL,

SUBSTR(( [Link]), 1, 20) AS CODIGOFORNECEDOR,
SUBSTR(( [Link]), 1, 20) AS CODIGOPRODUTO,

[Link] AS PTABELA, --DCARVALHO 03/02/2022

[Link] AS PVENDA, --DCARVALHO 03/02/2022
[Link] AS PDESCONTO, --DCARVALHO 03/02/2022

SUBSTR(( [Link]), 1, 20) AS CODIGOSECAO,

[Link] AS PRODUTO,
[Link],
[Link] AS SECAO,
[Link] AS DEPARTAMENTO,
SUBSTR(( [Link]), 1, 20) AS CODIGODEPARTAMENTO,

[Link],--DCARVALHO 30.06.2021
[Link], --DCARVALHO 30.06.2021
[Link], --DCARVALHO 30.06.2021
[Link], --DCARVALHO 30.06.2021
[Link], --DCARVALHO 30.06.2021

SUBSTR(( [Link]), 1, 20) AS CODIGORCA,

SUBSTR(( [Link]), 1, 20) AS NOME_RCA,
PCPEDC.CODUSUR2 AS CODRCA2, --DCARVALHO 12/05/2022
[Link] AS CODIGOSUPERVISOR,
SUBSTR(( [Link]), 1, 20) AS CODIGOCLIENTE,
SUBSTR(( [Link]), 1, 20) AS CODIGOEMITENTE,
SUBSTR(( [Link]), 1, 20) AS CODIGOPLANOPAGAMENTO, -- DCARVALHO
04/06/2024
SUBSTR(( [Link] || '-' || [Link]), 1, 20) AS
CODIGOCARTEIRA,
SUBSTR(( NVL((SELECT [Link]
FROM PCCARREG CAR
WHERE [Link] = [Link]),
0)), 1, 20) AS CODIGOVEICULO,
SUBSTR(( NVL((SELECT [Link]
FROM PCCARREG CAR
WHERE [Link] = [Link]),
0)), 1, 20) AS CODIGOMOTORISTA,
TRUNC([Link]) AS DATAVENDA,
[Link] AS NUMEROPEDIDO,
[Link] AS CODIGOCOBRANCA,
[Link] AS NUMEROCARREGAMENTO,
[Link] AS NUMERONOTA,
[Link] AS ROTINALANC,
[Link] AS FUNCLANC,
[Link] AS CODIGODEVOLUCAO,
[Link] AS MOTIVODEVOLUCAO,

(CASE
/* FREDSON */
WHEN [Link] IN ('38769', '38360', '38388')
THEN 'FREDSON'--4

/* SUPERVISORES IGNORADOS 8-FILIAIS / 46-VENDAS IMPORT / 605-TRANSFERENCIA

ATACK */
WHEN [Link] IN (SELECT [Link] FROM PCUSUARI
WHERE [Link] IN ('8','46'))
 THEN 'TRANSFERENCIA FILIAIS'--0

/* GILSON FLORA SEM O ATACK */

WHEN [Link] NOT IN ('14427', '32507', '32923')
AND [Link] IN (SELECT [Link] FROM PCPRODUT
WHERE [Link] IN ('5'))
THEN 'GILSON'--2

/* ALESSANDRO */
WHEN [Link] IN (SELECT [Link] FROM PCPRODUT
WHERE [Link] IN ('3','60','65','67'))
THEN 'ALESSANDRO'--1

/* GILSON */
WHEN [Link] IN (SELECT [Link] FROM PCPRODUT
WHERE [Link] IN ('1', '5', '14','15', '20', '27', '28',
'29', '30', '34', '46', '50','61', '69', '73','80', '86', '1000'))
THEN 'GILSON'--2,

/* OMAR */
WHEN [Link] IN (SELECT [Link] FROM PCPRODUT
WHERE [Link] IN
('2','4','10','22','23','24','25','36','41','43','55','56','57','59',
'62','63','70', '89', '90', '100','11','47','48','49'))
THEN 'OMAR'--3

/*\* OMAR PRINGLES*\

WHEN [Link] IN (SELECT [Link] FROM PCPRODUT
WHERE [Link] IN ('10'))
THEN 'OMAR'--6*/

/* ALESSANDRO IMPORTADOS */
WHEN [Link] IN (SELECT [Link] FROM PCPRODUT
WHERE [Link] IN ('42', '71'))
THEN 'ALESSANDRO IMPORTADOS'--5

/* MAURO */
WHEN [Link] IN (SELECT [Link] FROM PCPRODUT
WHERE [Link] IN ('33','51','52','53','64','66','150'))
THEN 'MAURO'--7

ELSE '0' END) GERENTEEQUIPE,

DECODE([Link],5, 'DEVOL BONIFICAC?O', 'DEVOLUC?O') AS

CODIGOOPERACAO,

ROUND(((NVL([Link], 0) *
NVL([Link], 0))*(-1)),6) AS PESOTOTALLIQUIDO,

ROUND(((NVL([Link], 0) *
NVL([Link], 0))*(-1)),6) AS PESOTOTALBRUTO,

ROUND((NVL((SELECT [Link]
FROM PCFORMPROD
WHERE [Link] = [Link]
AND [Link] = [Link]),
1) * CASE
WHEN [Link] IN ('C', 'CB') THEN
0
 ELSE
CASE
WHEN [Link] = 0 THEN
[Link]
ELSE
[Link]
END END) * (-1),
6) AS QUANTIDADE,

ROUND((((NVL((SELECT [Link]
FROM PCFORMPROD
WHERE [Link] = [Link]
AND [Link] = [Link]),
1) * CASE
WHEN [Link] IN ('C', 'CB') THEN
0
ELSE
CASE
WHEN [Link] = 0 THEN
[Link]
ELSE
[Link]
END END)/ NVL([Link], 1)) * (-1)),
6) AS QUANTIDADECAIXA,

ROUND((NVL((SELECT [Link]
FROM PCFORMPROD
WHERE [Link] = [Link]
AND [Link] = [Link]),
1) * CASE
WHEN [Link] IN ('C', 'CB') THEN
0
ELSE
CASE
WHEN [Link] = 0 THEN
[Link]
ELSE
[Link]
END END) * (nvl((Select max([Link]) as alienacao
from pcalienafornec a
where is_number([Link]) = 1
and [Link] = [Link]
group by [Link]),
1)) * (-1),
6) AS QUANTIDADECONVERTIDA,

ROUND((NVL((SELECT [Link]
FROM PCFORMPROD
WHERE [Link] = [Link]
AND [Link] = [Link]),
1) *
CASE
WHEN [Link] IN ('C', 'CB') THEN 0
ELSE CASE
WHEN [Link] = 0 THEN [Link]
ELSE [Link]
END
END) * NVL([Link], [Link]) *
NVL([Link], 0) * (-1),
 6) AS VOLUMETOTAL,

ROUND(( NVL([Link], 0) *
DECODE([Link],5,
[Link] + NVL([Link],0) +
NVL([Link], 0),6,
[Link] + NVL([Link],0) +
NVL([Link], 0),11,
[Link] + NVL([Link],0) +
NVL([Link], 0),
0)),6) AS VALORDEVOLUCAOBONIFICACAO,

ROUND((NVL([Link], 0)
* DECODE([Link],6,0,11,0,
nvl([Link],0))),6) AS VALORCUSTODEVOLUCAO,

ROUND((NVL([Link], 0) *
DECODE([Link], 5, 0, 6, 0, 11, 0,
(DECODE([Link],
0,
[Link],
NULL,
[Link],
[Link]) + NVL([Link], 0) +
NVL([Link], 0) +
NVL(PCMOV.VLFRETE_RATEIO, 0) + NVL([Link], 0) -
NVL([Link], 0)))),
6) AS VALORDEVOLUCAO,

[Link]||(CASE [Link]
WHEN 'B' THEN '- BALC?O'
WHEN 'T' THEN '- TELEMARKETING'
WHEN 'C' THEN '- TELEMARKETING ATIVO (TELEVENDAS) / CONFERENTE'
WHEN 'R' THEN '- TELEMARKETING RECEPTIVO (CONF. VALOR DE PEDIDO)'
WHEN 'O' THEN '- TELEMARKETING PARA RCA 2'
WHEN 'A' THEN '- BALC?O RESERVA'
WHEN 'S' THEN '- VENDA AVULSA'
WHEN 'E' THEN '- BALC?O ESPECIAL - SOMENTE BALC?O'
WHEN 'M' THEN '- TELEMARKETING ESPECIAL - SOMENTE TELEMARKETING'
WHEN 'D' THEN '- DEFINIR NA VENDA'
WHEN 'V' THEN '- BALC?O DE VAREJO'
WHEN 'L' THEN '- VENDA BALC?O DESABILITADO'
WHEN 'F' THEN '- FORCA DE VENDAS'
WHEN 'W' THEN '- E-COMERCE'
END) AS ORIGEMPEDIDO,

(CASE [Link]
WHEN 'M' THEN
'MONTADO'
WHEN 'C' THEN
'CANCELADO'
WHEN 'B' THEN
'BLOQUEADO'
WHEN 'F' THEN
'FATURADO'
WHEN 'L' THEN
'LIBERADO'
 WHEN 'P' THEN
'PENDENTE'
END) AS POSICAOPEDIDO,

[Link] AS NOMEFORNECEDOR,/*ADD POR DCARVALHO 10.06.21*/

[Link], --DCARVALHO 30.06.2021
[Link], --DCARVALHO 30.06.2021

[Link], /*ADD POR DCARVALHO A PEDIDO DA IN 21.06.2021 */

[Link] AS NOMESUPERVISOR, /*ADD POR MAXWELL 14.04.21*/

[Link] AS CODIGOGERENTE, /*ADD POR MAXWELL 14.04.21*/
[Link] AS NOMEGERENTE, /*ADD POR MAXWELL 14.04.21*/
[Link] AS NOMERCA, /*ADD POR MAXWELL 14.04.21*/
[Link] AS SITUACAORCA, /*ADD POR MAXWELL 14.04.21*/
(SELECT [Link] FROM PCCARREG CAR WHERE [Link] =
[Link]) AS CODROTAPRINC, -- ADD POR DCARVALHO 17.04.2021
[Link] AS OBSERVACAO, -- ADD POR DCARVALHO 03.02.2022
PCPEDC.OBS1 AS OBSERVACAO1,-- ADD POR DCARVALHO 03.02.2022
PCPEDC.OBS2 AS OBSERVACAO2,-- ADD POR DCARVALHO 03.02.2022
[Link],
NVL([Link],0) PERCOM-- ADD POR DCARVALHO 08.04.2024

FROM PCNFENT,
PCESTCOM,
PCEMPR,
PCNFSAID,
PCMOV,
PCPRODUT,
PCCLIENT,
PCFORNEC,
PCPRACA,
PCTABDEV,
PCTABDEV PCTABDEV2,
PCDEPTO,
PCSECAO,
PCUSUARI,
PCPLPAG,
PCSUPERV,
PCATIVI,
PCPEDC,
PCCIDADE,
PCMARCA,
PCGERENTE,
PCROTAEXP

WHERE [Link] = [Link]

AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link](+)
AND [Link] = [Link](+)
AND [Link] = [Link](+)
AND [Link] = [Link](+)
AND [Link] = [Link](+)
AND [Link] = [Link](+)
AND [Link] = [Link](+)
AND PCCLIENT.CODATV1 = [Link](+)
 AND [Link] = [Link](+)
AND [Link] = [Link](+)
AND [Link] = [Link](+)
AND NVL([Link], [Link]) = [Link]
AND [Link] = [Link](+)
AND [Link] = [Link](+)
AND [Link] = [Link](+)
-- AND [Link] <> 0 comentando para considerar devolu??es
avulsas
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] IN ('6', '7')
AND NVL([Link], 0) IN (131, 132, 231, 232, 199, 299)
AND [Link] IS NULL
AND [Link] = 'ED'
AND NVL([Link], 'X') <> 'NF CANCELADA'
AND NVL([Link], 0) NOT IN (4, 10, 13, 20, 98, 99))
;