Untitled
unknown
plain_text
a month ago
7.5 kB
1
Indexable
Never
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$ ;