Author: Martin Donovan | GitHub gist | profile.json
drop function if exists get_theoreticals_active_product_instances_only(date);
CREATE OR REPLACE FUNCTION public.get_theoreticals_active_product_instances_only(IN end_date DATE)
RETURNS TABLE(product_instance_id INT, product_instance_location_id INT,
beginning_inventory NUMERIC, bottle_sales NUMERIC, glass_sales NUMERIC,
transfers_from NUMERIC, transfers_to NUMERIC,
purchases NUMERIC, theoretical_on_hand NUMERIC)
AS
$BODY$
BEGIN
RETURN QUERY
with
last_inv as (
select max(inventory_date) as dt
from inventories
where inventory_date <= end_date
),
pre_data as
(
select
pila.product_instance_location_id as pilid,
pila.product_instance_id as piid,
0 as initial, 0 as beg_inv, 0 as btl_sales, 0 as gl_sales, 0 as purch, 0 as trans_from, 0 as trans_to
from product_instance_location_associations pila
join product_instances pi on pi.product_instance_id = pila.product_instance_id
where pi.active = true
union
select
invd.product_instance_location_id as pilid,
pila.product_instance_id as piid,
0 as initial,
invd.quantity_counted as beg_inv, 0 as btl_sales, 0 as gl_sales, 0 as purch, 0 as trans_from, 0 as trans_to
from inventory_details invd
join inventories inv on inv.inventory_id = invd.inventory_id
join product_instance_location_associations pila on pila.product_instance_location_id = invd.product_instance_location_id
join product_instances pi on pi.product_instance_id = pila.product_instance_id
cross join last_inv
where pi.active = true
and inv.inventory_date = last_inv.dt
union
select
pc.product_instance_location_id as pilid,
pila.product_instance_id as piid,
0 as initial, 0 as beg_inv, 0 as btl_sales,
round(sum(
(vu.equivalent_ml * pc.sales_units_sold * pc.num_sold) / bs.bottle_size * vu_bottle.equivalent_ml
), 2) as gl_sales,
0 as purch, 0 as trans_from, 0 as trans_to
from pos_chkitems pc
join volume_units vu on vu.volume_unit_id = pc.sales_volume_unit_id
join product_instance_location_associations pila on pila.product_instance_location_id = pc.product_instance_location_id
join product_instances pi on pi.product_instance_id = pila.product_instance_id
join bottle_sizes bs on bs.bottle_size_id = pi.bottle_size_id
join volume_units vu_bottle on vu_bottle.volume_unit_id = bs.bottle_size_unit
cross join last_inv
where pc.is_inventory_unit = 'false'
and pc.deletion = 0
and pc.date_of_sale > last_inv.dt
and pc.date_of_sale <= end_date
group by pilid, piid
union
select
pc.product_instance_location_id as pilid,
pila.product_instance_id as piid,
0 as initial, 0 as beg_inv,
sum(pc.num_sold) as btl_sales,
0 as gl_sales, 0 as purch, 0 as trans_from, 0 as trans_to
from pos_chkitems pc
join product_instance_location_associations pila on pila.product_instance_location_id = pc.product_instance_location_id
cross join last_inv
where pc.is_inventory_unit = 'true'
and pc.deletion = 0
and pc.date_of_sale > last_inv.dt
and pc.date_of_sale <= end_date
group by pilid, piid
union
select
invdet.product_instance_location_id as pilid,
pila.product_instance_id as piid,
0 as initial,
0 as beg_inv, 0 as btl_sales, 0 as gl_sales,
cast(sum(invdet.number_received) as numeric) as purch,
0 as trans_from, 0 as trans_to
from invoices inv
join invoice_details invdet on invdet.invoice_id = inv.invoice_id
join product_instance_location_associations pila on pila.product_instance_location_id = invdet.product_instance_location_id
join product_instances pi on pi.product_instance_id = pila.product_instance_id
cross join last_inv
where pi.active = true
and inv.payment_date > last_inv.dt
and inv.payment_date <= end_date
group by pilid, piid
union
select
plt.from_location as pilid,
pila.product_instance_id as piid,
0 as initial,
0 as beg_inv, 0 as btl_sales, 0 as gl_sales, 0 as purch,
sum(plt.quantity) as trans_from,
0 as trans_to
from product_instance_location_transfers plt
join product_instance_location_associations pila on pila.product_instance_location_id = plt.from_location
join product_instances pi on pi.product_instance_id = pila.product_instance_id
cross join last_inv
where pi.active = true
and plt.transfer_date > last_inv.dt
and plt.transfer_date <= end_date
group by pilid, piid
union
select
plt.to_location as pilid,
pila.product_instance_id as piid,
0 as initial,
0 as beg_inv, 0 as btl_sales, 0 as gl_sales, 0 as purch,
0 as trans_from,
sum(plt.quantity) as trans_to
from product_instance_location_transfers plt
join product_instance_location_associations pila on pila.product_instance_location_id = plt.to_location
join product_instances pi on pi.product_instance_id = pila.product_instance_id
cross join last_inv
where pi.active = true
and plt.transfer_date > last_inv.dt
and plt.transfer_date <= end_date
group by pilid, piid
)
select
piid as product_instance_id,
pilid as product_instance_location_id,
sum(beg_inv) as beginning_inventory,
sum(btl_sales) as bottle_sales,
sum(gl_sales) as glass_sales,
sum(trans_from) as transfers_from,
sum(trans_to) as transfers_to,
sum(purch) as purchases,
round(
sum(initial) + sum(beg_inv) - sum(gl_sales)
- sum(btl_sales) + sum(purch)
- sum(trans_from) + sum(trans_to),
2
) as theoretical_on_hand
from pre_data
group by pilid, product_instance_id
order by product_instance_id, pilid;
END;
$BODY$
LANGUAGE plpgsql VOLATILE