connect cinema/cinema@cinema
DROP TABLE TE_IMPOSTE;
CREATE TABLE TE_IMPOSTE
(
TE         VARCHAR2(2),
PERC_IVA   NUMBER,
PERC_INTRA NUMBER);

INSERT INTO TE_IMPOSTE(TE, PERC_IVA, PERC_INTRA) VALUES ('30', 21, 60);
INSERT INTO TE_IMPOSTE(TE, PERC_IVA, PERC_INTRA) VALUES ('33',  0, 10);
INSERT INTO TE_IMPOSTE(TE, PERC_IVA, PERC_INTRA) VALUES ('61', 21, 16);
INSERT INTO TE_IMPOSTE(TE, PERC_IVA, PERC_INTRA) VALUES ('64', 21, 16);
INSERT INTO TE_IMPOSTE(TE, PERC_IVA, PERC_INTRA) VALUES ('78', 21,  8);
INSERT INTO TE_IMPOSTE(TE, PERC_IVA, PERC_INTRA) VALUES ('84', 21,  8);
INSERT INTO TE_IMPOSTE(TE, PERC_IVA, PERC_INTRA) VALUES ('85', 21,  8);
COMMIT;
update iva_intrattenimento IT
set
IT.iva = (SELECT TEI.PERC_IVA FROM TE_IMPOSTE TEI, CALENDARIO C
          WHERE C.IDCALEND = IT.IDCALEND AND C.TE = TEI.TE)
where
IT.iva <> (SELECT TEI.PERC_IVA FROM TE_IMPOSTE TEI, CALENDARIO C
           WHERE C.IDCALEND = IT.IDCALEND AND C.TE = TEI.TE)
and
idcalend in (select idcalend from calendario where si='I' and trunc(data_ora) >= trunc(sysdate));
insert into iva_intrattenimento
SELECT
C.IDCALEND, TEI.PERC_IVA
FROM
(SELECT IDCALEND FROM CALENDARIO 
 MINUS
 SELECT IDCALEND FROM IVA_INTRATTENIMENTO) X,
CALENDARIO C, TE_IMPOSTE TEI
WHERE
TRUNC(C.DATA_ORA) >= TRUNC(SYSDATE)
AND
C.TE = TEI.TE
AND
C.SI = 'I'
AND
C.IDCALEND = X.IDCALEND;
commit;
disc;
connect wtic/obelix@cinema
CREATE TABLE WTIC.TIMESTAMP_SIGILLO
(
  DATA_AGG  DATE                                DEFAULT sysdate
)
LOGGING 
NOCACHE
NOPARALLEL
NOMONITORING;


ALTER TABLE WTIC.TIMESTAMP_SIGILLO ADD (
  CONSTRAINT TIMESTAMP_SIGILLO_PK
 PRIMARY KEY
 (DATA_AGG));

delete from timestamp_sigillo;
insert into timestamp_sigillo select sysdate from dual;
commit;


CREATE OR REPLACE TRIGGER WTIC.TRG_TIMESTAMP
AFTER INSERT
ON WTIC.LOGTRANS 
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
tmpVar NUMBER;
/******************************************************************************
   NAME:       TRG_TIMESTAMP
   PURPOSE:    

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        22/11/2011             1. Created this trigger.

******************************************************************************/
BEGIN

   UPDATE TIMESTAMP_SIGILLO SET DATA_AGG = TO_DATE(substr(:new.LOG_DATA, 65, 12), 'yyyymmddHH24mi') where DATA_AGG <= TO_DATE(substr(:new.LOG_DATA, 65, 12), 'yyyymmddHH24mi');
                                                                                                     
   EXCEPTION
     WHEN OTHERS THEN NULL;
       -- Consider logging the error and then re-raise
       -- RAISE;
END TRG_TIMESTAMP;
/



