Solicitud de Compra de Materia Prima

-- drop type solicitud_compra_materia;

create type solicitud_compra_materia as
(
id_producto numeric(10,0),
cod_producto varchar,
producto varchar,
unidad_medida varchar,
consumo numeric(10,2),
consumo_promedio numeric(10,2),
existencia numeric(10,2),
existencia_meses numeric(10,2),
cantidad_sugerida numeric(10,2)
);


-- drop function rep_solicitud_compra_materia(finicial date, ffinal date);

create or replace function rep_solicitud_compra_materia(finicial date, ffinal date)
returns setof solicitud_compra_materia as $$
declare
resultado solicitud_compra_materia%rowtype;

cantidad_meses numeric(10,2);
begin

cantidad_meses = (cast(ffinal - finicial as numeric(10,2)) + 1) / 30;

RAISE NOTICE 'Cantidad de meses = % ', cantidad_meses;

-- EXISTENCIAS DE LOS PRODUCTOS

drop table if exists tmp_existencia_total;

create temporary table tmp_existencia_total as
select
   s.m_product_id as id_producto,
   sum(s.qtyonhand) as existencia_total
from m_storage s
join m_locator l
on s.m_locator_id = l.m_locator_id
join m_warehouse w
on l.m_warehouse_id = w.m_warehouse_id
where s.ad_client_id = 1000001
and w.m_warehouse_id = 1000001 -- Bodega Materia Prima
group by
   s.m_product_id;

-- CONSUMO DE LOS PRODUCTOS (SALIDAS, VENTAS Y TRASLADOS)

drop table if exists tmp_consumo_total;

create temporary table tmp_consumo_total as
select
   t.m_product_id as id_producto,
   abs(sum(movementqty)) as consumo_total
from m_transaction t
join m_locator l
on t.m_locator_id = l.m_locator_id
join m_warehouse w
on l.m_warehouse_id = w.m_warehouse_id
where t.movementtype in ('I-', 'C-', 'M-')
and t.ad_client_id = 1000001
and w.m_warehouse_id = 1000001 -- Bodega Materia Prima
and t.movementdate between finicial and ffinal
group by
   t.m_product_id;

-- CALCULO DEL CONSUMO PROMEDIO Y LA CANTIDAD SUGERIDA

drop table if exists tmp_solicitud_compra_materia;

create temporary table tmp_solicitud_compra_materia as
select
   p.m_product_id as id_producto,
   p.value as cod_producto,
   p.name as producto,
   u.name as unidad_medida,
   cast(ct.consumo_total as numeric(10,2)) as consumo,
   cast((ct.consumo_total / cantidad_meses) as numeric(10,2)) as consumo_promedio,
   cast(et.existencia_total as numeric(10,2)) as existencia,
   cast(case
when round(ct.consumo_total, 0) = 0 then 0 else et.existencia_total / round(ct.consumo_total, 0)
   end as numeric(10,2)) as existencia_meses,
   case
when round(ct.consumo_total, 0) = 0 then 0
   else
case
when et.existencia_total / round(ct.consumo_total, 0) > 1.5 then 0
else
(1.5 * round(ct.consumo_total, 0)) - et.existencia_total
end
   end as cantidad_sugerida
from m_product p
   join c_uom u
   on p.c_uom_id = u.c_uom_id
   left outer join tmp_existencia_total et
   on p.m_product_id = et.id_producto
   left outer join tmp_consumo_total ct
   on p.m_product_id = ct.id_producto
where p.ad_client_id = 1000001;

-- MOSTRAR RESULTADO

for resultado in execute
'select
t.id_producto,
t.cod_producto,
t.producto,
t.unidad_medida,
coalesce(t.consumo, 0) as consumo,
coalesce(t.consumo_promedio, 0) as consumo_promedio,
coalesce(t.existencia, 0) as existencia,
coalesce(t.existencia_meses, 0) as existencia_meses,
coalesce(t.cantidad_sugerida, 0) as cantidad_sugerida
from tmp_solicitud_compra_materia t
where t.existencia is not null
and t.existencia <> 0
order by
coalesce(t.cantidad_sugerida, 0) desc,
t.id_producto;'
loop
return next resultado;
end loop;

drop table if exists tmp_existencia_total;

drop table if exists tmp_consumo_total;

drop table if exists tmp_solicitud_compra_materia;
end;
$$ language plpgsql;