Untitled

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$
;