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.
select
w.fullname,
wt.descr type,
pr.name reason,
count( 1 ) locations,
sum(od.packs) packs,
round(sum(od.packs)/count( 1 ), 2 ) packs_in_cells,
to_char(trunc(Sysdate) + sum(od.work_time)/( 24 * 60 * 60 ), 'HH24:MI:SS') sum_time,
to_char(trunc(Sysdate) + (sum(od.work_time)/count( 1 ))/( 24 * 60 * 60 ), 'HH24:MI:SS') time_one_loc,
to_char(trunc(Sysdate) + (
case
when sum(od.packs)= 0
then 0
else (sum(od.work_time)/sum(od.packs))
end
)/( 24 * 60 * 60 ), 'HH24:MI:SS') time_one_pack
from
wms.oldpick_detail od, wms.oldpick ol, wms.pick_reason pr, wms.worker w, wms.work_type wt
where od.deleted > to_date('04.12.2008 09:00:00', 'DD.MM.YYYY HH24:MI:SS')
and od.deleted < to_date('04.12.2008 21:00:00', 'DD.MM.YYYY HH24:MI:SS')
and od.worker_id > 0
-- and ol.reason = 'order_picking'
and od.complete = 't'
and w.id=od.worker_id
and wt.name=od.opcode
and not (
od.opcode = 'ABSENCE'
or (ol.reason = 'order_picking'
and od.opcode = 'PICK')
)
-- and od.opcode in ('FROM_CNT', 'FROM_PAL')
and od.pick_id=ol.id
and pr.id=ol.reason
group by (w.fullname, wt.descr, pr.name)
union
select
w.fullname,
'Перемещение отобранной паллеты в ЕТ%' type,
pr.name reason,
count( 1 ) locations,
sum(od.packs) packs,
round(sum(od.packs)/count( 1 ), 2 ) packs_in_cells,
to_char(trunc(Sysdate) + sum(od.work_time)/( 24 * 60 * 60 ), 'HH24:MI:SS') sum_time,
to_char(trunc(Sysdate) + (sum(od.work_time)/count( 1 ))/( 24 * 60 * 60 ), 'HH24:MI:SS') time_one_loc,
to_char(trunc(Sysdate) + (
case
when sum(od.packs)= 0
then 0
else (sum(od.work_time)/sum(od.packs))
end
)/( 24 * 60 * 60 ), 'HH24:MI:SS') time_one_pack
from
wms_archive.oldpick_detail od, wms_archive.oldpick ol, wms.pick_reason pr, wms.worker w, wms.work_type wt
where od.deleted > to_date('04.12.2008 09:00:00', 'DD.MM.YYYY HH24:MI:SS')
and od.deleted < to_date('04.12.2008 21:00:00', 'DD.MM.YYYY HH24:MI:SS')
and od.worker_id > 0
and ol.reason = 'order_picking'
and od.opcode = 'PICK'
and od.complete = 't'
and od.order_id = 0
and w.id = od.worker_id
and wt.name = od.opcode
and od.pick_id = ol.id
and pr.id = ol.reason
group by (w.fullname, wt.descr, pr.name)
union
select
w.fullname,
'Перемещение монопаллеты в Е%' type,
pr.name reason,
count( 1 ) locations,
sum(od.packs) packs,
round(sum(od.packs)/count( 1 ), 2 ) packs_in_cells,
to_char(trunc(Sysdate) + sum(od.work_time)/( 24 * 60 * 60 ), 'HH24:MI:SS') sum_time,
to_char(trunc(Sysdate) + (sum(od.work_time)/count( 1 ))/( 24 * 60 * 60 ), 'HH24:MI:SS') time_one_loc,
to_char(trunc(Sysdate) + (
case
when sum(od.packs)= 0
then 0
else (sum(od.work_time)/sum(od.packs))
end
)/( 24 * 60 * 60 ), 'HH24:MI:SS') time_one_pack
from
wms_archive.oldpick_detail od, wms_archive.oldpick ol, wms.pick_reason pr, wms.worker w, wms.work_type wt
where od.deleted > to_date('04.12.2008 09:00:00', 'DD.MM.YYYY HH24:MI:SS')
and od.deleted < to_date('04.12.2008 21:00:00', 'DD.MM.YYYY HH24:MI:SS')
and od.worker_id > 0
and ol.reason = 'order_picking'
and od.opcode = 'PICK'
and od.complete = 't'
and od.order_id > 0
and w.id = od.worker_id
and wt.name = od.opcode
and od.pick_id = ol.id
and pr.id = ol.reason
group by (w.fullname, wt.descr, pr.name)
union
select
w.fullname,
'Перемещение S->S%' type,
pr.name reason,
count( 1 ) locations,
sum(od.packs) packs,
round(sum(od.packs)/count( 1 ), 2 ) packs_in_cells,
-- lead (od.start_date,1, null) over (order by od.pick_id) d,
case when lead (od.pick_id, 1 ) over (order by od.pick_id) = od.pick_id then
to_char(trunc(Sysdate) + (((lead (od.start_date, 1 ) over (order by od.pick_id, od.start_date))-od.finish_date)), 'HH24:MI:SS')
else
to_char(trunc(Sysdate) + 0 , 'HH24:MI:SS')
end sum_time,
to_char(trunc(Sysdate) + (sum(od.work_time)/count( 1 ))/( 24 * 60 * 60 ), 'HH24:MI:SS') time_one_loc,
to_char(trunc(Sysdate) + (
case
when sum(od.packs)= 0
then 0
else (sum(od.work_time)/sum(od.packs))
end
)/( 24 * 60 * 60 ), 'HH24:MI:SS') time_one_pack
from
wms_archive.oldpick_detail od, wms_archive.oldpick ol, wms.pick_reason pr, wms.worker w, wms.work_type wt
where od.deleted > to_date('04.12.2008 09:00:00', 'DD.MM.YYYY HH24:MI:SS')
and od.deleted < to_date('04.12.2008 21:00:00', 'DD.MM.YYYY HH24:MI:SS')
and od.worker_id > 0
and ol.reason = 'order_picking'
-- and od.opcode = 'PICK'
and od.complete = 't'
and (wt.name = 'FROM_CNT' or wt.name = 'FROM_PAL')
and od.order_id > 0
and w.id = od.worker_id
and wt.name = od.opcode
and od.pick_id = ol.id
and pr.id = ol.reason
group by w.fullname, wt.descr, pr.name, od.pick_id, od.start_date, od.finish_date
order by fullname
;
