DROP FUNCTION ANALISE_RENTABILIDADEROP           
/

DROP TYPE ANALISE_RENTABILIDADEROP_TABLE
/

DROP TYPE ANALISE_RENTABILIDADEROP_TYPE
/

CREATE TYPE ANALISE_RENTABILIDADEROP_TYPE AS
OBJECT (CDFILIAL            VARCHAR2(04),
        CODIGOSERVICO       VARCHAR2(05),
        NOMESERVICO         VARCHAR2(40),
        VOLUMESERVICO       FLOAT(126),
        PRECOBRUTO          FLOAT(126),
        VRIMPOSTO           FLOAT(126),
        PRECOLIQUIDO        FLOAT(126),
        PUSERVICO           FLOAT(126),
        PERC_PU             FLOAT(126),
        VR_FATURLIQUIDO     FLOAT(126),
        VR_MCONTRBUICAO     FLOAT(126),
        PERC_MCONTRIBUICAO  FLOAT(126),
        VR_MATERIAPRIMA     FLOAT(126),
        PERC_MATERIAPRIMA   FLOAT(126),
        PERC_FATURTOTAL     FLOAT(126),
        NRORG               NUMBER)
/

CREATE TYPE ANALISE_RENTABILIDADEROP_TABLE AS
TABLE OF ANALISE_RENTABILIDADEROP_TYPE
/

CREATE OR REPLACE FUNCTION ANALISE_RENTABILIDADEROP(
    P_CDFILIAL  IN VARCHAR2,
    P_DTINICIAL IN DATE,
    P_DTFINAL   IN DATE
    ) RETURN ANALISE_RENTABILIDADEROP_TABLE PIPELINED
IS
  V_CDFILIAL           VARCHAR2(04);
  V_DTENTRSAID         DATE;
  V_CDSERVFILI         VARCHAR2(5);
  V_NMSERVICO          VARCHAR2(200);
  V_VRFATURAMENTO      FLOAT;
  V_QTFATURAMENTO      INTEGER;
  V_PU                 FLOAT;
  V_VRIMPOSTO          FLOAT;
  V_VRFATLIQUIDO       FLOAT;
  V_RETIRADADRO        FLOAT;
  V_VRTOTALFATURAMENTO FLOAT;

  OUT_RECORD ANALISE_RENTABILIDADEROP_TYPE := ANALISE_RENTABILIDADEROP_TYPE (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
                                                                             NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
BEGIN
   V_VRFATURAMENTO      := 0;
   V_QTFATURAMENTO      := 0;
   V_PU                 := 0;
   V_VRIMPOSTO          := 0;
   V_VRFATLIQUIDO       := 0;
   V_RETIRADADRO        := 0;
   V_VRTOTALFATURAMENTO := 0;

   /*Valor total faturado no periodo*/
   BEGIN
      SELECT SUM(VRFATURAMENTO)
        INTO V_VRTOTALFATURAMENTO
        FROM FATURAMENTOROP F, SERVICO S
       WHERE F.CDFILIAL     = P_CDFILIAL
         AND F.CDFILIALLANC = P_CDFILIAL
         AND F.DTENTRSAID   BETWEEN P_DTINICIAL AND P_DTFINAL
         AND F.IDTPLANCROP  = 'FAT_EFETIVO'
         AND F.CDSERVFILI   = S.CDSERVFILI
         AND F.CDFILIAL     = S.CDFILIAL;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
        V_VRTOTALFATURAMENTO := 0;
   END;
  
   FOR C_EFETIVOS IN (
      SELECT F.CDFILIAL,S.CDSERVFILI,S.NMSERVICO,SUM(VRFATURAMENTO) VRFATURAMENTO,SUM(QTFATURAMENTO) QTFATURAMENTO,
             F.NRORG
        FROM FATURAMENTOROP F, SERVICO S
       WHERE F.CDFILIAL     = P_CDFILIAL
         AND F.DTENTRSAID   BETWEEN P_DTINICIAL AND P_DTFINAL
         AND F.IDTPLANCROP  = 'FAT_EFETIVO'
         AND F.CDSERVFILI   = S.CDSERVFILI
         AND F.CDFILIAL     = S.CDFILIAL
       GROUP BY F.CDFILIAL, S.CDSERVFILI,S.NMSERVICO, F.NRORG) LOOP
   
      V_VRFATURAMENTO    := C_EFETIVOS.VRFATURAMENTO;
      V_QTFATURAMENTO    := C_EFETIVOS.QTFATURAMENTO;

      V_VRFATLIQUIDO     := V_VRFATURAMENTO + V_VRIMPOSTO;

      /*OBTENHO  PRECO UNITARIO DO SERVICO*/
      IF C_EFETIVOS.QTFATURAMENTO > 0 THEN
         V_PU := C_EFETIVOS.VRFATURAMENTO/C_EFETIVOS.QTFATURAMENTO;
      ELSE
         V_PU := 0;
      END IF;

      /*SELECIONDO OS IMPOSTOS QUE INCIDEM SOBRE O SERVICO*/
      SELECT SUM(VRDEDUCAO)
        INTO V_VRIMPOSTO
        FROM DEDUCAOROP
       WHERE CDFILIAL   = C_EFETIVOS.CDFILIAL
         AND CDSERVFILI = C_EFETIVOS.CDSERVFILI
         AND DTREFERENC BETWEEN P_DTINICIAL AND P_DTFINAL;

      /*SELECIONDO AS  RETIRADAS QUE EXISTEM PARA O SERVICO FATURADO*/
      BEGIN
         SELECT NVL(SUM(R.VRLANCTOESTQ),0)
           INTO V_RETIRADADRO
           FROM RETIRADADRO R, FILIALROP F
          WHERE R.NRFILIALROP  = F.NRFILIALROP
            AND F.CDFILIAL     = C_EFETIVOS.CDFILIAL
            AND R.CDSERVFILI   = C_EFETIVOS.CDSERVFILI
            AND R.DTRETICUSMOV BETWEEN P_DTINICIAL AND P_DTFINAL;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
           V_RETIRADADRO := 0;
      END;

      OUT_RECORD.CDFILIAL           := C_EFETIVOS.CDFILIAL;
      OUT_RECORD.CODIGOSERVICO      := C_EFETIVOS.CDSERVFILI;
      OUT_RECORD.NOMESERVICO        := C_EFETIVOS.NMSERVICO;
      OUT_RECORD.VOLUMESERVICO      := V_QTFATURAMENTO;
      OUT_RECORD.PRECOBRUTO         := ROUND(V_VRFATURAMENTO,2);
      OUT_RECORD.VRIMPOSTO          := ROUND(V_VRIMPOSTO,2);
      OUT_RECORD.PRECOLIQUIDO       := ROUND(V_VRFATURAMENTO + V_VRIMPOSTO,2);
      OUT_RECORD.PUSERVICO          := ROUND(V_PU,2);
      OUT_RECORD.PERC_PU            := ROUND(V_PU * 100 / V_VRFATLIQUIDO,2);
      OUT_RECORD.VR_FATURLIQUIDO    := ROUND(V_QTFATURAMENTO  * ROUND(V_VRFATURAMENTO + V_VRIMPOSTO,2),2);
      OUT_RECORD.VR_MCONTRBUICAO    := ROUND(V_VRFATLIQUIDO - V_PU,2);
      OUT_RECORD.PERC_MCONTRIBUICAO := ROUND((V_VRFATLIQUIDO - V_PU) * 100 / V_VRFATLIQUIDO,2);
      OUT_RECORD.VR_MATERIAPRIMA    := ROUND(V_RETIRADADRO,2);
      OUT_RECORD.PERC_MATERIAPRIMA  := ROUND((V_RETIRADADRO * 100 ) / V_VRFATLIQUIDO,2);
      OUT_RECORD.PERC_FATURTOTAL    := ROUND((V_VRFATLIQUIDO * 100) / V_VRTOTALFATURAMENTO,2);
      OUT_RECORD.NRORG              := C_EFETIVOS.NRORG;

      /*
      VR_MCONTRBUICAO     - VALOR DA MARGEM DE CONTRIBUICAO
      PERC_MCONTRIBUICAO  - PERCENTUAL DA MARGEM DE CONTROBUICAO APLICADO AO FATURAMENTO LIQUIDO
      VR_MATERIAPRIMA     - VALOR DAS RETIRADAS PARA O SERVICO FATURAMDO NO PERIODO
      PERC_MATERIAPRIMA   - PERCENTUAL DA MATERIA PRIMA APLICADA SOBRE O FATURAMENTO LIQUIDO
      PERC_FATURTOTAL     - PERCENTUAL QUE O VALOR LIQUIDO DO SERVICO REPRESENTA DO FATURAMENTO TOTAL DE EFETIVOS*/

      PIPE ROW (OUT_RECORD);
   END LOOP;
END;