CREATE OR REPLACE PROCEDURE DETERMINA_FLUXO_PRODUCAO
(
P_CDFILIAL IN VARCHAR2,
P_CDSETOR IN VARCHAR2,
P_NRCATEGORIAPROD IN VARCHAR2,
P_NRSUBCATEGORIAPROD IN VARCHAR2,
P_NRTIPOPROD IN VARCHAR2,
P_NRTIPONEGOCIOINF IN VARCHAR2,
P_CDRECEITA IN VARCHAR2,
P_CDCIA IN VARCHAR2,
P_NRVOO IN VARCHAR2,
P_CDSETORPAI IN VARCHAR2,
P_NRCATEGORIAPRODPAI IN VARCHAR2,
P_NRSUBCATEGORIAPRODPAI IN VARCHAR2,
P_CDRECEITAPAI IN VARCHAR2,
P_CDSETORROOT IN VARCHAR2,
P_NRCATEGORIAPRODROOT IN VARCHAR2,
P_NRSUBCATEGORIAPRODROOT IN VARCHAR2,
P_CDRECEITAROOT IN VARCHAR2,
P_NRTURNOETD IN VARCHAR2,
P_NRORG IN NUMBER,
P_NRTURNO OUT VARCHAR2,
P_NRDIAS OUT VARCHAR2
)
AS
V_PRIOR NUMBER;
V_MAIORPRIOR NUMBER;
V_IDNACIVOO VARCHAR2(1);
BEGIN

SELECT MAX(IDNACIVOO) INTO V_IDNACIVOO FROM VOO WHERE CDFILIAL = P_CDFILIAL AND CDCIA = P_CDCIA AND NRVOO = P_NRVOO AND NRORG = P_NRORG;

V_MAIORPRIOR := -1;
FOR CUR_FLUX IN (
  SELECT * FROM FLUXPRODREC
  WHERE CDFILIAL = P_CDFILIAL AND CDSETOR = P_CDSETOR AND NRCATEGORIAPROD = P_NRCATEGORIAPROD AND NVL(NRSUBCATEGORIAPROD, -1) = NVL(P_NRSUBCATEGORIAPROD, -1) AND NRORG = P_NRORG
  )
LOOP
  V_PRIOR := 0;
  
  IF (CUR_FLUX.IDABRANTPP = 1)
  THEN
    IF (CUR_FLUX.NRTIPOPROD = P_NRTIPOPROD)
    THEN
      V_PRIOR := V_PRIOR + 1;
    ELSE
      CONTINUE;
    END IF;
  END IF;
  
  IF (CUR_FLUX.IDABRANTPN = 1)
  THEN
    IF (CUR_FLUX.NRTIPONEGOCIOINF = P_NRTIPONEGOCIOINF)
    THEN
      V_PRIOR := V_PRIOR + 2;
    ELSE
      CONTINUE;
    END IF;
  END IF;
  
  IF (CUR_FLUX.IDABRANREC = 1)
  THEN
    IF (CUR_FLUX.CDRECEITA = P_CDRECEITA)
    THEN
      V_PRIOR := V_PRIOR + 4;
    ELSE
      CONTINUE;
    END IF;
  END IF;
  
  IF (CUR_FLUX.IDABRANCIA = 1)
  THEN
    IF (CUR_FLUX.CDCIA = P_CDCIA)
    THEN
      V_PRIOR := V_PRIOR + 8;
    ELSE
      CONTINUE;
    END IF;
  END IF;
  
  IF (CUR_FLUX.IDABRANVOO = V_IDNACIVOO)
  THEN
    V_PRIOR := V_PRIOR + 16;
  END IF;
  
  IF (CUR_FLUX.IDABRANVOO = 3)
  THEN
    IF (CUR_FLUX.CDCIA = P_CDCIA AND CUR_FLUX.NRVOO = P_NRVOO)
    THEN
      V_PRIOR := V_PRIOR + 32;
    ELSE
      CONTINUE;
    END IF;
  END IF;
  
  IF (CUR_FLUX.IDSTRPARENTDEPENDENT = 1)
  THEN
    IF (CUR_FLUX.PAI_CDSETOR = P_CDSETORPAI)
    THEN
      V_PRIOR := V_PRIOR + 64;
    ELSE
      CONTINUE;
    END IF;
  END IF;
  
  IF (CUR_FLUX.IDCATPARENTDEPENDENT = 1)
  THEN
    IF (CUR_FLUX.PAI_NRCATEGORIAPROD = P_NRCATEGORIAPRODPAI)
    THEN
      V_PRIOR := V_PRIOR + 128;
    ELSE
      CONTINUE;
    END IF;
  END IF;
  
  IF (CUR_FLUX.IDSUBCATPARENTDEPENDENT = 1)
  THEN
    IF (CUR_FLUX.PAI_NRSUBCATEGORIAPROD = P_NRSUBCATEGORIAPRODPAI)
    THEN
      V_PRIOR := V_PRIOR + 256;
    ELSE
      CONTINUE;
    END IF;
  END IF;
  
  IF (CUR_FLUX.IDPARENTDEPENDENT = 1)
  THEN
    IF (CUR_FLUX.PAI_CDRECEITA = P_CDRECEITAPAI)
    THEN
      V_PRIOR := V_PRIOR + 512;
    ELSE
      CONTINUE;
    END IF;
  END IF;
  
  
  IF (CUR_FLUX.IDSTRROOTDEPENDENT = 1)
  THEN
    IF (CUR_FLUX.ROOT_CDSETOR = P_CDSETORROOT)
    THEN
      V_PRIOR := V_PRIOR + 1024;
    ELSE
      CONTINUE;
    END IF;
  END IF;
  
  IF (CUR_FLUX.IDCATROOTDEPENDENT = 1)
  THEN
    IF (CUR_FLUX.ROOT_NRCATEGORIAPROD = P_NRCATEGORIAPRODROOT)
    THEN
      V_PRIOR := V_PRIOR + 2048;
    ELSE
      CONTINUE;
    END IF;
  END IF;
  
  IF (CUR_FLUX.IDSUBCATROOTDEPENDENT = 1)
  THEN
    IF (CUR_FLUX.ROOT_NRSUBCATEGORIAPROD = P_NRSUBCATEGORIAPRODROOT)
    THEN
      V_PRIOR := V_PRIOR + 4096;
    ELSE
      CONTINUE;
    END IF;
  END IF;
  
  IF (CUR_FLUX.IDROOTDEPENDENT = 1)
  THEN
    IF (CUR_FLUX.ROOT_CDRECEITA = P_CDRECEITAROOT)
    THEN
      V_PRIOR := V_PRIOR + 8192;
    ELSE
      CONTINUE;
    END IF;
  END IF;
  
  IF (V_PRIOR > V_MAIORPRIOR)
  THEN
    V_MAIORPRIOR := V_PRIOR;
    P_NRTURNO := CASE P_NRTURNOETD WHEN 1 THEN CUR_FLUX.NRTURNOANTES WHEN 2 THEN CUR_FLUX.NRTURNODEPOIS END;
    P_NRDIAS := CASE P_NRTURNOETD WHEN 1 THEN CUR_FLUX.NRDIASANTES WHEN 2 THEN CUR_FLUX.NRDIASDEPOIS END;
  END IF;
END LOOP;
END;
/