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
|
DECLARE
DATESYS DATE;
DATEJOB DATE;
ERR_NUM VARCHAR2(255);
ERR_MSG VARCHAR2(255);
ACK VARCHAR2(1);
OLD_A_LIRE VARCHAR2(1);
NEW_A_LIRE VARCHAR2(1);
BEGIN
IF ( :NEW.CUSTOMER_ID = 'BPLVA' ) THEN
BEGIN
BEGIN
SELECT M_ACK, M_A_LIRE INTO ACK, OLD_A_LIRE FROM PROD.INT_SAMPLE
WHERE ID_NUMERIC = :NEW.ID_NUMERIC;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
IF (OLD_A_LIRE = 'I') THEN
NEW_A_LIRE := 'I';
ELSE
NEW_A_LIRE := 'U';
END IF;
IF ( ACK = 'V') THEN
UPDATE PROD.INT_SAMPLE SET M_ACK = 'O'
WHERE ID_NUMERIC = :NEW.ID_NUMERIC;
ELSE
BEGIN
DATESYS := ADD_MONTHS(SYSDATE, -4);
SELECT DATE_CREATED INTO DATEJOB
FROM PROD.JOB_HEADER PJH
WHERE PJH.JOB_NAME = :NEW.JOB_NAME;
IF ((DATEJOB > DATESYS) AND (DATEJOB IS NOT NULL )) THEN
BEGIN
UPDATE PROD.INT_SAMPLE SET
ID_TEXT =:NEW.ID_TEXT,
JOB_NAME =:NEW.JOB_NAME,
STATUS =:NEW.STATUS,
OLD_STATUS =:NEW.OLD_STATUS,
COMPARED =:NEW.COMPARED,
ON_SPEC =:NEW.ON_SPEC,
RE_SAMPLED =:NEW.RE_SAMPLED,
ORIGINAL_SAMPLE =:NEW.ORIGINAL_SAMPLE,
LINK_NUMBER =:NEW.LINK_NUMBER,
LOGIN_DATE =:NEW.LOGIN_DATE,
LOGIN_BY =:NEW.LOGIN_BY,
SAMPLED_DATE =:NEW.SAMPLED_DATE,
RECD_DATE =:NEW.RECD_DATE,
DATE_STARTED =:NEW.DATE_STARTED,
STARTER =:NEW.STARTER,
DATERESREQ =:NEW.DATERESREQ,
DATE_COMPLETED =:NEW.DATE_COMPLETED,
COMPLETER =:NEW.COMPLETER,
DATERESAVAIL =:NEW.DATERESAVAIL,
DATE_AUTHORISED =:NEW.DATE_AUTHORISED,
AUTHORISER =:NEW.AUTHORISER,
AUTHORISATION_NOTES =:NEW.AUTHORISATION_NOTES,
PRODUCT =:NEW.PRODUCT,
PRODUCT_VERSION =:NEW.PRODUCT_VERSION,
GRADE_CODE =:NEW.GRADE_CODE,
TESTS_TO_DO =:NEW.TESTS_TO_DO,
ON_WKS =:NEW.ON_WKS,
PROJECT_ID =:NEW.PROJECT_ID,
BATCH_NAME =:NEW.BATCH_NAME,
SAMPLING_POINT =:NEW.SAMPLING_POINT,
SAMPLE_TYPE =:NEW.SAMPLE_TYPE,
SAMPLE_NAME =:NEW.SAMPLE_NAME,
DESCRIPTION =:NEW.DESCRIPTION,
PREPARATION =:NEW.PREPARATION,
HAZARD =:NEW.HAZARD,
PRIORITY =:NEW.PRIORITY,
LOCATION_ID =:NEW.LOCATION_ID,
CUSTOMER_ID =:NEW.CUSTOMER_ID,
INVOICE_NUMBER =:NEW.INVOICE_NUMBER,
TEST_SCHEDULE =:NEW.TEST_SCHEDULE,
TEMPLATE_ID =:NEW.TEMPLATE_ID,
COMP_PROD_NAME =:NEW.COMP_PROD_NAME,
COMP_PROD_VER =:NEW.COMP_PROD_VER,
COMP_PROD_GRADE =:NEW.COMP_PROD_GRADE,
STANDARD =:NEW.STANDARD,
STANDARD_ID =:NEW.STANDARD_ID,
STANDARD_VERSION =:NEW.STANDARD_VERSION,
INSPECTION_HEADER =:NEW.INSPECTION_HEADER,
STANDARD_TYPE =:NEW.STANDARD_TYPE,
BATCH_ID =:NEW.BATCH_ID,
DYNAMIC_PRODUCT =:NEW.DYNAMIC_PRODUCT,
TOTAL_ELEMENTS =:NEW.TOTAL_ELEMENTS,
SAMPLING_PROCEDURE =:NEW.SAMPLING_PROCEDURE,
REPLICATE_LINK_NO =:NEW.REPLICATE_LINK_NO,
AUTO_VALIDATE =:NEW.AUTO_VALIDATE,
HAS_INCIDENTS =:NEW.HAS_INCIDENTS,
M_DEMANDEUR =:NEW.M_DEMANDEUR,
M_COMMENT_PRELEV =:NEW.M_COMMENT_PRELEV,
M_COMMENTLABO =:NEW.M_COMMENTLABO,
M_COMMENTCLIENT =:NEW.M_COMMENTCLIENT,
M_DEGRE_URGENCE =:NEW.M_DEGRE_URGENCE,
M_ECHPERE =:NEW.M_ECHPERE,
M_CONFORMITE =:NEW.M_CONFORMITE,
M_STATUS_ORIGINAL =:NEW.M_STATUS_ORIGINAL,
GROUP_ID =:NEW.GROUP_ID,
M_DATE_CONF =:NEW.M_DATE_CONF,
M_FLAG_PI =:NEW.M_FLAG_PI,
M_TYPE_PLAN =:NEW.M_TYPE_PLAN,
M_REFCLIENT =:NEW.M_REFCLIENT,
M_DEVALIDEUR =:NEW.M_DEVALIDEUR,
M_COMMENT_DEVALIDATION =:NEW.M_COMMENT_DEVALIDATION,
M_DATE_DEVALIDATION =:NEW.M_DATE_DEVALIDATION,
M_DATE_BA =:NEW.M_DATE_BA,
M_OP_BA =:NEW.M_OP_BA,
M_COM_BA =:NEW.M_COM_BA,
M_DATEMAJ = SYSDATE,
M_LOCK = 'N',
M_ACK = 'Q',
M_A_LIRE = NEW_A_LIRE
WHERE PROD.INT_SAMPLE.ID_NUMERIC = :NEW.ID_NUMERIC;
EXCEPTION
WHEN OTHERS THEN
ERR_NUM := TO_CHAR( SQLCODE );
ERR_MSG := SQLERRM;
INSERT INTO INT_TRIGGER_ERROR (ERROR_DATE, TABLE_NAME, TRIGGER_NAME, ERROR_MESSAGE)
VALUES (SYSDATE, 'SAMPLE', 'TAU_SAMPLE', 'erreur '||ERR_NUM||' '||ERR_MSG||' Cle primaire : '||:NEW.ID_NUMERIC);
END;
END IF;
END;
END IF;
END;
END IF;
END; |
