--
-- acs_reference__new/5
--
create or replace function acs_reference__new(
  p_table_name character varying,
  p_last_update timestamp with time zone,
  p_source character varying,
  p_source_url character varying,
  p_effective_date timestamp with time zone
) returns int4 as $$

DECLARE
    v_repository_id acs_reference_repositories.repository_id%TYPE;
BEGIN
    return acs_reference__new(null, p_table_name, 'f', null, null, p_source, p_source_url,
                              p_effective_date, null, null, null, null, null, null,
                              'acs_reference_repository', null);
END;

$$ language plpgsql;


--
-- acs_reference__new/16
--
create or replace function acs_reference__new(
  integer,
  character varying,
  boolean,
  character varying,
  timestamp with time zone,
  character varying,
  character varying,
  timestamp with time zone,
  timestamp with time zone,
  integer,
  integer,
  character varying,
  character varying,
  integer,
  character varying,
  integer
) returns int4 as $$

declare
    p_repository_id   alias for $1; -- default null
    p_table_name      alias for $2; -- 
    p_internal_data_p alias for $3; -- default "f"
    p_package_name    alias for $4; -- default null
    p_last_update     alias for $5; -- default sysdate
    p_source          alias for $6; -- default null
    p_source_url      alias for $7; -- default null
    p_effective_date  alias for $8; -- default sysdate
    p_expiry_date     alias for $9; -- default null
    p_maintainer_id   alias for $10; -- default null
    p_notes           alias for $11; -- default null (not Oracle empty_blob())
-- I really see no need for these as parameters
--    creation_date   alias for $12; -- default sysdate
    p_first_names     alias for $12; -- default null
    p_last_name      alias for $13; -- default null
    p_creation_ip     alias for $14; -- default null
    p_object_type     alias for $15; -- default "acs_reference_repository"
    p_creation_user   alias for $16; -- default null
    v_repository_id acs_reference_repositories.repository_id%TYPE;
    v_object_type   acs_objects.object_type%TYPE;
    v_maintainer_id persons.person_id%TYPE;
begin
    if p_object_type is null then
        v_object_type := 'acs_reference_repository';
    else
        v_object_type := p_object_type;
    end if;

    v_repository_id := acs_object__new (
         p_repository_id,    
         v_object_type,
         now(),
         p_creation_user,
         p_creation_ip,
         null
    );

    -- This logic is not correct as the maintainer could already exist
    -- The way around this is a little clunky as you can search persons
    -- then pick an existing person or add a new one, to many screens!
    -- I really doubt the need for person anyway.
    --
    -- It probably needs to just be a UI function and pass
    -- in the value for maintainer.
    --
    -- IN OTHER WORDS
    -- Guaranteed to probably break in the future if you depend on
    -- first_names and last_name to still exist as a param
    -- This needs to be updated in the Oracle version also
    -- NEEDS TO BE FIXED - jag

    if p_first_names is not null and p_last_name is not null and p_maintainer_id is null then
        v_maintainer_id := person__new (null, 'person', now(), null, null, null, null,
	                                p_first_names, p_last_name, null);
	else if p_maintainer_id is not null then
           v_maintainer_id := p_maintainer_id;
        else 
	    v_maintainer_id := null;
	end if;
    end if;

    insert into acs_reference_repositories
        (repository_id,table_name,internal_data_p,
         last_update,package_name,source, 
         source_url,effective_date,expiry_date,
         maintainer_id,lob)
    values 
        (v_repository_id, p_table_name, p_internal_data_p,
         p_last_update, p_package_name, p_source, p_source_url,
         p_effective_date, p_expiry_date, v_maintainer_id, p_notes);

    return v_repository_id;    
end;
$$ language plpgsql;


--
-- acs_reference__new/16
--
create or replace function acs_reference__new(
  p_repository_id integer,
  p_table_name character varying,
  p_internal_data_p boolean,
  p_package_name character varying,
  p_last_update timestamp with time zone,
  p_source character varying,
  p_source_url character varying,
  p_effective_date timestamp with time zone,
  p_expiry_date timestamp with time zone,
  p_maintainer_id integer,
  p_notes integer,
  p_first_names character varying,
  p_last_name character varying,
  p_creation_ip character varying,
  p_object_type character varying,
  p_creation_user integer
) returns int4 as $$

DECLARE
    v_repository_id acs_reference_repositories.repository_id%TYPE;
    v_object_type   acs_objects.object_type%TYPE;
    v_maintainer_id persons.person_id%TYPE;
BEGIN
    if p_object_type is null then
        v_object_type := 'acs_reference_repository';
    else
        v_object_type := p_object_type;
    end if;

    v_repository_id := acs_object__new (
         p_repository_id,    
         v_object_type,
         now(),
         p_creation_user,
         p_creation_ip,
         null,
         't',
         p_source,
         null
    );

    -- This logic is not correct as the maintainer could already exist
    -- The way around this is a little clunky as you can search persons
    -- then pick an existing person or add a new one, to many screens!
    -- I really doubt the need for person anyway.
    --
    -- It probably needs to just be a UI function and pass
    -- in the value for maintainer.
    --
    -- IN OTHER WORDS
    -- Guaranteed to probably break in the future if you depend on
    -- first_names and last_name to still exist as a param
    -- This needs to be updated in the Oracle version also
    -- NEEDS TO BE FIXED - jag

    if p_first_names is not null and p_last_name is not null and p_maintainer_id is null then
        v_maintainer_id := person__new (null, 'person', now(), null, null, null, null,
                                    p_first_names, p_last_name, null);
    else if p_maintainer_id is not null then
           v_maintainer_id := p_maintainer_id;
        else 
        v_maintainer_id := null;
    end if;
    end if;

    insert into acs_reference_repositories
        (repository_id,table_name,internal_data_p,
         last_update,package_name,source, 
         source_url,effective_date,expiry_date,
         maintainer_id,lob)
    values 
        (v_repository_id, p_table_name, p_internal_data_p,
         p_last_update, p_package_name, p_source, p_source_url,
         p_effective_date, p_expiry_date, v_maintainer_id, p_notes);

    return v_repository_id;    
END;

$$ language plpgsql;