DROP FUNCTION CFR_SINTETICO_SERV
/

DROP TYPE CFR_SINTETICO_SERV_TABLE
/

DROP TYPE CFR_SINTETICO_SERV_TYPE
/
CREATE TYPE CFR_SINTETICO_SERV_TYPE AS OBJECT
  (IDGRUPO NUMBER,
   CDFILIAL VARCHAR2(4),
   NMFILIAL VARCHAR2(50),
   IDTPIJURCLIE VARCHAR2(1),
   NRINSJURCLIE VARCHAR2(20),
   NMRAZSOCCLIE VARCHAR2(100),
   NRAPURACGD VARCHAR2(10),
   CDCLIENTE VARCHAR2(21),
   CDSERVICO01 VARCHAR2(5),
   CDSERVICO02 VARCHAR2(5),
   CDSERVICO03 VARCHAR2(5),
   CDSERVICO04 VARCHAR2(5),
   CDSERVICO05 VARCHAR2(5),
   CDSERVICO06 VARCHAR2(5),
   NMSERVICO01 VARCHAR2(40),
   NMSERVICO02 VARCHAR2(40),
   NMSERVICO03 VARCHAR2(40),
   NMSERVICO04 VARCHAR2(40),
   NMSERVICO05 VARCHAR2(40),
   NMSERVICO06 VARCHAR2(40),   
   QTSERVICO01 FLOAT(126),
   QTSERVICO02 FLOAT(126),
   QTSERVICO03 FLOAT(126),
   QTSERVICO04 FLOAT(126),
   QTSERVICO05 FLOAT(126),
   QTSERVICO06 FLOAT(126),
   VRSERVICO01 FLOAT(126),
   VRSERVICO02 FLOAT(126),
   VRSERVICO03 FLOAT(126),
   VRSERVICO04 FLOAT(126),
   VRSERVICO05 FLOAT(126),
   VRSERVICO06 FLOAT(126),
   DTAPURACFAT DATE,
   DTINICIAL DATE,
   DTFINAL DATE
  )
/

CREATE TYPE CFR_SINTETICO_SERV_TABLE AS TABLE OF CFR_SINTETICO_SERV_TYPE
/

create or replace FUNCTION CFR_SINTETICO_SERV
  (P_CDFILIALAPGD IN SERVICO.CDFILIAL%TYPE,
   P_NRAPURACGD IN CTRLAPURFAT.NRAPURACFAT%TYPE,
   P_CDCLIENTEAPGD IN VARCHAR,
   P_DTINICIAL IN DATE,
   P_DTFINAL IN DATE
   )
   RETURN CFR_SINTETICO_SERV_TABLE PIPELINED IS

   OUT_RECORD CFR_SINTETICO_SERV_TYPE := CFR_SINTETICO_SERV_TYPE
   (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL);

V_CONTREG NUMBER;
V_GRUPO NUMBER;
V_COLUNA NUMBER;
V_CLIENTE VARCHAR2(21);
V_FILIAL VARCHAR2(4);
V_SERVICO VARCHAR2(5);
V_DTAPURAC DATE;
V_OLD_GRUPO NUMBER;
BEGIN
   V_CLIENTE := NULL;
   V_SERVICO := NULL;
   V_FILIAL := NULL;
   V_DTAPURAC := NULL;
   V_OLD_GRUPO := NULL;
   FOR C_CFR IN (
         SELECT DCS.CDFILIALAPGD, DCS.CDCLIENTEAPGD,  DCS.CDSERVFILIAPGD,
                DCS.NRAPURACGD, DCS.DTDATA AS DTAPURACFAT, NVL(TA.NREFETIVOSAPGD,0) AS NREFETIVOSAPGD,
                TA.VRPRECUNITAPGD, SE.NMSERVICO, FI.NMFILIAL, CL.IDTPIJURCLIE, CL.NRINSJURCLIE, CL.NMRAZSOCCLIE
         FROM TRAB_APURAC_GD TA, SERVICO SE, FILIAL FI,CLIENTE CL,
                                  (SELECT CDFILIALCENTRAL, NRAPURACENTRAL, CDFILIALAPGD, NRAPURACGD, DTDATA, CDCLIENTEAPGD, CDSERVFILIAPGD
                                  FROM DATA, (SELECT DISTINCT CDFILIALCENTRAL, NRAPURACENTRAL, CDFILIALAPGD, NRAPURACGD, CDCLIENTEAPGD, CDSERVFILIAPGD
                                              FROM TRAB_APURAC_GD
                                              WHERE (CDFILIALCENTRAL  = P_CDFILIALAPGD)  AND
                                                    (VALOR_NA_LISTA(CDCLIENTEAPGD, P_CDCLIENTEAPGD) <> 0) AND
                                                    (NRAPURACENTRAL    = P_NRAPURACGD)) CS
                                  WHERE DTDATA BETWEEN P_DTINICIAL and P_DTFINAL) DCS
         WHERE (DCS.CDFILIALCENTRAL = P_CDFILIALAPGD)        AND
               (DCS.NRAPURACENTRAL = P_NRAPURACGD)          AND
               (VALOR_NA_LISTA(DCS.CDCLIENTEAPGD, P_CDCLIENTEAPGD) <> 0) AND
               (DCS.CDFILIALAPGD   = TA.CDFILIALAPGD(+))     AND
               (DCS.NRAPURACGD     = TA.NRAPURACGD(+))       AND
               (DCS.DTDATA         = TA.DTAPURACFATGD(+))    AND
               (DCS.CDCLIENTEAPGD  = TA.CDCLIENTEAPGD(+))    AND
               (DCS.CDSERVFILIAPGD = TA.CDSERVFILIAPGD(+))   AND
               (FI.CDFILIAL        = DCS.CDFILIALAPGD)       AND
               (SE.CDFILIAL        = DCS.CDFILIALAPGD)       AND
               (SE.CDSERVFILI      = DCS.CDSERVFILIAPGD)     AND
               (CL.CDCLIENTE       = DCS.CDCLIENTEAPGD)      AND
               (SE.CDSERVPREQUI      IS NULL)
         ORDER BY CDFILIALAPGD, CDCLIENTEAPGD,DTAPURACFAT, CDSERVFILIAPGD
   )
   LOOP
      /*TROCOU DE FILIAL, CLIENTE OU DATA, ZERA O CONTADOR*/
      IF (V_FILIAL IS NULL OR V_FILIAL <> C_CFR.CDFILIALAPGD) OR (V_CLIENTE IS NULL OR V_CLIENTE <> C_CFR.CDCLIENTEAPGD) OR (V_DTAPURAC IS NULL OR V_DTAPURAC <> C_CFR.DTAPURACFAT) THEN 
         IF V_DTAPURAC IS NOT NULL AND V_DTAPURAC <> C_CFR.DTAPURACFAT THEN
            PIPE ROW (OUT_RECORD);
         END IF;
         V_FILIAL := C_CFR.CDFILIALAPGD;
         V_CLIENTE := C_CFR.CDCLIENTEAPGD;
         V_SERVICO := C_CFR.CDSERVFILIAPGD;
         V_DTAPURAC := C_CFR.DTAPURACFAT;
         V_CONTREG := 1;
         V_GRUPO := 1;
         V_OLD_GRUPO := V_GRUPO;
         V_COLUNA := 1;  
      END IF;
      /*TROCOU DE SERVICO, ATUALIZA A COLUNA*/
      IF V_SERVICO <> C_CFR.CDSERVFILIAPGD THEN
         V_SERVICO := C_CFR.CDSERVFILIAPGD;
         V_GRUPO := TRUNC(V_CONTREG/6) + 1;
          IF V_GRUPO <> V_OLD_GRUPO THEN
             PIPE ROW (OUT_RECORD);
             V_OLD_GRUPO := V_GRUPO;
          END IF;         
         V_CONTREG := V_CONTREG + 1;
         V_COLUNA := V_COLUNA + 1; 
         IF V_COLUNA > 6 THEN
            V_COLUNA := 1;
         END IF;
      END IF;

      IF V_COLUNA = 1 THEN
          OUT_RECORD := CFR_SINTETICO_SERV_TYPE (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL);
          OUT_RECORD.CDFILIAL := C_CFR.CDFILIALAPGD;
          OUT_RECORD.NMFILIAL := C_CFR.NMFILIAL;
          OUT_RECORD.IDGRUPO := V_GRUPO;
          OUT_RECORD.CDCLIENTE := C_CFR.CDCLIENTEAPGD;
          OUT_RECORD.NRAPURACGD := C_CFR.NRAPURACGD;
          OUT_RECORD.IDTPIJURCLIE := C_CFR.IDTPIJURCLIE;
          OUT_RECORD.NRINSJURCLIE := C_CFR.NRINSJURCLIE;
          OUT_RECORD.NMRAZSOCCLIE := C_CFR.NMRAZSOCCLIE;
          OUT_RECORD.DTAPURACFAT := C_CFR.DTAPURACFAT;
          OUT_RECORD.DTINICIAL := P_DTINICIAL;
          OUT_RECORD.DTFINAL := P_DTFINAL;

          OUT_RECORD.CDSERVICO01 := C_CFR.CDSERVFILIAPGD;
          OUT_RECORD.NMSERVICO01 := C_CFR.NMSERVICO;
          OUT_RECORD.QTSERVICO01 := C_CFR.NREFETIVOSAPGD;
          OUT_RECORD.VRSERVICO01 := C_CFR.VRPRECUNITAPGD;
      ELSIF V_COLUNA = 2 THEN
          OUT_RECORD.CDSERVICO02 := C_CFR.CDSERVFILIAPGD;
          OUT_RECORD.NMSERVICO02 := C_CFR.NMSERVICO;
          OUT_RECORD.QTSERVICO02 := C_CFR.NREFETIVOSAPGD;
          OUT_RECORD.VRSERVICO02 := C_CFR.VRPRECUNITAPGD;
      ELSIF V_COLUNA = 3 THEN  
          OUT_RECORD.CDSERVICO03 := C_CFR.CDSERVFILIAPGD;
          OUT_RECORD.NMSERVICO03 := C_CFR.NMSERVICO;
          OUT_RECORD.QTSERVICO03 := C_CFR.NREFETIVOSAPGD;
          OUT_RECORD.VRSERVICO03 := C_CFR.VRPRECUNITAPGD;
      ELSIF V_COLUNA = 4 THEN  
          OUT_RECORD.CDSERVICO04 := C_CFR.CDSERVFILIAPGD;
          OUT_RECORD.NMSERVICO04 := C_CFR.NMSERVICO;
          OUT_RECORD.QTSERVICO04 := C_CFR.NREFETIVOSAPGD;
          OUT_RECORD.VRSERVICO04 := C_CFR.VRPRECUNITAPGD;
      ELSIF V_COLUNA = 5 THEN
          OUT_RECORD.CDSERVICO05 := C_CFR.CDSERVFILIAPGD;
          OUT_RECORD.NMSERVICO05 := C_CFR.NMSERVICO;
          OUT_RECORD.QTSERVICO05 := C_CFR.NREFETIVOSAPGD;
          OUT_RECORD.VRSERVICO05 := C_CFR.VRPRECUNITAPGD;
      ELSIF V_COLUNA = 6 THEN
          OUT_RECORD.CDSERVICO06 := C_CFR.CDSERVFILIAPGD;
          OUT_RECORD.NMSERVICO06 := C_CFR.NMSERVICO;
          OUT_RECORD.QTSERVICO06 := C_CFR.NREFETIVOSAPGD;
          OUT_RECORD.VRSERVICO06 := C_CFR.VRPRECUNITAPGD;
      END IF;  

   END LOOP;
   PIPE ROW (OUT_RECORD);
   RETURN;
END;
/