Index: openacs-4/packages/organizations/organizations.info =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/organizations/organizations.info,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/organizations/organizations.info 14 Mar 2003 15:47:43 -0000 1.1 @@ -0,0 +1,34 @@ + + + + + Organization + Organizations + f + f + + + + oracle + postgresql + + Jon Griffin + Implementation of HR-XML organizations spec. + Mayuli Enterprises LLC + Implementation of HR-XML organizations spec. + + + + + + + + + + + + + + + + Index: openacs-4/packages/organizations/sql/oracle/organizations-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/organizations/sql/oracle/organizations-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/organizations/sql/oracle/organizations-create.sql 14 Mar 2003 15:53:52 -0000 1.1 @@ -0,0 +1,54 @@ +-- @cvs-id $Id: organizations-create.sql,v 1.1 2003/03/14 15:53:52 jong Exp $ + +create table organization_types ( + organization_type_id integer + constraint company_types_pk + primary key, + type varchar2 (40) + constraint company_type_name_uq + unique + constraint company_type_name_nn + not null +); + +comment on table organization_types is ' +This is a lookup table displaying organization types. +'; + +comment on column organization_types.organization_type_id is ' +Primary key. +'; + +comment on column organization_types.type is ' +Pretty name. +'; + +-- add some data +insert into organization_types values (acs_object_id_seq.nextval,'Vendor'); +insert into organization_types values (acs_object_id_seq.nextval,'Customer'); +insert into organization_types values (acs_object_id_seq.nextval,'Prospect'); +insert into organization_types values (acs_object_id_seq.nextval,'Misc.'); + +-- organization +-- this will be a party + +create table organizations ( + organization_id integer + constraint organization_id_pk + primary key + constraint organization_id_fk + references parties(party_id), + name varchar2(200) + constraint organization_name_nn + not null + constraint organization_name_uq + unique, + -- usually the same as name + legal_name varchar2(200), + -- this can be ein/ssn/vat + reg_number varchar2(100), + notes varchar2(4000) +); + + +@ 'organizations-plsql-create.sql' Index: openacs-4/packages/organizations/sql/oracle/organizations-plsql-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/organizations/sql/oracle/organizations-plsql-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/organizations/sql/oracle/organizations-plsql-create.sql 14 Mar 2003 15:53:52 -0000 1.1 @@ -0,0 +1,117 @@ +-- packages/organization/sql/oracle/organization-plsql.sql +-- +-- @author Jon Griffin +-- @creation-date 13 March 2003 +-- @cvs-id $Id: organizations-plsql-create.sql,v 1.1 2003/03/14 15:53:52 jong Exp $ + +begin + acs_object_type.create_type ( + object_type => 'organization', + pretty_name => 'Organization', + pretty_plural => 'Organizations', + supertype => 'party', + table_name => 'organizations', + id_column => 'organization_id' + ); +end; +/ +show errors + +create or replace package organization +as + function new ( + p_organization_id in organizations.organization_id%TYPE default null, + p_legal_name in organizations.legal_name%TYPE, + p_name in organizations.name%TYPE, + p_notes in organizations.notes%TYPE default null, + p_reg_number in organizations.reg_number%TYPE default null, + p_email in parties.email%TYPE default null, + p_url in parties.url%TYPE default null, + p_object_type in acs_objects.object_type%TYPE default 'organization', + p_creation_date in acs_objects.creation_date%TYPE default sysdate, + p_creation_user in acs_objects.creation_user%TYPE default null, + p_creation_ip in acs_objects.creation_ip%TYPE default null, + p_context_id in acs_objects.context_id%TYPE default null + ) return organizations.organization_id%TYPE; + + procedure del ( + p_organization_id in organizations.organization_id%TYPE + ); + +end organization; +/ +show errors + +create or replace package body organization +as + function new ( + p_organization_id in organizations.organization_id%TYPE default null, + p_legal_name in organizations.legal_name%TYPE, + p_name in organizations.name%TYPE, + p_notes in organizations.notes%TYPE default null, + p_reg_number in organizations.reg_number%TYPE default null, + p_email in parties.email%TYPE default null, + p_url in parties.url%TYPE default null, + p_object_type in acs_objects.object_type%TYPE default 'organization', + p_creation_date in acs_objects.creation_date%TYPE default sysdate, + p_creation_user in acs_objects.creation_user%TYPE default null, + p_creation_ip in acs_objects.creation_ip%TYPE default null, + p_context_id in acs_objects.context_id%TYPE default null + ) return organizations.organization_id%TYPE + is + v_organization_id organizations.organization_id%TYPE; + begin + v_organization_id := party.new ( + party_id => null, + object_type => p_object_type, + creation_user => p_creation_user, + creation_ip => p_creation_ip, + email => p_email, + url => p_url, + context_id => p_context_id + ); + + + insert into organizations ( + legal_name, + name, + notes, + organization_id, + reg_number + ) + values ( + p_legal_name, + p_name, + p_notes, + v_organization_id, + p_reg_number + ); + + acs_permission.grant_permission ( + object_id => v_organization_id, + grantee_id => p_creation_user, + privilege => 'admin' + ); + return v_organization_id; +end new; + + -- + -- + procedure del ( + p_organization_id in organizations.organization_id%TYPE + ) + is + begin + delete from acs_permissions + where object_id = organization.del.p_organization_id; + + delete from organizations + where organization_id = organization.del.p_organization_id; + + party.delete(organization.del.p_organization_id); + + end del; + +end organization; +/ +show errors Index: openacs-4/packages/organizations/sql/postgresql/organizations-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/organizations/sql/postgresql/organizations-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/organizations/sql/postgresql/organizations-create.sql 14 Mar 2003 15:55:17 -0000 1.1 @@ -0,0 +1,55 @@ +-- @cvs-id $Id: organizations-create.sql,v 1.1 2003/03/14 15:55:17 jong Exp $ + +create table organization_types ( + organization_type_id serial + constraint company_types_pk + primary key, + type varchar (40) + constraint company_type_name_uq + unique + constraint company_type_name_nn + not null +); + +comment on table organization_types is ' +This is a lookup table displaying organization types. +'; + +comment on column organization_types.organization_type_id is ' +Primary key. +'; + +comment on column organization_types.type is ' +Pretty name. +'; + +-- add some data +insert into organization_types values (acs_object_id_seq.nextval,'Vendor'); +insert into organization_types values (acs_object_id_seq.nextval,'Customer'); +insert into organization_types values (acs_object_id_seq.nextval,'Prospect'); +insert into organization_types values (acs_object_id_seq.nextval,'Misc.'); + +-- organization +-- this will be a party +-- probably should be it's own package + +create table organizations ( + organization_id integer + constraint organization_id_pk + primary key + constraint organization_id_fk + references parties(party_id), + name varchar(200) + constraint organization_name_nn + not null + constraint organization_name_uq + unique, + -- usually the same as name + legal_name varchar(200), + -- this can be ein/ssn/vat + reg_number varchar(100), + notes text +); + +create index organization_name_ix on organizations(name); +\i organizations-plsql-create.sql Index: openacs-4/packages/organizations/sql/postgresql/organizations-plsql-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/organizations/sql/postgresql/organizations-plsql-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/organizations/sql/postgresql/organizations-plsql-create.sql 14 Mar 2003 15:55:17 -0000 1.1 @@ -0,0 +1,138 @@ +-- packages/organization/sql/postgresql/organization-plsql.sql +-- +-- @author Jon Griffin +-- @creation-date 24 February 2003 +-- @cvs-id $Id: organizations-plsql-create.sql,v 1.1 2003/03/14 15:55:17 jong Exp $ + +-- What no comments? + +create function inline_0 () +returns integer as ' +begin + PERFORM acs_object_type__create_type ( + ''organization'', -- object_type + ''Organization'', -- pretty_name + ''Organization'', -- pretty_plural + ''party'', -- supertype + ''organizations'', -- table_name + ''organization_id'', -- id_column + ''organization'', -- package_name + ''f'', -- abstract_p + null, -- type_extension_table + null -- name_method + ); + + return 0; +end;' language 'plpgsql'; + +select inline_0 (); + +drop function inline_0 (); + + + +------ start of oacs new proc +create or replace function organization__new ( varchar,varchar,text,integer,varchar, +varchar,varchar,integer,varchar,integer ) +returns integer as ' +declare + p_legal_name alias for $1; -- comment + p_name alias for $2; -- comment + p_notes alias for $3; -- comment + p_organization_id alias for $4; -- comment + p_reg_number alias for $5; -- comment + p_email alias for $6; -- email + p_url alias for $7; + p_creation_user alias for $8; -- comment + p_creation_ip alias for $9; + p_context_id alias for $10; -- comment + + -- local vars + v_organization_id organizations.organization_id%TYPE; +begin + v_organization_id := party__new ( + null, -- party_id + ''organization'', + now(), + p_creation_user, + p_creation_ip, + p_email, + p_url, + p_context_id + ); + + + insert into organizations ( + legal_name, + name, + notes, + organization_id, + reg_number + ) + values ( + p_legal_name, + p_name, + p_notes, + v_organization_id, + p_reg_number + ); + + PERFORM acs_permission__grant_permission ( + v_organization_id, + p_creation_user, + ''admin'' + ); + + raise NOTICE ''Adding organization - %'',p_name; + return v_organization_id; + +end;' language 'plpgsql'; + +------ end new proc + +create or replace function organization__del (integer) +returns integer as ' +declare + p_organization_id alias for $1; + v_return integer := 0; +begin + + delete from acs_permissions + where object_id = p_organization_id; + + delete from organizations + where organization_id = p_organization_id; + + raise NOTICE ''Deleting organization - %'',p_organization_id; + + PERFORM party_delete(p_organization_id); + + return v_return; + +end;' language 'plpgsql'; + +create or replace function organization__set (varchar,varchar,text,integer,varchar) +returns integer as ' +declare + p_legal_name alias for $1; -- comment + p_name alias for $2; -- comment + p_notes alias for $3; -- comment + p_organization_id alias for $4; -- comment + p_reg_number alias for $5; -- comment + + v_return integer := 0; +begin + + update organizations + set + legal_name = p_legal_name, + name = p_name, + notes = p_notes, + organization_id = p_organization_id, + reg_number = p_reg_number + where organization_id = p_organization_id; + + raise NOTICE ''Updating - organization - %'',organization_id; + +return v_return; +end;' language 'plpgsql'; \ No newline at end of file