DROP TABLE TMP_ITEMDEMOF
/

CREATE GLOBAL TEMPORARY TABLE TMP_ITEMDEMOF
(
  CDTIPODEMO VARCHAR2(02),
  CDITEMSTRU VARCHAR2(04),
  VRITEMSTRU NUMBER
) ON COMMIT DELETE ROWS
/

CREATE OR REPLACE PROCEDURE DEMONSTRATIVO_OFICIAL(
   P_CDEMPRESA IN EMPRESA.CDEMPRESA%TYPE,
   P_CDTIPODEMO IN TIPODEMO.CDTIPODEMO%TYPE,
   P_DATA IN DATE,
   P_PERIODO IN VARCHAR,
   P_NRDRE OUT NUMBER)
AS
   V_NRDEMOCTBTEMP VARCHAR2(20);
   V_NRITDEMOCTB VARCHAR2(20);
   V_DATAREF DATE;
   V_VRITEMSTRU NUMBER;
   V_COUNT NUMBER;
BEGIN
   V_DATAREF := LAST_DAY(P_DATA);
   V_COUNT := 0;
   BEGIN
      SELECT COUNT(*)
        INTO V_COUNT
        FROM LANCCONT
       WHERE CDEMPRESA = P_CDEMPRESA
         AND DTLANCCONT BETWEEN TO_DATE('01/'||TO_CHAR(V_DATAREF, 'MM/YYYY')) AND V_DATAREF
         AND IDLANCINTE = 'T';
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_COUNT := 0;
   END;
   NOVO_CODIGO_NT('DREOFICIAL', V_NRDEMOCTBTEMP);

   FOR C_AUX IN (
      SELECT I.CDTIPODEMO, I.CDITEMSTRU, I.IDORIGVALOR
        FROM ITEMDEMOF I
       WHERE I.CDTIPODEMO = P_CDTIPODEMO
         AND I.IDORIGVALOR <> 'T'
      ORDER BY I.CDTIPODEMO, I.CDITEMSTRU) LOOP
      IF C_AUX.IDORIGVALOR = 'A' THEN
         IF ((V_COUNT <> 0) AND (P_PERIODO = 'T')) THEN
            SELECT SUM(VRSALDOITEM)
            INTO V_VRITEMSTRU
            FROM (SELECT NVL(SUM(ROUND(S.VRSALDOTRANSF, 2)),0) AS VRSALDOITEM
                  FROM SALDOTRANSF S, ITEMAPUCTB I
                  WHERE S.CDEMPRESA  = P_CDEMPRESA
                    AND S.DTSALDOTRANSF = V_DATAREF
                    AND S.CDCONTCTBL = I.CDORIGDEMO
                    AND I.CDTIPODEMO = C_AUX.CDTIPODEMO
                    AND I.CDITEMSTRU = C_AUX.CDITEMSTRU
                    AND I.IDTIPOORIG = 'S'
                  UNION ALL
                  SELECT NVL(SUM(ROUND(S.VRITEMSTRU, 2)), 0) AS VRSALDOITEM
                  FROM TMP_ITEMDEMOF S, ITEMAPUCTB I
                  WHERE I.CDTIPODEMO = C_AUX.CDTIPODEMO
                    AND I.CDITEMSTRU = C_AUX.CDITEMSTRU
                    AND S.CDTIPODEMO = SUBSTR(I.CDORIGDEMO, 1, 2)
                    AND S.CDITEMSTRU = SUBSTR(I.CDORIGDEMO, 3, 4)
                    AND I.IDTIPOORIG = 'V');
            INSERT INTO TMP_ITEMDEMOF
               (CDTIPODEMO, CDITEMSTRU, VRITEMSTRU)
            VALUES
               (C_AUX.CDTIPODEMO, C_AUX.CDITEMSTRU, V_VRITEMSTRU);
         ELSE
            SELECT SUM(VRSALDOITEM)
             INTO V_VRITEMSTRU
             FROM (SELECT NVL(SUM(ROUND(S.VRSALDEMPR, 2)),0) AS VRSALDOITEM
                     FROM SALDEMPR S, ITEMAPUCTB I
                    WHERE S.CDEMPRESA  = P_CDEMPRESA
                      AND S.DTSALDEMPR = V_DATAREF
                      AND S.CDCONTCTBL = I.CDORIGDEMO
                      AND I.CDTIPODEMO = C_AUX.CDTIPODEMO
                      AND I.CDITEMSTRU = C_AUX.CDITEMSTRU
                      AND I.IDTIPOORIG = 'S'
                    UNION ALL
                   SELECT NVL(SUM(ROUND(S.VRITEMSTRU, 2)), 0) AS VRSALDOITEM
                     FROM TMP_ITEMDEMOF S, ITEMAPUCTB I
                    WHERE I.CDTIPODEMO = C_AUX.CDTIPODEMO
                      AND I.CDITEMSTRU = C_AUX.CDITEMSTRU
                      AND S.CDTIPODEMO = SUBSTR(I.CDORIGDEMO, 1, 2)
                      AND S.CDITEMSTRU = SUBSTR(I.CDORIGDEMO, 3, 4)
                      AND I.IDTIPOORIG = 'V');
            INSERT INTO TMP_ITEMDEMOF
               (CDTIPODEMO, CDITEMSTRU, VRITEMSTRU)
            VALUES
               (C_AUX.CDTIPODEMO, C_AUX.CDITEMSTRU, V_VRITEMSTRU);
         END IF;
      END IF;
   END LOOP;

   FOR C_DEMONSTRATIVO IN (
      SELECT S.CDTIPODEMO, S.CDITEMSTRU, S.VRITEMSTRU
        FROM ITEMDEMOF I, TMP_ITEMDEMOF S
       WHERE I.CDTIPODEMO = P_CDTIPODEMO
         AND I.IDORIGVALOR <> 'T'
         AND I.CDTIPODEMO = S.CDTIPODEMO
         AND I.CDITEMSTRU = S.CDITEMSTRU
       ORDER BY I.CDTIPODEMO, I.CDITEMSTRU) LOOP
      NOVO_CODIGO_NT('DREOFICIALITEM', V_NRITDEMOCTB);
      INSERT INTO DEMOCTBTEMP
        (NRDEMOCTBTEMP, NRITDEMOCTB,
         CDTIPODEMO, CDITEMSTRU, VRITEMSTRU)
      VALUES
        (TO_NUMBER(V_NRDEMOCTBTEMP), TO_NUMBER(V_NRITDEMOCTB),
         C_DEMONSTRATIVO.CDTIPODEMO, C_DEMONSTRATIVO.CDITEMSTRU, C_DEMONSTRATIVO.VRITEMSTRU);
   END LOOP;

   P_NRDRE := TO_NUMBER(V_NRDEMOCTBTEMP);
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('DEMONSTRATIVO_OFICIAL','5.75.xxx'); END;
/

