Calculation for year "turn" with trigger

Question

Calculation for year "turn" with trigger

Navigation

#1 by (2 votes)
#2 by (0 votes)

2

How to treat the trigger below so that operations performed between 11/21/2016 and 12/20/2016 return date 01/20/2017 instead of 01/20/2016. p>

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;
/

oracle pl-sql trigger

asked by anonymous 10.11.2016 / 18:52

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;

11.11.2016 / 15:47

Requesting APIs with Database for Angles Grab a button in Splinter (python)

score 0 · Accepted Answer

Thank you for the contribution; we edit your version and it works 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;