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
| with cte_union (cost_center, eqpts, tot_PREV, tot_cur, cout_pieces) as
(
select ws.cd_cost_center cost_center
, substr(ws.cd_box, 1, 4) eqpts
, substr(sum(case when ws.cd_action_type in ('PREV', 'PREV_N_PRO', 'INTERV_PRO') then OCCUPATION.pr_hour_cost * OCCUPATION.tm_occupation else 0 end) / 100000, 1, 6) tot_PREV
, substr(sum(case when ws.cd_action_type = 'CUR' then OCCUPATION.pr_hour_cost * OCCUPATION.tm_occupation else 0 end) / 100000, 1, 6) tot_Cur
, null cout_pieces
from Ws
join OCCUPATION on OCCUPATION.cd_wo = ws.cd_wo
and OCCUPATION.cd_ws = ws.cd_ws
where ws.dt_ws_begin >= to_date('01022015 00:00:01','DDMMYYYY HH24:MI:SS')
and ws.dt_ws_begin <= to_date('28022015 23:59:59','DDMMYYYY HH24:MI:SS')
and ws.cd_action_type in ('CUR', 'PREV', 'PREV_N_PRO', 'INTERV_PRO')
group by ws.cd_cost_center, substr(ws.cd_box, 1, 4)
union all
select stock_mouvemt.cd_cost_center cost_center, substr(ws.cd_box,1, 4) eqpts,null tot_prev, null tot_cur, substr(abs(sum(stock_mouvemt.pr_externe)),1, 8) cout_pieces
from ws
join stock_mouvemt on ws.cd_wo = stock_mouvemt.cd_wo
and ws.cd_ws = stock_mouvemt.cd_ws
join item on item.cd_item = stock_mouvemt.cd_item
where stock_mouvemt.dttm_stock_mvt >= to_date('01022015 00:00:01','DDMMYYYY HH24:MI:SS')
and stock_mouvemt.dttm_stock_mvt <= to_date('28022015 23:59:59','DDMMYYYY HH24:MI:SS')
and stock_mouvemt.cd_mvt_type like'S%'
group by stock_mouvemt.cd_cost_center, substr(ws.cd_box, 1, 4)
)
select max(cost_center) as cost_center, max (eqpts) as eqpts, max(tot_PREV) as tot_PREV, max(tot_Cur) as tot_Cur, max(cout_pieces) as cout_pieces from cte_union
group by cost_center, eqpts
order by cost_center, eqpts |
Partager