1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146
|
CREATE OR REPLACE TRIGGER "SRVTEST"."TRAVAIL_AFTER_CRE" AFTER
INSERT ON "TRAVAIL" FOR EACH ROW
DECLARE
VarMessage Varchar2(100);
BEGIN
VarMessage := 'Erreur sur Insert des tables TRAVAIL_COMPLET et TRAVAIL_AUTORIS';
INSERT INTO TRAVAIL_AUTORIS (NUM,TRAVAIL) values (:new.num,-1);
INSERT INTO TRAVAIL_COMPLET (NUM) values (:new.num);
VarMessage := 'Erreur sur le Update de la table TRAVAIL';
UPDATE TRAVAIL W
SET (
W.EMPLACEMENT
,W.EQUIPE
,W.ATELIER
,W.PARENT
,W.UNITE
,W.ZONE
,W.URGENCE
,W.STATUT
,W.CHANGEDATE
) = (
SELECT NVL(W.EMPLACEMENT, PM.EMPLACEMENT)
,NVL(W.EQUIPE, PM.EQUIPE)
,NVL(W.ATELIER, PM.ATELIER)
,NVL(W.PARENT, PM.PARENT)
,NVL(W.UNITE, PM.UNITE)
,NVL(W.ZONE, PM.ZONE)
,NVL(W.URGENCE, PM.URGENCE)
,NVL(W.STATUT, PM.WOSTATUT)
,SYSDATE
FROM PM_COMPLET PM
WHERE PM.PNUM IN (
SELECT W1.PNUM
FROM TRAVAIL W1
,TRAVAIL_COMPLET BT
WHERE W1.PNUM IS NOT NULL
AND (
W1.STATUT = 'PREPARE'
OR W1.STATUT = 'VALIDE'
)
AND W1.NUM = BT.NUM
AND BT.COPIE_PM IS NULL
AND W1.WONUM = :new.num
)
)
WHERE W.NUM = :new.num;
VarMessage := 'Erreur sur le Update de la table TRAVAIL_AUTORIS';
UPDATE TRAVAIL_AUTORIS BTAUTO
SET (
BTAUTO.AUTO_FEU
,BTAUTO.AUTO_FOUILLE
,BTAUTO.AUTO_TOITURE
,BTAUTO.AUTO_PENETRER
,BTAUTO.AUTO_TRAVAIL
,BTAUTO.AUTO_TRAVAIL_EXT
,BTAUTO.AUTO_LEVAGE
,BTAUTO.AUTO_TIRS
,BTAUTO.AUTO_NACELLE
,BTAUTO.AUTO_TRANSFERT
,BTAUTO.AUTO_AUCUNE
) = (
SELECT NVL(BTAUTO.AUTO_FEU, PMAUTO.AUTO_FEU) AS AUTO_FEU
,NVL(BTAUTO.AUTO_FOUILLE, PMAUTO.AUTO_FOUILLE) AS AUTO_FOUILLE
,NVL(BTAUTO.AUTO_TOITURE, PMAUTO.AUTO_TOITURE) AS AUTO_TOITURE
,NVL(BTAUTO.AUTO_PENETRER, PMAUTO.AUTO_PENETRER) AS AUTO_PENETRER
,NVL(BTAUTO.AUTO_TRAVAIL, PMAUTO.AUTO_TRAVAIL) AS AUTO_TRAVAIL
,NVL(BTAUTO.AUTO_TRAVAIL_EXT, PMAUTO.AUTO_TRAVAIL_EXT) AS AUTO_TRAVAIL_EXT
,NVL(BTAUTO.AUTO_LEVAGE, PMAUTO.AUTO_LEVAGE) AS AUTO_LEVAGE
,NVL(BTAUTO.AUTO_TIRS, PMAUTO.AUTO_TIRS) AS AUTO_TIRS
,NVL(BTAUTO.AUTO_NACELLE, PMAUTO.AUTO_NACELLE) AS AUTO_NACELLE
,NVL(BTAUTO.AUTO_TRANSFERT, PMAUTO.AUTO_TRANSFERT) AS AUTO_TRANSFERT
,NVL(BTAUTO.AUTO_AUCUNE, PMAUTO.AUTO_AUCUNE) AS AUTO_AUCUNE
FROM PM_WOAUTORIS PMAUTO
WHERE PMAUTO.PNUM IN (
SELECT W.PNUM
FROM TRAVAIL W
,TRAVAIL_COMPLET BT
WHERE W.PNUM IS NOT NULL
AND (
W.STATUT = 'PREPARE'
OR W.STATUT = 'VALIDE'
)
AND W.NUM = BT.NUM
AND BT.COPIE_PM IS NULL
AND W.NUM = :new.num
)
)
WHERE BTAUTO.NUM = :new.num;
VarMessage := 'Erreur sur le Update de la table TRAVAIL_COMPLET';
UPDATE TRAVAIL_COMPLET BT
SET (
BT.DERN_NUM_PRODUIT
,BT.DERN_DESIGNATION
,BT.DERN_CORROSIF
,BT.DERN_INFLAMMABLE
,BT.DERN_NOCIF
,BT.DERN_TOXIQUE
,BT.DERN_IRRITANT
,BT.DERN_AUTRE
,BT.COPIE_PM
,BT.DATE_PM
,BT.CAI_PM
) = (
SELECT NVL(BT.DERN_NUM_PRODUIT, PM.DERN_NUM_PRODUIT) AS DERN_NUM_PRODUIT
,NVL(BT.DERN_DESIGNATION, PM.DERN_DESIGNATION) AS DERN_DESIGNATION
,NVL(BT.DERN_CORROSIF, PM.DERN_CORROSIF) AS DERN_CORROSIF
,NVL(BT.DERN_INFLAMMABLE, PM.DERN_INFLAMMABLE) AS DERN_INFLAMMABLE
,NVL(BT.DERN_NOCIF, PM.DERN_NOCIF) AS DERN_NOCIF
,NVL(BT.DERN_TOXIQUE, PM.DERN_TOXIQUE) AS DERN_TOXIQUE
,NVL(BT.DERN_IRRITANT, PM.DERN_IRRITANT) AS DERN_IRRITANT
,NVL(BT.DERN_AUTRE, PM.DERN_AUTRE) AS DERN_AUTRE
,'OUI'
,SYSDATE
,PM.CHANGEBY
FROM PM_COMPLET PM
WHERE PM.PNUM IN (
SELECT W.PNUM
FROM TRAVAIL W
,TRAVAIL_COMPLET BTC
WHERE W.PNUM IS NOT NULL
AND (
W.STATUT = 'PREPARE'
OR W.STATUT = 'VALIDE'
)
AND W.NUM = BTC.NUM
AND BTC.COPIE_PM IS NULL
AND W.NUM = :new.num
)
)
WHERE BT.COPIE_PM IS NULL
AND BT.NUM = :new.num;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, VarMessage || '(Erreur:' || substr(SQLERRM, 1, 200) || ')');
END TRAVAIL_AFTER_CRE;
/ |
Partager