CREATE OR REPLACE FUNCTION public.get_stock_h3_v2(partid_input character varying, dealer_code character varying)
RETURNS TABLE(ts_id integer, ts_locatorid integer, ts_quantity bigint, ml_code character varying, part_id integer, part_code character varying, part_mdsellingprice double precision, part_het double precision, ahass_id integer, ahass_code character varying, ms_id bigint, ms_code character varying, ms_priority character varying)
LANGUAGE plpgsql
AS $function$
DECLARE
ahass_code varchar;
part_group varchar;
BEGIN
ahass_code = (select a.damdealercode from mst_ahass a where a.code = dealer_code);
part_group = (select partgroup from mst_part mp where code = partid_input);
IF part_group LIKE '%EV%' then
return QUERY
with wo_inv_null as (
select wi.id, wi.woid, wi.invoice
from get_wo_invoice_null(ahass_code) wi
)
select
ts.id as ts_id, ts.locatorid as ts_locatorid,
(ts.quantity - coalesce(pb.tpdb_quantity,0) - coalesce(pp.tpdp_quantity,0))::bigint as ts_quantity,
ml.code as ml_code,
mp.id as part_id, mp.code as part_code, mp.basicprice as part_mdsellingprice, mp.het as part_het,
ma.id as ahass_id, ma.code as ahass_code, ms.id as ms_id, ms.code as ms_code, ms.priority as ms_priority
from trx_stock ts
inner join mst_locator ml on ts.locatorid = ml.id and ml.code != 'RAK.KPB'
inner join mst_subinventory ms on ml.subinventoryid = ms.id
inner join mst_warehouse mw on ms.warehouseid = mw.id
inner join mst_part mp on ts.partid = mp.code
inner join mst_ahass ma on mw.ahass_id = ma.code
left join (
select
tpd.partid as tpdb_partid, sum(tpd.qty) as tpdb_quantity, tpd.locatorid as tpdb_locatorid, ts.ahasscode as tpdb_ahasscode
from trx_salesorder ts
inner join trx_pickslip tph on tph.salesorderid = ts.salesorderid
inner join trx_pickslipdetailev tpd on tpd.pickslipnumber = tph.id
where ts.salesorderlastknownstatus = 'BOOK'
and tph.pickslipstatustype IN ('OPEN', 'PRINT', 'REPRINT')
and ts.ahasscode = dealer_code
and tpd.partid = partid_input
group by ts.ahasscode, tpd.partid, tpd.locatorid
) pb on pb.tpdb_partid = mp.code and pb.tpdb_locatorid = ts.locatorid and pb.tpdb_ahasscode = ma.code
left join (
select tpdp.tpdp_partid, sum(tpdp.tpdp_quantity) as tpdp_quantity, tpdp.tpdp_locatorid, tpdp.tpdp_ahasscode
from (
select
tpd.partid as tpdp_partid, sum(tpd.actualqty) as tpdp_quantity, tpd.locatorid as tpdp_locatorid, ts.ahasscode as tpdp_ahasscode
from trx_salesorder ts
inner join trx_pickslip tph on tph.salesorderid = ts.salesorderid
inner join trx_pickslipdetailev tpd on tpd.pickslipnumber = tph.id
where ts.salesorderlastknownstatus = 'PICKED'
and tph.pickslipstatustype = 'COMPLETED'
and ts.ahasscode = dealer_code
and tpd.partid = partid_input
and ts.nonotapenjualan is null
group by ts.ahasscode, tpd.partid, tpd.locatorid
union all
select
tpd.partid as tpdp_partid, sum(tpd.actualqty) as tpdp_quantity, tpd.locatorid as tpdp_locatorid, ts.ahasscode as tpdp_ahasscode
from trx_salesorder ts
inner join trx_pickslip tph on tph.salesorderid = ts.salesorderid
inner join trx_pickslipdetailev tpd on tpd.pickslipnumber = tph.id
inner join wo_inv_null vw on ts.workorderid = vw.woid
where ts.salesorderlastknownstatus = 'PICKED'
and tph.pickslipstatustype = 'COMPLETED'
and ts.ahasscode = dealer_code
and tpd.partid = partid_input
and vw.invoice is null
group by ts.ahasscode, tpd.partid, tpd.locatorid)tpdp
group by tpdp.tpdp_partid, tpdp.tpdp_locatorid, tpdp.tpdp_ahasscode
) pp on pp.tpdp_partid = mp.code and pp.tpdp_locatorid = ts.locatorid and pp.tpdp_ahasscode = ma.code
where ma.code = dealer_code
and ts.quantity > 0
and mp.code = partid_input
and (ml.nrfs is null or ml.nrfs is false)
order by ms.priority;
else
return QUERY
with wo_inv_null as (
select wi.id, wi.woid, wi.invoice
from get_wo_invoice_null(ahass_code) wi
)
select
ts.id as ts_id, ts.locatorid as ts_locatorid,
(
ts.quantity - coalesce(pb.tpdb_quantity,0) - coalesce(pp.tpdp_quantity,0)
)::bigint as ts_quantity,
ml.code as ml_code,
mp.id as part_id, mp.code as part_code, mp.basicprice as part_mdsellingprice, mp.het as part_het,
ma.id as ahass_id, ma.code as ahass_code, ms.id as ms_id, ms.code as ms_code, ms.priority as ms_priority
from trx_stock ts
inner join mst_locator ml on ts.locatorid = ml.id and ml.code != 'RAK.KPB'
inner join mst_subinventory ms on ml.subinventoryid = ms.id
inner join mst_warehouse mw on ms.warehouseid = mw.id
inner join mst_part mp on ts.partid = mp.code
inner join mst_ahass ma on mw.ahass_id = ma.code
left join (
select
tpd.partid as tpdb_partid, sum(tpd.qty) as tpdb_quantity, tpd.locatorid as tpdb_locatorid, ts.ahasscode as tpdb_ahasscode
from trx_salesorder ts
inner join trx_pickslip tph on tph.salesorderid = ts.salesorderid
inner join trx_pickslipdetail tpd on tpd.pickslipnumber = tph.id
where ts.salesorderlastknownstatus = 'BOOK'
and tph.pickslipstatustype IN ('OPEN', 'PRINT', 'REPRINT')
and ts.ahasscode = dealer_code
and tpd.partid = partid_input
group by ts.ahasscode, tpd.partid, tpd.locatorid
) pb on pb.tpdb_partid = mp.code and pb.tpdb_locatorid = ts.locatorid and pb.tpdb_ahasscode = ma.code
left join (
select tpdp.tpdp_partid, sum(tpdp.tpdp_quantity) as tpdp_quantity, tpdp.tpdp_locatorid, tpdp.tpdp_ahasscode
from (
select
tpd.partid as tpdp_partid, sum(tpd.actualqty) as tpdp_quantity, tpd.locatorid as tpdp_locatorid, ts.ahasscode as tpdp_ahasscode
from trx_salesorder ts
inner join trx_pickslip tph on tph.salesorderid = ts.salesorderid
inner join trx_pickslipdetail tpd on tpd.pickslipnumber = tph.id
where ts.salesorderlastknownstatus = 'PICKED'
and tph.pickslipstatustype = 'COMPLETED'
and ts.ahasscode = dealer_code
and tpd.partid = partid_input
and ts.nonotapenjualan is null
group by ts.ahasscode, tpd.partid, tpd.locatorid
union all
select
tpd.partid as tpdp_partid, sum(tpd.actualqty) as tpdp_quantity, tpd.locatorid as tpdp_locatorid, ts.ahasscode as tpdp_ahasscode
from trx_salesorder ts
inner join trx_pickslip tph on tph.salesorderid = ts.salesorderid
inner join trx_pickslipdetail tpd on tpd.pickslipnumber = tph.id
inner join wo_inv_null vw on ts.workorderid = vw.woid
where ts.salesorderlastknownstatus = 'PICKED'
and tph.pickslipstatustype = 'COMPLETED'
and ts.ahasscode = dealer_code
and tpd.partid = partid_input
and vw.invoice is null
group by ts.ahasscode, tpd.partid, tpd.locatorid)tpdp
group by tpdp.tpdp_partid, tpdp.tpdp_locatorid, tpdp.tpdp_ahasscode
) pp on pp.tpdp_partid = mp.code and pp.tpdp_locatorid = ts.locatorid and pp.tpdp_ahasscode = ma.code
where ma.code = dealer_code
and ts.quantity > 0
and mp.code = partid_input
and (ml.nrfs is null or ml.nrfs is false)
order by ms.priority;
end if;
END;
$function$
;