CREATE OR REPLACE TRIGGER GRAVA_PERIODOORCAGOP
BEFORE UPDATE OF IDPERIODOORCA, NRMESINIORCA, NRDIAINIORCA  ON PARAMORCAOPERAC
FOR EACH ROW
BEGIN
   IF (NVL(:OLD.IDPERIODOORCA, '!') <> NVL(:NEW.IDPERIODOORCA, '!')) OR  
      (NVL(:OLD.NRMESINIORCA, '!') <> NVL(:NEW.NRMESINIORCA, '!')) OR
      (NVL(:OLD.NRDIAINIORCA, 0) <> NVL(:NEW.NRDIAINIORCA, 0)) THEN

      DELETE FROM PERIODOORCAGOP;

      INSERT INTO PERIODOORCAGOP (NRPERIODOORCAGOP, IDPERIODOORCA, NRPERIODO, DTINIPERIORCA, DTFIMPERIORCA, CDFILIAL)
      SELECT ROWNUM, IDPERIODOORCA, ROWNUM NRPERIODO, 
             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,
             ADD_MONTHS(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'),CASE WHEN :NEW.NRDIAINIORCA = 1 THEN 1 ELSE 0 END)-1 DTFIMPERIORCA,
             'P' CDFILIAL
        FROM (SELECT ROWNUM, :NEW.IDPERIODOORCA IDPERIODOORCA, ROWNUM NRPERIODO, 
                     ADD_MONTHS(TO_DATE(:NEW.NRDIAINIORCA||'/'||:NEW.NRMESINIORCA||'/'||TO_CHAR(SYSDATE,'YYYY'),'DD/MM/YYYY'),(12/L.QTDEPERIODO)*(ROWNUM-1)) DTINIPERIORCA,
                     ADD_MONTHS(TO_DATE(:NEW.NRDIAINIORCA||'/'||:NEW.NRMESINIORCA||'/'||TO_CHAR(SYSDATE,'YYYY'),'DD/MM/YYYY'),(12/L.QTDEPERIODO)*ROWNUM)-1 DTFIMPERIORCA, :NEW.NRDIAINIORCA NRDIAINIORCA
                FROM DUAL P, (SELECT CASE :NEW.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 DUAL 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 :NEW.IDPERIODOORCA IDPERIODOORCA, ROWNUM NRPERIODO, 
                     ADD_MONTHS(TO_DATE('01/'||:NEW.NRMESINIORCA||'/'||TO_CHAR(SYSDATE,'YYYY'),'DD/MM/YYYY'),(12/L.QTDEPERIODO)*(ROWNUM-1)) DTINIPERIORCA,
                     ADD_MONTHS(TO_DATE('01/'||:NEW.NRMESINIORCA||'/'||TO_CHAR(SYSDATE,'YYYY'),'DD/MM/YYYY'),(12/L.QTDEPERIODO)*ROWNUM)-1 DTFIMPERIORCA
                FROM DUAL P, (SELECT CASE :NEW.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 DUAL 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;
    END IF;
END;
/