FI: Extensions Cost Accounting - Accounts, Management of Overhead Costs, Cash Flow Report

Reasoning & Objectives:

• Phase 1: Improve management of "Overhead Costs"
• Eliminate the need to set up "Dummy Projects" to keep track of overhead costs
• Allow management of "recurring/planned overhead costs" in order to provide better control over overhead costs
  
• Provide support for "Cash Flow Planning"
• Provide support for other account based features such as "vacation/overtime" that require balance calculation & transactions
• Phase 2: 
  
• Provide export/import interfaces to 3rd party accounting software of captured cost items  
  

Scope:

• The general scope is limited to measures that provide support for "Managerial Accounting"  

                                  Figure: The structure of accounting as documented by the International Federation of Accountants
                                                                Source: Professional Accountants in Business Committee / Wikipedia

New Report: Cash Flow Planning (First Draft)

Implementation 

-- /packages/intranet-cost-calculation/sql/postgresql/intranet-cost-calculation-create.sql
--
-- Copyright (c) 2011 ]project-open[
--
-- All rights reserved. Please check
-- http://www.project-open.com/license/ for details.
--
-- @author klaus.hofeditz@project-open.com

SELECT acs_object_type__create_type (
        'im_account',                   -- object_type
        'Account',                      -- pretty_name
        'Accounts',                     -- pretty_plural
        'acs_object',                      -- supertype
        'im_accounts',                  -- table_name
        'account_id',                   -- id_column
        'intranet-cost-calculation',    -- package_name
        'f',                            -- abstract_p
        null,                           -- type_extension_table
        'im_account__name'              -- name_method
);


insert into acs_object_type_tables (object_type,table_name,id_column)
values ('im_account', 'im_accounts', 'account_id');

insert into im_biz_object_urls (object_type, url_type, url) values (
'im_account','view','/intranet-cost-calculation/new?display_mode=display&account_id=');


---------------------------------------------------------
-- Main tables
---------------------------------------------------------

create sequence im_account_seq start 1;

create table im_accounts (
        account_id     		integer
                                primary key,
	account_type_id		integer,
        name		        varchar(400),
        commment	        varchar(400),
	unit_type_id		integer,		
        initial_vale	        float,
	parent_id		integer
);

create sequence im_transaction_seq start 1;

create table im_transactions (
        transaction_id 		integer
                                primary key,
	credit_account_id	integer,
	debit_account_id	integer,
        transaction_type        integer
                                not null,
        transaction_date        date
                                not null,
        commment	        varchar(400),
        amount		        float
);

SELECT im_category_new(77000,'Simple Debit Transaction','Intranet Transaction Type');
SELECT im_category_new(77010,'Simple Credit Transaction','Intranet Transaction Type');
SELECT im_category_new(77020,'Expected Income','Intranet Transaction Type');
SELECT im_category_new(77030,'Expected Expenditure','Intranet Transaction Type');

---------------------------------------------------------
-- Components
---------------------------------------------------------

-- Component in member-add page
--

SELECT im_component_plugin__new (
	null,					-- plugin_id
	'im_component_plugin',			-- object_type
	now(),					-- creation_date
	null,					-- creation_user
	null,					-- creation_ip
	null,					-- context_id
	'Account List',				-- plugin_name
	'intranet-cost-calculation',		-- package_name
	'left',					-- location
	'/intranet-cost-calculation/',		-- page_url
	null,					-- view_name
	200,					-- sort_order
	'im_account_list_component -user_id_from_search $user_id',
	'lang::message::lookup "" intranet-cost-calculation.AccountListTitle "Accounts"'
);


-- Permissions on component
--
CREATE OR REPLACE FUNCTION inline_0 ()
RETURNS INTEGER AS '

declare
        v_plugin_id             integer;
	v_employees		integer;
begin

	select group_id into v_employees from groups where group_name = ''Employees'';

        select  plugin_id
        into    v_plugin_id
        from    im_component_plugins pl
        where   plugin_name = ''RWH Balance Component'';

        PERFORM im_grant_permission(v_plugin_id, v_employees, ''read'');

        select  plugin_id
        into    v_plugin_id
        from    im_component_plugins pl
        where   plugin_name = ''Overtime Balance Component'';

        PERFORM im_grant_permission(v_plugin_id, v_employees, ''read'');

        return 1;

end;' LANGUAGE 'plpgsql';

SELECT inline_0 ();
DROP FUNCTION inline_0 ();


-------------------------------------------------------------
-- Permissions and Privileges
-------------------------------------------------------------

select acs_privilege__create_privilege('add_account','Add Accounts','Add Accounts');
select acs_privilege__add_child('admin', 'add_accounts');

select acs_privilege__create_privilege('view_account','View Account','View Accounts');
select acs_privilege__add_child('admin', 'view_accounts');

select acs_privilege__create_privilege('view_accounts_all','View All Accounts','View All Accounts');
select acs_privilege__add_child('admin', 'view_accounts_all');


select im_priv_create('add_account','Senior Managers');
select im_priv_create('add_account','Accounting');

select im_priv_create('view_account','Senior Managers');
select im_priv_create('view_account','Accounting');

select im_priv_create('view_accounts_all','Senior Managers');
select im_priv_create('view_account_all','Accounting');


---------------------------------------------------------
-- Menus
---------------------------------------------------------

create or replace function inline_0 ()
returns integer as '
declare
        -- Menu IDs
        v_menu                  integer;
        v_reporting_menu        integer;

BEGIN
        -- Determine the main menu. "Label" is used to
        -- identify menus.
        select menu_id into v_top_menu
        from im_menus where label=''top'';

        -- Create the menu.
        v_menu := im_menu__new (
                null,                           -- p_menu_id
                ''acs_object'',                 -- object_type
                now(),                          -- creation_date
                null,                           -- creation_user
                null,                           -- creation_ip
                null,                           -- context_id
                ''intranet-cost-calculation'',  -- package_name
                ''intranet-accounts'',		-- label
                ''Accounts'',		        -- name
                ''/intranet-cost-calculation/'',-- url
                505,                            -- sort_order
                v_top_menu,                     -- parent_menu_id
                null                            -- p_visible_tcl
        );

        return 0;
end;' language 'plpgsql';
-- Execute and then drop the function
select inline_0 ();
drop function inline_0 ();