TGS_SAF_ITEM_BI.SQL

CREATE OR REPLACE TRIGGER TGS_SAF_ITEM_BI
  AFTER INSERT ON GA_SAF_ITEM FOR EACH ROW
DECLARE  
  CURSOR C_ESTOQUE IS
    SELECT ERP.CODPRO AS PRODUTO,
           DEP.CODDEP AS DEPOSITO,
           EMP.COD_EMPR_GA AS EMPRESA,
           ERP.QTDEST - ERP.QTDRES AS SALDO
      FROM SAPIENS.E210EST ERP,
           SAPIENS.E205DEP DEP,
           DB_INTEGRACAO.GATEC_DEPARA_EMPRESA EMP
     WHERE ERP.CODEMP = DEP.CODEMP
       AND ERP.CODDEP = DEP.CODDEP
       AND ERP.CODEMP = EMP.COD_EMPR_ERP_SEMFILIAL
       AND DEP.CODFIL = EMP.COD_EMPR_ERP_FILIAL
       AND ERP.QTDEST - ERP.QTDRES > 0
       AND ERP.CODPRO = :NEW.COD_ITEM
       AND EXISTS (SELECT 1
                     FROM GATEC_SAF.GA_EMPR A
                    WHERE A.COD_EMPR = EMP.COD_EMPR_GA)
       AND EXISTS (SELECT 1
                     FROM GATEC_SAF.GA_SAF_LOCAL B
                    WHERE B.COD_EMPR = EMP.COD_EMPR_GA
                      AND B.COD_LOCAL = DEP.CODDEP);
  
  R_ESTOQUE C_ESTOQUE%ROWTYPE;
BEGIN
  OPEN C_ESTOQUE;
  
  LOOP
    FETCH C_ESTOQUE INTO R_ESTOQUE;
    EXIT WHEN C_ESTOQUE%NOTFOUND;
    
    BEGIN
      DB_INTEGRACAO.GAP_SALDO_ESTOQUE(
        R_ESTOQUE.PRODUTO,
        R_ESTOQUE.DEPOSITO,
        R_ESTOQUE.EMPRESA,
        R_ESTOQUE.SALDO
      );
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;   
  END LOOP;
END TGS_SAF_ITEM_BI;