DROP FUNCTION OPERACESSOINDIC_DSB
/

DROP TYPE OPERACESSOINDIC_DSB_TABLE
/

DROP TYPE OPERACESSOINDIC_DSB_TYPE
/

CREATE TYPE OPERACESSOINDIC_DSB_TYPE AS
     OBJECT (NROPERACESSOINDIC_DSB NUMBER,
             NRINDICADORTF NUMBER,
             NRORG NUMBER)
/

CREATE TYPE OPERACESSOINDIC_DSB_TABLE AS
      TABLE OF OPERACESSOINDIC_DSB_TYPE
/

CREATE OR REPLACE FUNCTION OPERACESSOINDIC_DSB
    (P_CDFILIAL   IN VARCHAR2,
     P_CDOPERADOR IN VARCHAR2,
     P_NRORG      IN NUMBER) RETURN OPERACESSOINDIC_DSB_TABLE PIPELINED
IS
  V_SEQ NUMBER;
  V_L_NRPERFILACESSO VARCHAR2(4000);
  V_CDFILIAL VARCHAR2(04);
  V_IDUTLDSBTPADRAO VARCHAR2(01);
  V_NRINDICADORTF VARCHAR2(10);

  OUT_RECORD OPERACESSOINDIC_DSB_TYPE := OPERACESSOINDIC_DSB_TYPE(NULL, NULL, NULL);
BEGIN
  FOR C_CURSOR IN (SELECT IP.NRINDICADORTF
                     FROM PERFACLPAINEL PP INNER JOIN INDICPAINEL IP ON PP.NRPERFACLPAINEL = IP.NRPERFACLPAINEL
                                           INNER JOIN PERFILACESSO PA ON PA.NRPERFILACESSO = PP.NRPERFILACESSO
                                           INNER JOIN PERFACESSPARC PF ON PA.NRPERFILACESSO = PF.NRPERFILACESSO
                                           INNER JOIN OPERADOR O ON PF.NRPARCNEGOCIO = O.NRPARCNEGOCIO
                                           LEFT  JOIN INDICPAINEXCOPER EO ON EO.NRINDICPAINEL = IP.NRINDICPAINEL AND EO.CDOPERADOR = O.CDOPERADOR
                    WHERE O.CDOPERADOR = P_CDOPERADOR
                      AND PP.CDFILIAL = DECODE((SELECT IDUTLDSBTPADRAO
                                                  FROM PARAMFILIAL
                                                 WHERE CDFILIAL = P_CDFILIAL), 'N', P_CDFILIAL, 'S', 'P', P_CDFILIAL)                            
                      AND PP.NRORG = P_NRORG
                      AND (IP.IDVISUINDIC = 'N' OR IP.NRINDICPAINEL = EO.NRINDICPAINEL)
                    UNION
                   SELECT IT.NRINDICADORTF
                     FROM INDICADORTF IT LEFT JOIN PERFACLPAINEL P ON IT.NRPAINEL = P.NRPAINEL
                    WHERE NOT EXISTS (SELECT PA.NRPERFILACESSO, PA.NMPERFILACESSO
                                        FROM PERFILACESSO PA, OPERADOR O, PERFACESSPARC PP
                                       WHERE PA.NRPERFILACESSO = PP.NRPERFILACESSO
                                         AND PP.NRPARCNEGOCIO = O.NRPARCNEGOCIO
                                         AND O.CDOPERADOR = P_CDOPERADOR
                                         AND P.NRPERFILACESSO = PP.NRPERFILACESSO)
                      AND CASE WHEN P.NRPERFILACESSO IS NULL THEN 'N' ELSE 'S' END = 'N') LOOP

      OUT_RECORD.NROPERACESSOINDIC_DSB := 0;
      OUT_RECORD.NRINDICADORTF := C_CURSOR.NRINDICADORTF;
      OUT_RECORD.NRORG := 0;

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

-- VERSAO --
BEGIN ATUVEROBJ('OPERACESSOINDIC_DSB','6.35.009'); END;
/
