Annotation: public.add_office

Information: public.add_office

Implementation: public.add_office

CREATE OR REPLACE FUNCTION public.add_office(_office_code character varying, _office_name character varying, _nick_name character varying, _registration_date date, _currency_code character varying, _currency_symbol character varying, _currency_name character varying, _hundredth_name character varying, _admin_name character varying, _user_name character varying, _password character varying)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
    DECLARE _office_id      integer;
    DECLARE _user_id		integer;
BEGIN
    IF NOT EXISTS
    (
        SELECT 0 
        FROM core.currencies
        WHERE currency_code=_currency_code
    ) THEN
        INSERT INTO core.currencies(currency_code, currency_symbol, currency_name, hundredth_name)
        SELECT _currency_code, _currency_symbol, _currency_name, _hundredth_name;
    END IF;


    INSERT INTO office.offices(office_code, office_name, nick_name, registration_date, currency_code)
    SELECT _office_code, _office_name, _nick_name, _registration_date, _currency_code
    RETURNING office_id INTO _office_id;

    INSERT INTO office.users(role_id, office_id, user_name, password, full_name)
    SELECT office.get_role_id_by_role_code('SYST'), _office_id, 'sys', '', 'System';
    
    INSERT INTO office.users(role_id,office_id,user_name,password, full_name, elevated)
    SELECT office.get_role_id_by_role_code('ADMN'), _office_id, _user_name, _password, _admin_name, true
    RETURNING user_id INTO _user_id;

    INSERT INTO policy.menu_access(office_id, menu_id, user_id)
    SELECT _office_id, core.menus.menu_id, _user_id
    FROM core.menus;

    RETURN;
END;
$function$