Calculation for year "turn" with Trigger

Asked

Viewed 60 times

2

How to treat the Trigger below so that the operations carried out between 21/11/2016 and 20/12/2016 return date 20/01/2017 instead of 20/01/2016.

DECLARE

  P_COUNT     NUMBER(5);
  DIA_VENDA  VARCHAR(2);
  MES_VENDA  VARCHAR(2);
  MES_VENDA2  VARCHAR(2);
  MES_VENDA_DEPOIS   VARCHAR(2);
  ANO_VENDA  VARCHAR(4);
  P_DATA     DATE;
  P_DATA2    DATE;
BEGIN

    IF :NEW.CODTIPVENDA=33 AND :NEW.TIPMOV IN('P') THEN

  
  SELECT TO_CHAR(SYSDATE,'DD') INTO DIA_VENDA
  FROM DUAL;
  
  SELECT TO_CHAR(ADD_MONTHS(sysdate,1),'MM') INTO MES_VENDA
  FROM DUAL; 
  
    SELECT TO_CHAR(ADD_MONTHS(sysdate,2),'MM') INTO MES_VENDA_DEPOIS
  FROM DUAL; 
  
  SELECT TO_CHAR((sysdate),'MM') INTO MES_VENDA2
  FROM DUAL;       
  
  SELECT TO_CHAR(sysdate,'YYYY') INTO ANO_VENDA
  FROM DUAL;   

  P_DATA:= TO_DATE('20'|| MES_VENDA_DEPOIS || ANO_VENDA,'DD/MM/YYYY');
  P_DATA2:= TO_DATE('20'|| MES_VENDA || ANO_VENDA,'DD/MM/YYYY');
      
     IF DIA_VENDA >= '21' AND DIA_VENDA <='31' THEN
     
         UPDATE TGFFIN
         SET DTVENC = P_DATA
         WHERE NUNOTA = :NEW.NUNOTA;
    
     END IF;
     
      IF DIA_VENDA >= '01' AND DIA_VENDA <='20' THEN
     
         UPDATE TGFFIN
         SET DTVENC = P_DATA2
         WHERE NUNOTA = :NEW.NUNOTA;
    
     END IF;
    
   
 END IF;
 
END;
/

2 answers

2

I BELIEVE IT WORKS

DECLARE

  P_COUNT     NUMBER(5);
  DIA_VENDA  VARCHAR(2);
  MES_VENDA  VARCHAR(2);
  MES_VENDA2  VARCHAR(2);
  MES_VENDA_DEPOIS   VARCHAR(2);
  ANO_VENDA  VARCHAR(4);
  P_DATA     DATE;
  P_DATA2    DATE;
BEGIN

  IF :NEW.CODTIPVENDA=33 AND :NEW.TIPMOV IN('P') THEN


  SELECT TO_CHAR(SYSDATE,'DD') INTO DIA_VENDA
  FROM DUAL;

  SELECT TO_CHAR(ADD_MONTHS(sysdate,1),'MM/YYYY') INTO ANOMES_VENDA
  FROM DUAL; 

    SELECT TO_CHAR(ADD_MONTHS(sysdate,2),'MM/YYYY') INTO ANOMES_VENDA_DEPOIS
  FROM DUAL; 

  SELECT TO_CHAR((sysdate),'MM') INTO MES_VENDA2
  FROM DUAL;       

  SELECT TO_CHAR(sysdate,'YYYY') INTO ANO_VENDA
  FROM DUAL;   

  P_DATA:= TO_DATE('20/'|| ANOMES_VENDA_DEPOIS,'DD/MM/YYYY');
  P_DATA2:= TO_DATE('20/'|| ANOMES_VENDA,'DD/MM/YYYY');

     IF DIA_VENDA >= '21' AND DIA_VENDA <='31' THEN

         UPDATE TGFFIN
         SET DTVENC = P_DATA
         WHERE NUNOTA = :NEW.NUNOTA;

     END IF;

      IF DIA_VENDA >= '01' AND DIA_VENDA <='20' THEN

         UPDATE TGFFIN
         SET DTVENC = P_DATA2
         WHERE NUNOTA = :NEW.NUNOTA;

     END IF;


 END IF;

0


thanks for the contribution; we edited its version and became functional as follows:

DECLARE

  DIA_VENDA  VARCHAR(2);
  MES_VENDA  VARCHAR(2);
  MES_VENDA2  VARCHAR(2);
  MES_VENDA_DEPOIS   VARCHAR(2);
  ANO_VENDA  VARCHAR(4);
  P_DATA     DATE;
  P_DATA2    DATE;
BEGIN

  IF :NEW.CODTIPVENDA=33 AND :NEW.TIPMOV IN('P') THEN

  
  SELECT TO_CHAR(SYSDATE,'DD') INTO DIA_VENDA
  FROM DUAL;
  
  SELECT TO_CHAR(ADD_MONTHS(sysdate,1),'MM') INTO MES_VENDA
  FROM DUAL; 
  
    SELECT TO_CHAR(ADD_MONTHS(sysdate,2),'MM') INTO MES_VENDA_DEPOIS
  FROM DUAL; 
  
  SELECT TO_CHAR((sysdate),'MM') INTO MES_VENDA2
  FROM DUAL;       

  IF (MES_VENDA2 >= '11' AND DIA_VENDA >= '21') OR (MES_VENDA2 = '12') THEN

	SELECT TO_CHAR(ADD_MONTHS(sysdate,12),'YYYY') INTO ANO_VENDA
	FROM DUAL;     

  ELSE
   
        SELECT TO_CHAR(sysdate,'YYYY') INTO ANO_VENDA
	FROM DUAL;   
    
  END IF;

  P_DATA:= TO_DATE('20'|| MES_VENDA_DEPOIS || ANO_VENDA,'DD/MM/YYYY');
  P_DATA2:= TO_DATE('20'|| MES_VENDA || ANO_VENDA,'DD/MM/YYYY');
      
     IF DIA_VENDA >= '21' AND DIA_VENDA <='31' THEN
     
         UPDATE TGFFIN
         SET DTVENC = P_DATA
         WHERE NUNOTA = :NEW.NUNOTA;
    
     END IF;
     
      IF DIA_VENDA >= '01' AND DIA_VENDA <='20' THEN
     
         UPDATE TGFFIN
         SET DTVENC = P_DATA2
         WHERE NUNOTA = :NEW.NUNOTA;
    
     END IF;
    
   
 END IF;
 
END;

Browser other questions tagged

You are not signed in. Login or sign up in order to post.