DROP TYPE GOP_DRO_TABLE
/
DROP TYPE GOP_DRO_TYPE
/
CREATE OR REPLACE TYPE GOP_DRO_TYPE AS OBJECT
(
      NRORG NUMBER, CDAREAGERE VARCHAR2(32767), NMAREAGERE VARCHAR2(40), CDFILIAL VARCHAR2(32767), NMFILIAL VARCHAR2(40), CDSUPERVISOR VARCHAR2(32767), NMSUPERVISOR VARCHAR2(40), MES VARCHAR2(2), ANO VARCHAR2(4), GOP_VERSAO NUMBER
    , GOP_FAT_BRU FLOAT, GOP_CONSUMO FLOAT, GOP_MAOBRA FLOAT, GOP_DESPMATRIZ FLOAT, GOP_DESPDIVERSA FLOAT, GOP_DESPESADFE FLOAT, GOP_FATLIQU FLOAT, GOP_IMPOSTO FLOAT, GOP_IMPLUC FLOAT, GOP_IMPRECU FLOAT, GOP_DEPREC FLOAT, GOP_INVEST FLOAT
    , QTDE_DESP_MOB FLOAT, QTDE_CONS_COMP FLOAT, QTDE_CONS_SAIDA FLOAT, QTDE_RET_ESTOQ FLOAT, QTDE_RET_PLA FLOAT, QTDE_AJUSTES FLOAT, QTDE_IMPLANT FLOAT, QTDE_FAT_NF FLOAT, QTDE_FAT_EFET FLOAT, QTDE_DES_CONT_PAG FLOAT, QTDE_DESP_DIV FLOAT, QTDE_DESP_ENTR FLOAT, QTDE_RET_CONS FLOAT, QTDE_INVEST FLOAT, QTDE_DEPREC_BENS FLOAT, QTDE_INV_INICIAL FLOAT, QTDE_INV_FINAL FLOAT
    , VALOR_DESP_MOB FLOAT, VALOR_CONS_COMP FLOAT, VALOR_CONS_SAIDA FLOAT, VALOR_RET_ESTOQ FLOAT, VALOR_RET_PLA FLOAT, VALOR_AJUSTES FLOAT, VALOR_IMPLANT FLOAT, VALOR_FAT_NF FLOAT, VALOR_FAT_EFET FLOAT, VALOR_DES_CONT_PAG FLOAT, VALOR_DESP_DIV FLOAT, VALOR_DESP_ENTR FLOAT, VALOR_RET_CONS FLOAT, VALOR_INVEST FLOAT, VALOR_DEPREC_BENS FLOAT, VALOR_INV_INICIAL FLOAT, VALOR_INV_FINAL FLOAT
    , IMPOSTO_DESP_MOB FLOAT, IMPOSTO_CONS_COMP FLOAT, IMPOSTO_CONS_SAIDA FLOAT, IMPOSTO_RET_ESTOQ FLOAT, IMPOSTO_RET_PLA FLOAT, IMPOSTO_AJUSTES FLOAT, IMPOSTO_IMPLANT FLOAT, IMPOSTO_FAT_NF FLOAT, IMPOSTO_FAT_EFET FLOAT, IMPOSTO_DES_CONT_PAG FLOAT, IMPOSTO_DESP_DIV FLOAT, IMPOSTO_DESP_ENTR FLOAT, IMPOSTO_RET_CONS FLOAT, IMPOSTO_INVEST FLOAT, IMPOSTO_DEPREC_BENS FLOAT, IMPOSTO_INV_INICIAL FLOAT, IMPOSTO_INV_FINAL FLOAT
    , IMPOSTO_REC FLOAT
)
/
CREATE OR REPLACE TYPE GOP_DRO_TABLE AS TABLE OF GOP_DRO_TYPE
/
create or replace FUNCTION GOP_DRO
(
      P_NRORG IN ORGANIZACAO.NRORG%TYPE
    , P_CDAREAGERE IN AREAGERE.CDAREAGERE%TYPE
    , P_CDFILIAL IN FILIAL.CDFILIAL%TYPE
    , P_CDSUPERVISOR IN OPERADOR.CDOPERADOR%TYPE
    , P_MES IN VARCHAR2
    , P_ANO IN VARCHAR2
    , P_FLAGAGG IN CHAR
    , P_FLAGFILTER IN CHAR
)
RETURN GOP_DRO_TABLE
PIPELINED AS
    L_GOP_DRO_TYPE GOP_DRO_TYPE;
BEGIN
    FOR L_MYROW IN 
    (
        -- GOP_DRO_VIEW
        SELECT NRORG
             , CASE P_FLAGAGG WHEN 'N' THEN CDAREAGERE WHEN 'R' THEN CDAREAGERE ELSE '000' END AS CDAREAGERE
             , CASE P_FLAGAGG WHEN 'N' THEN NMAREAGERE WHEN 'R' THEN NMAREAGERE ELSE NULL END AS NMAREAGERE
             , CASE P_FLAGAGG WHEN 'N' THEN CDFILIAL ELSE '0000' END AS CDFILIAL
             , CASE P_FLAGAGG WHEN 'N' THEN NMFILIAL ELSE NULL END AS NMFILIAL
             , CASE P_FLAGAGG WHEN 'N' THEN CDSUPERVISOR WHEN 'S' THEN CDSUPERVISOR ELSE '000000000000' END AS CDSUPERVISOR
             , CASE P_FLAGAGG WHEN 'N' THEN NMSUPERVISOR WHEN 'S' THEN NMSUPERVISOR ELSE NULL END AS NMSUPERVISOR
             , MES
             , ANO
             , GOP_VERSAO
             , SUM(GOP_FAT_BRU) AS GOP_FAT_BRU, SUM(GOP_CONSUMO) AS GOP_CONSUMO, SUM(GOP_MAOBRA) AS GOP_MAOBRA, SUM(GOP_DESPMATRIZ) AS GOP_DESPMATRIZ, SUM(GOP_DESPDIVERSA) AS GOP_DESPDIVERSA, SUM(GOP_DESPESADFE) AS GOP_DESPESADFE, SUM(GOP_FATLIQU) AS GOP_FATLIQU, SUM(GOP_IMPOSTO) AS GOP_IMPOSTO, SUM(GOP_IMPLUC) AS GOP_IMPLUC, SUM(GOP_IMPRECU) AS GOP_IMPRECU, SUM(GOP_DEPREC) AS GOP_DEPREC, SUM(GOP_INVEST) AS GOP_INVEST
             , SUM(QTDE_DESP_MOB) AS QTDE_DESP_MOB, SUM(QTDE_CONS_COMP) AS QTDE_CONS_COMP, SUM(QTDE_CONS_SAIDA) AS QTDE_CONS_SAIDA, SUM(QTDE_RET_ESTOQ) AS QTDE_RET_ESTOQ, SUM(QTDE_RET_PLA) AS QTDE_RET_PLA, SUM(QTDE_AJUSTES) AS QTDE_AJUSTES, SUM(QTDE_IMPLANT) AS QTDE_IMPLANT, SUM(QTDE_FAT_NF) AS QTDE_FAT_NF, SUM(QTDE_FAT_EFET) AS QTDE_FAT_EFET, SUM(QTDE_DES_CONT_PAG) AS QTDE_DES_CONT_PAG, SUM(QTDE_DESP_DIV) AS QTDE_DESP_DIV, SUM(QTDE_DESP_ENTR) AS QTDE_DESP_ENTR, SUM(QTDE_RET_CONS) AS QTDE_RET_CONS, SUM(QTDE_INVEST) AS QTDE_INVEST, SUM(QTDE_DEPREC_BENS) AS QTDE_DEPREC_BENS, SUM(QTDE_INV_INICIAL) AS QTDE_INV_INICIAL, SUM(QTDE_INV_FINAL) AS QTDE_INV_FINAL
             , SUM(VALOR_DESP_MOB) AS VALOR_DESP_MOB, SUM(VALOR_CONS_COMP) AS VALOR_CONS_COMP, SUM(VALOR_CONS_SAIDA) AS VALOR_CONS_SAIDA, SUM(VALOR_RET_ESTOQ) AS VALOR_RET_ESTOQ, SUM(VALOR_RET_PLA) AS VALOR_RET_PLA, SUM(VALOR_AJUSTES) AS VALOR_AJUSTES, SUM(VALOR_IMPLANT) AS VALOR_IMPLANT, SUM(VALOR_FAT_NF) AS VALOR_FAT_NF, SUM(VALOR_FAT_EFET) AS VALOR_FAT_EFET, SUM(VALOR_DES_CONT_PAG) AS VALOR_DES_CONT_PAG, SUM(VALOR_DESP_DIV) AS VALOR_DESP_DIV, SUM(VALOR_DESP_ENTR) AS VALOR_DESP_ENTR, SUM(VALOR_RET_CONS) AS VALOR_RET_CONS, SUM(VALOR_INVEST) AS VALOR_INVEST, SUM(VALOR_DEPREC_BENS) AS VALOR_DEPREC_BENS, SUM(VALOR_INV_INICIAL) AS VALOR_INV_INICIAL, SUM(VALOR_INV_FINAL) AS VALOR_INV_FINAL
             , SUM(IMPOSTO_DESP_MOB) AS IMPOSTO_DESP_MOB, SUM(IMPOSTO_CONS_COMP) AS IMPOSTO_CONS_COMP, SUM(IMPOSTO_CONS_SAIDA) AS IMPOSTO_CONS_SAIDA, SUM(IMPOSTO_RET_ESTOQ) AS IMPOSTO_RET_ESTOQ, SUM(IMPOSTO_RET_PLA) AS IMPOSTO_RET_PLA, SUM(IMPOSTO_AJUSTES) AS IMPOSTO_AJUSTES, SUM(IMPOSTO_IMPLANT) AS IMPOSTO_IMPLANT, SUM(IMPOSTO_FAT_NF) AS IMPOSTO_FAT_NF, SUM(IMPOSTO_FAT_EFET) AS IMPOSTO_FAT_EFET, SUM(IMPOSTO_DES_CONT_PAG) AS IMPOSTO_DES_CONT_PAG, SUM(IMPOSTO_DESP_DIV) AS IMPOSTO_DESP_DIV, SUM(IMPOSTO_DESP_ENTR) AS IMPOSTO_DESP_ENTR, SUM(IMPOSTO_RET_CONS) AS IMPOSTO_RET_CONS, SUM(IMPOSTO_INVEST) AS IMPOSTO_INVEST, SUM(IMPOSTO_DEPREC_BENS) AS IMPOSTO_DEPREC_BENS, SUM(IMPOSTO_INV_INICIAL) AS IMPOSTO_INV_INICIAL, SUM(IMPOSTO_INV_FINAL) AS IMPOSTO_INV_FINAL
             , SUM(IMPOSTO_REC) AS IMPOSTO_REC
        FROM
        (
            SELECT * FROM GOP_DRO_VIEW
        )
        WHERE NRORG=P_NRORG
        AND (MES = P_MES OR P_MES IS NULL)
        AND (ANO = P_ANO OR P_ANO IS NULL)
        AND (
          ((INSTR(P_CDFILIAL||',',CDFILIAL||',')>0) AND P_FLAGFILTER = 'W')
          OR ((INSTR(P_CDFILIAL||',',CDFILIAL||',')=0) AND P_FLAGFILTER = 'B')
          OR P_CDFILIAL = 'T'
        )
        AND (
          ((INSTR(P_CDSUPERVISOR||',',CDSUPERVISOR||',')>0) AND P_FLAGFILTER = 'W')
          OR ((INSTR(P_CDSUPERVISOR||',',CDSUPERVISOR||',')=0) AND P_FLAGFILTER = 'B')
          OR P_CDSUPERVISOR = 'T'
        )
        AND (
          ((INSTR(P_CDAREAGERE||',',CDAREAGERE||',')>0) AND P_FLAGFILTER = 'W')
          OR ((INSTR(P_CDAREAGERE||',',CDAREAGERE||',')=0) AND P_FLAGFILTER = 'B')
          OR P_CDAREAGERE = 'T'
        )
        GROUP BY NRORG
             , CASE P_FLAGAGG WHEN 'N' THEN CDAREAGERE WHEN 'R' THEN CDAREAGERE ELSE '000' END
             , CASE P_FLAGAGG WHEN 'N' THEN NMAREAGERE WHEN 'R' THEN NMAREAGERE ELSE NULL END
             , CASE P_FLAGAGG WHEN 'N' THEN CDFILIAL ELSE '0000' END
             , CASE P_FLAGAGG WHEN 'N' THEN NMFILIAL ELSE NULL END
             , CASE P_FLAGAGG WHEN 'N' THEN CDSUPERVISOR WHEN 'S' THEN CDSUPERVISOR ELSE '000000000000' END
             , CASE P_FLAGAGG WHEN 'N' THEN NMSUPERVISOR WHEN 'S' THEN NMSUPERVISOR ELSE NULL END
             , MES
             , ANO
             , GOP_VERSAO
        ORDER BY CDAREAGERE, CDSUPERVISOR, CDFILIAL, ANO, MES, GOP_VERSAO
    ) LOOP
    L_GOP_DRO_TYPE := GOP_DRO_TYPE
    (
        L_MYROW.NRORG, L_MYROW.CDAREAGERE, L_MYROW.NMAREAGERE, L_MYROW.CDFILIAL, L_MYROW.NMFILIAL, L_MYROW.CDSUPERVISOR, L_MYROW.NMSUPERVISOR, L_MYROW.MES, L_MYROW.ANO, L_MYROW.GOP_VERSAO
      , L_MYROW.GOP_FAT_BRU, L_MYROW.GOP_CONSUMO, L_MYROW.GOP_MAOBRA, L_MYROW.GOP_DESPMATRIZ, L_MYROW.GOP_DESPDIVERSA, L_MYROW.GOP_DESPESADFE, L_MYROW.GOP_FATLIQU, L_MYROW.GOP_IMPOSTO, L_MYROW.GOP_IMPLUC, L_MYROW.GOP_IMPRECU, L_MYROW.GOP_DEPREC, L_MYROW.GOP_INVEST
      , L_MYROW.QTDE_DESP_MOB, L_MYROW.QTDE_CONS_COMP, L_MYROW.QTDE_CONS_SAIDA, L_MYROW.QTDE_RET_ESTOQ, L_MYROW.QTDE_RET_PLA, L_MYROW.QTDE_AJUSTES, L_MYROW.QTDE_IMPLANT, L_MYROW.QTDE_FAT_NF, L_MYROW.QTDE_FAT_EFET, L_MYROW.QTDE_DES_CONT_PAG, L_MYROW.QTDE_DESP_DIV, L_MYROW.QTDE_DESP_ENTR, L_MYROW.QTDE_RET_CONS, L_MYROW.QTDE_INVEST, L_MYROW.QTDE_DEPREC_BENS, L_MYROW.QTDE_INV_INICIAL, L_MYROW.QTDE_INV_FINAL
      , L_MYROW.VALOR_DESP_MOB, L_MYROW.VALOR_CONS_COMP, L_MYROW.VALOR_CONS_SAIDA, L_MYROW.VALOR_RET_ESTOQ, L_MYROW.VALOR_RET_PLA, L_MYROW.VALOR_AJUSTES, L_MYROW.VALOR_IMPLANT, L_MYROW.VALOR_FAT_NF, L_MYROW.VALOR_FAT_EFET, L_MYROW.VALOR_DES_CONT_PAG, L_MYROW.VALOR_DESP_DIV, L_MYROW.VALOR_DESP_ENTR, L_MYROW.VALOR_RET_CONS, L_MYROW.VALOR_INVEST, L_MYROW.VALOR_DEPREC_BENS, L_MYROW.VALOR_INV_INICIAL, L_MYROW.VALOR_INV_FINAL
      , L_MYROW.IMPOSTO_DESP_MOB, L_MYROW.IMPOSTO_CONS_COMP, L_MYROW.IMPOSTO_CONS_SAIDA, L_MYROW.IMPOSTO_RET_ESTOQ, L_MYROW.IMPOSTO_RET_PLA, L_MYROW.IMPOSTO_AJUSTES, L_MYROW.IMPOSTO_IMPLANT, L_MYROW.IMPOSTO_FAT_NF, L_MYROW.IMPOSTO_FAT_EFET, L_MYROW.IMPOSTO_DES_CONT_PAG, L_MYROW.IMPOSTO_DESP_DIV, L_MYROW.IMPOSTO_DESP_ENTR, L_MYROW.IMPOSTO_RET_CONS, L_MYROW.IMPOSTO_INVEST, L_MYROW.IMPOSTO_DEPREC_BENS, L_MYROW.IMPOSTO_INV_INICIAL, L_MYROW.IMPOSTO_INV_FINAL
      , L_MYROW.IMPOSTO_REC
    );
    PIPE ROW (L_GOP_DRO_TYPE);
    END LOOP;
    RETURN;
END GOP_DRO;
/