CREATE OR REPLACE FORCE VIEW RECEITA_RECURSIVA AS SELECT * FROM (
SELECT CDFILIAL,
       CDRECEITA AS CDRECROOT,
       NRVARIACAOREC AS NRVRECROOT,
       '' AS CDRECPAI,
       NULL AS NRVRECPAI,
       CDRECEITA,
       NRVARIACAOREC,
       0 AS LVL,
       CDRECEITA AS ORD,
       NRORG
FROM FILIRECEITA
UNION
SELECT FIL.CDFILIAL,
       CONNECT_BY_ROOT(SUB.CDRECEITA) AS CDRECROOT,
       CONNECT_BY_ROOT(SUB.NRVARIACAOREC) AS NRVRECROOT,
       SUB.CDRECEITA AS CDRECPAI,
       SUB.NRVARIACAOREC AS NRVRECPAI,
       SUB.CDSUBRECEITA AS CDRECEITA,
       SBFIL.NRVARIACAOREC AS NRVARIACAOREC,
       LEVEL AS LVL,
       CONNECT_BY_ROOT(SUB.CDRECEITA)||SYS_CONNECT_BY_PATH(LPAD(SUB.NRORDSUBREC, 3, '0')||REC.DSRECEITA, '') AS ORD,
       SUB.NRORG
FROM SUBRECEITAM SUB
JOIN RECEITAM RECPAI
  ON RECPAI.CDRECEITA = SUB.CDRECEITA AND
     RECPAI.NRORG = SUB.NRORG AND
     ((RECPAI.IDRECFLEXIVEL <> 'P') OR (SUB.IDPREFERENCIA = 'S'))
JOIN RECEITAM REC
  ON REC.CDRECEITA = SUB.CDSUBRECEITA
 AND REC.NRORG = SUB.NRORG
JOIN FILIRECEITA FIL
  ON FIL.CDRECEITA = SUB.CDRECEITA
 AND FIL.NRVARIACAOREC = SUB.NRVARIACAOREC
 AND FIL.NRORG = SUB.NRORG
LEFT JOIN FILIRECEITA SBFIL
  ON SBFIL.CDRECEITA = SUB.CDSUBRECEITA
 AND SBFIL.CDFILIAL = FIL.CDFILIAL
 AND SBFIL.NRORG = SUB.NRORG
CONNECT BY SUB.CDRECEITA = PRIOR SUB.CDSUBRECEITA
       AND FIL.CDFILIAL = PRIOR SBFIL.CDFILIAL
       AND SUB.NRORG = PRIOR SUB.NRORG
)
/