113 lines
5.6 KiB
SQL
113 lines
5.6 KiB
SQL
-- +goose Up
|
|
-- +goose StatementBegin
|
|
CREATE OR REPLACE PROCEDURE VITRUVIO.PRC_FLASH_CARGA_RESUMO_VENDA (
|
|
p_data_inicial IN DATE,
|
|
p_data_final IN DATE
|
|
) AS
|
|
BEGIN
|
|
DELETE FROM TB_FLASH_NODO_RESUMO_VENDA
|
|
WHERE data_referencia >= TRUNC(p_data_inicial)
|
|
AND data_referencia < TRUNC(p_data_final) + 1;
|
|
|
|
INSERT INTO TB_FLASH_NODO_RESUMO_VENDA (
|
|
id_resumo, id_nodo, data_referencia, ano_referencia, mes_referencia, dia_referencia,
|
|
valor_venda, valor_meta_propria, valor_meta_filhos, valor_meta_efetiva,
|
|
percentual_meta_propria, percentual_meta_filhos, percentual_meta_efetiva,
|
|
desvio, percentual_atingimento, data_apuracao, nro_segmento, nome_segmento
|
|
)
|
|
WITH venda_direta AS (
|
|
SELECT f.id_nodo,
|
|
TRUNC(f.data_referencia) AS data_referencia,
|
|
EXTRACT(YEAR FROM TRUNC(f.data_referencia)) AS ano_referencia,
|
|
EXTRACT(MONTH FROM TRUNC(f.data_referencia)) AS mes_referencia,
|
|
EXTRACT(DAY FROM TRUNC(f.data_referencia)) AS dia_referencia,
|
|
SUM(f.vlr_venda) AS valor_venda,
|
|
MAX(f.nrosegmento) AS nro_segmento,
|
|
MAX(f.nome_segmento) AS nome_segmento
|
|
FROM TB_FLASH_FATO_VENDA f
|
|
WHERE f.data_referencia >= TRUNC(p_data_inicial)
|
|
AND f.data_referencia < TRUNC(p_data_final) + 1
|
|
GROUP BY f.id_nodo, TRUNC(f.data_referencia)
|
|
),
|
|
hierarquia AS (
|
|
SELECT CONNECT_BY_ROOT n.id_nodo AS id_nodo_ancestral,
|
|
n.id_nodo AS id_nodo_descendente
|
|
FROM TB_FLASH_NODO n
|
|
CONNECT BY PRIOR n.id_nodo = n.id_nodo_pai
|
|
),
|
|
venda_hierarquia AS (
|
|
SELECT h.id_nodo_ancestral AS id_nodo,
|
|
vd.data_referencia, vd.ano_referencia, vd.mes_referencia, vd.dia_referencia,
|
|
SUM(vd.valor_venda) AS valor_venda
|
|
FROM hierarquia h
|
|
JOIN venda_direta vd ON vd.id_nodo = h.id_nodo_descendente
|
|
GROUP BY h.id_nodo_ancestral, vd.data_referencia, vd.ano_referencia, vd.mes_referencia, vd.dia_referencia
|
|
),
|
|
meta_propria_diaria AS (
|
|
SELECT m.id_nodo,
|
|
TRUNC(m.data_referencia) AS data_referencia,
|
|
EXTRACT(YEAR FROM TRUNC(m.data_referencia)) AS ano_referencia,
|
|
EXTRACT(MONTH FROM TRUNC(m.data_referencia)) AS mes_referencia,
|
|
EXTRACT(DAY FROM TRUNC(m.data_referencia)) AS dia_referencia,
|
|
MAX(m.percentual_meta) AS percentual_meta_propria,
|
|
SUM(m.valor_meta) AS valor_meta_propria
|
|
FROM TB_FLASH_META_VENDA m
|
|
WHERE m.data_referencia >= TRUNC(p_data_inicial)
|
|
AND m.data_referencia < TRUNC(p_data_final) + 1
|
|
GROUP BY m.id_nodo, TRUNC(m.data_referencia)
|
|
),
|
|
meta_filhos_diaria AS (
|
|
SELECT pai.id_nodo,
|
|
mpd.data_referencia, mpd.ano_referencia, mpd.mes_referencia, mpd.dia_referencia,
|
|
SUM(mpd.valor_meta_propria) AS valor_meta_filhos
|
|
FROM TB_FLASH_NODO pai
|
|
JOIN TB_FLASH_NODO filho ON filho.id_nodo_pai = pai.id_nodo
|
|
JOIN meta_propria_diaria mpd ON mpd.id_nodo = filho.id_nodo
|
|
GROUP BY pai.id_nodo, mpd.data_referencia, mpd.ano_referencia, mpd.mes_referencia, mpd.dia_referencia
|
|
),
|
|
base AS (
|
|
SELECT id_nodo, data_referencia, ano_referencia, mes_referencia, dia_referencia FROM venda_hierarquia
|
|
UNION
|
|
SELECT id_nodo, data_referencia, ano_referencia, mes_referencia, dia_referencia FROM meta_propria_diaria
|
|
UNION
|
|
SELECT id_nodo, data_referencia, ano_referencia, mes_referencia, dia_referencia FROM meta_filhos_diaria
|
|
)
|
|
SELECT SEQ_TB_FLASH_NODO_RESUMO_VENDA.NEXTVAL,
|
|
b.id_nodo, b.data_referencia, b.ano_referencia, b.mes_referencia, b.dia_referencia,
|
|
NVL(vh.valor_venda, 0) AS valor_venda,
|
|
mpd.valor_meta_propria,
|
|
mfd.valor_meta_filhos,
|
|
CASE WHEN mpd.valor_meta_propria IS NOT NULL THEN mpd.valor_meta_propria ELSE mfd.valor_meta_filhos END AS valor_meta_efetiva,
|
|
mpd.percentual_meta_propria,
|
|
CAST(NULL AS NUMBER(10,4)) AS percentual_meta_filhos,
|
|
CASE WHEN mpd.percentual_meta_propria IS NOT NULL THEN mpd.percentual_meta_propria ELSE NULL END AS percentual_meta_efetiva,
|
|
NVL(vh.valor_venda, 0) - NVL(CASE WHEN mpd.valor_meta_propria IS NOT NULL THEN mpd.valor_meta_propria ELSE mfd.valor_meta_filhos END, 0) AS desvio,
|
|
CASE
|
|
WHEN NVL(CASE WHEN mpd.valor_meta_propria IS NOT NULL THEN mpd.valor_meta_propria ELSE mfd.valor_meta_filhos END, 0) = 0 THEN NULL
|
|
ELSE NVL(vh.valor_venda, 0) / CASE WHEN mpd.valor_meta_propria IS NOT NULL THEN mpd.valor_meta_propria ELSE mfd.valor_meta_filhos END * 100
|
|
END AS percentual_atingimento,
|
|
SYSDATE,
|
|
vd.nro_segmento,
|
|
vd.nome_segmento
|
|
FROM base b
|
|
LEFT JOIN venda_hierarquia vh ON vh.id_nodo = b.id_nodo AND vh.data_referencia = b.data_referencia
|
|
LEFT JOIN meta_propria_diaria mpd ON mpd.id_nodo = b.id_nodo AND mpd.data_referencia = b.data_referencia
|
|
LEFT JOIN meta_filhos_diaria mfd ON mfd.id_nodo = b.id_nodo AND mfd.data_referencia = b.data_referencia
|
|
LEFT JOIN venda_direta vd ON vd.id_nodo = b.id_nodo AND vd.data_referencia = b.data_referencia;
|
|
|
|
COMMIT;
|
|
END;
|
|
-- +goose StatementEnd
|
|
|
|
-- +goose Down
|
|
-- +goose StatementBegin
|
|
DECLARE
|
|
v_count NUMBER;
|
|
BEGIN
|
|
SELECT COUNT(*) INTO v_count FROM user_objects WHERE object_name = 'PRC_FLASH_CARGA_RESUMO_VENDA' AND object_type = 'PROCEDURE';
|
|
IF v_count > 0 THEN
|
|
EXECUTE IMMEDIATE 'DROP PROCEDURE PRC_FLASH_CARGA_RESUMO_VENDA';
|
|
END IF;
|
|
END;
|
|
-- +goose StatementEnd
|