268 lines
7.9 KiB
MySQL
268 lines
7.9 KiB
MySQL
-- Table HIST_BLOQUEIO_SEMANAL
|
|
|
|
-- CREATE TABLE
|
|
|
|
CREATE TABLE HIST_BLOQUEIO_SABADO (
|
|
DATA_SABADO DATE NOT NULL,
|
|
TIPO_BLOQUEIO VARCHAR2(30) NOT NULL,
|
|
CODFILIAL VARCHAR2(10) NOT NULL,
|
|
CODPROD NUMBER NOT NULL,
|
|
DESCRICAO VARCHAR2(200),
|
|
MARCA VARCHAR2(80),
|
|
QTESTGER NUMBER(18,4),
|
|
QT NUMBER(18,4),
|
|
CUSTOFIN NUMBER(18,6),
|
|
VALOR NUMBER(18,2),
|
|
DATA_TIPO DATE, -- (DADOS.DATA)
|
|
CODMOTIVO NUMBER,
|
|
MOTIVO VARCHAR2(200),
|
|
CODFUNC NUMBER,
|
|
FUNCIONARIO VARCHAR2(120),
|
|
HISTORICO VARCHAR2(4000),
|
|
FAIXA_PRIM VARCHAR2(30),
|
|
DT_CARGA TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
|
|
CONSTRAINT PK_HIST_BLOQ_SAB PRIMARY KEY
|
|
(DATA_SABADO, CODFILIAL, CODPROD, TIPO_BLOQUEIO)
|
|
);
|
|
CREATE INDEX IX_HIST_BLOQ_SAB_01
|
|
ON HIST_BLOQUEIO_SABADO (CODFILIAL, CODPROD, DATA_SABADO);
|
|
CREATE INDEX IX_HIST_BLOQ_SAB_02
|
|
ON HIST_BLOQUEIO_SABADO (DATA_SABADO, TIPO_BLOQUEIO, FAIXA_PRIM);
|
|
|
|
|
|
|
|
-- CREATE PROCEDURE
|
|
|
|
CREATE OR REPLACE PROCEDURE PRC_SNAPSHOT_BLOQUEIO_SABADO (
|
|
P_DATA_BASE IN DATE DEFAULT SYSDATE
|
|
) AS
|
|
V_SABADO DATE;
|
|
BEGIN
|
|
V_SABADO := TRUNC(P_DATA_BASE,'IW') + 5; -- sábado
|
|
DELETE FROM HIST_BLOQUEIO_SABADO
|
|
WHERE DATA_SABADO = V_SABADO;
|
|
INSERT INTO HIST_BLOQUEIO_SABADO (
|
|
DATA_SABADO, TIPO_BLOQUEIO, CODFILIAL, CODPROD,
|
|
DESCRICAO, MARCA, QTESTGER,
|
|
QT, CUSTOFIN, VALOR,
|
|
DATA_TIPO, CODMOTIVO, MOTIVO, CODFUNC, FUNCIONARIO, HISTORICO,
|
|
FAIXA_PRIM, DT_CARGA
|
|
)
|
|
WITH
|
|
/* ---------------------------------------------------------
|
|
BASE ESTOQUE (fonte de quantidades da 9871)
|
|
--------------------------------------------------------- */
|
|
BASE_EST AS (
|
|
SELECT
|
|
E.CODFILIAL,
|
|
E.CODPROD,
|
|
NVL(E.QTBLOQUEADA,0) AS QT_BLOQ_TOTAL,
|
|
NVL(E.QTINDENIZ,0) AS QT_AVARIA,
|
|
NVL(E.QTEXPOSICAO,0) AS QT_EXPOSICAO,
|
|
E.QTESTGER,
|
|
NVL(E.CUSTOFIN,0) AS CUSTOFIN
|
|
FROM PCEST E
|
|
),
|
|
/* ---------------------------------------------------------
|
|
ÚLTIMO MOV 53 (só para DATA/CODMOTIVO/CODFUNC/HIST)
|
|
--------------------------------------------------------- */
|
|
MOV53 AS (
|
|
SELECT
|
|
CODFILIAL, CODPROD,
|
|
TRUNC(DTINCLUSAO) AS DATA_EXPO,
|
|
CODMOTIVO,
|
|
CODFUNC,
|
|
HISTORICO
|
|
FROM (
|
|
SELECT
|
|
M.*,
|
|
ROW_NUMBER() OVER (
|
|
PARTITION BY M.CODFILIAL, M.CODPROD
|
|
ORDER BY M.DTINCLUSAO DESC, M.ROWID DESC
|
|
) RN
|
|
FROM ESTMOVBLOQESTOQUE M
|
|
WHERE M.CODMOTIVO IN (53)
|
|
)
|
|
WHERE RN = 1
|
|
),
|
|
/* ---------------------------------------------------------
|
|
ÚLTIMO EA (AVARIA) para DATA/CODMOTIVO/CODFUNC/HIST
|
|
--------------------------------------------------------- */
|
|
EA AS (
|
|
SELECT
|
|
CODFILIAL, CODPROD,
|
|
TRUNC(DATA) AS DATA_EA,
|
|
CODMOTIVO,
|
|
CODFUNC,
|
|
HISTORICO
|
|
FROM (
|
|
SELECT
|
|
M.*,
|
|
ROW_NUMBER() OVER (
|
|
PARTITION BY M.CODFILIAL, M.CODPROD
|
|
ORDER BY TRUNC(M.DATA) DESC, M.ROWID DESC
|
|
) RN
|
|
FROM ESTMOVBLOQESTOQUE M
|
|
WHERE M.TIPO = 'EA'
|
|
)
|
|
WHERE RN = 1
|
|
),
|
|
/* ---------------------------------------------------------
|
|
Data ENTRADA (último B no log, exceto 9871) igual 8189
|
|
--------------------------------------------------------- */
|
|
DT_ENTRADA AS (
|
|
SELECT CODFILIAL, CODPROD, MAX(TRUNC(DATA)) AS DATA_ENT
|
|
FROM ESTLOGBLOQESTOQUE
|
|
WHERE PROGRAMA NOT LIKE '%9871%'
|
|
AND TIPOBLOQUEIO = 'B'
|
|
GROUP BY CODFILIAL, CODPROD
|
|
),
|
|
/* ---------------------------------------------------------
|
|
EXPOSIÇÃO (lógica 9871)
|
|
--------------------------------------------------------- */
|
|
EXPOSICAO AS (
|
|
SELECT
|
|
'BLOQUEIO EXPOSICAO' AS TIPO_BLOQUEIO,
|
|
B.QT_EXPOSICAO AS QUANTIDADE,
|
|
M53.DATA_EXPO AS DATA,
|
|
B.CODPROD,
|
|
B.CODFILIAL,
|
|
M53.CODMOTIVO,
|
|
M53.CODFUNC,
|
|
M53.HISTORICO
|
|
FROM BASE_EST B
|
|
LEFT JOIN MOV53 M53
|
|
ON M53.CODFILIAL = B.CODFILIAL
|
|
AND M53.CODPROD = B.CODPROD
|
|
WHERE B.QT_EXPOSICAO > 0
|
|
),
|
|
/* ---------------------------------------------------------
|
|
MANUAL (lógica 9871) = QTBLOQMANUAL - QTEXPOSICAO
|
|
--------------------------------------------------------- */
|
|
MANUAL AS (
|
|
SELECT
|
|
'BLOQUEIO MANUAL' AS TIPO_BLOQUEIO,
|
|
GREATEST(NVL(EBP.QTBLOQMANUAL,0) - B.QT_EXPOSICAO, 0) AS QUANTIDADE,
|
|
TRUNC(EBP.DTPRIMBLOQUEIO) AS DATA,
|
|
B.CODPROD,
|
|
B.CODFILIAL,
|
|
CAST(NULL AS NUMBER) AS CODMOTIVO,
|
|
CAST(NULL AS NUMBER) AS CODFUNC,
|
|
CAST(NULL AS VARCHAR2(4000)) AS HISTORICO
|
|
FROM BASE_EST B
|
|
LEFT JOIN ESTBLOQUEIOPRODUTO EBP
|
|
ON EBP.CODFILIAL = B.CODFILIAL
|
|
AND EBP.CODPROD = B.CODPROD
|
|
WHERE GREATEST(NVL(EBP.QTBLOQMANUAL,0) - B.QT_EXPOSICAO, 0) > 0
|
|
),
|
|
/* ---------------------------------------------------------
|
|
ENTRADA (lógica 9871 = 8189)
|
|
Entrada = QTBLOQUEADA - QTINDENIZ - QTBLOQMANUAL
|
|
--------------------------------------------------------- */
|
|
ENTRADA AS (
|
|
SELECT
|
|
'BLOQUEIO ENTRADA' AS TIPO_BLOQUEIO,
|
|
GREATEST(B.QT_BLOQ_TOTAL - B.QT_AVARIA - NVL(EBP.QTBLOQMANUAL,0), 0) AS QUANTIDADE,
|
|
X.DATA_ENT AS DATA,
|
|
B.CODPROD,
|
|
B.CODFILIAL,
|
|
CAST(NULL AS NUMBER) AS CODMOTIVO,
|
|
CAST(NULL AS NUMBER) AS CODFUNC,
|
|
CAST(NULL AS VARCHAR2(4000)) AS HISTORICO
|
|
FROM BASE_EST B
|
|
LEFT JOIN ESTBLOQUEIOPRODUTO EBP
|
|
ON EBP.CODFILIAL = B.CODFILIAL
|
|
AND EBP.CODPROD = B.CODPROD
|
|
INNER JOIN DT_ENTRADA X
|
|
ON X.CODFILIAL = B.CODFILIAL
|
|
AND X.CODPROD = B.CODPROD
|
|
WHERE GREATEST(B.QT_BLOQ_TOTAL - B.QT_AVARIA - NVL(EBP.QTBLOQMANUAL,0), 0) > 0
|
|
),
|
|
/* ---------------------------------------------------------
|
|
AVARIA (quantidade vem do PCEST.QTINDENIZ)
|
|
--------------------------------------------------------- */
|
|
AVARIA AS (
|
|
SELECT
|
|
'BLOQUEIO AVARIA' AS TIPO_BLOQUEIO,
|
|
B.QT_AVARIA AS QUANTIDADE,
|
|
EA.DATA_EA AS DATA,
|
|
B.CODPROD,
|
|
B.CODFILIAL,
|
|
EA.CODMOTIVO,
|
|
EA.CODFUNC,
|
|
EA.HISTORICO
|
|
FROM BASE_EST B
|
|
LEFT JOIN EA
|
|
ON EA.CODFILIAL = B.CODFILIAL
|
|
AND EA.CODPROD = B.CODPROD
|
|
WHERE B.QT_AVARIA > 0
|
|
),
|
|
DADOS AS (
|
|
SELECT * FROM EXPOSICAO
|
|
UNION ALL
|
|
SELECT * FROM MANUAL
|
|
UNION ALL
|
|
SELECT * FROM ENTRADA
|
|
UNION ALL
|
|
SELECT * FROM AVARIA
|
|
)
|
|
SELECT
|
|
V_SABADO AS DATA_SABADO,
|
|
DADOS.TIPO_BLOQUEIO,
|
|
TO_CHAR(DADOS.CODFILIAL) AS CODFILIAL,
|
|
DADOS.CODPROD,
|
|
P.DESCRICAO,
|
|
M.MARCA,
|
|
E.QTESTGER,
|
|
DADOS.QUANTIDADE AS QT,
|
|
ROUND(E.CUSTOFIN, 2) AS CUSTOFIN,
|
|
ROUND(DADOS.QUANTIDADE * E.CUSTOFIN, 2) AS VALOR,
|
|
DADOS.DATA AS DATA_TIPO,
|
|
DADOS.CODMOTIVO,
|
|
TD.MOTIVO,
|
|
DADOS.CODFUNC,
|
|
F.NOME AS FUNCIONARIO,
|
|
DADOS.HISTORICO,
|
|
CASE
|
|
WHEN DADOS.DATA IS NULL THEN '?'
|
|
WHEN V_SABADO - TRUNC(DADOS.DATA) <= 3 THEN '1 - 0 A 3 DIAS'
|
|
WHEN V_SABADO - TRUNC(DADOS.DATA) <= 7 THEN '2 - 4 A 7 DIAS'
|
|
WHEN V_SABADO - TRUNC(DADOS.DATA) <= 14 THEN '3 - 8 A 14 DIAS'
|
|
WHEN V_SABADO - TRUNC(DADOS.DATA) <= 30 THEN '4 - 15 A 30 DIAS'
|
|
WHEN V_SABADO - TRUNC(DADOS.DATA) <= 60 THEN '5 - 30 A 60 DIAS'
|
|
ELSE '6 - 60 + DIAS'
|
|
END AS FAIXA_PRIM,
|
|
SYSTIMESTAMP AS DT_CARGA
|
|
FROM DADOS
|
|
JOIN PCPRODUT P
|
|
ON P.CODPROD = DADOS.CODPROD
|
|
JOIN PCEST E
|
|
ON E.CODPROD = DADOS.CODPROD
|
|
AND E.CODFILIAL = DADOS.CODFILIAL
|
|
LEFT JOIN PCMARCA M
|
|
ON M.CODMARCA = P.CODMARCA
|
|
LEFT JOIN PCTABDEV TD
|
|
ON TD.CODDEVOL = DADOS.CODMOTIVO
|
|
LEFT JOIN PCEMPR F
|
|
ON F.MATRICULA = DADOS.CODFUNC;
|
|
|
|
COMMIT;
|
|
END;
|
|
/
|
|
|
|
|
|
-- job (todo sábado)
|
|
|
|
BEGIN
|
|
DBMS_SCHEDULER.CREATE_JOB (
|
|
job_name => 'JOB_SNAPSHOT_BLOQUEIO_SABADO',
|
|
job_type => 'STORED_PROCEDURE',
|
|
job_action => 'PRC_SNAPSHOT_BLOQUEIO_SABADO',
|
|
start_date => SYSTIMESTAMP,
|
|
repeat_interval => 'FREQ=WEEKLY;BYDAY=SAT;BYHOUR=23;BYMINUTE=55;BYSECOND=0',
|
|
enabled => TRUE
|
|
);
|
|
END;
|
|
/
|
|
|