Untitled

mail@pastecode.io avatar
unknown
pgsql
2 years ago
1.8 kB
8
Indexable
Never
SELECT 
                        pp.id as product_id,
                        sum(case 
                                when src_loc.usage in ('transit', 'internal') and dest_loc.usage not in ('internal', 'view', 'transit') then 
                                    move.qty_done 
                                else 
                                    0 
                            end) -
                        sum(case 
                                when src_loc.usage not in ('internal', 'view', 'transit') and dest_loc.usage in ('transit', 'internal') then 
                                    move.qty_done 
                                else 
                                0 
                            end) 
                    as qty
                    FROM stock_move_line move
                        join product_product pp on pp.id = product_id
                        join stock_location src_loc on src_loc.id = move.location_id
                        join stock_location dest_loc on dest_loc.id = move.location_dest_id
                    WHERE
                        ((move.location_id in (select id from stock_location where usage not in ('internal', 'view', 'transit')) or
                             move.location_id in (select id from stock_location where usage in ('transit', 'internal'))) or
                         (move.location_dest_id in (select id from stock_location where usage not in  ('internal', 'view', 'transit')) or
                             move.location_id in (select id from stock_location where usage in ('transit', 'internal')))) and
                        move.state = 'done' and 
                        date <= (trans_date::date - interval '9 hours')
                    GROUP BY
                        pp.id,
                        pp.default_code
                    ),