DROP PROCEDURE IF EXISTS rds.sp_updateremains;

CREATE OR REPLACE PROCEDURE sp_updateremains(
    pharmacy int
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_pharmacy int := pharmacy;
BEGIN
    
    UPDATE rds.t_remains r
    SET billofgoods = b.billofgoodsid
    FROM rds.t_pharmacy p
    JOIN pharm.t_bill_of_goods b ON b.otherid = r.drugcode
    WHERE p.code = r.pharmacy
      AND p.usepharmacyproduct = FALSE
      AND r.billofgoods IS NULL;
    
    UPDATE rds.t_remains r
    SET druglinkbillofgoods = dr.DrugLinkBillOfGoodsID,
        billofgoods         = dr.billofgoods
    FROM rds.t_pharmacy p
    JOIN rds.t_drug_link_bill_of_goods dr ON dr.code
    WHERE p.code = r.pharmacy
      AND p.usepharmacyproduct = TRUE
      AND (r.billofgoods IS NULL OR r.druglinkbillofgoods IS NULL)
      AND dr.pharmacy = r.pharmacy;

END;
$$