CREATE OR REPLACE PROCEDURE STP_INSERE_VLRARMAZ
AS
BEGIN
DECLARE
P_NUNOTA INT;
P_VLRNOTA [Link]%TYPE;
P_VLRTOTNOTA [Link]%TYPE;
P_VLRCUSTO [Link]%TYPE;
P_CONTADOR INT;
CURSOR NOTAS IS
SELECT NUNOTA_NFE, [Link]
FROM AD_TCNACOPED TCN
INNER JOIN TGFCAB CAB ON TCN.NUNOTA_NFE = [Link]
WHERE TRUNC(DTAFATURA_NFE) BETWEEN trunc(ADD_MONTHS(SYSDATE,-1),'MM') AND
trunc(LAST_DAY(ADD_MONTHS(SYSDATE,-1)));
BEGIN
SELECT SUM(CUSTO) AS CUSTO
INTO P_VLRCUSTO
FROM
(
SELECT
TO_CHAR(REFERENCIA, 'MMYYYY') AS PERIODO,
SUM(CASE WHEN [Link] = 'D' THEN VLRLANC ELSE -VLRLANC END) AS
CUSTO,
0 AS ENTREGA
FROM
TCBLAN LAN, TCBPLA PLA
WHERE
[Link] = [Link]
AND [Link] = 1701
AND [Link] = (CASE WHEN TO_CHAR(REFERENCIA,'YYYY') >= 2023 THEN
99 ELSE 500 END)
AND TO_CHAR([Link],'YYYY') = 2023
AND (CODHISTCTB <> 126 OR CODHISTCTB IS NULL)
GROUP BY
REFERENCIA
)
WHERE PERIODO = TO_CHAR(trunc(ADD_MONTHS(SYSDATE,-1),'MM'),'MMYYYY')
GROUP BY PERIODO
ORDER BY PERIODO;
--EXECUTE IMMEDIATE 'ALTER TRIGGER TRG_UPD_TGFCAB DISABLE';
--EXECUTE IMMEDIATE 'ALTER TRIGGER TRG_INC_UPD_TGFCAB_ORD DISABLE';
SELECT SUM([Link])
INTO P_VLRTOTNOTA
FROM AD_TCNACOPED TCN
INNER JOIN TGFCAB CAB ON TCN.NUNOTA_NFE = [Link]
WHERE TRUNC(DTAFATURA_NFE) BETWEEN trunc(ADD_MONTHS(SYSDATE,-1),'MM') AND
trunc(LAST_DAY(ADD_MONTHS(SYSDATE,-1)));
IF P_VLRCUSTO > 0 THEN
OPEN NOTAS;
LOOP
FETCH NOTAS INTO P_NUNOTA, P_VLRNOTA;
EXIT WHEN NOTAS%NOTFOUND;
--((([Link])* ([Link]/(SELECT SUM(VLRTOT) FROM
[Link] WHERE NUNOTA = [Link]))),2)
UPDATE AD_TCNACOPED SET VLRARMAZ =
P_VLRNOTA*(P_VLRCUSTO/P_VLRTOTNOTA) WHERE NUNOTA_NFE = P_NUNOTA;
END LOOP;
CLOSE NOTAS;
COMMIT;
END IF;
--EXECUTE IMMEDIATE 'ALTER TRIGGER TRG_UPD_TGFCAB ENABLE';
--EXECUTE IMMEDIATE 'ALTER TRIGGER TRG_INC_UPD_TGFCAB_ORD ENABLE';
END;
END;
