ALTER TABLE PERIODOORCAGOP ADD CDFILIAL VARCHAR2(04) DEFAULT 'P' NOT NULL
/

TRUNCATE TABLE PERIODOORCAGOP
/

INSERT INTO PERIODOORCAGOP (NRPERIODOORCAGOP, IDPERIODOORCA, NRPERIODO, DTINIPERIORCA, DTFIMPERIORCA, CDFILIAL)
SELECT ROWNUM, IDPERIODOORCA, ROWNUM, 
       TO_DATE(CASE WHEN E.NRDIAINIORCA > TO_NUMBER(TO_CHAR(LAST_DAY(E.DTINIPERIORCA),'DD'))
                    THEN TO_CHAR(LAST_DAY(E.DTINIPERIORCA),'DD')
                    ELSE LPAD(E.NRDIAINIORCA,2,'0')
               END || TO_CHAR(E.DTINIPERIORCA,'MM/YYYY'),'DD/MM/YYYY') DTINIPERIORCA,
       TO_DATE(CASE WHEN E.NRDIAINIORCA > TO_NUMBER(TO_CHAR(LAST_DAY(E.DTFIMPERIORCA),'DD'))
                    THEN TO_CHAR(LAST_DAY(E.DTFIMPERIORCA),'DD')
                    ELSE LPAD(E.NRDIAINIORCA,2,'0')
               END || TO_CHAR(E.DTFIMPERIORCA,'MM/YYYY'),'DD/MM/YYYY')-1 DTFIMPERIORCA,
       'P'
  FROM (SELECT ROWNUM, P.IDPERIODOORCA, ROWNUM, 
               ADD_MONTHS(TO_DATE(P.NRDIAINIORCA||'/'||P.NRMESINIORCA||'/'||TO_CHAR(SYSDATE,'YYYY'),'DD/MM/YYYY'),(12/L.QTDEPERIODO)*(ROWNUM-1)) DTINIPERIORCA,
               ADD_MONTHS(TO_DATE(P.NRDIAINIORCA||'/'||P.NRMESINIORCA||'/'||TO_CHAR(SYSDATE,'YYYY'),'DD/MM/YYYY'),(12/L.QTDEPERIODO)*ROWNUM)-1 DTFIMPERIORCA, P.NRDIAINIORCA
          FROM PARAMORCAOPERAC P, (SELECT CASE PO.IDPERIODOORCA 
                                             WHEN 'SEMESTRAL' THEN 2
                                             WHEN 'QUADRIMESTRAL' THEN 3
                                             WHEN 'TRIMESTRAL' THEN 4
                                             WHEN 'BIMESTRAL' THEN 6
                                             WHEN 'MENSAL' THEN 12
                                             ELSE 1
                                          END QTDEPERIODO
                                     FROM PARAMORCAOPERAC PO) L
       CONNECT BY LEVEL <= L.QTDEPERIODO) E
/

INSERT INTO PERIODOORCAGOP (NRPERIODOORCAGOP, IDPERIODOORCA, NRPERIODO, DTINIPERIORCA, DTFIMPERIORCA, CDFILIAL)
SELECT (SELECT MAX(NRPERIODOORCAGOP)
          FROM PERIODOORCAGOP) + ROWNUM, E.IDPERIODOORCA, E.NRPERIODO, 
       TO_DATE(CASE WHEN F.NRDIAINIORCAGOP > TO_NUMBER(TO_CHAR(LAST_DAY(E.DTINIPERIORCA),'DD'))
                    THEN TO_CHAR(LAST_DAY(E.DTINIPERIORCA),'DD')
                    ELSE LPAD(F.NRDIAINIORCAGOP,2,'0')
               END || TO_CHAR(E.DTINIPERIORCA,'MM/YYYY'),'DD/MM/YYYY') DTINIPERIORCA,
       ADD_MONTHS(TO_DATE(CASE WHEN F.NRDIAINIORCAGOP > TO_NUMBER(TO_CHAR(LAST_DAY(E.DTFIMPERIORCA),'DD'))
                               THEN TO_CHAR(LAST_DAY(E.DTFIMPERIORCA),'DD')
                               ELSE LPAD(F.NRDIAINIORCAGOP,2,'0')
                          END || TO_CHAR(E.DTFIMPERIORCA,'MM/YYYY'),'DD/MM/YYYY'),1)-1 DTFIMPERIORCA,
       F.CDFILIAL
  FROM (SELECT P.IDPERIODOORCA, ROWNUM NRPERIODO, 
               ADD_MONTHS(TO_DATE('01/'||P.NRMESINIORCA||'/'||TO_CHAR(SYSDATE,'YYYY'),'DD/MM/YYYY'),(12/L.QTDEPERIODO)*(ROWNUM-1)) DTINIPERIORCA,
               ADD_MONTHS(TO_DATE('01/'||P.NRMESINIORCA||'/'||TO_CHAR(SYSDATE,'YYYY'),'DD/MM/YYYY'),(12/L.QTDEPERIODO)*ROWNUM)-1 DTFIMPERIORCA
          FROM PARAMORCAOPERAC P, (SELECT CASE PO.IDPERIODOORCA 
                                             WHEN 'SEMESTRAL' THEN 2
                                             WHEN 'QUADRIMESTRAL' THEN 3
                                             WHEN 'TRIMESTRAL' THEN 4
                                             WHEN 'BIMESTRAL' THEN 6
                                             WHEN 'MENSAL' THEN 12
                                             ELSE 1
                                          END QTDEPERIODO
                                     FROM PARAMORCAOPERAC PO) L
       CONNECT BY LEVEL <= L.QTDEPERIODO) E, FILIAL F
 WHERE F.NRDIAINIORCAGOP IS NOT NULL
   AND F.IDCNTRRESC <> 'S'
 ORDER BY 2, 3, 4
/