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
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
| create or replace
PROCEDURE TMP_HANDLE_TLS_MISHKAL
IS
CURSOR C_EMP IS
SELECT *
FROM weight_conf
WHERE NVL(datetime_conf,TO_DATE('01-01-1970','dd-mm-yyyy')) = TO_DATE('01-01-1970','dd-mm-yyyy')
;
cur C_emp%ROWTYPE;
CURSOR transp (transpid transports.transport_id%TYPE) IS
SELECT * FROM transports WHERE transport_id = transpid FOR UPDATE OF exit_weight, entry_weight;
lt_trans transp%ROWTYPE;
CURSOR wconftoday IS
SELECT * FROM weight_conf WHERE datetime >= trunc(sysdate) ;
cur_wconf wconftoday%ROWTYPE;
CURSOR transp_today (transpid transports.transport_id%TYPE) IS
select * from transports where entry_date > sysdate-1 AND transport_id = transpid FOR UPDATE OF sent_status_cd, sent_status_date;
lt_transtoday transp_today%ROWTYPE;
CURSOR tr_tosend IS
SELECT * FROM transports
WHERE(sent_status_cd = -1 or sent_status_cd is null or sent_status_cd = 0 or sent_status_cd=-2 )
AND exit_date IS NULL and action_id<>2 and factory_id=2 and entry_date > sysdate-1
ORDER BY transports.transport_id;
ls_tr_tosend tr_tosend%ROWTYPE;
CURSOR car_by_licensno(trailerid transports.trailer_id%TYPE) IS
select emptyweight,maxweight from cars where licensno = trailerid;
ls_car_by_licensno car_by_licensno%ROWTYPE;
PROCEDURE error_log (p_errorcode IN NUMBER, p_trans IN NUMBER, p_trailer IN NUMBER, p_errdescr IN VARCHAR2, p_errora IN VARCHAR2, p_errcom IN VARCHAR2)
IS
lv_sql NUMBER;
BEGIN -- executable part starts here
INSERT INTO Errors_log_a
(SEQ_ID, ERROR_CODE, TRANSPORT_ID, TRAILER_ID, DATETIMERR, ERROR_DESCR, ERROR_ORA, ERROR_COMMENT )
values (
lv_sql,
p_errcode,
p_trans,
p_trailer,
sysdate,
p_errdescr,
p_errora,
p_errcom);
END error_log;
BEGIN
error_log(51,0,0,'-','-','TM_A Started');
Insert Into Weight_conf
SELECT Weight.*
FROM Weight, Weight_CONF , transports
WHERE Weight.TRANSPORT_ID = Weight_CONF.TRANSPORT_ID (+)
AND Weight_conf.TRANSPORT_ID IS NULL
and weight.DateTime_W > sysdate - 1
AND transports.transport_id = weight.transport_id
AND nvl(transports.exit_date,to_date('01-01-1970','dd-mm-yyyy')) = to_date('01-01-1970','dd-mm-yyyy');
OPEN C_EMP;
Loop
FETCH C_EMP INTO Cur;
dbms_output.put_line(c_EMP%ROWCOUNT);
EXIT WHEN C_EMP%NOTFOUND;
OPEN transp(cur.transport_id);
FETCH transp INTO lt_trans;
IF transp%NOTFOUND THEN
-- error_log(53, cur.transport_id, 0, 'No rec in Transports for rec in Weight_Conf', '-', 'tr_by_tid');
dbms_output.put_line('test1');
ELSE
dbms_output.put_line('test2');
UPDATE transports SET exit_weight= cur.weight_o, entry_weight= cur.weight_i WHERE CURRENT OF transp;
END IF;
CLOSE transp;
End loop ;
CLOSE C_EMP;
UPDATE weight_conf SET dateTime_conf = sysdate WHERE datetime_conf IS NULL;
OPEN wconftoday;
Loop
FETCH wconftoday INTO cur_wconf;
dbms_output.put_line(wconftoday%ROWCOUNT);
EXIT WHEN wconftoday%NOTFOUND;
OPEN transp_today(cur_wconf.transport_id);
FETCH transp_today INTO lt_transtoday ;
IF transp_today%NOTFOUND THEN
-- error_log(54, wconftoday.transport_id, 0, 'No rec in Transports for rec in Weight_Conf', '-', 'tr_today');
dbms_output.put_line('test1');
ELSE
dbms_output.put_line('test2');
IF lt_transtoday.sent_status_cd NOT IN (0,1,2,3) THEN
UPDATE transports SET sent_status_cd=0, sent_status_date = sysdate WHERE CURRENT OF transp_today;
END IF;
END IF;
CLOSE transp_today;
End loop ;
CLOSE wconftoday;
OPEN tr_tosend;
Loop
FETCH tr_tosend INTO ls_tr_tosend;
dbms_output.put_line(tr_tosend%ROWCOUNT);
EXIT WHEN tr_tosend%NOTFOUND;
OPEN car_by_licensno(ls_tr_tosend.trailer_id);
FETCH car_by_licensno INTO ls_car_by_licensno;
INSERT INTO weight_req (transport_id,trailer_id,weight_empty,weight_full,weight_max,flag_1,flag_2,datetime)
VALUES
(
ls_tr_tosend.transport_id,
ls_tr_tosend.trailer_id,
NVL(ls_car_by_licensno.emptyweight,0),
0,
NVL(ls_car_by_licensno.maxweight,0),
0,
0,
sysdate
);
CLOSE car_by_licensno;
End loop ;
CLOSE tr_tosend;
--EXCEPTION
-- WHEN OTHERS THEN RAISE;
END TMP_HANDLE_TLS_MISHKAL; |
