DROP FUNCTION PRINCINDIC_ROP
/

DROP TYPE PRINCINDIC_ROP_TABLE
/

DROP TYPE PRINCINDIC_ROP_TYPE
/

CREATE TYPE PRINCINDIC_ROP_TYPE AS
OBJECT (NRPRINCINDIC_ROP    NUMBER,
        NMSUBGRPROD         VARCHAR2(100),
        VRANTERIOR          FLOAT(126),
        VRATUAL             FLOAT(126),
        VRVARIACAO          FLOAT(126),
        NRORG               NUMBER)
/

CREATE TYPE PRINCINDIC_ROP_TABLE AS
TABLE OF PRINCINDIC_ROP_TYPE
/

CREATE OR REPLACE FUNCTION PRINCINDIC_ROP
 (P_CDFILIAL  IN VARCHAR2,
  P_DATAINI   IN DATE,
  P_DATAFIN   IN DATE) RETURN PRINCINDIC_ROP_TABLE PIPELINED
IS
  V_CDFILIAL         VARCHAR2(04);
  V_DATAINIANT       DATE;
  V_DATAFINANT       DATE;
  V_SEQ              NUMBER;
  V_NRORG            NUMBER;
  V_VRTOTALANT       FLOAT;
  V_VRTOTALATU       FLOAT;
  V_VRRETIRADA       FLOAT;
  V_QTEFETPRIN       FLOAT; 
  V_QTEFETNPRIN      FLOAT;
  V_VREFETPR         FLOAT;
  V_VREFETNPR        FLOAT;
  V_QTEFETIVO        FLOAT;
  V_VREFETIVO        FLOAT;
  V_VRATUAL          FLOAT;
  V_VRANT            FLOAT;
  V_QTEFETIVOANT     FLOAT;
  V_QTEFETIVOATU     FLOAT;
  V_QTEFETPRINANT    FLOAT;
  V_DESPESA          FLOAT;
  V_AUX              FLOAT;
  V_VREFETIVOANT     FLOAT;
  V_QTEFETNPRINANT   FLOAT;
  V_VREFETNPRANT     FLOAT;
  V_VREFETPRANT      FLOAT;  

  OUT_RECORD PRINCINDIC_ROP_TYPE := PRINCINDIC_ROP_TYPE(NULL, NULL, NULL, NULL, NULL, NULL);
BEGIN
   V_CDFILIAL := P_CDFILIAL;
   V_SEQ := 0;
   V_VRTOTALANT := 0;
   V_VRTOTALATU := 0;
   
   V_DATAINIANT := P_DATAINI - (P_DATAFIN - P_DATAINI);
   V_DATAFINANT := P_DATAFIN - (P_DATAFIN - P_DATAINI);
   
   SELECT MAX(NRORG)
     INTO V_NRORG
     FROM FILIAL
    WHERE CDFILIAL = V_CDFILIAL;
         
   SELECT NVL(SUM(DECODE(S.IDSERVPRINC, 'S',NVL(F.QTFATURAMENTO,0),0)),0) AS QTEFETPRIN,
          NVL(SUM(DECODE(S.IDSERVPRINC, 'N',NVL(F.QTFATURAMENTO,0),0)),0) AS QTEFETNPRIN,
          NVL(SUM(DECODE(S.IDSERVPRINC, 'S',NVL(F.VRFATURAMENTO,0),0)),0) AS VREFETPR,
          NVL(SUM(DECODE(S.IDSERVPRINC, 'N',NVL(F.VRFATURAMENTO,0),0)),0) AS VREFETNPR,
          NVL(SUM(F.QTFATURAMENTO),0) QTEFETIVO,
          NVL(SUM(F.VRFATURAMENTO),0) VREFETIVO
     INTO V_QTEFETPRINANT, V_QTEFETNPRINANT, V_VREFETPRANT, V_VREFETNPRANT, V_QTEFETIVOANT, V_VREFETIVOANT
     FROM FATURAMENTOROP F INNER JOIN SERVICO S ON S.CDFILIAL = F.CDFILIAL
                                               AND S.CDSERVFILI = F.CDSERVFILI
    WHERE (F.CDFILIAL = V_CDFILIAL OR F.CDFILIALCENT = V_CDFILIAL)
      AND F.IDTPLANCROP = 'FAT_EFETIVO'
      AND F.DTENTRSAID BETWEEN V_DATAINIANT AND V_DATAFINANT
      AND S.IDSERVPRINC IN ('S','N');
         
   SELECT SUM(DECODE(S.IDSERVPRINC, 'S',NVL(F.QTFATURAMENTO,0),0)) AS  QTEFETPRIN,
          SUM(DECODE(S.IDSERVPRINC, 'N',NVL(F.QTFATURAMENTO,0),0)) AS  QTEFETNPRIN,
          SUM(DECODE(S.IDSERVPRINC, 'S',NVL(F.VRFATURAMENTO,0),0)) AS  VREFETPR,
          SUM(DECODE(S.IDSERVPRINC, 'N',NVL(F.VRFATURAMENTO,0),0)) AS  VREFETNPR,
          SUM(F.QTFATURAMENTO) QTEFETIVO,
          SUM(F.VRFATURAMENTO) VREFETIVO
     INTO V_QTEFETPRIN, V_QTEFETNPRIN, V_VREFETPR, V_VREFETNPR, V_QTEFETIVO, V_VREFETIVO
     FROM FATURAMENTOROP F INNER JOIN SERVICO S ON S.CDFILIAL = F.CDFILIAL
                                               AND S.CDSERVFILI = F.CDSERVFILI
    WHERE (F.CDFILIAL = V_CDFILIAL OR F.CDFILIALCENT = V_CDFILIAL)
      AND F.IDTPLANCROP = 'FAT_EFETIVO'
      AND F.DTENTRSAID BETWEEN P_DATAINI AND P_DATAFIN
      AND S.IDSERVPRINC IN ('S','N');
   
   FOR C_SUBGRUP IN (SELECT S.CDGRUPPROD, S.CDSUBGRPROD, S.NMSUBGRPROD
                       FROM SUBGRUPPROD S INNER JOIN PARFILIALROP P ON S.CDGRUPPROD = P.CDGRUPPROD
                      WHERE P.CDFILIAL = TIPOPARAM_DRO(V_CDFILIAL)) LOOP
                      
      SELECT NVL(SUM(R.VRLANCTOESTQ),0) AS VRRETIRADA
        INTO V_VRRETIRADA
        FROM RETIRADADRO R INNER JOIN FILIALROP F ON R.NRFILIALROP = F.NRFILIALROP
                           INNER JOIN SERVICO S ON S.CDSERVFILI = R.CDSERVFILI
                                               AND S.CDFILIAL = F.CDFILIAL
       WHERE (F.CDFILIAL = V_CDFILIAL OR R.CDFILIALCENT = V_CDFILIAL)
         AND R.IDTPLANCROP = 'RETIRADA_PLA'
         AND R.DTRETICUSMOV BETWEEN V_DATAINIANT AND V_DATAFINANT
         AND S.IDSERVPRINC = 'S'
         AND R.CDSUBGRPROD = C_SUBGRUP.CDSUBGRPROD;
               
      V_VRANT := CASE WHEN V_QTEFETPRINANT = 0 THEN 0 ELSE V_VRRETIRADA/V_QTEFETPRINANT END;
      V_VRTOTALANT := V_VRTOTALANT + V_VRANT;
      
      SELECT NVL(SUM(R.VRLANCTOESTQ),0) AS VRRETIRADA
        INTO V_VRRETIRADA
        FROM RETIRADADRO R INNER JOIN FILIALROP F ON R.NRFILIALROP = F.NRFILIALROP
                           INNER JOIN SERVICO S ON S.CDSERVFILI = R.CDSERVFILI
                                               AND S.CDFILIAL = F.CDFILIAL
       WHERE (F.CDFILIAL = V_CDFILIAL OR R.CDFILIALCENT = V_CDFILIAL)
         AND R.IDTPLANCROP = 'RETIRADA_PLA'
         AND R.DTRETICUSMOV BETWEEN P_DATAINI AND P_DATAFIN
         AND S.IDSERVPRINC = 'S'
         AND R.CDSUBGRPROD = C_SUBGRUP.CDSUBGRPROD;
      
      V_VRATUAL := NVL(CASE WHEN V_QTEFETPRIN = 0 THEN 0 ELSE V_VRRETIRADA/V_QTEFETPRIN END,0);
      V_VRTOTALATU := V_VRTOTALATU + V_VRATUAL;    
               
      V_SEQ := V_SEQ + 1;
      OUT_RECORD.NRPRINCINDIC_ROP := V_SEQ;
      OUT_RECORD.NMSUBGRPROD := C_SUBGRUP.NMSUBGRPROD;
      OUT_RECORD.VRANTERIOR := V_VRANT;
      OUT_RECORD.VRATUAL := V_VRATUAL;
      OUT_RECORD.VRVARIACAO := CASE WHEN V_VRANT = 0 THEN 0 ELSE ((V_VRATUAL - V_VRANT)*100)/ V_VRANT END;
      OUT_RECORD.NRORG := V_NRORG;
      PIPE ROW (OUT_RECORD);
   END LOOP;
   V_SEQ := V_SEQ + 1;
   OUT_RECORD.NRPRINCINDIC_ROP := V_SEQ;
   OUT_RECORD.NMSUBGRPROD := 'Total PUs';
   OUT_RECORD.VRANTERIOR := V_VRTOTALANT;
   OUT_RECORD.VRATUAL := V_VRTOTALATU;
   OUT_RECORD.VRVARIACAO := NVL(((V_VRTOTALATU - V_VRTOTALANT)*100)/CASE WHEN V_VRTOTALANT = 0 THEN 1 ELSE V_VRTOTALANT END,0);
   OUT_RECORD.NRORG := V_NRORG;
   PIPE ROW (OUT_RECORD);
   
   SELECT CASE WHEN NVL(SUM(F.QTFATURAMENTO),0) = 0 THEN 1 ELSE NVL(SUM(F.QTFATURAMENTO),0) END
     INTO V_AUX
     FROM FATURAMENTOROP F INNER JOIN SERVICO S ON F.CDSERVFILI = S.CDSERVFILI
    WHERE F.IDTPLANCROP = 'FAT_EFETIVO'
      AND F.CDFILIAL = V_CDFILIAL
      AND F.DTENTRSAID BETWEEN V_DATAINIANT AND V_DATAFINANT
      AND S.IDSERVPRINC = 'S';
      
   V_SEQ := V_SEQ + 1;
   OUT_RECORD.NRPRINCINDIC_ROP := V_SEQ;
   OUT_RECORD.NMSUBGRPROD := 'Mao de Obra';
   
   SELECT NVL(SUM(D.VRDESPESA),0)
     INTO V_DESPESA
     FROM DESPESASROP D INNER JOIN FILIALROP F ON D.NRFILIALROP = F.NRFILIALROP
    WHERE D.IDTPLANCROP = 'MAOOBRA'
      AND (F.CDFILIAL = V_CDFILIAL OR D.CDFILIALCENT = V_CDFILIAL)
      AND D.DTDESPESA BETWEEN V_DATAINIANT AND V_DATAFINANT;
   
   IF V_AUX > 0 THEN
      V_VRANT := V_DESPESA/V_AUX;
   ELSE
      V_VRANT := V_DESPESA;
   END IF;   
   OUT_RECORD.VRANTERIOR := V_VRANT;
   
   SELECT CASE WHEN NVL(SUM(F.QTFATURAMENTO),0) = 0 THEN 1 ELSE NVL(SUM(F.QTFATURAMENTO),0) END
     INTO V_AUX
     FROM FATURAMENTOROP F INNER JOIN SERVICO S ON F.CDSERVFILI = S.CDSERVFILI
    WHERE F.IDTPLANCROP = 'FAT_EFETIVO'
      AND F.CDFILIAL = V_CDFILIAL
      AND F.DTENTRSAID BETWEEN P_DATAINI AND P_DATAFIN
      AND S.IDSERVPRINC = 'S';
   
   SELECT NVL(SUM(D.VRDESPESA),0)
     INTO V_DESPESA
     FROM DESPESASROP D INNER JOIN FILIALROP F ON D.NRFILIALROP = F.NRFILIALROP
    WHERE D.IDTPLANCROP = 'MAOOBRA'
      AND (F.CDFILIAL = V_CDFILIAL OR D.CDFILIALCENT = V_CDFILIAL)
      AND D.DTDESPESA BETWEEN P_DATAINI AND P_DATAFIN;
   
   IF V_AUX > 0 THEN
      V_VRATUAL := NVL(V_DESPESA/V_AUX,0);
   ELSE
      V_VRATUAL := NVL(V_DESPESA,0);
   END IF;
   
   OUT_RECORD.VRATUAL := V_VRATUAL;
   OUT_RECORD.VRVARIACAO := ((V_VRATUAL - V_VRANT)*100)/CASE WHEN V_VRANT = 0 THEN 1 ELSE V_VRANT END;
   OUT_RECORD.NRORG := V_NRORG;
   PIPE ROW (OUT_RECORD);
   
   V_SEQ := V_SEQ + 1;
   OUT_RECORD.NRPRINCINDIC_ROP := V_SEQ;
   OUT_RECORD.NMSUBGRPROD := 'Custo Total';
   OUT_RECORD.VRANTERIOR := V_VRTOTALANT + V_VRANT;   
   OUT_RECORD.VRATUAL := V_VRTOTALATU + V_VRATUAL;
   OUT_RECORD.VRVARIACAO := NVL((((V_VRTOTALATU+V_VRATUAL) - (V_VRTOTALANT+V_VRANT))*100)/CASE WHEN (V_VRTOTALANT+V_VRANT) = 0 THEN 1 ELSE (V_VRTOTALANT+V_VRANT) END,0);
   OUT_RECORD.NRORG := V_NRORG;
   PIPE ROW (OUT_RECORD);
   
   V_SEQ := V_SEQ + 1;
   OUT_RECORD.NRPRINCINDIC_ROP := V_SEQ;
   OUT_RECORD.NMSUBGRPROD := 'Venda Total';
   OUT_RECORD.VRANTERIOR := CASE WHEN V_QTEFETIVOANT = 0 THEN 0 ELSE V_VREFETIVOANT/V_QTEFETIVOANT END;
   OUT_RECORD.VRATUAL := NVL(CASE WHEN V_QTEFETIVO = 0 THEN 0 ELSE V_VREFETIVO/V_QTEFETIVO END,0);
   OUT_RECORD.VRVARIACAO := CASE WHEN V_QTEFETIVOANT = 0 THEN 0 ELSE
                             (((NVL(CASE WHEN V_QTEFETIVO = 0 THEN 0 ELSE V_VREFETIVO/V_QTEFETIVO END,0)) -
                              (NVL(CASE WHEN V_QTEFETIVOANT = 0 THEN 0 ELSE V_VREFETIVOANT/V_QTEFETIVOANT END,0)))*100)/
                              (NVL(CASE WHEN V_QTEFETIVOANT = 0 THEN 0 ELSE V_VREFETIVOANT/V_QTEFETIVOANT END,0)) END;
   OUT_RECORD.NRORG := V_NRORG;
   PIPE ROW (OUT_RECORD);
   
   V_SEQ := V_SEQ + 1;
   OUT_RECORD.NRPRINCINDIC_ROP := V_SEQ;
   OUT_RECORD.NMSUBGRPROD := 'Ref. Principal';
   OUT_RECORD.VRANTERIOR := CASE WHEN V_QTEFETPRINANT = 0 THEN 0 ELSE V_VREFETPRANT/V_QTEFETPRINANT END;
   OUT_RECORD.VRATUAL := NVL(CASE WHEN V_QTEFETPRIN = 0 THEN 0 ELSE V_VREFETPR/V_QTEFETPRIN END,0);
   OUT_RECORD.VRVARIACAO := CASE WHEN V_QTEFETPRINANT = 0 THEN 0 ELSE
                             (((NVL(CASE WHEN V_QTEFETPRIN = 0 THEN 0 ELSE V_VREFETPR/V_QTEFETPRIN END,0)) -
                              (NVL(CASE WHEN V_QTEFETPRINANT = 0 THEN 0 ELSE V_VREFETPRANT/V_QTEFETPRINANT END,0)))*100)/
                              (NVL(CASE WHEN V_QTEFETPRINANT = 0 THEN 0 ELSE V_VREFETPRANT/V_QTEFETPRINANT END,0)) END;
   OUT_RECORD.NRORG := V_NRORG;
   PIPE ROW (OUT_RECORD);
   
   SELECT NVL((SUM(R.VRLANCTOESTQ)/COUNT(1)),0)
     INTO V_VRANT
     FROM RETIRADADRO R INNER JOIN FILIALROP F ON F.NRFILIALROP = R.NRFILIALROP
    WHERE (F.CDFILIAL = V_CDFILIAL OR R.CDFILIALCENT = V_CDFILIAL)
      AND R.IDTPLANCROP = 'CUSTO'
      AND R.DTRETICUSMOV BETWEEN V_DATAINIANT AND V_DATAFINANT;
      
   SELECT NVL((SUM(R.VRLANCTOESTQ)/COUNT(1)),0)
     INTO V_VRATUAL
     FROM RETIRADADRO R INNER JOIN FILIALROP F ON F.NRFILIALROP = R.NRFILIALROP
    WHERE (F.CDFILIAL = V_CDFILIAL OR R.CDFILIALCENT = V_CDFILIAL)
      AND R.IDTPLANCROP = 'CUSTO'
      AND R.DTRETICUSMOV BETWEEN P_DATAINI AND P_DATAFIN;
      
   V_SEQ := V_SEQ + 1;
   OUT_RECORD.NRPRINCINDIC_ROP := V_SEQ;
   OUT_RECORD.NMSUBGRPROD := 'PU Objetivo - Ref. Principal';
   OUT_RECORD.VRANTERIOR := V_VRANT;
   OUT_RECORD.VRATUAL := V_VRATUAL;
   OUT_RECORD.VRVARIACAO := CASE WHEN V_VRANT = 0 THEN 0 ELSE (V_VRATUAL - V_VRANT*100)/V_VRANT END;
   OUT_RECORD.NRORG := V_NRORG;
   PIPE ROW (OUT_RECORD);
END;