add_item_sp

-- PROCEDURE: public.add_item_sp(integer, integer, integer, integer, character varying, character varying, integer, character varying, double precision, double precision, character varying, character varying, character varying, character varying)

-- DROP PROCEDURE public.add_item_sp(integer, integer, integer, integer, character varying, character varying, integer, character varying, double precision, double precision, character varying, character varying, character varying, character varying);

CREATE OR REPLACE PROCEDURE public.add_item_sp(
    p_mode integer,
    p_orderid integer,
    p_id integer,
    p_vendorid integer,
    p_login character varying,
    p_name character varying,
    p_qty integer,
    p_units character varying,
    p_total double precision,
    p_tax double precision,
    p_currency character varying,
    p_account character varying,
    p_vendor character varying,
    INOUT p_output character varying)
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
    DECLARE
        l_TID          INTEGER = NULL;
        l_ItemName     varchar(250);
        l_Value        varchar(200);
        l_VendorID     INTEGER;
        l_Vendor       varchar(100);
        l_now          TIMESTAMP  WITHOUT TIME ZONE;
BEGIN
    SET l_now = now;
    --
    --    Check Vendor exists
    --
    CALL public.CHECK_Vendor_sp (1, p_VendorID, p_Vendor, l_VendorID );

    DECLARE
        EXISTS BIT = NULL;
BEGIN
SELECT EXISTS (SELECT 1 FROM public.Orders_tb WHERE TID=p_OrderID);

IF EXISTS = 1 THEN
        IF p_ID > 0 THEN
         DECLARE
            l_old_VendorID INT = NULL;
            l_old_exists BIT = NULL;
         BEGIN
            SELECT l_old_VendorID=VendorID FROM public.Items_tb WHERE TID=p_ID;
            UPDATE public.Items_tb SET
                OrderID=p_OrderID,
                VendorID=l_VendorID,
                LOGIN=p_Login,
                NAME=p_Name,
                Qty=p_Qty,
                Units=p_Units,
                Total=cast(p_Total AS NUMERIC),
                Tax=cast(p_Tax AS NUMERIC),
                Currency=p_Currency,
                Account=p_Account,
                RD=l_now
            WHERE TID=p_ID;
            --
            -- Check & Remove DIC Vendor item if not exists
            --
               IF l_old_VendorID > 0 AND l_VendorID != l_old_VendorID THEN
                 SELECT l_old_exists=1 FROM public.Items_tb WHERE VendorID=l_old_VendorID;
                    IF l_old_exists != 1 THEN
                    DELETE FROM public.DIC_Vendors_tb WHERE TID=@l_old_VendorID;
                    END IF;
               l_TID = p_ID;
               p_Output = 'Updated';
               ELSE
               INSERT INTO public.Items_tb(
                OrderID,
                VendorID,
                LOGIN,
                NAME,
                Qty,
                Units,
                Total,
                Tax,
                Currency,
                Account,
                RD)
                VALUES (
                p_OrderID,
                l_VendorID,
                p_Login,
                p_Name,
                p_Qty,
                p_Units,
                cast(@p_Total AS NUMERIC),
                cast(@p_Tax AS NUMERIC),
                p_Currency,
                p_Account,
                l_now);
               SELECT l_TID = CAST(scope_identity() AS INTEGER);
               p_Output = 'Registered';
               END IF;
        --
        -- Get Item name and value
        --
        SELECT l_ItemName = NAME, l_Value =
            cast(Qty AS VARCHAR) || ':' ||
            Units || ':' ||
            cast(Total AS VARCHAR) || ':' ||
            cast(Tax AS VARCHAR) || ':' ||
            cast(Currency AS VARCHAR) || ':' ||
            cast(Account AS VARCHAR) || ':' ||
            cast(Vendor AS VARCHAR)
        FROM public.WEB_OrderItems_vw WHERE TID=@l_TID LIMIT 1;
        --
        -- Add to Order Changes log
        --
        INSERT INTO public.OrderChanges_tb (OrderID, LOGIN, NAME, VALUE) VALUES
            (p_OrderID, p_Login, 'Счет:' || l_ItemName, l_Value);
            END;
ELSE
        l_TID = 0;
        p_Output = 'Invalid';
END IF;

     p_Output = @p_Output || ':' || cast(l_TID AS VARCHAR) || ':' || cast(l_VendorID AS VARCHAR);

  IF p_Mode = 0 THEN
     SELECT l_TID, p_Output FROM
     public.sysobjects WHERE id = object_id(N'public.ADD_Item_sp')
     AND OBJECTPROPERTY(id, N'IsProcedure') = 1;
  ELSE
     RETURN;
  END if;
  end if;
  end;
  end;
END;
$BODY$;