#MegaERP - Materiais - Pedido de Compra - Trigger para impedir baixar Programação de Entrega no PRESENTE/FUTURO enquanto existe Programação em Aberto no PASSADO

[MGCLI]_[TRIGGER]_[T_CLI_EST_ITENSPEDPROGR].sql

  CREATE OR REPLACE TRIGGER "MGCLI"."T_CLI_EST_ITENSPEDPROGR" 
FOR INSERT OR UPDATE ON MGADM.EST_ITENSPEDPROGRAMADOS 
COMPOUND TRIGGER
    vOrigem             MGCLI.MAIL_LOG_GENERICO.BI_ST_ORIGEM%TYPE := 'MGCLI.T_CLI_EST_ITENSPEDPROGR';
    
    TYPE TYP_CLI_RC_ITNPDCPROG IS RECORD (
        ORG_TAB_IN_CODIGO MGADM.EST_ITENSPEDPROGRAMADOS.ORG_TAB_IN_CODIGO%TYPE,
        ORG_PAD_IN_CODIGO MGADM.EST_ITENSPEDPROGRAMADOS.ORG_PAD_IN_CODIGO%TYPE,
        ORG_IN_CODIGO MGADM.EST_ITENSPEDPROGRAMADOS.ORG_IN_CODIGO%TYPE,
        ORG_TAU_ST_CODIGO MGADM.EST_ITENSPEDPROGRAMADOS.ORG_TAU_ST_CODIGO%TYPE,
        SER_TAB_IN_CODIGO MGADM.EST_ITENSPEDPROGRAMADOS.SER_TAB_IN_CODIGO%TYPE,
        SER_IN_SEQUENCIA MGADM.EST_ITENSPEDPROGRAMADOS.SER_IN_SEQUENCIA%TYPE,
        PDC_IN_CODIGO MGADM.EST_ITENSPEDPROGRAMADOS.PDC_IN_CODIGO%TYPE,
        ITP_IN_SEQUENCIA MGADM.EST_ITENSPEDPROGRAMADOS.ITP_IN_SEQUENCIA%TYPE,
        ITP_DT_ENTREGA MGADM.EST_ITENSPEDPROGRAMADOS.ITP_DT_ENTREGA%TYPE
    );
    
    TYPE TYP_CLI_TB_ITNPDCPROG IS TABLE OF TYP_CLI_RC_ITNPDCPROG
        INDEX BY BINARY_INTEGER;
    
    rITNPDCPROG TYP_CLI_TB_ITNPDCPROG;
    
    -- Executed before each row change- :NEW, :OLD are available
    BEFORE EACH ROW IS
        i INTEGER := 0;
    BEGIN
        
        i := rITNPDCPROG.count + 1;
        
        rITNPDCPROG(i).ORG_TAB_IN_CODIGO := :NEW.ORG_TAB_IN_CODIGO;
        rITNPDCPROG(i).ORG_PAD_IN_CODIGO := :NEW.ORG_PAD_IN_CODIGO;
        rITNPDCPROG(i).ORG_IN_CODIGO     := :NEW.ORG_IN_CODIGO;
        rITNPDCPROG(i).ORG_TAU_ST_CODIGO := :NEW.ORG_TAU_ST_CODIGO;
        rITNPDCPROG(i).SER_TAB_IN_CODIGO := :NEW.SER_TAB_IN_CODIGO;
        rITNPDCPROG(i).SER_IN_SEQUENCIA  := :NEW.SER_IN_SEQUENCIA;
        rITNPDCPROG(i).PDC_IN_CODIGO     := :NEW.PDC_IN_CODIGO;
        rITNPDCPROG(i).ITP_IN_SEQUENCIA  := :NEW.ITP_IN_SEQUENCIA;
        rITNPDCPROG(i).ITP_DT_ENTREGA    := :NEW.ITP_DT_ENTREGA;
        
    END BEFORE EACH ROW;
    
    --Executed after DML statement
    AFTER STATEMENT IS
    BEGIN
        FOR i IN NVL(rITNPDCPROG.FIRST, 1) .. NVL(rITNPDCPROG.LAST,0)
        LOOP
            FOR cDT IN (
                SELECT  IP.org_in_codigo, IP.ser_in_sequencia, 
                        IP.pdc_in_codigo, IP.itp_in_sequencia
                        , I.PRO_IN_CODIGO, IP.ITP_DT_ENTREGA
                FROM mgadm.est_itenspedprogramados IP
                LEFT JOIN MGADM.est_itenspedcompra I
                ON  I.org_tab_in_codigo = IP.org_tab_in_codigo
                AND I.org_pad_in_codigo = IP.org_pad_in_codigo
                AND I.org_in_codigo     = IP.org_in_codigo
                AND I.org_tau_st_codigo = IP.org_tau_st_codigo
                AND I.ser_tab_in_codigo = IP.ser_tab_in_codigo
                AND I.ser_in_sequencia  = IP.ser_in_sequencia
                AND I.pdc_in_codigo     = IP.pdc_in_codigo
                AND I.itp_in_sequencia  = IP.itp_in_sequencia
                WHERE
                    
                    IP.org_tab_in_codigo = rITNPDCPROG(i).org_tab_in_codigo
                AND IP.org_pad_in_codigo = rITNPDCPROG(i).org_pad_in_codigo
                AND IP.org_in_codigo     = rITNPDCPROG(i).org_in_codigo
                AND IP.org_tau_st_codigo = rITNPDCPROG(i).org_tau_st_codigo
                AND IP.ser_tab_in_codigo = rITNPDCPROG(i).ser_tab_in_codigo
                AND IP.ser_in_sequencia  = rITNPDCPROG(i).ser_in_sequencia
                AND IP.pdc_in_codigo     = rITNPDCPROG(i).pdc_in_codigo
                AND IP.itp_in_sequencia  = rITNPDCPROG(i).itp_in_sequencia
                
                AND IP.itpp_re_quantidade <> IP.itpp_re_qtderecebida
                AND IP.itp_dt_entrega    < rITNPDCPROG(i).itp_dt_entrega
                AND ROWNUM < 1
            ) LOOP
                RAISE_APPLICATION_ERROR(-20101, 'Não é permitido baixar uma Programação de Entrega com Data['||rITNPDCPROG(i).ITP_DT_ENTREGA||'] enquanto houver outra Programação em Aberto com Data Anterior ['|| cDT.ITP_DT_ENTREGA ||']. Favor reavaliar o vinculo com o Pedido['||cDT.PDC_IN_CODIGO||']Item['||cDT.PRO_IN_CODIGO||'].' || CHR(13) || 'Em caso de dúvida, favor contatar o suporte [' || vOrigem ||']');
            END LOOP;
        END LOOP;
    END AFTER STATEMENT;
END;
ALTER TRIGGER "MGCLI"."T_CLI_EST_ITENSPEDPROGR" ENABLE