--
--


--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: 
--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;


--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';



--
-- Name: add_adjustment(character varying, character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION add_adjustment(character varying, character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	adj							RECORD;
	rec							RECORD;
	v_period_id					integer;
	v_org_id					integer;
	v_adjustment_factor			integer;
	v_default_adjustment_id		integer;
	v_amount					real;
	msg							varchar(120);
BEGIN

	SELECT adjustment_id, adjustment_name, org_id, adjustment_type, formural, default_amount, in_payroll, in_tax, visible INTO adj
	FROM adjustments
	WHERE (adjustment_id = $1::integer);
	
	IF(adj.adjustment_type = 2)THEN
		v_adjustment_factor := -1;
	ELSE
		v_adjustment_factor := 1;
	END IF;
	
	IF ($3 = '1') THEN
		SELECT max(period_id) INTO v_period_id
		FROM periods
		WHERE (closed = false) AND (org_id = adj.org_id);
		
		FOR rec IN SELECT employee_month_id, exchange_rate
			FROM employee_month 
			WHERE (period_id = v_period_id) 
		LOOP
			
			IF(adj.formural is not null)THEN
				EXECUTE 'SELECT ' || adj.formural || ' FROM employee_month WHERE employee_month_id = ' || rec.employee_month_id
				INTO v_amount;
			END IF;
			IF(v_amount is null)THEN
				v_amount := adj.default_amount;
			END IF;
	
			IF(v_amount is not null)THEN
				INSERT INTO employee_adjustments (employee_month_id, adjustment_id, org_id,
					adjustment_type, adjustment_factor, pay_date, amount,
					exchange_rate, in_payroll, in_tax, visible)
				VALUES(rec.employee_month_id, adj.adjustment_id, adj.org_id,
					adj.adjustment_type, v_adjustment_factor, current_date, v_amount,
				(1 / rec.exchange_rate), adj.in_payroll, adj.in_tax, adj.visible);
			END IF;
		END LOOP;
		msg := 'Added ' || adj.adjustment_name || ' to month';
	ELSIF ($3 = '2') THEN	
		FOR rec IN SELECT entity_id
			FROM employees
			WHERE (active = true) AND (org_id = adj.org_id) 
		LOOP
			
			SELECT default_adjustment_id INTO v_default_adjustment_id
			FROM default_adjustments
			WHERE (entity_id = rec.entity_id) AND (adjustment_id = adj.adjustment_id);
			
			IF(v_default_adjustment_id is null)THEN
				INSERT INTO default_adjustments (entity_id, adjustment_id, org_id,
					amount, active)
				VALUES (rec.entity_id, adj.adjustment_id, adj.org_id,
					adj.default_amount, true);
			END IF;
		END LOOP;
		msg := 'Added ' || adj.adjustment_name || ' to employees';
	END IF;

	RETURN msg;
END;
$_$;


ALTER FUNCTION public.add_adjustment(character varying, character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: add_client(character varying, character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION add_client(character varying, character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	rec						RECORD;
	v_lead_id				integer;
	v_entity_id				integer;
	v_entity_type_id		integer;
	v_industry_id			integer;
	v_sales_id				integer;
	v_account_id			integer;
	msg 					varchar(120);
BEGIN

	msg := null;

	IF($3 = '1')THEN
		SELECT org_id, business_id, business_name, business_address, city,
			state, country_id, number_of_employees, telephone, website,
			primary_contact, job_title, primary_email
		INTO rec
		FROM leads WHERE lead_id = $1::integer;
		
		SELECT entity_id INTO v_entity_id
		FROM entitys WHERE user_name = lower(trim(rec.primary_email));
		
		SELECT max(entity_type_id) INTO v_entity_type_id
		FROM entity_types
		WHERE (org_id = rec.org_id) AND (use_key_id = 2);
		
		SELECT account_id INTO v_account_id
		FROM default_accounts 
		WHERE (org_id = rec.org_id) AND (use_key_id = 51);

		IF(rec.business_id is not null)THEN
			msg := 'The business is already added.';
		ELSIF(rec.primary_email is null)THEN
			RAISE EXCEPTION 'You must enter an email address';
		ELSIF(v_entity_id is not null)THEN
			RAISE EXCEPTION 'You must have a unique email address';
		ELSIF(v_entity_type_id is null)THEN
			RAISE EXCEPTION 'You must and entity type with use key being 2';
		ELSE
			v_entity_id := nextval('entitys_entity_id_seq');
			INSERT INTO entitys (entity_id, org_id, entity_type_id, account_id, entity_name, attention, user_name, primary_email,  function_role, use_key_id)
			VALUES (v_entity_id, rec.org_id, v_entity_type_id, v_account_id, rec.business_name, rec.primary_contact, lower(trim(rec.primary_email)), lower(trim(rec.primary_email)), 'client', 2);
			
			INSERT INTO address (address_name, sys_country_id, table_name, org_id, table_id, premises, town, phone_number, website, is_default) 
			VALUES (rec.business_name, rec.country_id, 'entitys', rec.org_id, v_entity_id, rec.business_address, rec.city, rec.telephone, rec.website, true);
			
			UPDATE leads SET business_id = v_entity_id WHERE (lead_id = $1::integer);
			
			msg := 'You have added the client';
		END IF;
	ELSIF($3 = '2')THEN
		SELECT a.org_id, a.entity_id, a.entity_type_id, a.org_id, a.entity_name, a.user_name, a.primary_email, 
			a.primary_telephone, a.attention, b.sys_country_id, b.address_name, 
			b.post_office_box, b.postal_code, b.premises, b.town, b.website INTO rec
		FROM entitys a LEFT JOIN
			(SELECT address_id, address_type_id, sys_country_id, org_id, address_name, 
			table_id, post_office_box, postal_code, premises, 
			street, town, phone_number, mobile, email, website
			FROM address
			WHERE (is_default = true) AND (table_name = 'entitys')) b
		ON a.entity_id = b.table_id
		WHERE (a.entity_id = $1::integer);
		
		SELECT lead_id INTO v_lead_id
		FROM leads 
		WHERE (business_id = rec.entity_id) OR (lower(trim(business_name)) = lower(trim(rec.entity_name)));
		
		IF(v_lead_id is not null)THEN
			msg := 'The business is already added.';
		ELSE		
			SELECT min(industry_id) INTO v_industry_id
			FROM industry WHERE (org_id = rec.org_id);
			
			SELECT min(entity_id) INTO v_sales_id
			FROM entitys
			WHERE (org_id = rec.org_id) AND (use_function = 0);
	
			INSERT INTO leads(industry_id, entity_id, org_id, business_id, business_name, 
				business_address, city, country_id, 
				telephone, primary_contact, primary_email, website)
			VALUES (v_industry_id, v_sales_id, rec.org_id, rec.entity_id, rec.entity_name,
				rec.premises, rec.town, rec.sys_country_id,
				rec.primary_telephone, rec.attention, rec.primary_email, rec.website);
			
            msg := 'You have added the lead';
		END IF;
	END IF;

	return msg;
END;
$_$;


ALTER FUNCTION public.add_client(character varying, character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: add_employee(character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION add_employee(character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	v_application_id		integer;
	v_entity_id				integer;
	v_employee_id			integer;
	msg		 				varchar(120);
BEGIN

	v_application_id := CAST($1 as int);
	SELECT employees.entity_id, applications.employee_id INTO v_entity_id, v_employee_id
	FROM applications LEFT JOIN employees ON applications.entity_id = employees.entity_id
	WHERE (application_id = v_application_id);

	IF(v_employee_id is null) AND (v_entity_id is null)THEN
		INSERT INTO employees (org_id, currency_id, bank_branch_id,
			department_role_id, pay_scale_id, pay_group_id, location_id,  
			person_title, surname, first_name, middle_name,
			date_of_birth, gender, nationality, marital_status,
			picture_file, identity_card, language, interests, objective,
			contract, appointment_date, current_appointment, contract_period,
			basic_salary)

		SELECT orgs.org_id, orgs.currency_id, 0,
			intake.department_role_id, intake.pay_scale_id, intake.pay_group_id, intake.location_id,
			applicants.person_title, applicants.surname, applicants.first_name, applicants.middle_name,  
			applicants.date_of_birth, applicants.gender, applicants.nationality, applicants.marital_status, 
			applicants.picture_file, applicants.identity_card, applicants.language, applicants.interests, applicants.objective,
			
			
			intake.contract, applications.contract_date, applications.contract_start, 
			applications.contract_period, applications.initial_salary
		FROM orgs INNER JOIN applicants ON orgs.org_id = applicants.org_id
			INNER JOIN applications ON applicants.entity_id = applications.entity_id
			INNER JOIN intake ON applications.intake_id = intake.intake_id
			
		WHERE (applications.application_id = v_application_id);
		
		UPDATE applications SET employee_id = currval('entitys_entity_id_seq'), approve_status = 'Completed'
		WHERE (application_id = v_application_id);
			
		msg := 'Employee added';
	ELSIF(v_employee_id is null)THEN
		UPDATE applications SET employee_id = v_employee_id, 
			department_role_id = intake.department_role_id, pay_scale_id = intake.pay_scale_id, 
			pay_group_id = intake.pay_group_id, location_id = intake.location_id,
			approve_status = 'Completed'
		FROM intake  
		WHERE (applications.intake_id = intake.intake_id) AND (applications.application_id = v_application_id);
		
		msg := 'Employee details updated';
	ELSE
		msg := 'Employeed already added to the system';
	END IF;
	

	return msg;
END;
$_$;


ALTER FUNCTION public.add_employee(character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: add_periods(character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION add_periods(character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	v_org_id			integer;
	v_period_id			integer;
	msg					varchar(120);
BEGIN

	SELECT org_id INTO v_org_id
	FROM fiscal_years
	WHERE (fiscal_year_id = $1::int);
	
	UPDATE periods SET fiscal_year_id = fiscal_years.fiscal_year_id
	FROM fiscal_years WHERE (fiscal_years.fiscal_year_id = $1::int)
		AND (fiscal_years.fiscal_year_start <= start_date) AND (fiscal_years.fiscal_year_end >= end_date);
	
	SELECT period_id INTO v_period_id
	FROM periods
	WHERE (fiscal_year_id = $1::int) AND (org_id = v_org_id);
	
	IF(v_period_id is null)THEN
		INSERT INTO periods (fiscal_year_id, org_id, start_date, end_date)
		SELECT $1::int, v_org_id, period_start, CAST(period_start + CAST('1 month' as interval) as date) - 1
		FROM (SELECT CAST(generate_series(fiscal_year_start, fiscal_year_end, '1 month') as date) as period_start
			FROM fiscal_years WHERE fiscal_year_id = $1::int) as a;
		msg := 'Months for the year generated';
	ELSE
		msg := 'Months year already created';
	END IF;

	RETURN msg;
END;
$_$;


ALTER FUNCTION public.add_periods(character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: add_project_staff(character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION add_project_staff(character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	msg		 				varchar(120);
	v_entity_id				integer;
	v_org_id				integer;
BEGIN

	SELECT entity_id INTO v_entity_id
	FROM project_staff WHERE (entity_id = CAST($1 as int)) AND (project_id = CAST($3 as int));
	
	IF(v_entity_id is null)THEN
		SELECT org_id INTO v_org_id
		FROM projects WHERE (project_id = CAST($3 as int));
		
		INSERT INTO  project_staff (project_id, entity_id, org_id)
		VALUES (CAST($3 as int), CAST($1 as int), v_org_id);

		msg := 'Added to project';
	ELSE
		msg := 'Already Added to project';
	END IF;
	
	return msg;
END;
$_$;


ALTER FUNCTION public.add_project_staff(character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: add_shortlist(character varying, character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION add_shortlist(character varying, character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	msg		 				varchar(120);
BEGIN

	IF ($3 = '1') THEN
		UPDATE applications SET short_listed = 1
		WHERE application_id = CAST($1 as int);
		msg := 'Added to short list';
	ELSIF ($3 = '2') THEN
		UPDATE applications SET short_listed = 0
		WHERE application_id = CAST($1 as int);
		msg := 'Removed from short list';
	END IF;
	
	return msg;
END;
$_$;


ALTER FUNCTION public.add_shortlist(character varying, character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: add_tx_link(character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION add_tx_link(character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
BEGIN
	
	INSERT INTO transaction_details (transaction_id, org_id, item_id, quantity, amount, tax_amount, narrative, details)
	SELECT CAST($3 as integer), org_id, item_id, quantity, amount, tax_amount, narrative, details
	FROM transaction_details
	WHERE (transaction_detail_id = CAST($1 as integer));

	INSERT INTO transaction_links (org_id, transaction_detail_id, transaction_detail_to, quantity, amount)
	SELECT org_id, transaction_detail_id, currval('transaction_details_transaction_detail_id_seq'), quantity, amount
	FROM transaction_details
	WHERE (transaction_detail_id = CAST($1 as integer));

	return 'DONE';
END;
$_$;


ALTER FUNCTION public.add_tx_link(character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: advance_aplication(character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION advance_aplication(character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	msg 				varchar(120);
BEGIN
	msg := 'Advance applied';
	
	UPDATE employee_advances SET approve_status = 'Completed'
	WHERE (employee_advance_id = CAST($1 as int)) AND (approve_status = 'Draft');

	return msg;
END;
$_$;


ALTER FUNCTION public.advance_aplication(character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: af_upd_transaction_details(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION af_upd_transaction_details() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	v_amount					real;
	v_tax_amount				real;
BEGIN

	IF(TG_OP = 'DELETE')THEN
		SELECT SUM(quantity * (amount + tax_amount)), SUM(quantity *  tax_amount) INTO v_amount, v_tax_amount
		FROM transaction_details WHERE (transaction_id = OLD.transaction_id);
		
		UPDATE transactions SET transaction_amount = v_amount, transaction_tax_amount = v_tax_amount
		WHERE (transaction_id = OLD.transaction_id);	
	ELSE
		SELECT SUM(quantity * (amount + tax_amount)), SUM(quantity *  tax_amount) INTO v_amount, v_tax_amount
		FROM transaction_details WHERE (transaction_id = NEW.transaction_id);
		
		UPDATE transactions SET transaction_amount = v_amount, transaction_tax_amount = v_tax_amount
		WHERE (transaction_id = NEW.transaction_id);	
	END IF;

	RETURN NULL;
END;
$$;


ALTER FUNCTION public.af_upd_transaction_details() OWNER TO postgres;

--
-- Name: amortise(integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION amortise(assetid integer) RETURNS character varying
    LANGUAGE plpgsql
    AS $$
DECLARE
	periodid 		int;
	rate 			real;
	pvalue 			real;
	cvalue 			real;
	depreciation	real;
BEGIN
	SELECT asset_types.Depreciation_rate, assets.purchase_value, YEAR(assets.purchase_date) INTO rate, pvalue, periodid
	FROM asset_types INNER JOIN assets ON asset_types.asset_type_id = assets.asset_type_id
	WHERE asset_id = assetid;

	DELETE FROM amortisation WHERE (asset_id = assetid);

	cvalue := pvalue;
	depreciation := pvalue * rate / 100;
	LOOP
		IF (cvalue <= 0) THEN EXIT; END IF; -- exit loop

		pvalue := 0;
		SELECT asset_value INTO pvalue
		FROM asset_valuations
		WHERE (asset_id = assetid) AND (valuation_year = periodid);
		IF(pvalue > 1) THEN
			cvalue := pvalue;
			depreciation := pvalue * rate / 100;
		END IF;

		IF (cvalue < depreciation) THEN
			depreciation := cvalue;
		END IF;
		IF(depreciation > 1) THEN
			INSERT INTO amortisation (asset_id, amortisation_year, asset_value, amount)
			VALUES (assetid, periodid, cvalue, depreciation);
		END IF;

		periodid := periodid + 1;
		cvalue := cvalue - depreciation;
	END LOOP;

	RETURN 'Done';
END;
$$;


ALTER FUNCTION public.amortise(assetid integer) OWNER TO postgres;

--
-- Name: amortise_post(integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION amortise_post(yearid integer) RETURNS character varying
    LANGUAGE plpgsql
    AS $$
DECLARE
	cur1	RECORD;
	cur2	RECORD;
	cur3	RECORD;

	j_id 	integer;
BEGIN
	INSERT INTO journals (period_id, journal_date)
	SELECT period_id, CURRENT_DATE
	FROM periods
	WHERE (period_start <= CURRENT_DATE) AND (period_end >= CURRENT_DATE);

	j_id := currval('journals_journal_id_seq');

	-- Depreciation posting
	FOR cur1 IN SELECT asset_types.depreciation_account as a_a, asset_types.accumulated_account as a_b, 
		amortisation.amortisation_id as a_id, amortisation.amount as da
	FROM asset_types INNER JOIN assets ON asset_types.asset_type_id = assets.asset_type_id
		INNER JOIN amortisation ON assets.asset_id = amortisation.asset_id
	WHERE (amortisation.posted = false) AND (amortisation_year = yearid) LOOP
		INSERT INTO gls (journal_id, account_id, debit, credit)
		VALUES (j_id, cur1.a_a, cur1.da, 0); 

		INSERT INTO gls (journal_id, account_id, debit, credit)
		VALUES (j_id, cur1.a_b, 0, cur1.da); 

		UPDATE amortisation SET posted = true WHERE amortisation_id = cur1.a_id;
	END LOOP;

	-- Open cursor
	FOR cur2 IN SELECT asset_types.asset_account as a_a, asset_types.valuation_account as a_b, 
		asset_valuations.asset_valuation_id as a_id, asset_valuations.value_change as da
	FROM asset_types INNER JOIN assets ON asset_types.asset_type_id = assets.asset_type_id
		INNER JOIN asset_valuations ON assets.asset_id = asset_valuations.asset_id
	WHERE (asset_valuations.posted = false) AND (asset_valuations.valuation_year = yearid) LOOP
		INSERT INTO gls (journal_id, account_id, debit, credit)
		VALUES (j_id, cur2.a_a, cur2.da, 0);

		INSERT INTO gls (journal_id, account_id, debit, credit)
		VALUES (j_id, cur2.a_b, 0, cur2.da);

		UPDATE asset_valuations SET posted = true WHERE asset_valuation_id = cur2.a_id;
	END LOOP;

	-- Open cursor
	FOR cur3 IN SELECT asset_types.asset_account as a_a, asset_types.accumulated_account as a_b, 
		asset_types.disposal_account as a_c, assets.asset_id as a_id,
		assets.disposal_amount as da, assets.purchase_value as pc,
		COALESCE(sum(asset_valuations.value_change), 0) as vc
	FROM asset_types INNER JOIN assets ON asset_types.asset_type_id = assets.asset_type_id
		LEFT JOIN asset_valuations ON assets.asset_id = asset_valuations.asset_id
	WHERE (assets.inactive = true) AND (assets.disposal_posting = false) AND (YEAR(disposal_date) = yearid)
	GROUP BY asset_types.asset_account, asset_types.accumulated_account, 
		asset_types.disposal_account, assets.disposal_amount, assets.purchase_value LOOP

			INSERT INTO gls (journal_id, account_id, debit, credit)
			VALUES (j_id, cur3.a_a, 0, (cur3.pv + cur3.vc));

			INSERT INTO gls (journal_id, account_id, debit, credit)
			VALUES (j_id, cur3.a_c, cur3.da, 0);

			INSERT INTO gls (journal_id, account_id, debit, credit)
			VALUES (j_id, cur3.a_b, (cur3.pv + cur3.vc - cur3.da), 0);

			UPDATE assets SET disposal_posting = true WHERE asset_id = a_id;
		END LOOP;

	RETURN 'Done';
END;
$$;


ALTER FUNCTION public.amortise_post(yearid integer) OWNER TO postgres;

--
-- Name: apt_grade_change(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION apt_grade_change() RETURNS trigger
    LANGUAGE plpgsql
    AS $$ 
BEGIN

	IF (NEW.grade !=0) THEN
		INSERT INTO sys_emailed(sys_email_id, org_id, table_id, table_name)
		VALUES (3, NEW.org_id, NEW.aptitude_grade_id, 'aptitude_grades' );
	END IF;

RETURN NEW;
END;
$$;


ALTER FUNCTION public.apt_grade_change() OWNER TO postgres;

--
-- Name: budget_process(character varying, character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION budget_process(character varying, character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	rec 	RECORD;
	recb 	RECORD;

	nb_id 	INTEGER;
	ntrx	INTEGER;
	msg 	varchar(120);
BEGIN
	SELECT budget_id, org_id, fiscal_year_id, department_id, link_budget_id, budget_type, budget_name, approve_status INTO rec
	FROM budgets
	WHERE (budget_id = CAST($1 as integer));
	
	IF($3 = '1') THEN
		IF(rec.approve_status = 'Draft') THEN
			UPDATE budgets SET approve_status = 'Completed', entity_id = CAST($2 as integer)
			WHERE budget_id = rec.budget_id;
		END IF;
		msg := 'Transaction completed.';
	ELSIF (($3 = '2') OR ($3 = '3')) THEN
		IF(rec.approve_status = 'Approved') THEN
			IF(rec.link_budget_id is null) THEN
				nb_id := create_budget(rec.budget_id, rec.fiscal_year_id, CAST($3 as int));
				UPDATE budgets SET link_budget_id = nb_id WHERE budget_id = rec.budget_id;
				msg := 'The budget created.';
			ELSE
				msg := 'Another budget has already been created';
			END IF;
		ELSE
			msg := 'The budget needs to be aprroved first';
		END IF;
	ELSIF (($3 = '4')) THEN
		SELECT transaction_id, approve_status INTO recb 
		FROM vw_budget_lines WHERE (budget_line_id = CAST($1 as integer));

		IF(recb.approve_status != 'Approved') THEN
			msg := 'The budget neets approval first.';
		ELSIF(recb.transaction_id is null) THEN
			INSERT INTO transactions (org_id, currency_id, entity_id, department_id, transaction_type_id, transaction_date)
			SELECT orgs.org_id, orgs.currency_id, CAST($2 as integer), vw_budget_lines.department_id, 16, current_date
			FROM vw_budget_lines INNER JOIN orgs ON vw_budget_lines.org_id = orgs.org_id
			WHERE (budget_line_id = CAST($1 as integer));

			ntrx := currval('transactions_transaction_id_seq');

			INSERT INTO transaction_details (org_id, transaction_id, account_id, item_id, quantity, amount, tax_amount, narrative, details)
			SELECT org_id, ntrx, account_id, item_id, quantity, amount, tax_amount, narrative, details
			FROM vw_budget_lines
			WHERE (budget_line_id = CAST($1 as integer));

			UPDATE budget_lines SET transaction_id = ntrx WHERE (budget_line_id = CAST($1 as integer));

			msg := 'Requisition Created.';
		ELSE
			msg := 'Requisition had been created from this budget.';
		END IF;
	ELSE
		msg := 'Transaction alerady completed.';
	END IF;

	return msg;
END;
$_$;


ALTER FUNCTION public.budget_process(character varying, character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: change_password(character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION change_password(character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	old_password 	varchar(64);
	passchange 		varchar(120);
	entityID		integer;
BEGIN
	passchange := 'Password Error';
	entityID := CAST($1 AS INT);
	SELECT Entity_password INTO old_password
	FROM entitys WHERE (entity_id = entityID);

	IF ($2 = '0') THEN
		passchange := first_password();
		UPDATE entitys SET first_password = passchange, Entity_password = md5(passchange) WHERE (entity_id = entityID);
		passchange := 'Password Changed';
	ELSIF (old_password = md5($2)) THEN
		UPDATE entitys SET Entity_password = md5($3) WHERE (entity_id = entityID);
		passchange := 'Password Changed';
	ELSE
		passchange := null;
	END IF;

	return passchange;
END;
$_$;


ALTER FUNCTION public.change_password(character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: claims_aplication(character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION claims_aplication(character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	v_amount			real;
	msg 				varchar(120);
BEGIN
	msg := 'Advance applied';
	
	SELECT sum(amount) INTO v_amount
	FROM vw_claim_details
	WHERE (claim_id = $1::int);
	
	IF(v_amount is null)THEN
		RAISE EXCEPTION 'You need to add claim details';
	END IF;
	
	UPDATE claims SET approve_status = 'Completed'
	WHERE (claim_id = CAST($1 as int)) AND (approve_status = 'Draft');

	RETURN msg;
END;
$_$;


ALTER FUNCTION public.claims_aplication(character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: close_issue(character varying, character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION close_issue(character varying, character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	msg 					varchar(120);
BEGIN

	msg := null;
	
	IF($3 = '1')THEN
		UPDATE helpdesk SET closed_by = $2::integer, solved_time = current_timestamp, is_solved = true
		WHERE helpdesk_id = $1::integer;
		
		msg := 'Closed the call';
	END IF;
	
	return msg;
END;
$_$;


ALTER FUNCTION public.close_issue(character varying, character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: close_periods(character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION close_periods(character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	msg 					varchar(120);
BEGIN
	
	IF(v_period_id is null)THEN
		INSERT INTO periods (fiscal_year_id, org_id, start_date, end_date)
		SELECT $1::int, v_org_id, period_start, CAST(period_start + CAST('1 month' as interval) as date) - 1
		FROM (SELECT CAST(generate_series(fiscal_year_start, fiscal_year_end, '1 month') as date) as period_start
			FROM fiscal_years WHERE fiscal_year_id = $1::int) as a;
		msg := 'Months for the year generated';
	ELSE
		msg := 'Months year already created';
	END IF;

	RETURN msg;
END;
$_$;


ALTER FUNCTION public.close_periods(character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: close_year(character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION close_year(character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	trx_date		DATE;
	periodid		INTEGER;
	journalid		INTEGER;
	profit_acct		INTEGER;
	retain_acct		INTEGER;
	rec				RECORD;
	msg				varchar(120);
BEGIN
	SELECT fiscal_year_id, fiscal_year_start, fiscal_year_end, year_opened, year_closed INTO rec
	FROM fiscal_years
	WHERE (fiscal_year_id = CAST($1 as integer));

	SELECT account_id INTO profit_acct FROM default_accounts WHERE default_account_id = 1;
	SELECT account_id INTO retain_acct FROM default_accounts WHERE default_account_id = 2;
	
	trx_date := CAST($1 || '-12-31' as date);
	periodid := get_open_period(trx_date);
	IF(periodid is null) THEN
		msg := 'Cannot post. No active period to post.';
	ELSIF(rec.year_opened = false)THEN
		msg := 'Cannot post. The year is not opened.';
	ELSIF(rec.year_closed = true)THEN
		msg := 'Cannot post. The year is closed.';
	ELSE
		INSERT INTO journals (period_id, journal_date, narrative, year_closing)
		VALUES (periodid, trx_date, 'End of year closing', false);
		journalid := currval('journals_journal_id_seq');

		INSERT INTO gls (journal_id, account_id, debit, credit, gl_narrative)
		SELECT journalid, account_id, dr_amount, cr_amount, 'Account Balance'
		FROM ((SELECT account_id, sum(bal_credit) as dr_amount, sum(bal_debit) as cr_amount
		FROM vw_ledger
		WHERE (chat_type_id > 3) AND (fiscal_year_id = rec.fiscal_year_id) AND (acc_balance <> 0)
		GROUP BY account_id)
		UNION
		(SELECT profit_acct, (CASE WHEN sum(bal_debit) > sum(bal_credit) THEN sum(bal_debit - bal_credit) ELSE 0 END),
		(CASE WHEN sum(bal_debit) < sum(bal_credit) THEN sum(bal_credit - bal_debit) ELSE 0 END)
		FROM vw_ledger
		WHERE (chat_type_id > 3) AND (fiscal_year_id = rec.fiscal_year_id) AND (acc_balance <> 0))) as a;

		msg := process_journal(CAST(journalid as varchar),'0','0');

		INSERT INTO journals (period_id, journal_date, narrative, year_closing)
		VALUES (periodid, trx_date, 'Retained Earnings', false);
		journalid := currval('journals_journal_id_seq');

		INSERT INTO gls (journal_id, account_id, debit, credit, gl_narrative)
		SELECT journalid, profit_acct, (CASE WHEN sum(bal_debit) < sum(bal_credit) THEN sum(bal_credit - bal_debit) ELSE 0 END),
			(CASE WHEN sum(bal_debit) > sum(bal_credit) THEN sum(bal_debit - bal_credit) ELSE 0 END), 'Retained Earnings'
		FROM vw_ledger
		WHERE (account_id = profit_acct) AND (fiscal_year_id = rec.fiscal_year_id) AND (acc_balance <> 0);

		INSERT INTO gls (journal_id, account_id, debit, credit, gl_narrative)
		SELECT journalid, retain_acct, (CASE WHEN sum(bal_debit) > sum(bal_credit) THEN sum(bal_debit - bal_credit) ELSE 0 END),
			(CASE WHEN sum(bal_debit) < sum(bal_credit) THEN sum(bal_credit - bal_debit) ELSE 0 END), 'Retained Earnings'
		FROM vw_ledger
		WHERE (account_id = profit_acct) AND (fiscal_year_id = rec.fiscal_year_id) AND (acc_balance <> 0);

		msg := process_journal(CAST(journalid as varchar),'0','0');

		UPDATE fiscal_years SET year_closed = true WHERE fiscal_year_id = rec.fiscal_year_id;
		UPDATE periods SET period_closed = true WHERE fiscal_year_id = rec.fiscal_year_id;
	END IF;

	return msg;
END;
$_$;


ALTER FUNCTION public.close_year(character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: complete_transaction(character varying, character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION complete_transaction(character varying, character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	rec RECORD;
	bankacc INTEGER;
	msg varchar(120);
BEGIN
	SELECT transaction_id, transaction_type_id, transaction_status_id, bank_account_id INTO rec
	FROM transactions
	WHERE (transaction_id = CAST($1 as integer));

	IF($3 = '2') THEN
		UPDATE transactions SET transaction_status_id = 4 
		WHERE transaction_id = rec.transaction_id;
		msg := 'Transaction Archived';
	ELSIF($3 = '1') AND (rec.transaction_status_id = 1)THEN
		IF((rec.transaction_type_id = 7) or (rec.transaction_type_id = 8)) THEN
			IF(rec.bank_account_id is null)THEN
				msg := 'Transaction completed.';
				RAISE EXCEPTION 'You need to add the bank account to receive the funds';
			ELSE
				UPDATE transactions SET transaction_status_id = 2, approve_status = 'Completed'
				WHERE transaction_id = rec.transaction_id;
				msg := 'Transaction completed.';
			END IF;
		ELSE
			UPDATE transactions SET transaction_status_id = 2, approve_status = 'Completed'
			WHERE transaction_id = rec.transaction_id;
			msg := 'Transaction completed.';
		END IF;
	ELSE
		msg := 'Transaction alerady completed.';
	END IF;

	return msg;
END;
$_$;


ALTER FUNCTION public.complete_transaction(character varying, character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: compute_loans(character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION compute_loans(character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	rec							RECORD;
	v_period_id					integer;
	v_org_id					integer;
	msg							varchar(120);
BEGIN

	SELECT period_id, org_id INTO v_period_id, v_org_id
	FROM periods
	WHERE (period_id = $1::integer);
	
	FOR rec IN SELECT loan_month_id, employee_adjustment_id FROM loan_monthly WHERE period_id = v_period_id
	LOOP
		DELETE FROM loan_monthly WHERE loan_month_id = rec.loan_month_id;
		DELETE FROM employee_adjustments WHERE employee_adjustment_id = rec.employee_adjustment_id;
	END LOOP;
	
	INSERT INTO loan_monthly (period_id, org_id, loan_id, interest_amount, interest_paid, repayment)
	SELECT v_period_id, org_id, loan_id, (loan_balance * interest / 1200), (loan_balance * interest / 1200),
		(CASE WHEN loan_balance > monthly_repayment THEN monthly_repayment ELSE loan_balance END)
	FROM vw_loans 
	WHERE (loan_balance > 0) AND (approve_status = 'Approved') AND (reducing_balance =  true) AND (org_id = v_org_id);

	INSERT INTO loan_monthly (period_id, org_id, loan_id, interest_amount, interest_paid, repayment)
	SELECT v_period_id, org_id, loan_id, (principle * interest / 1200), (principle * interest / 1200),
		(CASE WHEN loan_balance > monthly_repayment THEN monthly_repayment ELSE loan_balance END)
	FROM vw_loans 
	WHERE (loan_balance > 0) AND (approve_status = 'Approved') AND (reducing_balance =  false) AND (org_id = v_org_id);

	msg := 'Loans re-computed';

	RETURN msg;
END;
$_$;


ALTER FUNCTION public.compute_loans(character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: copy_transaction(character varying, character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION copy_transaction(character varying, character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	msg varchar(120);
BEGIN

	INSERT INTO transactions (org_id, department_id, entity_id, currency_id, transaction_type_id, transaction_date, order_number, payment_terms, job, narrative, details)
	SELECT org_id, department_id, entity_id, currency_id, transaction_type_id, CURRENT_DATE, order_number, payment_terms, job, narrative, details
	FROM transactions
	WHERE (transaction_id = CAST($1 as integer));

	INSERT INTO transaction_details (org_id, transaction_id, account_id, item_id, quantity, amount, tax_amount, narrative, details)
	SELECT org_id, currval('transactions_transaction_id_seq'), account_id, item_id, quantity, amount, tax_amount, narrative, details
	FROM transaction_details
	WHERE (transaction_id = CAST($1 as integer));

	msg := 'Transaction Copied';

	return msg;
END;
$_$;


ALTER FUNCTION public.copy_transaction(character varying, character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: cpy_trx_ledger(character varying, character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION cpy_trx_ledger(character varying, character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	v_ledger_date				timestamp;
	last_date					timestamp;
	v_start						integer;
	v_end						integer;
	v_inteval					interval;
	msg							varchar(120);
BEGIN

	SELECT max(payment_date)::timestamp INTO last_date
	FROM transactions
	WHERE (to_char(payment_date, 'YYYY.MM') = $1);
	v_start := EXTRACT(YEAR FROM last_date) * 12 + EXTRACT(MONTH FROM last_date);
	
	SELECT max(payment_date)::timestamp INTO v_ledger_date
	FROM transactions;
	v_end := EXTRACT(YEAR FROM v_ledger_date) * 12 + EXTRACT(MONTH FROM v_ledger_date) + 1;
	v_inteval :=  ((v_end - v_start) || ' months')::interval;

	IF ($3 = '1') THEN
		INSERT INTO transactions(ledger_type_id, entity_id, bank_account_id, 
				currency_id, journal_id, org_id, exchange_rate, tx_type, payment_date, 
				transaction_amount, transaction_tax_amount, reference_number, 
				narrative, transaction_type_id, transaction_date)
		SELECT ledger_type_id, entity_id, bank_account_id, 
			currency_id, journal_id, org_id, exchange_rate, tx_type, (payment_date + v_inteval), 
			transaction_amount, transaction_tax_amount, reference_number,
			narrative, transaction_type_id, (transaction_date  + v_inteval)
		FROM transactions
		WHERE (tx_type is not null) AND (to_char(payment_date, 'YYYY.MM') = $1);

		msg := 'Appended a new month';
	END IF;

	RETURN msg;
END;
$_$;


ALTER FUNCTION public.cpy_trx_ledger(character varying, character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: create_budget(integer, integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION create_budget(integer, integer, integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
DECLARE
	rec 	RECORD;
	
	nb_id 	INTEGER;
	p_id	INTEGER;
	p_date	DATE;
BEGIN
	INSERT INTO budgets (budget_type, org_id, fiscal_year_id, department_id, entity_id, budget_name)
	SELECT $3, org_id, fiscal_year_id, department_id, entity_id, budget_name
	FROM budgets
	WHERE (budget_id = $1);

	nb_id := currval('budgets_budget_id_seq');

	FOR rec IN SELECT org_id, period_id, account_id, item_id, spend_type, quantity, amount, tax_amount, income_budget, narrative
	FROM budget_lines WHERE (budget_id =  $1) ORDER BY period_id LOOP
		IF(rec.spend_type = 1)THEN
			INSERT INTO budget_lines (budget_id, period_id, org_id, account_id, item_id, quantity, amount, tax_amount, income_budget, narrative)
			SELECT nb_id, period_id, rec.org_id, rec.account_id, rec.item_id, rec.quantity, rec.amount, rec.tax_amount, rec.income_budget, rec.narrative
			FROM periods
			WHERE (fiscal_year_id = $2);
		ELSIF(rec.spend_type = 2)THEN
			FOR i IN 0..3 LOOP
				SELECT start_date + (i*3 || ' month')::INTERVAL INTO p_date 
				FROM periods WHERE (period_id = rec.period_id);
				SELECT period_id INTO p_id
				FROM periods WHERE (start_date <= p_date) AND (end_date >= p_date);

				IF(p_id is not null)THEN
					INSERT INTO budget_lines (budget_id, period_id, org_id, account_id, item_id, quantity, amount, tax_amount, income_budget, narrative)
					VALUES(nb_id, p_id, rec.org_id, rec.account_id, rec.item_id, rec.quantity, rec.amount, rec.tax_amount, rec.income_budget, rec.narrative);
				END IF;
			END LOOP;
		ELSE
			INSERT INTO budget_lines (budget_id, period_id, org_id, account_id, item_id, quantity, amount, tax_amount, income_budget, narrative)
			VALUES(nb_id, rec.period_id, rec.org_id, rec.account_id, rec.item_id, rec.quantity, rec.amount, rec.tax_amount, rec.income_budget, rec.narrative);
		END IF;
	END LOOP;

	RETURN nb_id;
END;
$_$;


ALTER FUNCTION public.create_budget(integer, integer, integer) OWNER TO postgres;

--
-- Name: curr_base_returns(date, date); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION curr_base_returns(date, date) RETURNS real
    LANGUAGE sql
    AS $_$
    SELECT COALESCE(sum(base_credit - base_debit), 0)
	FROM vw_gls
	WHERE (chat_type_id > 3) AND (posted = true) AND (year_closing = false)
		AND (journal_date >= $1) AND (journal_date <= $2);
$_$;


ALTER FUNCTION public.curr_base_returns(date, date) OWNER TO postgres;

--
-- Name: curr_returns(date, date); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION curr_returns(date, date) RETURNS real
    LANGUAGE sql
    AS $_$
    SELECT COALESCE(sum(credit - debit), 0)
	FROM vw_gls
	WHERE (chat_type_id > 3) AND (posted = true) AND (year_closing = false)
		AND (journal_date >= $1) AND (journal_date <= $2);
$_$;


ALTER FUNCTION public.curr_returns(date, date) OWNER TO postgres;

--
-- Name: default_currency(character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION default_currency(character varying) RETURNS integer
    LANGUAGE sql
    AS $_$
	SELECT orgs.currency_id
	FROM orgs INNER JOIN entitys ON orgs.org_id = entitys.org_id
	WHERE (entitys.entity_id = CAST($1 as integer));
$_$;


ALTER FUNCTION public.default_currency(character varying) OWNER TO postgres;

--
-- Name: del_period(integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION del_period(integer) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	msg 		varchar(120);
BEGIN
	DELETE FROM loan_monthly WHERE period_id = $1;
	
	DELETE FROM advance_deductions WHERE (employee_month_id IN (SELECT employee_month_id FROM employee_month WHERE period_id = $1));
	DELETE FROM employee_advances WHERE (employee_month_id IN (SELECT employee_month_id FROM employee_month WHERE period_id = $1));
	DELETE FROM employee_banking WHERE (employee_month_id IN (SELECT employee_month_id FROM employee_month WHERE period_id = $1));
	DELETE FROM employee_adjustments WHERE (employee_month_id IN (SELECT employee_month_id FROM employee_month WHERE period_id = $1));
	DELETE FROM employee_overtime WHERE (employee_month_id IN (SELECT employee_month_id FROM employee_month WHERE period_id = $1));
	DELETE FROM employee_tax_types WHERE (employee_month_id IN (SELECT employee_month_id FROM employee_month WHERE period_id = $1));
	DELETE FROM period_tax_rates WHERE (period_tax_type_id IN (SELECT period_tax_type_id FROM period_tax_types WHERE period_id = $1));
	DELETE FROM period_tax_types WHERE period_id = $1;

	DELETE FROM employee_month WHERE period_id = $1;
	DELETE FROM periods WHERE period_id = $1;

	msg := 'Period Deleted';

	RETURN msg;
END;
$_$;


ALTER FUNCTION public.del_period(integer) OWNER TO postgres;

--
-- Name: emailed(integer, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION emailed(integer, character varying) RETURNS void
    LANGUAGE sql
    AS $_$
    UPDATE sys_emailed SET emailed = true WHERE (sys_emailed_id = CAST($2 as int));
$_$;


ALTER FUNCTION public.emailed(integer, character varying) OWNER TO postgres;

--
-- Name: emailed_contract(integer, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION emailed_contract(integer, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
BEGIN
	UPDATE applications SET notice_email = true WHERE (application_id = $2::int);
	RETURN 'Done';
END;
$_$;


ALTER FUNCTION public.emailed_contract(integer, character varying) OWNER TO postgres;

--
-- Name: emailed_dob(integer, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION emailed_dob(integer, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	v_org_id				integer;
	v_entity_name			varchar(120);
BEGIN
	SELECT org_id, entity_name INTO v_org_id, v_entity_name
	FROM entitys WHERE (entity_id = $2::int);
	
	INSERT INTO sys_emailed (sys_email_id, org_id, email_type, narrative)
	VALUES (9, 0, 9, 'Its birthday for ' || v_entity_name);

	UPDATE employees SET dob_email = current_date WHERE (entity_id = $2::int);

	RETURN 'Done';
END;
$_$;


ALTER FUNCTION public.emailed_dob(integer, character varying) OWNER TO postgres;

--
-- Name: farm_payroll(character varying, character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION farm_payroll(character varying, character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	rec 		RECORD;
	msg 		varchar(120);
BEGIN
	IF ($3 = '1') THEN
		FOR rec IN SELECT works.entity_id, sum(works.work_amount) as sum_amount
			FROM works INNER JOIN day_works ON works.day_work_id = day_works.day_work_id
			WHERE (day_works.period_id = $1::int) 
			GROUP BY works.entity_id
		LOOP
		
			UPDATE employee_month SET basic_pay = rec.sum_amount
			WHERE (entity_id = rec.entity_id) 
				AND (period_id = $1::int);
				
		END LOOP;
		
		msg := 'Payroll Processed';
	END IF;

	return msg;
END;
$_$;


ALTER FUNCTION public.farm_payroll(character varying, character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: first_password(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION first_password() RETURNS character varying
    LANGUAGE plpgsql
    AS $$
DECLARE
	rnd integer;
	passchange varchar(12);
BEGIN
	passchange := trunc(random()*1000);
	rnd := trunc(65+random()*25);
	passchange := passchange || chr(rnd);
	passchange := passchange || trunc(random()*1000);
	rnd := trunc(65+random()*25);
	passchange := passchange || chr(rnd);
	rnd := trunc(65+random()*25);
	passchange := passchange || chr(rnd);

	return passchange;
END;
$$;


ALTER FUNCTION public.first_password() OWNER TO postgres;

--
-- Name: generate_payroll(character varying, character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION generate_payroll(character varying, character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	v_period_tax_type_id		integer;
	v_employee_month_id			integer;
	v_period_id					integer;
	v_org_id					integer;
	v_month_name				varchar(50);

	msg 						varchar(120);
BEGIN
	SELECT period_id, org_id, to_char(start_date, 'Month YYYY') INTO v_period_id, v_org_id, v_month_name
	FROM periods
	WHERE (period_id = CAST($1 as integer));
	
	SELECT period_tax_type_id INTO v_period_tax_type_id
	FROM period_tax_types
	WHERE (period_id = v_period_id) AND (org_id = v_org_id);
	
	SELECT employee_month_id INTO v_employee_month_id
	FROM employee_month
	WHERE (period_id = v_period_id) AND (org_id = v_org_id);

	IF(v_period_tax_type_id is null) AND (v_employee_month_id is null)THEN

		INSERT INTO period_tax_types (period_id, org_id, tax_type_id, period_tax_type_name, formural, tax_relief, percentage, linear, employer, employer_ps, tax_type_order, in_tax, account_id)
		SELECT v_period_id, org_id, tax_type_id, tax_type_name, formural, tax_relief, percentage, linear, employer, employer_ps, tax_type_order, in_tax, account_id
		FROM tax_types
		WHERE (active = true) AND (org_id = v_org_id);

		INSERT INTO employee_month (period_id, org_id, pay_group_id, entity_id, bank_branch_id, department_role_id, currency_id, bank_account, basic_pay)
		SELECT v_period_id, org_id, pay_group_id, entity_id, bank_branch_id, department_role_id, currency_id, bank_account, basic_salary
		FROM employees
		WHERE (employees.active = true) and (employees.org_id = v_org_id);

		INSERT INTO loan_monthly (period_id, org_id, loan_id, interest_amount, interest_paid, repayment)
		SELECT v_period_id, org_id, loan_id, (loan_balance * interest / 1200), (loan_balance * interest / 1200),
			(CASE WHEN loan_balance > monthly_repayment THEN monthly_repayment ELSE loan_balance END)
		FROM vw_loans 
		WHERE (loan_balance > 0) AND (approve_status = 'Approved') AND (reducing_balance =  true) AND (org_id = v_org_id);

		INSERT INTO loan_monthly (period_id, org_id, loan_id, interest_amount, interest_paid, repayment)
		SELECT v_period_id, org_id, loan_id, (principle * interest / 1200), (principle * interest / 1200),
			(CASE WHEN loan_balance > monthly_repayment THEN monthly_repayment ELSE loan_balance END)
		FROM vw_loans 
		WHERE (loan_balance > 0) AND (approve_status = 'Approved') AND (reducing_balance =  false) AND (org_id = v_org_id);

		PERFORM updTax(employee_month_id, Period_id)
		FROM employee_month
		WHERE (period_id = v_period_id);
		
		INSERT INTO sys_emailed (sys_email_id, table_id, table_name, narrative, org_id)
		SELECT 7, entity_id, 'periods', v_month_name, v_org_id
		FROM entity_subscriptions
		WHERE entity_type_id = 6;
	
		msg := 'Payroll Generated';
	ELSE
		msg := 'Payroll was previously Generated';
	END IF;

	RETURN msg;
END;
$_$;


ALTER FUNCTION public.generate_payroll(character varying, character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: get_acct(integer, date, date); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION get_acct(integer, date, date) RETURNS real
    LANGUAGE sql
    AS $_$
    SELECT sum(gls.debit - gls.credit)
	FROM gls INNER JOIN journals ON gls.journal_id = journals.journal_id
	WHERE (gls.account_id = $1) AND (journals.posted = true) AND (journals.year_closing = false)
		AND (journals.journal_date >= $2) AND (journals.journal_date <= $3);
$_$;


ALTER FUNCTION public.get_acct(integer, date, date) OWNER TO postgres;

--
-- Name: get_approval_date(integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION get_approval_date(integer) RETURNS date
    LANGUAGE plpgsql
    AS $_$
DECLARE
	v_workflow_table_id		integer;
	v_date					date;
BEGIN
	v_workflow_table_id := $1;

	SELECT action_date INTO v_date
	FROM approvals 
	WHERE (approvals.table_id = v_workflow_table_id) AND (approvals.workflow_phase_id = 6);

	return v_date;
END;
$_$;


ALTER FUNCTION public.get_approval_date(integer) OWNER TO postgres;

--
-- Name: get_approver(integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION get_approver(integer) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	v_workflow_table_id		integer;
	v_approver				varchar(120);
BEGIN
	v_approver :='';
	v_workflow_table_id := $1;

	SELECT entitys.entity_name INTO v_approver
	FROM entitys 
	INNER JOIN approvals ON entitys.entity_id = approvals.app_entity_id
	WHERE (approvals.table_id = v_workflow_table_id) AND (approvals.workflow_phase_id = 6);

	return v_approver;
END;
$_$;


ALTER FUNCTION public.get_approver(integer) OWNER TO postgres;

--
-- Name: get_asset_value(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION get_asset_value(assetid integer, valueyear integer) RETURNS real
    LANGUAGE plpgsql
    AS $$
DECLARE
	vperiod 		int;
	pvalue 			real;
	depreciation 	real;
BEGIN
	pvalue := 0;

	SELECT assets.purchase_value INTO pvalue
	FROM assets
	WHERE (asset_id = assetid) AND (YEAR(assets.purchase_date) <= valueYear);

	SELECT sum(amount) INTO depreciation
	FROM amortisation
	WHERE (asset_id	 = assetid) AND (amortisation_year < valueYear);
	IF(pvalue > depreciation) THEN
		pvalue := pvalue - depreciation;
	END IF;

	SELECT max(valuation_year) INTO vperiod
	FROM asset_valuations
	WHERE (asset_id = assetid) AND (valuation_year <= valueYear);

	SELECT asset_value INTO pvalue
	FROM asset_valuations
	WHERE (asset_id = assetid) AND (valuation_year = vperiod);

	SELECT sum(amount) INTO depreciation
	FROM amortisation
	WHERE (asset_id	 = assetid) AND (amortisation_year >= vperiod) AND (amortisation_year < valueYear);
	IF(pvalue > depreciation) THEN
		pvalue := pvalue - depreciation;
	END IF;

	RETURN pvalue;
END;
$$;


ALTER FUNCTION public.get_asset_value(assetid integer, valueyear integer) OWNER TO postgres;

--
-- Name: get_balance(integer, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION get_balance(integer, character varying) RETURNS real
    LANGUAGE plpgsql
    AS $_$
DECLARE
	v_bal					real;
BEGIN

	SELECT COALESCE(sum(debit_amount - credit_amount), 0) INTO v_bal
	FROM vw_trx
	WHERE (vw_trx.approve_status = 'Approved')
		AND (vw_trx.for_posting = true)
		AND (vw_trx.entity_id = $1)
		AND (vw_trx.transaction_date < $2::date);
		
		
	RETURN v_bal;
END;
$_$;


ALTER FUNCTION public.get_balance(integer, character varying) OWNER TO postgres;

--
-- Name: get_base_acct(integer, date, date); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION get_base_acct(integer, date, date) RETURNS real
    LANGUAGE sql
    AS $_$
    SELECT sum(gls.debit * journals.exchange_rate - gls.credit * journals.exchange_rate) 
	FROM gls INNER JOIN journals ON gls.journal_id = journals.journal_id
	WHERE (gls.account_id = $1) AND (journals.posted = true) AND (journals.year_closing = false)
		AND (journals.journal_date >= $2) AND (journals.journal_date <= $3);
$_$;


ALTER FUNCTION public.get_base_acct(integer, date, date) OWNER TO postgres;

--
-- Name: get_budgeted(integer, date, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION get_budgeted(integer, date, integer) RETURNS real
    LANGUAGE plpgsql
    AS $_$
DECLARE
	reca		RECORD;
	app_id		Integer;
	v_bill		real;
	v_variance	real;
BEGIN

	FOR reca IN SELECT transaction_detail_id, account_id, amount 
		FROM transaction_details WHERE (transaction_id = $1) LOOP

		SELECT sum(amount) INTO v_bill
		FROM transactions INNER JOIN transaction_details ON transactions.transaction_id = transaction_details.transaction_id
		WHERE (transactions.department_id = $3) AND (transaction_details.account_id = reca.account_id)
			AND (transactions.journal_id is null) AND (transaction_details.transaction_detail_id <> reca.transaction_detail_id);
		IF(v_bill is null)THEN
			v_bill := 0;
		END IF;

		SELECT sum(budget_lines.amount) INTO v_variance
		FROM fiscal_years INNER JOIN budgets ON fiscal_years.fiscal_year_id = budgets.fiscal_year_id
			INNER JOIN budget_lines ON budgets.budget_id = budget_lines.budget_id
		WHERE (budgets.department_id = $3) AND (budget_lines.account_id = reca.account_id)
			AND (budgets.approve_status = 'Approved')
			AND (fiscal_years.fiscal_year_start <= $2) AND (fiscal_years.fiscal_year_end >= $2);
		IF(v_variance is null)THEN
			v_variance := 0;
		END IF;

		v_variance := v_variance - (reca.amount + v_bill);

		IF(v_variance < 0)THEN
			RETURN v_variance;
		END IF;
	END LOOP;

	RETURN v_variance;
END;
$_$;


ALTER FUNCTION public.get_budgeted(integer, date, integer) OWNER TO postgres;

--
-- Name: get_currency_rate(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION get_currency_rate(integer, integer) RETURNS real
    LANGUAGE sql
    AS $_$
	SELECT max(exchange_rate)
	FROM currency_rates
	WHERE (org_id = $1) AND (currency_id = $2)
		AND (exchange_date = (SELECT max(exchange_date) FROM currency_rates WHERE (org_id = $1) AND (currency_id = $2)));
$_$;


ALTER FUNCTION public.get_currency_rate(integer, integer) OWNER TO postgres;

--
-- Name: get_default_account(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION get_default_account(integer, integer) RETURNS integer
    LANGUAGE sql
    AS $_$
    SELECT accounts.account_no
	FROM default_accounts INNER JOIN accounts ON default_accounts.account_id = accounts.account_id
	WHERE (default_accounts.use_key_id = $1) AND (default_accounts.org_id = $2);
$_$;


ALTER FUNCTION public.get_default_account(integer, integer) OWNER TO postgres;

--
-- Name: get_default_account_id(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION get_default_account_id(integer, integer) RETURNS integer
    LANGUAGE sql
    AS $_$
    SELECT accounts.account_id
	FROM default_accounts INNER JOIN accounts ON default_accounts.account_id = accounts.account_id
	WHERE (default_accounts.use_key_id = $1) AND (default_accounts.org_id = $2);
$_$;


ALTER FUNCTION public.get_default_account_id(integer, integer) OWNER TO postgres;

--
-- Name: get_default_country(integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION get_default_country(integer) RETURNS character
    LANGUAGE sql
    AS $_$
	SELECT default_country_id::varchar(2)
	FROM orgs
	WHERE (org_id = $1);
$_$;


ALTER FUNCTION public.get_default_country(integer) OWNER TO postgres;

--
-- Name: get_default_currency(integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION get_default_currency(integer) RETURNS integer
    LANGUAGE sql
    AS $_$
	SELECT currency_id
	FROM orgs
	WHERE (org_id = $1);
$_$;


ALTER FUNCTION public.get_default_currency(integer) OWNER TO postgres;

--
-- Name: get_employee_tax(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION get_employee_tax(integer, integer) RETURNS double precision
    LANGUAGE plpgsql
    AS $_$
DECLARE
	v_employee_month_id			integer;
	v_period_tax_type_id		integer;
	v_exchange_rate				real;
	v_income					real;
	v_tax_relief				real;
	v_tax						real;
BEGIN

	SELECT employee_tax_types.employee_month_id, period_tax_types.period_tax_type_id, employee_tax_types.exchange_rate
		INTO v_employee_month_id, v_period_tax_type_id, v_exchange_rate
	FROM employee_tax_types INNER JOIN employee_month ON employee_tax_types.employee_month_id = employee_month.employee_month_id
		INNER JOIN period_tax_types ON (employee_month.period_id = period_tax_types.period_id)
			AND (employee_tax_types.tax_type_id = period_tax_types.tax_type_id)
	WHERE (employee_tax_types.employee_tax_type_id	= $1);
	
	IF(v_exchange_rate = 0) THEN v_exchange_rate := 1; END IF;

	IF ($2 = 1) THEN
		v_income := getAdjustment(v_employee_month_id, 1) / v_exchange_rate;
		v_tax := getTax(v_income, v_period_tax_type_id);

	ELSIF ($2 = 2) THEN
		v_income := getAdjustment(v_employee_month_id, 2) / v_exchange_rate;
		v_tax := getTax(v_income, v_period_tax_type_id) - getAdjustment(v_employee_month_id, 4, 25) / v_exchange_rate;

	ELSIF ($2 = 3) THEN
		v_income := getAdjustment(v_employee_month_id, 3) / v_exchange_rate;
		v_tax := getTax(v_income, v_period_tax_type_id);
	
	ELSIF ($2 = 4) THEN
		v_income := getAdjustment(v_employee_month_id, 2) / v_exchange_rate;
		v_tax_relief := getAdjustment(v_employee_month_id, 1) / 100;
		if(v_tax_relief < 16666.67) then v_tax_relief := 16666.67; end if;
		v_tax_relief := v_tax_relief + getAdjustment(v_employee_month_id, 1) / 5;
		v_income := v_income - v_tax_relief;
		v_tax := getTax(v_income, v_period_tax_type_id) - getAdjustment(v_employee_month_id, 4, 25) / v_exchange_rate;
	
	ELSE
		v_tax := 0;
	END IF;

	IF(v_tax is null) THEN
		v_tax := 0;
	ELSIF(v_tax < 0) THEN
		v_tax := 0;
	END IF;

	RETURN v_tax;
END;
$_$;


ALTER FUNCTION public.get_employee_tax(integer, integer) OWNER TO postgres;

--
-- Name: get_end_year(character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION get_end_year(character varying) RETURNS character varying
    LANGUAGE sql
    AS $$
	SELECT '31/12/' || to_char(current_date, 'YYYY'); 
$$;


ALTER FUNCTION public.get_end_year(character varying) OWNER TO postgres;

--
-- Name: get_formula_adjustment(integer, integer, real); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION get_formula_adjustment(integer, integer, real) RETURNS double precision
    LANGUAGE plpgsql
    AS $_$
DECLARE
	v_employee_month_id		integer;
	v_basic_pay				float;
	v_adjustment			float;
BEGIN

	SELECT employee_month.employee_month_id, employee_month.basic_pay INTO v_employee_month_id, v_basic_pay
	FROM employee_month
	WHERE (employee_month.employee_month_id = $1);

	IF ($2 = 1) THEN
		v_adjustment := v_basic_pay * $3;
	ELSE
		v_adjustment := 0;
	END IF;

	IF(v_adjustment is null) THEN
		v_adjustment := 0;
	END IF;

	RETURN v_adjustment;
END;
$_$;


ALTER FUNCTION public.get_formula_adjustment(integer, integer, real) OWNER TO postgres;

--
-- Name: get_intrest_repayment(integer, date); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION get_intrest_repayment(integer, date) RETURNS real
    LANGUAGE sql
    AS $_$
	SELECT CASE WHEN sum(interest_paid) is null THEN 0 ELSE sum(interest_paid) END
	FROM loan_monthly INNER JOIN periods ON loan_monthly.period_id = periods.period_id
	WHERE (loan_monthly.loan_id = $1) AND (periods.start_date < $2);
$_$;


ALTER FUNCTION public.get_intrest_repayment(integer, date) OWNER TO postgres;

--
-- Name: get_leave_approved_balance(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION get_leave_approved_balance(integer, integer) RETURNS real
    LANGUAGE plpgsql
    AS $_$
DECLARE
	reca					RECORD;
	v_months				integer;
	v_leave_starting		date;
	v_leave_carryover		real;
	v_leave_balance			real;
	v_leave_days			real;
	v_leave_work_days		real;
	v_leave_initial			real;
	v_year_leave			real;
BEGIN

	SELECT allowed_leave_days, month_quota, initial_days, maximum_carry 
		INTO reca
	FROM leave_types
	WHERE (leave_type_id = $2);

	SELECT leave_balance, leave_starting INTO v_leave_carryover, v_leave_starting
	FROM employee_leave_types
	WHERE (entity_id = $1) AND (leave_type_id = $2);
	IF(v_leave_carryover is null) THEN v_leave_carryover := 0; END IF;
	IF(v_leave_carryover > reca.maximum_carry) THEN v_leave_carryover := reca.maximum_carry; END IF;
	IF(v_leave_starting is null) THEN v_leave_starting := current_date; END IF;

	v_months := EXTRACT(MONTH FROM CURRENT_TIMESTAMP) - 1;
	v_leave_balance := reca.initial_days + reca.month_quota * v_months;
	if(reca.month_quota = 0)THEN v_leave_balance := reca.allowed_leave_days; END IF;

	IF(reca.maximum_carry = 0)THEN
		SELECT sum(employee_leave.leave_days) INTO v_leave_days
		FROM employee_leave 
		WHERE (entity_id = $1) AND (leave_type_id = $2)
			AND (approve_status = 'Approved')
			AND (EXTRACT(YEAR FROM leave_from) = EXTRACT(YEAR FROM now()));
		IF(v_leave_days is null) THEN v_leave_days := 0; END IF;

		SELECT SUM(CASE WHEN leave_work_days.half_day = true THEN 0.5 ELSE 1 END) INTO v_leave_work_days
		FROM leave_work_days INNER JOIN employee_leave ON leave_work_days.employee_leave_id = employee_leave.employee_leave_id
		WHERE (employee_leave.entity_id = $1) AND (employee_leave.leave_type_id = $2)
			AND (leave_work_days.approve_status = 'Approved')
			AND (EXTRACT(YEAR FROM employee_leave.leave_from) = EXTRACT(YEAR FROM now()));
		IF(v_leave_work_days is null) THEN v_leave_work_days := 0; END IF;
		v_leave_days := v_leave_days - v_leave_work_days;

		IF(v_leave_balance > reca.allowed_leave_days) THEN v_leave_balance := reca.allowed_leave_days; END IF;
		v_leave_balance := v_leave_balance - v_leave_days;
	ELSE
		SELECT sum(employee_leave.leave_days) INTO v_leave_days
		FROM employee_leave 
		WHERE (entity_id = $1) AND (leave_type_id = $2)
			AND (approve_status = 'Approved');
		IF(v_leave_days is null) THEN v_leave_days := 0; END IF;
		
		SELECT sum(employee_leave.leave_days) INTO v_year_leave
		FROM employee_leave 
		WHERE (entity_id = $1) AND (leave_type_id = $2)
			AND (approve_status = 'Approved')
			AND (EXTRACT(YEAR FROM leave_from) = EXTRACT(YEAR FROM now()));
		IF(v_year_leave is null) THEN v_year_leave := 0; END IF;

		SELECT SUM(CASE WHEN leave_work_days.half_day = true THEN 0.5 ELSE 1 END) INTO v_leave_work_days
		FROM leave_work_days INNER JOIN employee_leave ON leave_work_days.employee_leave_id = employee_leave.employee_leave_id
		WHERE (employee_leave.entity_id = $1) AND (employee_leave.leave_type_id = $2)
			AND (leave_work_days.approve_status = 'Approved');
		IF(v_leave_work_days is null) THEN v_leave_work_days := 0; END IF;
		v_leave_days := v_leave_days - v_leave_work_days;
		
		v_leave_initial := v_leave_carryover + (EXTRACT(YEAR FROM now()) - EXTRACT(YEAR FROM v_leave_starting)) * reca.allowed_leave_days;
		IF(EXTRACT(MONTH FROM v_leave_starting) > 1)THEN
			v_leave_initial := v_leave_carryover + (EXTRACT(YEAR FROM now()) - EXTRACT(YEAR FROM v_leave_starting) - 1) * reca.allowed_leave_days;
			IF(reca.month_quota = 0)THEN v_leave_initial := v_leave_initial + (13 - EXTRACT(MONTH FROM v_leave_starting)) * reca.month_quota;
			ELSE v_leave_initial := v_leave_initial + reca.allowed_leave_days;
			END IF;
		END IF;
		v_leave_initial := v_leave_initial - (v_leave_days - v_year_leave);
		IF(v_leave_initial > reca.maximum_carry) THEN v_leave_initial := reca.maximum_carry; END IF;		
		v_leave_balance := v_leave_initial + v_leave_balance - v_year_leave;
	END IF;

	RETURN v_leave_balance;
END;
$_$;


ALTER FUNCTION public.get_leave_approved_balance(integer, integer) OWNER TO postgres;

--
-- Name: get_leave_balance(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION get_leave_balance(integer, integer) RETURNS real
    LANGUAGE plpgsql
    AS $_$
DECLARE
	reca					RECORD;
	v_months				integer;
	v_leave_starting		date;
	v_leave_carryover		real;
	v_leave_balance			real;
	v_leave_days			real;
	v_leave_work_days		real;
	v_leave_initial			real;
	v_year_leave			real;
BEGIN

	SELECT allowed_leave_days, month_quota, initial_days, maximum_carry 
		INTO reca
	FROM leave_types
	WHERE (leave_type_id = $2);

	SELECT leave_balance, leave_starting INTO v_leave_carryover, v_leave_starting
	FROM employee_leave_types
	WHERE (entity_id = $1) AND (leave_type_id = $2);
	IF(v_leave_carryover is null) THEN v_leave_carryover := 0; END IF;
	IF(v_leave_carryover > reca.maximum_carry) THEN v_leave_carryover := reca.maximum_carry; END IF;
	IF(v_leave_starting is null) THEN v_leave_starting := current_date; END IF;

	v_months := EXTRACT(MONTH FROM CURRENT_TIMESTAMP) - 1;
	v_leave_balance := reca.initial_days + reca.month_quota * v_months;
	if(reca.month_quota = 0)THEN v_leave_balance := reca.allowed_leave_days; END IF;

	IF(reca.maximum_carry = 0)THEN
		SELECT sum(employee_leave.leave_days) INTO v_leave_days
		FROM employee_leave 
		WHERE (entity_id = $1) AND (leave_type_id = $2)
			AND (approve_status <> 'Rejected') AND (approve_status <> 'Draft')
			AND (EXTRACT(YEAR FROM leave_from) = EXTRACT(YEAR FROM now()));
		IF(v_leave_days is null) THEN v_leave_days := 0; END IF;

		SELECT SUM(CASE WHEN leave_work_days.half_day = true THEN 0.5 ELSE 1 END) INTO v_leave_work_days
		FROM leave_work_days INNER JOIN employee_leave ON leave_work_days.employee_leave_id = employee_leave.employee_leave_id
		WHERE (employee_leave.entity_id = $1) AND (employee_leave.leave_type_id = $2)
			AND (leave_work_days.approve_status = 'Approved')
			AND (EXTRACT(YEAR FROM employee_leave.leave_from) = EXTRACT(YEAR FROM now()));
		IF(v_leave_work_days is null) THEN v_leave_work_days := 0; END IF;
		v_leave_days := v_leave_days - v_leave_work_days;

		IF(v_leave_balance > reca.allowed_leave_days) THEN v_leave_balance := reca.allowed_leave_days; END IF;
		v_leave_balance := v_leave_balance - v_leave_days;
	ELSE
		SELECT sum(employee_leave.leave_days) INTO v_leave_days
		FROM employee_leave 
		WHERE (entity_id = $1) AND (leave_type_id = $2)
			AND (approve_status <> 'Rejected') AND (approve_status <> 'Draft');
		IF(v_leave_days is null) THEN v_leave_days := 0; END IF;
		
		SELECT sum(employee_leave.leave_days) INTO v_year_leave
		FROM employee_leave 
		WHERE (entity_id = $1) AND (leave_type_id = $2)
			AND (approve_status <> 'Rejected') AND (approve_status <> 'Draft')
			AND (EXTRACT(YEAR FROM leave_from) = EXTRACT(YEAR FROM now()));
		IF(v_year_leave is null) THEN v_year_leave := 0; END IF;

		SELECT SUM(CASE WHEN leave_work_days.half_day = true THEN 0.5 ELSE 1 END) INTO v_leave_work_days
		FROM leave_work_days INNER JOIN employee_leave ON leave_work_days.employee_leave_id = employee_leave.employee_leave_id
		WHERE (employee_leave.entity_id = $1) AND (employee_leave.leave_type_id = $2)
			AND (leave_work_days.approve_status = 'Approved');
		IF(v_leave_work_days is null) THEN v_leave_work_days := 0; END IF;
		v_leave_days := v_leave_days - v_leave_work_days;
		
		v_leave_initial := v_leave_carryover + (EXTRACT(YEAR FROM now()) - EXTRACT(YEAR FROM v_leave_starting)) * reca.allowed_leave_days;
		IF(EXTRACT(MONTH FROM v_leave_starting) > 1)THEN
			v_leave_initial := v_leave_carryover + (EXTRACT(YEAR FROM now()) - EXTRACT(YEAR FROM v_leave_starting) - 1) * reca.allowed_leave_days;
			IF(reca.month_quota = 0)THEN v_leave_initial := v_leave_initial + (13 - EXTRACT(MONTH FROM v_leave_starting)) * reca.month_quota;
			ELSE v_leave_initial := v_leave_initial + reca.allowed_leave_days;
			END IF;
		END IF;
		v_leave_initial := v_leave_initial - (v_leave_days - v_year_leave);
		IF(v_leave_initial > reca.maximum_carry) THEN v_leave_initial := reca.maximum_carry; END IF;		
		v_leave_balance := v_leave_initial + v_leave_balance - v_year_leave;
	END IF;

	RETURN v_leave_balance;
END;
$_$;


ALTER FUNCTION public.get_leave_balance(integer, integer) OWNER TO postgres;

--
-- Name: get_leave_days(date, date, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION get_leave_days(date, date, integer) RETURNS real
    LANGUAGE plpgsql
    AS $_$
DECLARE
	v_day			date;
	v_holiday		integer;
	v_holidays		integer;
	rec 			RECORD;
	ans 			real;
BEGIN
	ans := 0.0;
	v_day := $1;

	SELECT leave_type_id, allowed_leave_days, leave_days_span, month_quota, initial_days, 
		maximum_carry, include_holiday, 
		include_mon, include_tue, include_wed, include_thu, include_fri, include_sat, include_sun
		INTO rec
	FROM leave_types 
	WHERE (leave_type_id = $3);

	v_holidays := 0;

	LOOP
		IF(EXTRACT(isodow FROM v_day)::integer = 1) AND (rec.include_mon = true) THEN ans := ans + 1; END IF;
		IF(EXTRACT(isodow FROM v_day)::integer = 2) AND (rec.include_tue = true) THEN ans := ans + 1; END IF;
		IF(EXTRACT(isodow FROM v_day)::integer = 3) AND (rec.include_wed = true) THEN ans := ans + 1; END IF;
		IF(EXTRACT(isodow FROM v_day)::integer = 4) AND (rec.include_thu = true) THEN ans := ans + 1; END IF;
		IF(EXTRACT(isodow FROM v_day)::integer = 5) AND (rec.include_fri = true) THEN ans := ans + 1; END IF;
		IF(EXTRACT(isodow FROM v_day)::integer = 6) AND (rec.include_sat = true) THEN ans := ans + 1; END IF;
		IF(EXTRACT(isodow FROM v_day)::integer = 7) AND (rec.include_sun = true) THEN ans := ans + 1; END IF;

		IF(rec.include_holiday = false)THEN
			SELECT count(holiday_id) INTO v_holiday
			FROM holidays
			WHERE (holiday_date = v_day);
			v_holidays := v_holidays + v_holiday;
		END IF;

		v_day := v_day + 1;
		EXIT WHEN $2 < v_day;
	END LOOP;

	ans := ans - CAST(v_holidays as real);

	return ans;
END;
$_$;


ALTER FUNCTION public.get_leave_days(date, date, integer) OWNER TO postgres;

--
-- Name: get_leave_taken(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION get_leave_taken(integer, integer) RETURNS real
    LANGUAGE sql
    AS $_$
	SELECT COALESCE(sum(leave_days), 0)
	FROM employee_leave
	WHERE (approve_status = 'Approved') AND (to_char(leave_from, 'YYYY') = to_char(current_date, 'YYYY'))
		AND (entity_id = $1) AND (leave_type_id = $2);
$_$;


ALTER FUNCTION public.get_leave_taken(integer, integer) OWNER TO postgres;

--
-- Name: get_ledger_link(integer, integer, integer, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION get_ledger_link(integer, integer, integer, character varying, character varying) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
DECLARE
	v_ledger_type_id		integer;
	v_account_no			integer;
	v_account_id			integer;
BEGIN

	SELECT ledger_types.ledger_type_id, accounts.account_no INTO v_ledger_type_id, v_account_no
	FROM ledger_types INNER JOIN ledger_links ON ledger_types.ledger_type_id = ledger_links.ledger_type_id
		INNER JOIN accounts ON ledger_types.account_id = accounts.account_id
	WHERE (ledger_links.org_id = $1) AND (ledger_links.link_type = $2) AND (ledger_links.link_id = $3);
	
	IF(v_ledger_type_id is null)THEN
		v_ledger_type_id := nextval('ledger_types_ledger_type_id_seq');
		SELECT accounts.account_id INTO v_account_id
		FROM accounts
		WHERE (accounts.org_id = $1) AND (accounts.account_no::text = $4);
		
		INSERT INTO ledger_types (ledger_type_id, account_id, tax_account_id, org_id,
			ledger_type_name, ledger_posting, expense_ledger, income_ledger)
		VALUES (v_ledger_type_id, v_account_id, v_account_id, $1,
			$5, true, true, false);

		INSERT INTO ledger_links (ledger_type_id, org_id, link_type, link_id)
		VALUES (v_ledger_type_id, $1, $2, $3);
	END IF;
	
	RETURN v_ledger_type_id;
END;
$_$;


ALTER FUNCTION public.get_ledger_link(integer, integer, integer, character varying, character varying) OWNER TO postgres;

--
-- Name: get_loan_period(real, real, integer, real); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION get_loan_period(real, real, integer, real) RETURNS real
    LANGUAGE plpgsql
    AS $_$
DECLARE
	loanbalance real;
	ri real;
BEGIN
	ri := 1 + ($2/1200);
	IF (ri = 1) THEN
		loanbalance := $1;
	ELSE
		loanbalance := $1 * (ri ^ $3) - ($4 * ((ri ^ $3)  - 1) / (ri - 1));
	END IF;
	RETURN loanbalance;
END;
$_$;


ALTER FUNCTION public.get_loan_period(real, real, integer, real) OWNER TO postgres;

--
-- Name: get_open_period(date); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION get_open_period(date) RETURNS integer
    LANGUAGE sql
    AS $_$
	SELECT period_id FROM periods WHERE (start_date <= $1) AND (end_date >= $1)
		AND (opened = true) AND (closed = false); 
$_$;


ALTER FUNCTION public.get_open_period(date) OWNER TO postgres;

--
-- Name: get_payment_period(real, real, real); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION get_payment_period(real, real, real) RETURNS real
    LANGUAGE plpgsql
    AS $_$
DECLARE
	paymentperiod real;
	q real;
BEGIN
	q := $3/1200;
	
	IF ($2 = 0) OR (q = -1) OR ((q * $1) >= $2) THEN
		paymentperiod := 1;
	ELSIF (log(q + 1) = 0) THEN
		paymentperiod := 1;
	ELSE
		paymentperiod := (log($2) - log($2 - (q * $1))) / log(q + 1);
	END IF;

	RETURN paymentperiod;
END;
$_$;


ALTER FUNCTION public.get_payment_period(real, real, real) OWNER TO postgres;

--
-- Name: get_penalty(integer, date); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION get_penalty(integer, date) RETURNS real
    LANGUAGE sql
    AS $_$
	SELECT CASE WHEN sum(penalty) is null THEN 0 ELSE sum(penalty) END
	FROM loan_monthly INNER JOIN periods ON loan_monthly.period_id = periods.period_id
	WHERE (loan_monthly.loan_id = $1) AND (periods.start_date < $2);
$_$;


ALTER FUNCTION public.get_penalty(integer, date) OWNER TO postgres;

--
-- Name: get_period(date); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION get_period(date) RETURNS integer
    LANGUAGE sql
    AS $_$
	SELECT period_id FROM periods WHERE (start_date <= $1) AND (end_date >= $1); 
$_$;


ALTER FUNCTION public.get_period(date) OWNER TO postgres;

--
-- Name: get_phase_email(integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION get_phase_email(integer) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
    myrec	RECORD;
	myemail	varchar(320);
BEGIN
	myemail := null;
	FOR myrec IN SELECT entitys.primary_email
		FROM entitys INNER JOIN entity_subscriptions ON entitys.entity_id = entity_subscriptions.entity_id
		WHERE (entity_subscriptions.entity_type_id = $1) LOOP

		IF (myemail is null) THEN
			IF (myrec.primary_email is not null) THEN
				myemail := myrec.primary_email;
			END IF;
		ELSE
			IF (myrec.primary_email is not null) THEN
				myemail := myemail || ', ' || myrec.primary_email;
			END IF;
		END IF;

	END LOOP;

	RETURN myemail;
END;
$_$;


ALTER FUNCTION public.get_phase_email(integer) OWNER TO postgres;

--
-- Name: get_phase_entitys(integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION get_phase_entitys(integer) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
    myrec			RECORD;
	myentitys		varchar(320);
BEGIN
	myentitys := null;
	FOR myrec IN SELECT entitys.entity_name
		FROM entitys INNER JOIN entity_subscriptions ON entitys.entity_id = entity_subscriptions.entity_id
		WHERE (entity_subscriptions.entity_type_id = $1) LOOP

		IF (myentitys is null) THEN
			IF (myrec.entity_name is not null) THEN
				myentitys := myrec.entity_name;
			END IF;
		ELSE
			IF (myrec.entity_name is not null) THEN
				myentitys := myentitys || ', ' || myrec.entity_name;
			END IF;
		END IF;

	END LOOP;

	RETURN myentitys;
END;
$_$;


ALTER FUNCTION public.get_phase_entitys(integer) OWNER TO postgres;

--
-- Name: get_phase_status(boolean, boolean); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION get_phase_status(boolean, boolean) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	ps		varchar(16);
BEGIN
	ps := 'Draft';
	IF ($1 = true) THEN
		ps := 'Approved';
	END IF;
	IF ($2 = true) THEN
		ps := 'Rejected';
	END IF;

	RETURN ps;
END;
$_$;


ALTER FUNCTION public.get_phase_status(boolean, boolean) OWNER TO postgres;

--
-- Name: get_repayment(real, real, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION get_repayment(real, real, integer) RETURNS real
    LANGUAGE plpgsql
    AS $_$
DECLARE
	repayment real;
	ri real;
BEGIN
	ri := 1 + ($2/1200);
	IF ((ri ^ $3) = 1) THEN
		repayment := $1;
	ELSE
		repayment := $1 * (ri ^ $3) * (ri - 1) / ((ri ^ $3) - 1);
	END IF;
	RETURN repayment;
END;
$_$;


ALTER FUNCTION public.get_repayment(real, real, integer) OWNER TO postgres;

--
-- Name: get_reporting_list(integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION get_reporting_list(integer) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
    myrec	RECORD;
	mylist	varchar(320);
BEGIN
	mylist := null;
	FOR myrec IN SELECT entitys.entity_name
		FROM reporting INNER JOIN entitys ON reporting.report_to_id = entitys.entity_id
		WHERE (reporting.primary_report = true) AND (reporting.entity_id = $1) 
	LOOP

		IF (mylist is null) THEN
			mylist := myrec.entity_name;
		ELSE
			mylist := mylist || ', ' || myrec.entity_name;
		END IF;
	END LOOP;

	RETURN mylist;
END;
$_$;


ALTER FUNCTION public.get_reporting_list(integer) OWNER TO postgres;

--
-- Name: get_review_category(character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION get_review_category(character varying) RETURNS integer
    LANGUAGE sql
    AS $_$
    SELECT review_category_id
	FROM job_reviews
	WHERE (job_review_id = CAST($1 as int));
$_$;


ALTER FUNCTION public.get_review_category(character varying) OWNER TO postgres;

--
-- Name: get_review_entity(character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION get_review_entity(character varying) RETURNS integer
    LANGUAGE sql
    AS $_$
    SELECT entity_id
	FROM job_reviews
	WHERE (job_review_id = CAST($1 as int));
$_$;


ALTER FUNCTION public.get_review_entity(character varying) OWNER TO postgres;

--
-- Name: get_start_year(character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION get_start_year(character varying) RETURNS character varying
    LANGUAGE sql
    AS $$
	SELECT '01/01/' || to_char(current_date, 'YYYY'); 
$$;


ALTER FUNCTION public.get_start_year(character varying) OWNER TO postgres;

--
-- Name: get_total_interest(integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION get_total_interest(integer) RETURNS real
    LANGUAGE sql
    AS $_$
	SELECT CASE WHEN sum(interest_amount) is null THEN 0 ELSE sum(interest_amount) END 
	FROM loan_monthly
	WHERE (loan_id = $1);
$_$;


ALTER FUNCTION public.get_total_interest(integer) OWNER TO postgres;

--
-- Name: get_total_interest(integer, date); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION get_total_interest(integer, date) RETURNS real
    LANGUAGE sql
    AS $_$
	SELECT CASE WHEN sum(interest_amount) is null THEN 0 ELSE sum(interest_amount) END 
	FROM loan_monthly INNER JOIN periods ON loan_monthly.period_id = periods.period_id
	WHERE (loan_monthly.loan_id = $1) AND (periods.start_date < $2);
$_$;


ALTER FUNCTION public.get_total_interest(integer, date) OWNER TO postgres;

--
-- Name: get_total_repayment(integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION get_total_repayment(integer) RETURNS real
    LANGUAGE sql
    AS $_$
	SELECT CASE WHEN sum(repayment + interest_paid + penalty_paid + extra_payment) is null THEN 0 
		ELSE sum(repayment + interest_paid + penalty_paid + extra_payment) END
	FROM loan_monthly
	WHERE (loan_id = $1);
$_$;


ALTER FUNCTION public.get_total_repayment(integer) OWNER TO postgres;

--
-- Name: get_total_repayment(integer, date); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION get_total_repayment(integer, date) RETURNS real
    LANGUAGE sql
    AS $_$
	SELECT CASE WHEN sum(repayment + interest_paid + penalty_paid + extra_payment) is null THEN 0 
		ELSE sum(repayment + interest_paid + penalty_paid + extra_payment) END
	FROM loan_monthly INNER JOIN periods ON loan_monthly.period_id = periods.period_id
	WHERE (loan_monthly.loan_id = $1) AND (periods.start_date < $2);
$_$;


ALTER FUNCTION public.get_total_repayment(integer, date) OWNER TO postgres;

--
-- Name: get_total_repayment(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION get_total_repayment(integer, integer) RETURNS double precision
    LANGUAGE sql
    AS $_$
	SELECT sum(monthly_repayment + loan_intrest)
	FROM vw_loan_payments 
	WHERE (loan_id = $1) and (months <= $2);
$_$;


ALTER FUNCTION public.get_total_repayment(integer, integer) OWNER TO postgres;

--
-- Name: getadjustment(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION getadjustment(integer, integer) RETURNS double precision
    LANGUAGE plpgsql
    AS $_$
DECLARE
	adjustment float;
BEGIN

	IF ($2 = 1) THEN
		SELECT (Basic_Pay + getAdjustment(Employee_Month_ID, 4, 31) + getAdjustment(Employee_Month_ID, 4, 32) 
			+ getAdjustment(Employee_Month_ID, 4, 23)) 
		INTO adjustment
		FROM Employee_Month
		WHERE (Employee_Month_ID = $1);
	ELSIF ($2 = 2) THEN
		SELECT (Basic_Pay + getAdjustment(Employee_Month_ID, 4, 31) + getAdjustment(Employee_Month_ID, 4, 32)
			- getAdjustment(Employee_Month_ID, 4, 23) 
			- getAdjustment(Employee_Month_ID, 4, 12) - getAdjustment(Employee_Month_ID, 4, 24)) 
		INTO adjustment
		FROM Employee_Month
		WHERE (Employee_Month_ID = $1);
	ELSIF ($2 = 3) THEN
		SELECT (Basic_Pay + getAdjustment(Employee_Month_ID, 4, 31) + getAdjustment(Employee_Month_ID, 4, 32)) 
		INTO adjustment
		FROM Employee_Month
		WHERE (Employee_Month_ID = $1);
	ELSE
		adjustment := 0;
	END IF;

	IF(adjustment is null) THEN
		adjustment := 0;
	END IF;

	RETURN adjustment;
END;
$_$;


ALTER FUNCTION public.getadjustment(integer, integer) OWNER TO postgres;

--
-- Name: getadjustment(integer, integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION getadjustment(integer, integer, integer) RETURNS double precision
    LANGUAGE plpgsql
    AS $_$
DECLARE
	adjustment float;
BEGIN

	IF ($3 = 1) THEN
		SELECT SUM(exchange_rate * amount) INTO adjustment
		FROM employee_adjustments
		WHERE (Employee_Month_ID = $1) AND (adjustment_type = $2);
	ELSIF ($3 = 2) THEN
		SELECT SUM(exchange_rate * amount) INTO adjustment
		FROM employee_adjustments
		WHERE (Employee_Month_ID = $1) AND (adjustment_type = $2) AND (In_payroll = true) AND (Visible = true);
	ELSIF ($3 = 3) THEN
		SELECT SUM(exchange_rate * amount) INTO adjustment
		FROM employee_adjustments
		WHERE (Employee_Month_ID = $1) AND (adjustment_type = $2) AND (In_Tax = true);
	ELSIF ($3 = 4) THEN
		SELECT SUM(exchange_rate * amount) INTO adjustment
		FROM employee_adjustments
		WHERE (Employee_Month_ID = $1) AND (adjustment_type = $2) AND (In_payroll = true);
	ELSIF ($3 = 5) THEN
		SELECT SUM(exchange_rate * amount) INTO adjustment
		FROM employee_adjustments
		WHERE (Employee_Month_ID = $1) AND (adjustment_type = $2) AND (Visible = true);
	ELSIF ($3 = 11) THEN
		SELECT SUM(exchange_rate * (amount + additional)) INTO adjustment
		FROM employee_tax_types
		WHERE (Employee_Month_ID = $1);
	ELSIF ($3 = 12) THEN
		SELECT SUM(exchange_rate * (amount + additional)) INTO adjustment
		FROM employee_tax_types
		WHERE (Employee_Month_ID = $1) AND (In_Tax = true);
	ELSIF ($3 = 14) THEN
		SELECT SUM(exchange_rate * (amount + additional)) INTO adjustment
		FROM employee_tax_types
		WHERE (Employee_Month_ID = $1) AND (Tax_Type_ID = $2);
	ELSIF ($3 = 21) THEN
		SELECT SUM(exchange_rate * amount * adjustment_factor) INTO adjustment
		FROM employee_adjustments
		WHERE (employee_month_id = $1) AND (in_tax = true);
	ELSIF ($3 = 22) THEN
		SELECT SUM(exchange_rate * amount * adjustment_factor) INTO adjustment
		FROM employee_adjustments
		WHERE (Employee_Month_ID = $1) AND (In_payroll = true) AND (Visible = true);
	ELSIF ($3 = 23) THEN
		SELECT SUM(exchange_rate * amount * adjustment_factor) INTO adjustment
		FROM employee_adjustments
		WHERE (employee_month_id = $1) AND (in_tax = true) AND (adjustment_factor = 1);
	ELSIF ($3 = 24) THEN
		SELECT SUM(exchange_rate * tax_reduction_amount) INTO adjustment
		FROM employee_adjustments
		WHERE (employee_month_id = $1) AND (in_tax = true) AND (adjustment_factor = -1);
	ELSIF ($3 = 25) THEN
		SELECT SUM(exchange_rate * tax_relief_amount) INTO adjustment
		FROM employee_adjustments
		WHERE (employee_month_id = $1) AND (in_tax = true) AND (adjustment_factor = -1);
	ELSIF ($3 = 26) THEN
		SELECT SUM(exchange_rate * amount) INTO adjustment
		FROM employee_adjustments
		WHERE (employee_month_id = $1) AND (pension_id is not null) AND (adjustment_type = 2);
	ELSIF ($3 = 27) THEN
		SELECT SUM(employee_adjustments.exchange_rate * employee_adjustments.amount) INTO adjustment
		FROM employee_adjustments INNER JOIN adjustments ON employee_adjustments.adjustment_id = adjustments.adjustment_id
		WHERE (employee_adjustments.employee_month_id = $1) AND (adjustments.adjustment_effect_id = $2);
	ELSIF ($3 = 28) THEN
		SELECT SUM(employee_adjustments.exchange_rate * employee_adjustments.tax_relief_amount) INTO adjustment
		FROM employee_adjustments INNER JOIN adjustments ON employee_adjustments.adjustment_id = adjustments.adjustment_id
		WHERE (employee_adjustments.employee_month_id = $1) AND (adjustments.adjustment_effect_id = $2);
	ELSIF ($3 = 31) THEN
		SELECT SUM(overtime * overtime_rate) INTO adjustment
		FROM employee_overtime
		WHERE (Employee_Month_ID = $1) AND (approve_status = 'Approved');
	ELSIF ($3 = 32) THEN
		SELECT SUM(exchange_rate * tax_amount) INTO adjustment
		FROM employee_per_diem
		WHERE (Employee_Month_ID = $1) AND (approve_status = 'Approved');
	ELSIF ($3 = 33) THEN
		SELECT SUM(exchange_rate * (full_amount -  cash_paid)) INTO adjustment
		FROM Employee_Per_Diem
		WHERE (Employee_Month_ID = $1) AND (approve_status = 'Approved');
	ELSIF ($3 = 34) THEN
		SELECT SUM(exchange_rate * amount) INTO adjustment
		FROM employee_advances
		WHERE (Employee_Month_ID = $1) AND (in_payroll = true);
	ELSIF ($3 = 35) THEN
		SELECT SUM(exchange_rate * amount) INTO adjustment
		FROM advance_deductions
		WHERE (Employee_Month_ID = $1) AND (In_payroll = true);
	ELSIF ($3 = 36) THEN
		SELECT SUM(exchange_rate * paid_amount) INTO adjustment
		FROM employee_adjustments
		WHERE (Employee_Month_ID = $1) AND (In_payroll = true) AND (Visible = true);
	ELSIF ($3 = 37) THEN
		SELECT SUM(exchange_rate * tax_relief_amount) INTO adjustment
		FROM employee_adjustments
		WHERE (Employee_Month_ID = $1);

		IF(adjustment IS NULL)THEN
			adjustment := 0;
		END IF;
	ELSIF ($3 = 41) THEN
		SELECT SUM(exchange_rate * amount) INTO adjustment
		FROM employee_banking
		WHERE (employee_month_id = $1);
	ELSE
		adjustment := 0;
	END IF;

	IF(adjustment is null) THEN
		adjustment := 0;
	END IF;

	RETURN adjustment;
END;
$_$;


ALTER FUNCTION public.getadjustment(integer, integer, integer) OWNER TO postgres;

--
-- Name: getadvancebalance(integer, date); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION getadvancebalance(integer, date) RETURNS double precision
    LANGUAGE plpgsql
    AS $_$
DECLARE
	advance FLOAT;
	paid 	FLOAT;
BEGIN
	SELECT SUM(Amount) INTO advance
	FROM vw_employee_advances
	WHERE (entity_id = $1) AND (start_date <= $2) AND (approve_status = 'Approved');
	IF (advance is null) THEN advance := 0; END IF;
	
	SELECT SUM(Amount) INTO paid
	FROM vw_advance_deductions
	WHERE (entity_id = $1) AND (start_date <= $2);
	IF (paid is null) THEN paid := 0; END IF;

	advance := advance - paid;

	RETURN advance;
END;
$_$;


ALTER FUNCTION public.getadvancebalance(integer, date) OWNER TO postgres;

--
-- Name: getcomputedreviewpoints(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION getcomputedreviewpoints(v_type integer, v_job_review_id integer) RETURNS double precision
    LANGUAGE plpgsql
    AS $$
DECLARE
	v_points double precision;
BEGIN
	IF(v_type = 1) THEN
		SELECT SUM((vw_evaluation_objectives.objective_ps/100) * vw_evaluation_objectives.points)  INTO v_points
		FROM job_reviews INNER JOIN vw_evaluation_objectives
		ON job_reviews.job_review_id = vw_evaluation_objectives.job_review_id

		WHERE (job_reviews.job_review_id =v_job_review_id)
		AND (EXTRACT(YEAR FROM vw_evaluation_objectives.date_set) = EXTRACT(YEAR FROM job_reviews.review_date));
	ELSE
		SELECT SUM((vw_evaluation_objectives.objective_ps/100) * vw_evaluation_objectives.reviewer_points) INTO  v_points
		FROM job_reviews INNER JOIN vw_evaluation_objectives
		ON job_reviews.job_review_id = vw_evaluation_objectives.job_review_id

		WHERE (job_reviews.job_review_id = v_job_review_id)
		AND (EXTRACT(YEAR FROM vw_evaluation_objectives.date_set) = EXTRACT(YEAR FROM job_reviews.review_date));
	END IF;
	
	IF(v_points is null) THEN v_points := 0; END IF;
   
	RETURN v_points;
END;
$$;


ALTER FUNCTION public.getcomputedreviewpoints(v_type integer, v_job_review_id integer) OWNER TO postgres;

--
-- Name: gettax(double precision, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION gettax(double precision, integer) RETURNS double precision
    LANGUAGE plpgsql
    AS $_$
DECLARE
	reca		RECORD;
	tax			REAL;
BEGIN
	SELECT period_tax_type_id, formural, tax_relief, percentage, linear, in_tax, employer, employer_ps INTO reca
	FROM period_tax_types
	WHERE (period_tax_type_id = $2);

	IF(reca.linear = true) THEN
		SELECT SUM(CASE WHEN tax_range < $1 
		THEN (tax_rate / 100) * (tax_range - getTaxMin(tax_range, reca.period_tax_type_id)) 
		ELSE (tax_rate / 100) * ($1 - getTaxMin(tax_range, reca.period_tax_type_id)) END) INTO tax
		FROM period_tax_rates 
		WHERE (getTaxMin(tax_range, reca.period_tax_type_id) <= $1) AND (period_tax_type_id = reca.period_tax_type_id);
	ELSIF(reca.linear = false) THEN 
		SELECT max(tax_rate) INTO tax
		FROM period_tax_rates 
		WHERE (getTaxMin(tax_range, reca.period_tax_type_id) < $1) AND (tax_range >= $1) 
			AND (period_tax_type_id = reca.period_tax_type_id);
	END IF;

	IF (tax is null) THEN
		tax := 0;
	END IF;

	IF (tax > reca.tax_relief) THEN
		tax := tax - reca.tax_relief;
	ELSE
		tax := 0;
	END IF;

	RETURN tax;
END;
$_$;


ALTER FUNCTION public.gettax(double precision, integer) OWNER TO postgres;

--
-- Name: gettaxmin(double precision, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION gettaxmin(double precision, integer) RETURNS double precision
    LANGUAGE sql
    AS $_$
	SELECT CASE WHEN max(tax_range) is null THEN 0 ELSE max(tax_range) END 
	FROM period_tax_rates WHERE (tax_range < $1) AND (period_tax_type_id = $2);
$_$;


ALTER FUNCTION public.gettaxmin(double precision, integer) OWNER TO postgres;

--
-- Name: increment_payroll(character varying, character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION increment_payroll(character varying, character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	v_entity_id		integer;
	v_pay_step_id	integer;
	v_pay_step		integer;
	v_next_step_id	integer;
	v_pay_scale_id	integer;
	v_currency_id	integer;
	v_pay_amount	real;
	msg 			varchar(120);
BEGIN

	v_entity_id := CAST($1 as int);
	
	IF ($3 = '1') THEN
		SELECT pay_scale_steps.pay_scale_step_id, pay_scale_steps.pay_amount, pay_scales.currency_id
			INTO v_pay_step_id, v_pay_amount, v_currency_id
		FROM employees INNER JOIN pay_scale_steps ON employees.pay_scale_step_id = pay_scale_steps.pay_scale_step_id
			INNER JOIN pay_scales ON pay_scale_steps.pay_scale_id = pay_scales.pay_scale_id
		WHERE employees.entity_id = v_entity_id;
		
		IF((v_pay_amount is not null) AND (v_currency_id is not null))THEN
			UPDATE employees SET basic_salary = v_pay_amount, currency_id = v_currency_id
			WHERE entity_id = v_entity_id;
		END IF;

		msg := 'Updated the pay';
	ELSIF ($3 = '2') THEN
		SELECT pay_scale_steps.pay_scale_step_id, pay_scale_steps.pay_scale_id, pay_scale_steps.pay_step
			INTO v_pay_step_id, v_pay_scale_id, v_pay_step
		FROM employees INNER JOIN pay_scale_steps ON employees.pay_scale_step_id = pay_scale_steps.pay_scale_step_id
		WHERE employees.entity_id = v_entity_id;
		
		SELECT pay_scale_steps.pay_scale_step_id INTO v_next_step_id
		FROM pay_scale_steps
		WHERE (pay_scale_steps.pay_scale_id = v_pay_scale_id) AND (pay_scale_steps.pay_step = v_pay_step + 1);
		
		IF(v_next_step_id is not null)THEN
			UPDATE employees SET pay_scale_step_id = v_next_step_id
			WHERE entity_id = v_entity_id;
		END IF;

		msg := 'Pay step incremented';
	END IF;

	RETURN msg;
END;
$_$;


ALTER FUNCTION public.increment_payroll(character varying, character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: ins_address(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION ins_address() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	v_address_id		integer;
BEGIN
	SELECT address_id INTO v_address_id
	FROM address WHERE (is_default = true)
		AND (table_name = NEW.table_name) AND (table_id = NEW.table_id) AND (address_id <> NEW.address_id);

	IF(NEW.is_default = true) AND (v_address_id is not null) THEN
		RAISE EXCEPTION 'Only one default Address allowed.';
	ELSIF (NEW.is_default = false) AND (v_address_id is null) THEN
		NEW.is_default := true;
	END IF;

	RETURN NEW;
END;
$$;


ALTER FUNCTION public.ins_address() OWNER TO postgres;

--
-- Name: ins_applicants(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION ins_applicants() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	v_org_id				integer;
	v_entity_id				integer;
	v_entity_type_id		integer;
BEGIN
	IF (TG_OP = 'INSERT') THEN
		
		IF(NEW.entity_id IS NULL) THEN
			SELECT entity_id INTO v_entity_id
			FROM entitys
			WHERE (trim(lower(user_name)) = trim(lower(NEW.applicant_email)));
				
			IF(v_entity_id is null)THEN
				v_org_id := NEW.org_id;
				IF(v_org_id is null)THEN
					SELECT min(org_id) INTO v_org_id
					FROM orgs WHERE (is_default = true);
				END IF;
				
				SELECT entity_type_id INTO v_entity_type_id
				FROM entity_types 
				WHERE (org_id = v_org_id) AND (use_key_id = 4);

				NEW.entity_id := nextval('entitys_entity_id_seq');

				INSERT INTO entitys (entity_id, org_id, entity_type_id, use_key_id,
					entity_name, User_name, 
					primary_email, primary_telephone, function_role)
				VALUES (NEW.entity_id, v_org_id, v_entity_type_id, 4, 
					(NEW.Surname || ' ' || NEW.First_name || ' ' || COALESCE(NEW.Middle_name, '')),
					lower(NEW.applicant_email), lower(NEW.applicant_email), NEW.applicant_phone, 'applicant');
			ELSE
				RAISE EXCEPTION 'The username exists use a different one or reset password for the current one';
			END IF;
		END IF;

		INSERT INTO sys_emailed (sys_email_id, table_id, table_name)
		VALUES (1, NEW.entity_id, 'applicant');
	ELSIF (TG_OP = 'UPDATE') THEN
		UPDATE entitys  SET entity_name = (NEW.Surname || ' ' || NEW.First_name || ' ' || COALESCE(NEW.Middle_name, ''))
		WHERE entity_id = NEW.entity_id;
	END IF;

	RETURN NEW;
END;
$$;


ALTER FUNCTION public.ins_applicants() OWNER TO postgres;

--
-- Name: ins_applications(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION ins_applications() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	typeid	integer;
BEGIN
	
	IF ((NEW.entity_id is null) AND (NEW.employee_id is not null)) THEN
		NEW.entity_id := NEW.employee_id;
		NEW.approve_status := 'Completed';
	END IF;

	RETURN NEW;
END;
$$;


ALTER FUNCTION public.ins_applications() OWNER TO postgres;

--
-- Name: ins_applications(character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION ins_applications(character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	v_entity_id				integer;
	v_application_id		integer;
	v_sys_email_id			integer;
	v_address				integer;
	c_education_id			integer;
	c_referees				integer;
	c_files					integer;
	reca					RECORD;
	msg 					varchar(120);
BEGIN
	SELECT application_id INTO v_application_id
	FROM applications 
	WHERE (intake_id = $1::int) AND (entity_id = $2::int);
	
	SELECT org_id, entity_id, currency_id, previous_salary, expected_salary INTO reca
	FROM applicants
	WHERE (entity_id = $2::int);
	v_entity_id := reca.entity_id;
	IF(reca.entity_id is null) THEN
		SELECT org_id, entity_id, currency_id, basic_salary as previous_salary, basic_salary as expected_salary INTO reca
		FROM employees
		WHERE (entity_id = $2::int);
		v_entity_id := reca.entity_id;
	END IF;
	
	SELECT count(address_id) INTO v_address
	FROM vw_address
	WHERE (table_name = 'applicant') AND (is_default = true) AND (table_id  = v_entity_id);
	IF(v_address is null) THEN v_address = 0; END IF;
	
	SELECT count(education_id) INTO c_education_id
	FROM education
	WHERE (entity_id  = v_entity_id);
	IF(c_education_id is null) THEN c_education_id = 0; END IF;
	
	SELECT count(address_id) INTO c_referees
	FROM vw_referees
	WHERE (table_id  = v_entity_id);
	IF(c_referees is null) THEN c_referees = 0; END IF;
	
	SELECT count(sys_file_id) INTO c_files
	FROM sys_files
	WHERE (table_id  = v_entity_id);
	IF(c_files is null) THEN c_files = 0; END IF;

	IF v_application_id is not null THEN
		msg := 'There is another application for the post.';
		RAISE EXCEPTION '%', msg;
	ELSIF (reca.previous_salary is null) OR (reca.expected_salary is null) THEN
		msg := 'Kindly indicate your previous and expected salary';
		RAISE EXCEPTION '%', msg;
	ELSIF (v_address < 1) THEN
		msg := 'You need to have at least one full address added';
		RAISE EXCEPTION '%', msg;
	ELSIF (c_education_id < 2) THEN
		msg := 'You need to have at least two education levels added';
		RAISE EXCEPTION '%', msg;
	ELSIF (c_referees < 3) THEN
		msg := 'You need to have at least three referees added';
		RAISE EXCEPTION '%', msg;
	ELSIF (c_files < 2) THEN
		msg := 'CV and Cover Letter MUST be uploaded';
		RAISE EXCEPTION '%', msg;
	ELSE
		v_application_id := nextval('applications_application_id_seq');
		INSERT INTO applications (application_id, intake_id, org_id, entity_id, currency_id, previous_salary, expected_salary, approve_status)
		VALUES (v_application_id, $1::int, reca.org_id, reca.entity_id, reca.currency_id, reca.previous_salary, reca.expected_salary, 'Completed');
		
		SELECT sys_email_id INTO v_sys_email_id FROM sys_emails
		WHERE (use_type = 10) AND (org_id = reca.org_id);
		
		INSERT INTO sys_emailed (sys_email_id, org_id, table_id, table_name, email_type)
		VALUES (v_sys_email_id, reca.org_id, v_application_id, 'applications', 10);
		
		msg := 'Added Job application';
	END IF;

	return msg;
END;
$_$;


ALTER FUNCTION public.ins_applications(character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: ins_approvals(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION ins_approvals() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	reca	RECORD;
BEGIN

	IF (NEW.forward_id is not null) THEN
		SELECT workflow_phase_id, org_entity_id, app_entity_id, approval_level, table_name, table_id INTO reca
		FROM approvals
		WHERE (approval_id = NEW.forward_id);

		NEW.workflow_phase_id := reca.workflow_phase_id;
		NEW.approval_level := reca.approval_level;
		NEW.table_name := reca.table_name;
		NEW.table_id := reca.table_id;
		NEW.approve_status := 'Completed';
	END IF;

	RETURN NEW;
END;
$$;


ALTER FUNCTION public.ins_approvals() OWNER TO postgres;

--
-- Name: ins_asset_valuations(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION ins_asset_valuations() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
	NEW.value_change = NEW.asset_value - get_asset_value(NEW.asset_id, NEW.valuation_year);
	RETURN NEW;
END;
$$;


ALTER FUNCTION public.ins_asset_valuations() OWNER TO postgres;

--
-- Name: ins_budget(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION ins_budget() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN

	INSERT INTO pc_allocations (period_id, department_id, org_id)
	SELECT NEW.period_id, department_id, org_id
	FROM departments
	WHERE (departments.active = true) AND (departments.petty_cash = true) AND (departments.org_id = NEW.org_id);

	INSERT INTO pc_budget (	pc_allocation_id, org_id, pc_item_id, budget_units, budget_price)
	SELECT pc_allocations.pc_allocation_id, pc_allocations.org_id,
		pc_items.pc_item_id, pc_items.default_units, pc_items.default_price
	FROM pc_allocation CROSS JOIN pc_items
	WHERE (pc_allocation.period_id = NEW.period_id) AND (pc_allocation.org_id = NEW.org_id)
		AND (pc_items.default_units > 0);
	
	RETURN NULL;
END;
$$;


ALTER FUNCTION public.ins_budget() OWNER TO postgres;

--
-- Name: ins_employee_adjustments(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION ins_employee_adjustments() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	v_formural					varchar(430);
	v_tax_relief_ps				float;
	v_tax_reduction_ps			float;
	v_tax_max_allowed			float;
BEGIN
	IF((NEW.Amount = 0) AND (NEW.paid_amount <> 0))THEN
		NEW.Amount = NEW.paid_amount / 0.7;
	END IF;
	
	IF(NEW.exchange_rate is null) THEN NEW.exchange_rate = 1; END IF;
	IF(NEW.exchange_rate = 0) THEN NEW.exchange_rate = 1; END IF;

	SELECT adjustment_type, formural INTO NEW.adjustment_type, v_formural
	FROM adjustments 
	WHERE (adjustments.adjustment_id = NEW.adjustment_id);
	
	IF(NEW.adjustment_type = 2)THEN
		NEW.adjustment_factor = -1;
	END IF;
	
	IF(NEW.Amount = 0) and (v_formural is not null)THEN
		EXECUTE 'SELECT ' || v_formural || ' FROM employee_month WHERE employee_month_id = ' || NEW.employee_month_id
		INTO NEW.Amount;
		NEW.Amount := NEW.Amount / NEW.exchange_rate;
	END IF;

	IF(NEW.in_tax = true)THEN
		SELECT tax_reduction_ps, tax_relief_ps, tax_max_allowed INTO v_tax_reduction_ps, v_tax_relief_ps, v_tax_max_allowed
		FROM adjustments
		WHERE (adjustments.adjustment_id = NEW.adjustment_id);

		IF(v_tax_reduction_ps is null)THEN
			NEW.tax_reduction_amount := 0;
		ELSE
			NEW.tax_reduction_amount := NEW.amount * v_tax_reduction_ps / 100;
			NEW.tax_reduction_amount := NEW.tax_reduction_amount;
		END IF;

		IF(v_tax_relief_ps is null)THEN
			NEW.tax_relief_amount := 0;
		ELSE
			NEW.tax_relief_amount := NEW.amount * v_tax_relief_ps / 100;
			NEW.tax_relief_amount := NEW.tax_relief_amount;
		END IF;

		IF(v_tax_max_allowed is not null)THEN
			IF(NEW.tax_reduction_amount > v_tax_max_allowed)THEN
				NEW.tax_reduction_amount := v_tax_max_allowed;
			END IF;
			IF(NEW.tax_relief_amount > v_tax_max_allowed)THEN
				NEW.tax_relief_amount := v_tax_max_allowed;
			END IF;
		END IF;
	ELSE
		NEW.tax_relief_amount := 0;
		NEW.tax_reduction_amount := 0;
	END IF;
	
	RETURN NEW;
END;
$$;


ALTER FUNCTION public.ins_employee_adjustments() OWNER TO postgres;

--
-- Name: ins_employee_advances(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION ins_employee_advances() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	v_period_id			integer;
BEGIN

	IF(NEW.pay_upto is null)THEN
		NEW.pay_upto := current_date;
	END IF;
	IF(NEW.payment_amount is null)THEN
		NEW.payment_amount := NEW.amount;
		NEW.pay_period := 1;
	END IF;

	IF((NEW.approve_status = 'Approved') AND (OLD.approve_status = 'Completed'))THEN
		SELECT max(period_id) INTO v_period_id
		FROM periods
		WHERE (closed = false);
		
		SELECT max(employee_month_id) INTO NEW.employee_month_id
		FROM employee_month
		WHERE (period_id = v_period_id) AND (entity_id = NEW.entity_id);
		
		IF(v_period_id is null)THEN
			RAISE EXCEPTION 'You need to have the current period approved';
		ELSIF(NEW.employee_month_id is null)THEN
			RAISE EXCEPTION 'You need to have the staff in the current active month';
		END IF;
	END IF;

	RETURN NEW;
END;
$$;


ALTER FUNCTION public.ins_employee_advances() OWNER TO postgres;

--
-- Name: ins_employee_leave(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION ins_employee_leave() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
	
	IF(NEW.leave_to < NEW.leave_from)THEN
		RAISE EXCEPTION 'Check on your leave dates.';
	END IF;
	IF(NEW.start_half_day = true) AND (NEW.end_half_day = true) AND (NEW.leave_to = NEW.leave_from)THEN
		RAISE EXCEPTION 'The leave half days cannot be done on the same day';
	END IF;

	IF(NEW.approve_status = 'Draft') OR (NEW.leave_days is null)THEN
		NEW.leave_days := get_leave_days(NEW.leave_from, NEW.leave_to, NEW.leave_type_id);

		IF(NEW.start_half_day = true)THEN NEW.leave_days := NEW.leave_days - 0.5; END IF;
		IF(NEW.end_half_day = true)THEN NEW.leave_days := NEW.leave_days - 0.5; END IF;
	END IF;

	RETURN NEW;
END;
$$;


ALTER FUNCTION public.ins_employee_leave() OWNER TO postgres;

--
-- Name: ins_employee_leave_types(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION ins_employee_leave_types() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	reca					RECORD;
	v_months				integer;
BEGIN

	SELECT allowed_leave_days, month_quota, initial_days, maximum_carry INTO reca
	FROM leave_types
	WHERE (leave_type_id = NEW.leave_type_id);

	IF(reca.month_quota > 0)THEN
		v_months := EXTRACT(MONTH FROM NEW.leave_starting) - 1;
		NEW.leave_balance := reca.month_quota * v_months * -1;
	END IF;

	RETURN NEW;
END;
$$;


ALTER FUNCTION public.ins_employee_leave_types() OWNER TO postgres;

--
-- Name: ins_employee_limit(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION ins_employee_limit() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	v_employee_count	integer;
	v_employee_limit	integer;
BEGIN

	SELECT count(entity_id) INTO v_employee_count
	FROM employees
	WHERE (org_id = NEW.org_id);
	
	SELECT employee_limit INTO v_employee_limit
	FROM orgs
	WHERE (org_id = NEW.org_id);
	
	IF(v_employee_count > v_employee_limit)THEN
		RAISE EXCEPTION 'You have reached the maximum staff limit, request for a quite for more';
	END IF;

	RETURN NEW;
END;
$$;


ALTER FUNCTION public.ins_employee_limit() OWNER TO postgres;

--
-- Name: ins_employee_month(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION ins_employee_month() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN

	SELECT exchange_rate INTO NEW.exchange_rate
	FROM currency_rates
	WHERE (currency_rate_id = 
		(SELECT MAX(currency_rate_id)
		FROM currency_rates
		WHERE (currency_id = NEW.currency_id) AND (org_id = NEW.org_id)
			AND (exchange_date < CURRENT_DATE)));
		
	IF(NEW.exchange_rate is null)THEN NEW.exchange_rate := 1; END IF;	

	RETURN NEW;
END;
$$;


ALTER FUNCTION public.ins_employee_month() OWNER TO postgres;

--
-- Name: ins_employees(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION ins_employees() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	v_entity_type_id	integer;
	v_use_type			integer;
	v_org_sufix 		varchar(4);
	v_first_password	varchar(12);
	v_user_count		integer;
	v_user_name			varchar(120);
BEGIN
	IF (TG_OP = 'INSERT') THEN
		IF(NEW.entity_id IS NULL) THEN
			SELECT org_sufix INTO v_org_sufix
			FROM orgs WHERE (org_id = NEW.org_id);
			
			IF(v_org_sufix is null)THEN v_org_sufix := ''; END IF;

			NEW.entity_id := nextval('entitys_entity_id_seq');

			IF(NEW.employee_id is null) THEN
				NEW.employee_id := NEW.entity_id;
			END IF;
			
			SELECT entity_type_id INTO v_entity_type_id
			FROM entity_types 
			WHERE (org_id = NEW.org_id) AND (use_key_id = 1);

			v_first_password := first_password();
			v_user_name := lower(v_org_sufix || '.' || NEW.First_name || '.' || NEW.Surname);

			SELECT count(entity_id) INTO v_user_count
			FROM entitys
			WHERE (org_id = NEW.org_id) AND (user_name = v_user_name);
			IF(v_user_count > 0) THEN v_user_name := v_user_name || v_user_count::varchar; END IF;

			INSERT INTO entitys (entity_id, org_id, entity_type_id, use_key_id,
				entity_name, user_name, function_role, 
				first_password, entity_password)
			VALUES (NEW.entity_id, NEW.org_id, v_entity_type_id, 1, 
				(NEW.Surname || ' ' || NEW.First_name || ' ' || COALESCE(NEW.Middle_name, '')),
				v_user_name, 'staff',
				v_first_password, md5(v_first_password));
				
			INSERT INTO sys_emailed (org_id, sys_email_id, table_id, table_name)
			SELECT org_id, sys_email_id, NEW.entity_id, 'entitys'
			FROM sys_emails
			WHERE (use_type = 3) AND (org_id = NEW.org_id);
		END IF;

		v_use_type := 2;
		IF(NEW.gender = 'M')THEN v_use_type := 3; END IF;

		INSERT INTO employee_leave_types (entity_id, org_id, leave_type_id, leave_balance)
		SELECT NEW.entity_id, NEW.org_id, leave_type_id, 0
		FROM leave_types
		WHERE (org_id = NEW.org_id) AND ((use_type = 1) OR (use_type = v_use_type));
	ELSIF (TG_OP = 'UPDATE') THEN
		UPDATE entitys  SET entity_name = (NEW.Surname || ' ' || NEW.First_name || ' ' || COALESCE(NEW.Middle_name, ''))
		WHERE entity_id = NEW.entity_id;
	END IF;

	RETURN NEW;
END;
$$;


ALTER FUNCTION public.ins_employees() OWNER TO postgres;

--
-- Name: ins_entitys(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION ins_entitys() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
	IF(NEW.entity_type_id is not null) THEN
		INSERT INTO Entity_subscriptions (org_id, entity_type_id, entity_id, subscription_level_id)
		VALUES (NEW.org_id, NEW.entity_type_id, NEW.entity_id, 0);
	END IF;

	RETURN NULL;
END;
$$;


ALTER FUNCTION public.ins_entitys() OWNER TO postgres;

--
-- Name: ins_entry_form(character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION ins_entry_form(character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	rec 		RECORD;
	vorgid		integer;
	formName 	varchar(120);
	msg 		varchar(120);
BEGIN
	SELECT entry_form_id, org_id INTO rec
	FROM entry_forms 
	WHERE (form_id = CAST($1 as int)) AND (entity_ID = CAST($2 as int))
		AND (approve_status = 'Draft');

	SELECT form_name, org_id INTO formName, vorgid
	FROM forms WHERE (form_id = CAST($1 as int));

	IF rec.entry_form_id is null THEN
		INSERT INTO entry_forms (form_id, entity_id, org_id) 
		VALUES (CAST($1 as int), CAST($2 as int), vorgid);
		msg := 'Added Form : ' || formName;
	ELSE
		msg := 'There is an incomplete form : ' || formName;
	END IF;

	return msg;
END;
$_$;


ALTER FUNCTION public.ins_entry_form(character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: ins_entry_forms(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION ins_entry_forms() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	reca		RECORD;
BEGIN
	
	SELECT default_values, default_sub_values INTO reca
	FROM forms
	WHERE (form_id = NEW.form_id);
	
	NEW.answer := reca.default_values;
	NEW.sub_answer := reca.default_sub_values;

	RETURN NEW;
END;
$$;


ALTER FUNCTION public.ins_entry_forms() OWNER TO postgres;

--
-- Name: ins_fields(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION ins_fields() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	v_ord	integer;
BEGIN
	IF(NEW.field_order is null) THEN
		SELECT max(field_order) INTO v_ord
		FROM fields
		WHERE (form_id = NEW.form_id);

		IF (v_ord is null) THEN
			NEW.field_order := 10;
		ELSE
			NEW.field_order := v_ord + 10;
		END IF;
	END IF;

	RETURN NEW;
END;
$$;


ALTER FUNCTION public.ins_fields() OWNER TO postgres;

--
-- Name: ins_interns(character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION ins_interns(character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	v_intern_id			integer;
	v_org_id			integer;
	v_sys_email_id		integer;
	msg					varchar(120);
BEGIN
	SELECT intern_id INTO v_intern_id FROM interns 
	WHERE (internship_id = $1::int) AND (entity_id = $2::int);
	
	SELECT org_id INTO v_org_id FROM internships 
	WHERE (internship_id = $1::int);

	IF v_intern_id is null THEN
		v_intern_id := nextval('interns_intern_id_seq');
		INSERT INTO interns (intern_id, org_id, internship_id, entity_id, approve_status)
		VALUES (v_intern_id, v_org_id, $1::int, $2::int, 'Completed');
		
		SELECT sys_email_id INTO v_sys_email_id FROM sys_emails
		WHERE (use_type = 11) AND (org_id = v_org_id);
		
		INSERT INTO sys_emailed (sys_email_id, org_id, table_id, table_name, email_type)
		VALUES (v_sys_email_id, v_org_id, v_intern_id, 'interns', 11);
		
		msg := 'Added internship application';
	ELSE
		msg := 'There is another application for the internship.';
	END IF;

	return msg;
END;
$_$;


ALTER FUNCTION public.ins_interns(character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: ins_job_reviews(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION ins_job_reviews() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN

	INSERT INTO evaluation_points (job_review_id, org_id, review_point_id)
	SELECT NEW.job_review_id, NEW.org_id, review_point_id
	FROM review_points
	WHERE (review_category_id = NEW.review_category_id);

	INSERT INTO evaluation_points (job_review_id, org_id, objective_id)
	SELECT NEW.job_review_id, NEW.org_id, objectives.objective_id
	FROM objectives INNER JOIN employee_objectives ON objectives.employee_objective_id = employee_objectives.employee_objective_id
	WHERE (employee_objectives.entity_id = NEW.entity_id)
		AND (objectives.objective_completed = false);

	INSERT INTO evaluation_points (job_review_id, org_id, career_development_id)
	SELECT NEW.job_review_id, NEW.org_id, career_development_id
	FROM career_development
	WHERE (org_id = NEW.org_id);

	RETURN NULL;
END;
$$;


ALTER FUNCTION public.ins_job_reviews() OWNER TO postgres;

--
-- Name: ins_leave_work_days(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION ins_leave_work_days() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN

	SELECT entity_id INTO NEW.entity_id
	FROM employee_leave
	WHERE (employee_leave_id = NEW.employee_leave_id);

	RETURN NEW;
END;
$$;


ALTER FUNCTION public.ins_leave_work_days() OWNER TO postgres;

--
-- Name: ins_loans(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION ins_loans() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	v_default_interest	real;
	v_reducing_balance	boolean;
BEGIN

	SELECT default_interest, reducing_balance INTO v_default_interest, v_reducing_balance
	FROM loan_types 
	WHERE (loan_type_id = NEW.loan_type_id);
		
	IF(NEW.interest is null)THEN
		NEW.interest := v_default_interest;
	END IF;
	IF (NEW.reducing_balance is null)THEN
		NEW.reducing_balance := v_reducing_balance;
	END IF;
	IF(NEW.monthly_repayment is null) THEN
		NEW.monthly_repayment := 0;
	END IF;
	IF (NEW.repayment_period is null)THEN
		NEW.repayment_period := 0;
	END IF;
	IF(NEW.approve_status = 'Draft')THEN
		NEW.repayment_period := 0;
	END IF;
	
	IF(NEW.principle is null)THEN
		RAISE EXCEPTION 'You have to enter a principle amount';
	ELSIF((NEW.monthly_repayment = 0) AND (NEW.repayment_period = 0))THEN
		RAISE EXCEPTION 'You have need to enter either monthly repayment amount or repayment period';
	ELSIF((NEW.monthly_repayment = 0) AND (NEW.repayment_period < 1))THEN
		RAISE EXCEPTION 'The repayment period should be greater than 0';
	ELSIF((NEW.repayment_period = 0) AND (NEW.monthly_repayment < 1))THEN
		RAISE EXCEPTION 'The monthly repayment should be greater than 0';
	ELSIF((NEW.monthly_repayment = 0) AND (NEW.repayment_period > 0))THEN
		NEW.monthly_repayment := NEW.principle / NEW.repayment_period;
	ELSIF((NEW.repayment_period = 0) AND (NEW.monthly_repayment > 0))THEN
		NEW.repayment_period := NEW.principle / NEW.monthly_repayment;
	END IF;
	
	IF(NEW.monthly_repayment > NEW.principle)THEN
		RAISE EXCEPTION 'Repayment should be less than the principal amount';
	END IF;
	
	RETURN NEW;
END;
$$;


ALTER FUNCTION public.ins_loans() OWNER TO postgres;

--
-- Name: ins_objective_details(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION ins_objective_details() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	v_objective_ps				real;
	sum_ods_ps					real;
BEGIN

	IF(NEW.ln_objective_detail_id is not null)THEN
		SELECT objective_id INTO NEW.objective_id
		FROM objective_details
		WHERE objective_detail_id = NEW.ln_objective_detail_id;
	END IF;

	RETURN NEW;
END;
$$;


ALTER FUNCTION public.ins_objective_details() OWNER TO postgres;

--
-- Name: ins_objectives(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION ins_objectives() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	sum_objective_ps			real;
	sum_ods_ps					real;
BEGIN

	SELECT sum(objective_ps) INTO sum_objective_ps
	FROM objectives
	WHERE (employee_objective_id = NEW.employee_objective_id);
	SELECT sum(ods_ps) INTO sum_ods_ps
	FROM objective_details
	WHERE (objective_id = NEW.objective_id) AND (ods_ps is not null);
	
	IF(sum_objective_ps > 100)THEN
		RAISE EXCEPTION 'Your % objectives are more than 100', '%';
	END IF;
	IF(sum_ods_ps > NEW.objective_ps)THEN
		RAISE EXCEPTION 'The % objective details are more than the overall objective details', '%';
	END IF;

	RETURN NEW;
END;
$$;


ALTER FUNCTION public.ins_objectives() OWNER TO postgres;

--
-- Name: ins_password(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION ins_password() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	v_entity_id		integer;
BEGIN

	SELECT entity_id INTO v_entity_id
	FROM entitys
	WHERE (trim(lower(user_name)) = trim(lower(NEW.user_name)))
		AND entity_id <> NEW.entity_id;
		
	IF(v_entity_id is not null)THEN
		RAISE EXCEPTION 'The username exists use a different one or reset password for the current one';
	END IF;

	IF(TG_OP = 'INSERT') THEN
		IF(NEW.first_password is null)THEN
			NEW.first_password := first_password();
		END IF;

		IF (NEW.entity_password is null) THEN
			NEW.entity_password := md5(NEW.first_password);
		END IF;
	ELSIF(OLD.first_password <> NEW.first_password) THEN
		NEW.Entity_password := md5(NEW.first_password);
	END IF;
	
	IF(NEW.user_name is null)THEN
		SELECT org_sufix || '.' || lower(trim(replace(NEW.entity_name, ' ', ''))) INTO NEW.user_name
		FROM orgs
		WHERE org_id = NEW.org_id;
	END IF;

	RETURN NEW;
END;
$$;


ALTER FUNCTION public.ins_password() OWNER TO postgres;

--
-- Name: ins_period_tax_types(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION ins_period_tax_types() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
	INSERT INTO period_tax_rates (org_id, period_tax_type_id, tax_range, tax_rate)
	SELECT NEW.org_id, NEW.period_tax_type_id, tax_range, tax_rate
	FROM tax_rates
	WHERE (tax_type_id = NEW.tax_type_id);

	RETURN NULL;
END;
$$;


ALTER FUNCTION public.ins_period_tax_types() OWNER TO postgres;

--
-- Name: ins_periods(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION ins_periods() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	year_close 		BOOLEAN;
BEGIN
	SELECT year_closed INTO year_close
	FROM fiscal_years
	WHERE (fiscal_year_id = NEW.fiscal_year_id);
	
	IF(TG_OP = 'UPDATE')THEN    
		IF (OLD.closed = true) AND (NEW.closed = false) THEN
			NEW.approve_status := 'Draft';
		END IF;
	ELSE
		IF(NEW.gl_payroll_account is null)THEN NEW.gl_payroll_account := get_default_account(27, NEW.org_id); END IF;
		IF(NEW.gl_advance_account is null)THEN NEW.gl_advance_account := get_default_account(28, NEW.org_id); END IF;
	END IF;

	IF (NEW.approve_status = 'Approved') THEN
		NEW.opened = false;
		NEW.activated = false;
		NEW.closed = true;
	END IF;

	IF(year_close = true)THEN
		RAISE EXCEPTION 'The year is closed not transactions are allowed.';
	END IF;

	RETURN NEW;
END;
$$;


ALTER FUNCTION public.ins_periods() OWNER TO postgres;

--
-- Name: ins_productions(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION ins_productions() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
BEGIN

	IF(NEW.product_id = 1)THEN
		UPDATE orgs SET employee_limit = employee_limit + NEW.quantity, expiry_date = NEW.expiry_date
		WHERE org_id = NEW.org_id;
	END IF;

	RETURN NEW;
END;
$$;


ALTER FUNCTION public.ins_productions() OWNER TO postgres;

--
-- Name: ins_projects(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION ins_projects() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    myrec RECORD;
	start_days integer;
BEGIN
	start_days := 0;
	FOR myrec IN SELECT entity_type_id, Define_phase_name,  
		CAST(((NEW.ending_date - NEW.start_date) * Define_phase_time / 100) as integer) as date_range, 
		(NEW.project_cost * Define_phase_cost / 100) as phase_cost
		FROM Define_Phases
		WHERE (project_type_id = NEW.project_type_id)
		ORDER BY define_phases.phase_order 
	LOOP

		INSERT INTO Phases (org_id, project_id, entity_type_id, phase_name, start_date, end_date, phase_cost)
		VALUES(NEW.org_id, NEW.project_id, myrec.entity_type_id, myrec.Define_phase_name, 
			NEW.start_date + start_days, 
			NEW.start_date + myrec.date_range + start_days, 
			myrec.phase_cost);
		
		start_days := start_days + myrec.date_range + 1;
	END LOOP;

	RETURN NULL;
END;
$$;


ALTER FUNCTION public.ins_projects() OWNER TO postgres;

--
-- Name: ins_sub_fields(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION ins_sub_fields() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	v_ord	integer;
BEGIN
	IF(NEW.sub_field_order is null) THEN
		SELECT max(sub_field_order) INTO v_ord
		FROM sub_fields
		WHERE (field_id = NEW.field_id);

		IF (v_ord is null) THEN
			NEW.sub_field_order := 10;
		ELSE
			NEW.sub_field_order := v_ord + 10;
		END IF;
	END IF;

	RETURN NEW;
END;
$$;


ALTER FUNCTION public.ins_sub_fields() OWNER TO postgres;

--
-- Name: ins_subscriptions(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION ins_subscriptions() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	v_entity_id				integer;
	v_entity_type_id		integer;
	v_org_id				integer;
	v_currency_id			integer;
	v_department_id			integer;
	v_bank_id				integer;
	v_tax_type_id			integer;
	v_workflow_id			integer;
	v_org_suffix			char(2);
	myrec 					RECORD;
BEGIN

	IF (TG_OP = 'INSERT') THEN
		SELECT entity_id INTO v_entity_id
		FROM entitys WHERE lower(trim(user_name)) = lower(trim(NEW.primary_email));

		IF(v_entity_id is null)THEN
			NEW.entity_id := nextval('entitys_entity_id_seq');
			INSERT INTO entitys (entity_id, org_id, use_key_id, entity_type_id, entity_name, User_name, primary_email,  function_role, first_password)
			VALUES (NEW.entity_id, 0, 5, 5, NEW.primary_contact, lower(trim(NEW.primary_email)), lower(trim(NEW.primary_email)), 'subscription', null);
		
			INSERT INTO sys_emailed (sys_email_id, org_id, table_id, table_name)
			VALUES (4, 0, NEW.entity_id, 'subscription');
		
			NEW.approve_status := 'Completed';
		ELSE
			RAISE EXCEPTION 'You already have an account, login and request for services';
		END IF;
	ELSIF(NEW.approve_status = 'Approved')THEN

		NEW.org_id := nextval('orgs_org_id_seq');
		INSERT INTO orgs(org_id, currency_id, org_name, org_long_name, org_sufix, default_country_id, logo)
		VALUES(NEW.org_id, 2, NEW.business_name, NEW.business_name, NEW.org_id, NEW.country_id, 'logo.png');
		
		INSERT INTO address (address_name, sys_country_id, table_name, table_id, premises, town, phone_number, website, is_default) 
		VALUES (NEW.business_name, NEW.country_id, 'orgs', NEW.org_id, NEW.business_address, NEW.city, NEW.telephone, NEW.website, true);
		
		v_currency_id := nextval('currency_currency_id_seq');
		INSERT INTO currency (org_id, currency_id, currency_name, currency_symbol) VALUES (NEW.org_id, v_currency_id, 'Default Currency', 'DC');
		UPDATE orgs SET currency_id = v_currency_id WHERE org_id = NEW.org_id;
		
		INSERT INTO currency_rates (org_id, currency_id, exchange_rate) VALUES (NEW.org_id, v_currency_id, 1);
		
		INSERT INTO entity_types (org_id, entity_type_name, entity_role, use_key_id)
		SELECT NEW.org_id, entity_type_name, entity_role, use_key_id
		FROM entity_types WHERE org_id = 1;
		
		INSERT INTO subscription_levels (org_id, subscription_level_name)
		SELECT NEW.org_id, subscription_level_name
		FROM subscription_levels WHERE org_id = 1;
		
		INSERT INTO jobs_category (org_id, jobs_category)
		SELECT NEW.org_id, jobs_category
		FROM jobs_category WHERE org_id = 1;

		INSERT INTO contract_status (org_id, contract_status_name)
		SELECT NEW.org_id, contract_status_name
		FROM contract_status WHERE org_id = 1;

		INSERT INTO kin_types (org_id, kin_type_name)
		SELECT NEW.org_id, kin_type_name
		FROM kin_types WHERE org_id = 1;

		INSERT INTO education_class (org_id, education_class_name)
		SELECT NEW.org_id, education_class_name
		FROM education_class WHERE org_id = 1 ORDER BY education_class_id;
		
		INSERT INTO adjustments (org_id, currency_id, adjustment_type, adjustment_name, visible, in_tax, account_number)
		SELECT NEW.org_id, v_currency_id, adjustment_type, adjustment_name, visible, in_tax, account_number
		FROM adjustments WHERE org_id = 1;
		
		FOR myrec IN SELECT tax_type_id, use_key_id, tax_type_name, formural, tax_relief, 
			tax_type_order, in_tax, linear, percentage, employer, employer_ps, active,
			account_number, employer_account
			FROM tax_types WHERE org_id = 1 AND ((sys_country_id is null) OR (sys_country_id = NEW.country_id))
			ORDER BY tax_type_id 
		LOOP
			v_tax_type_id := nextval('tax_types_tax_type_id_seq');
			INSERT INTO tax_types (org_id, tax_type_id, use_key_id, tax_type_name, formural, tax_relief, tax_type_order, in_tax, linear, percentage, employer, employer_ps, active, currency_id, account_number, employer_account)
			VALUES (NEW.org_id, v_tax_type_id, myrec.use_key_id, myrec.tax_type_name, myrec.formural, myrec.tax_relief, myrec.tax_type_order, myrec.in_tax, myrec.linear, myrec.percentage, myrec.employer, myrec.employer_ps, myrec.active, v_currency_id, myrec.account_number, myrec.employer_account);
			
			INSERT INTO tax_rates (org_id, tax_type_id, tax_range, tax_rate)
			SELECT NEW.org_id,  v_tax_type_id, tax_range, tax_rate
			FROM tax_rates
			WHERE org_id = 1 and tax_type_id = myrec.tax_type_id;
		END LOOP;
		
		INSERT INTO pay_scales (org_id, pay_scale_name, min_pay, max_pay) VALUES (NEW.org_id, 'Basic', 0, 1000000);
		INSERT INTO pay_groups (org_id, pay_group_name) VALUES (NEW.org_id, 'Default');
		INSERT INTO locations (org_id, location_name) VALUES (NEW.org_id, 'Main office');
		INSERT INTO objective_types (org_id, objective_type_name) VALUES (NEW.org_id, 'General');

		v_department_id := nextval('departments_department_id_seq');
		INSERT INTO departments (org_id, department_id, department_name) VALUES (NEW.org_id, v_department_id, 'Board of Directors');
		INSERT INTO department_roles (org_id, department_id, department_role_name, active) VALUES (NEW.org_id, v_department_id, 'Board of Directors', true);
		
		v_bank_id := nextval('banks_bank_id_seq');
		INSERT INTO banks (org_id, bank_id, bank_name) VALUES (NEW.org_id, v_bank_id, 'Cash');
		INSERT INTO bank_branch (org_id, bank_id, bank_branch_name) VALUES (NEW.org_id, v_bank_id, 'Cash');
		
		INSERT INTO transaction_counters(transaction_type_id, org_id, document_number)
		SELECT transaction_type_id, NEW.org_id, 1
		FROM transaction_types;
		
		INSERT INTO sys_emails (org_id, use_type,  sys_email_name, title, details) 
		SELECT NEW.org_id, use_type, sys_email_name, title, details
		FROM sys_emails
		WHERE org_id = 1;
		
		INSERT INTO accounts_class (org_id, accounts_class_no, chat_type_id, chat_type_name, accounts_class_name)
		SELECT NEW.org_id, accounts_class_no, chat_type_id, chat_type_name, accounts_class_name
		FROM accounts_class
		WHERE org_id = 1;
		
		INSERT INTO account_types (org_id, accounts_class_id, account_type_no, account_type_name)
		SELECT a.org_id, a.accounts_class_id, b.account_type_no, b.account_type_name
		FROM accounts_class a INNER JOIN vw_account_types b ON a.accounts_class_no = b.accounts_class_no
		WHERE (a.org_id = NEW.org_id) AND (b.org_id = 1);
		
		INSERT INTO accounts (org_id, account_type_id, account_no, account_name)
		SELECT a.org_id, a.account_type_id, b.account_no, b.account_name
		FROM account_types a INNER JOIN vw_accounts b ON a.account_type_no = b.account_type_no
		WHERE (a.org_id = NEW.org_id) AND (b.org_id = 1);
		
		INSERT INTO default_accounts (org_id, use_key_id, account_id)
		SELECT c.org_id, a.use_key_id, c.account_id
		FROM default_accounts a INNER JOIN accounts b ON a.account_id = b.account_id
			INNER JOIN accounts c ON b.account_no = c.account_no
		WHERE (a.org_id = 1) AND (c.org_id = NEW.org_id);
		
		INSERT INTO item_category (org_id, item_category_name) VALUES (NEW.org_id, 'Services');
		INSERT INTO item_category (org_id, item_category_name) VALUES (NEW.org_id, 'Goods');

		INSERT INTO item_units (org_id, item_unit_name) VALUES (NEW.org_id, 'Each');
		
		SELECT entity_type_id INTO v_entity_type_id
		FROM entity_types 
		WHERE (org_id = NEW.org_id) AND (use_key_id = 0);
				
		UPDATE entitys SET org_id = NEW.org_id, entity_type_id = v_entity_type_id, function_role='subscription,admin,staff,finance'
		WHERE entity_id = NEW.entity_id;
		
		UPDATE entity_subscriptions SET entity_type_id = v_entity_type_id
		WHERE entity_id = NEW.entity_id;
		
		INSERT INTO workflows (link_copy, org_id, source_entity_id, workflow_name, table_name, approve_email, reject_email) 
		SELECT aa.workflow_id, cc.org_id, cc.entity_type_id, aa.workflow_name, aa.table_name, aa.approve_email, aa.reject_email
		FROM workflows aa INNER JOIN entity_types bb ON aa.source_entity_id = bb.entity_type_id
			INNER JOIN entity_types cc ON bb.use_key_id = cc.use_key_id
		WHERE aa.org_id = 1 AND cc.org_id = NEW.org_id
		ORDER BY aa.workflow_id;

		INSERT INTO workflow_phases (org_id, workflow_id, approval_entity_id, approval_level, return_level, 
			escalation_days, escalation_hours, required_approvals, advice, notice, 
			phase_narrative, advice_email, notice_email) 
		SELECT bb.org_id, bb.workflow_id, dd.entity_type_id, aa.approval_level, aa.return_level, 
			aa.escalation_days, aa.escalation_hours, aa.required_approvals, aa.advice, aa.notice, 
			aa.phase_narrative, aa.advice_email, aa.notice_email
		FROM workflow_phases aa INNER JOIN workflows bb ON aa.workflow_id = bb.link_copy
			INNER JOIN entity_types cc ON aa.approval_entity_id = cc.entity_type_id
			INNER JOIN entity_types dd ON cc.use_key_id = dd.use_key_id
		WHERE aa.org_id = 1 AND bb.org_id = NEW.org_id AND dd.org_id = NEW.org_id;
		
		INSERT INTO sys_emails (org_id, use_type, sys_email_name, title, details)
		SELECT NEW.org_id, use_type, sys_email_name, title, details
		FROM sys_emails
		WHERE org_id = 1;

		INSERT INTO sys_emailed (sys_email_id, org_id, table_id, table_name)
		VALUES (5, NEW.org_id, NEW.entity_id, 'subscription');
	END IF;

	RETURN NEW;
END;
$$;


ALTER FUNCTION public.ins_subscriptions() OWNER TO postgres;

--
-- Name: ins_sys_reset(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION ins_sys_reset() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	v_entity_id			integer;
	v_org_id			integer;
	v_password			varchar(32);
BEGIN
	SELECT entity_id, org_id INTO v_entity_id, v_org_id
	FROM entitys
	WHERE (lower(trim(primary_email)) = lower(trim(NEW.request_email)));

	IF(v_entity_id is not null) THEN
		v_password := upper(substring(md5(random()::text) from 3 for 9));

		UPDATE entitys SET first_password = v_password, entity_password = md5(v_password)
		WHERE entity_id = v_entity_id;

		INSERT INTO sys_emailed (org_id, sys_email_id, table_id, table_name)
		VALUES(v_org_id, 3, v_entity_id, 'entitys');
	END IF;

	RETURN NULL;
END;
$$;


ALTER FUNCTION public.ins_sys_reset() OWNER TO postgres;

--
-- Name: ins_taxes(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION ins_taxes() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
	INSERT INTO default_tax_types (org_id, entity_id, tax_type_id)
	SELECT NEW.org_id, NEW.entity_id, tax_type_id
	FROM tax_types
	WHERE (active = true) AND (org_id = NEW.org_id)
		AND ((use_key_id = 11) OR (use_key_id = 12));

	RETURN NULL;
END;
$$;


ALTER FUNCTION public.ins_taxes() OWNER TO postgres;

--
-- Name: ins_transactions(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION ins_transactions() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	v_counter_id	integer;
	transid 		integer;
	currid			integer;
BEGIN

	IF(TG_OP = 'INSERT') THEN
		SELECT transaction_counter_id, document_number INTO v_counter_id, transid
		FROM transaction_counters 
		WHERE (transaction_type_id = NEW.transaction_type_id) AND (org_id = NEW.org_id);
		UPDATE transaction_counters SET document_number = transid + 1 
		WHERE (transaction_counter_id = v_counter_id);

		NEW.document_number := transid;
		IF(NEW.currency_id is null)THEN
			SELECT currency_id INTO NEW.currency_id
			FROM orgs
			WHERE (org_id = NEW.org_id);
		END IF;
				
		IF(NEW.payment_date is null) AND (NEW.transaction_date is not null)THEN
			NEW.payment_date := NEW.transaction_date;
		END IF;
	ELSE
			
		IF (OLD.journal_id is null) AND (NEW.journal_id is not null) THEN
		ELSIF ((OLD.approve_status != 'Completed') AND (NEW.approve_status = 'Completed')) THEN
			NEW.completed = true;
		ELSIF ((OLD.approve_status = 'Completed') AND (NEW.approve_status != 'Completed')) THEN
		ELSIF ((OLD.is_cleared = false) AND (NEW.is_cleared = true)) THEN
		ELSIF ((OLD.journal_id is not null) AND (OLD.transaction_status_id = NEW.transaction_status_id)) THEN
			RAISE EXCEPTION 'Transaction % is already posted no changes are allowed.', NEW.transaction_id;
		ELSIF ((OLD.transaction_status_id > 1) AND (OLD.transaction_status_id = NEW.transaction_status_id)) THEN
			RAISE EXCEPTION 'Transaction % is already completed no changes are allowed.', NEW.transaction_id;
		END IF;
	END IF;
	
	IF ((NEW.approve_status = 'Draft') AND (NEW.completed = true)) THEN
		NEW.approve_status := 'Completed';
		NEW.transaction_status_id := 2;
	END IF;
	
	IF(NEW.transaction_type_id = 7)THEN
		NEW.tx_type := 1;
	END IF;
	IF(NEW.transaction_type_id = 8)THEN
		NEW.tx_type := -1;
	END IF;

	RETURN NEW;
END;
$$;


ALTER FUNCTION public.ins_transactions() OWNER TO postgres;

--
-- Name: ins_transactions_limit(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION ins_transactions_limit() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	v_transaction_count	integer;
	v_transaction_limit	integer;
BEGIN

	SELECT count(transaction_id) INTO v_transaction_count
	FROM transactions
	WHERE (org_id = NEW.org_id);
	
	SELECT transaction_limit INTO v_transaction_limit
	FROM orgs
	WHERE (org_id = NEW.org_id);
	
	IF(v_transaction_count > v_transaction_limit)THEN
		RAISE EXCEPTION 'You have reached the maximum transaction limit, request for a quite for more';
	END IF;

	RETURN NEW;
END;
$$;


ALTER FUNCTION public.ins_transactions_limit() OWNER TO postgres;

--
-- Name: ins_works(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION ins_works() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN

	IF(NEW.work_weight = 0) AND (NEW.work_pay = 0)THEN
		NEW.work_pay = 1;
	END IF;
	
	SELECT (work_rates.weight_rate * NEW.work_weight + work_rates.work_rate * NEW.work_pay +
		work_rates.overtime_rate * NEW.overtime + work_rates.special_rate * NEW.special_time) INTO NEW.work_amount
	
	FROM work_rates 
	WHERE work_rate_id = NEW.work_rate_id;
	

	RETURN NEW;
END;
$$;


ALTER FUNCTION public.ins_works() OWNER TO postgres;

--
-- Name: insa_employee_objectives(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION insa_employee_objectives() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN

	INSERT INTO objectives (employee_objective_id, org_id, objective_type_id,
		date_set, objective_ps, objective_name, objective_maditory)
	VALUES (NEW.employee_objective_id, NEW.org_id, 1,
		current_date, 0, 'Community service', true);

	RETURN null;
END;
$$;


ALTER FUNCTION public.insa_employee_objectives() OWNER TO postgres;

--
-- Name: insf_leave_types(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION insf_leave_types() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN

	IF(NEW.use_type = 1)THEN
		INSERT INTO employee_leave_types (entity_id, org_id, leave_type_id, leave_balance)
		SELECT entity_id, NEW.org_id, NEW.leave_type_id, 0
		FROM employees
		WHERE (org_id = NEW.org_id) AND (active = true);
	ELSIF(NEW.use_type = 2)THEN
		INSERT INTO employee_leave_types (entity_id, org_id, leave_type_id, leave_balance)
		SELECT entity_id, NEW.org_id, NEW.leave_type_id, 0
		FROM employees
		WHERE (org_id = NEW.org_id) AND (active = true) AND (gender = 'F');
	ELSIF(NEW.use_type = 1)THEN
		INSERT INTO employee_leave_types (entity_id, org_id, leave_type_id, leave_balance)
		SELECT entity_id, NEW.org_id, NEW.leave_type_id, 0
		FROM employees
		WHERE (org_id = NEW.org_id) AND (active = true) AND (gender = 'M');
	END IF;

	RETURN NULL;
END;
$$;


ALTER FUNCTION public.insf_leave_types() OWNER TO postgres;

--
-- Name: job_review_check(character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION job_review_check(character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	rec							RECORD;
	v_approve_status			varchar(16);
	v_rate_objectives			boolean;
	v_self_rating				integer;
	v_objective_ps				real;
	sum_ods_ps					real;
	v_point_check				integer;
	msg 						varchar(120);
BEGIN
	
	SELECT sum(objectives.objective_ps) INTO v_objective_ps
	FROM objectives INNER JOIN evaluation_points ON evaluation_points.objective_id = objectives.objective_id
	WHERE (evaluation_points.job_review_id = CAST($1 as int));
	
	SELECT sum(ods_ps) INTO sum_ods_ps
	FROM objective_details INNER JOIN evaluation_points ON evaluation_points.objective_id = objective_details.objective_id
	WHERE (evaluation_points.job_review_id = CAST($1 as int));
	
	SELECT evaluation_points.evaluation_point_id INTO v_point_check
	FROM objectives INNER JOIN evaluation_points ON evaluation_points.objective_id = objectives.objective_id
	WHERE (evaluation_points.job_review_id = CAST($1 as int))
		AND (objectives.objective_ps > 0) AND (evaluation_points.points = 0);
		
	SELECT job_reviews.self_rating, review_category.rate_objectives, job_reviews.approve_status
		INTO v_self_rating, v_rate_objectives, v_approve_status
	FROM job_reviews INNER JOIN review_category ON job_reviews.review_category_id = review_category.review_category_id
	WHERE (job_reviews.job_review_id = $1::int);
	IF(v_self_rating is null) THEN v_self_rating := 0; END IF;
		
	IF(sum_ods_ps is null)THEN
		sum_ods_ps := 100;
	END IF;
	IF(sum_ods_ps = 0)THEN
		sum_ods_ps := 100;
	END IF;
	
	IF(v_rate_objectives = false)THEN
		v_objective_ps := 100;
		sum_ods_ps := 100;
	END IF;

	IF(v_approve_status <> 'Draft')THEN
		msg := 'The review is already submitted';
	ELSIF(v_objective_ps <> 100)THEN
		msg := 'Objective % must add up to 100';
		RAISE EXCEPTION '%', msg;
	ELSIF(sum_ods_ps <> 100)THEN
		msg := 'Objective details % must add up to 100';
		RAISE EXCEPTION '%', msg;
	ELSIF(v_self_rating = 0) AND (v_rate_objectives = true)THEN
		msg := 'Indicate your self rating';
		RAISE EXCEPTION '%', msg;
	ELSIF(v_point_check is not null)THEN
		msg := 'All objective evaluations points must be between 1 to 4';
		RAISE EXCEPTION '%', msg;
	ELSE
		UPDATE job_reviews SET approve_status = 'Completed'
		WHERE (job_review_id = CAST($1 as int));

		msg := 'Review Applied';
	END IF;

	RETURN msg;
END;
$_$;


ALTER FUNCTION public.job_review_check(character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: leave_aplication(character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION leave_aplication(character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	v_leave_balance		real;
	v_leave_overlap		integer;
	v_approve_status	varchar(16);
	v_table_id			integer;
	rec					RECORD;
	msg 				varchar(120);
BEGIN
	msg := 'Leave applied';

	SELECT leave_types.leave_days_span, employee_leave.entity_id, employee_leave.leave_type_id,
		employee_leave.leave_days, employee_leave.leave_from, employee_leave.leave_to,
		employee_leave.contact_entity_id, employee_leave.narrative
		INTO rec
	FROM leave_types INNER JOIN employee_leave ON leave_types.leave_type_id = employee_leave.leave_type_id
	WHERE (employee_leave.employee_leave_id = CAST($1 as int));

	v_leave_balance := get_leave_balance(rec.entity_id, rec.leave_type_id);

	SELECT count(employee_leave_id) INTO v_leave_overlap
	FROM employee_leave
	WHERE (entity_id = rec.entity_id) AND ((approve_status = 'Completed') OR (approve_status = 'Approved'))
		AND (((leave_from, leave_to) OVERLAPS (rec.leave_from, rec.leave_to)) = true);

	SELECT approve_status INTO v_approve_status
	FROM employee_leave
	WHERE (employee_leave_id = CAST($1 as int));
	
	IF(rec.contact_entity_id is null)THEN
		RAISE EXCEPTION 'You must enter a contact person.';
	ELSIF(v_approve_status <> 'Draft')THEN
		msg := 'Your application is not a draft.';
		RAISE EXCEPTION '%', msg;
	ELSIF(rec.leave_days > rec.leave_days_span)THEN
		msg := 'Days applied for excced the span allowed';
		RAISE EXCEPTION '%', msg;
	ELSIF(v_leave_balance <= 0) THEN
		msg := 'You do not have enough days to apply for this leave';
		RAISE EXCEPTION '%', msg;
	ELSIF(v_leave_overlap > 0) THEN
		msg := 'You have applied for overlaping leave days';
		RAISE EXCEPTION '%', msg;
	ELSE
		UPDATE employee_leave SET approve_status = 'Completed'
		WHERE (employee_leave_id = CAST($1 as int));
		
		SELECT workflow_table_id INTO v_table_id
		FROM employee_leave
		WHERE (employee_leave_id = CAST($1 as int));
		
		UPDATE approvals SET approval_narrative = rec.narrative
		WHERE (table_name = 'employee_leave') AND (table_id = v_table_id);
	END IF;

	return msg;
END;
$_$;


ALTER FUNCTION public.leave_aplication(character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: leave_special(character varying, character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION leave_special(character varying, character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	v_leave_overlap		integer;
	v_approve_status	varchar(16);
	rec					RECORD;
	msg 				varchar(120);
BEGIN

	SELECT approve_status INTO v_approve_status
	FROM employee_leave
	WHERE (employee_leave_id = CAST($1 as int));

	SELECT leave_types.leave_days_span, employee_leave.entity_id, employee_leave.leave_type_id,
		employee_leave.leave_days, employee_leave.leave_from, employee_leave.leave_to
		INTO rec
	FROM leave_types INNER JOIN employee_leave ON leave_types.leave_type_id = employee_leave.leave_type_id
	WHERE (employee_leave.employee_leave_id = CAST($1 as int));

	IF(v_approve_status <> 'Draft')THEN
		msg := 'Your application is not a draft.';
		RAISE EXCEPTION '%', msg;
	ELSIF($3 = '1') THEN
		SELECT count(employee_leave_id) INTO v_leave_overlap
		FROM employee_leave
		WHERE (entity_id = rec.entity_id) AND ((approve_status = 'Completed') OR (approve_status = 'Approved'))
			AND (((leave_from, leave_to) OVERLAPS (rec.leave_from, rec.leave_to)) = true);

		IF(v_leave_overlap > 0) THEN
			msg := 'You have applied for overlaping leave days';
			RAISE EXCEPTION '%', msg;
		ELSE
			UPDATE employee_leave SET special_request = true
			WHERE (employee_leave_id = CAST($1 as int));

			msg := 'Special request send to HR';
		END IF;
	ELSIF($3 = '2') THEN
		UPDATE employee_leave SET approve_status = 'Completed'
		WHERE (employee_leave_id = CAST($1 as int));

		msg := 'Leave applied';
	ELSIF($3 = '3') THEN
		UPDATE employee_leave SET approve_status = 'Rejected', action_date = now()
		WHERE (employee_leave_id = CAST($1 as int));

		msg := 'Leave rejected';
	END IF;

	return msg;
END;
$_$;


ALTER FUNCTION public.leave_special(character varying, character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: loan_aplication(character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION loan_aplication(character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	msg 				varchar(120);
BEGIN
	msg := 'Loan applied';
	
	UPDATE loans SET approve_status = 'Completed'
	WHERE (loan_id = CAST($1 as int)) AND (approve_status = 'Draft');

	return msg;
END;
$_$;


ALTER FUNCTION public.loan_aplication(character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: objectives_review(character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION objectives_review(character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	v_objective_ps		real;
	max_objective_ps	real;
	sum_ods_ps			real;
	rec					RECORD;
	msg 				varchar(120);
BEGIN

	SELECT sum(objectives.objective_ps) INTO v_objective_ps
	FROM objectives
	WHERE (objectives.employee_objective_id = CAST($1 as int));
	SELECT max(objectives.objective_ps) INTO max_objective_ps
	FROM objectives
	WHERE (objectives.employee_objective_id = CAST($1 as int));
	SELECT sum(objective_details.ods_ps) INTO sum_ods_ps
	FROM objective_details INNER JOIN objectives ON objective_details.objective_id = objectives.objective_id
	WHERE (objectives.employee_objective_id = CAST($1 as int));
	
	IF(v_objective_ps is null)THEN
		v_objective_ps := 0;
	END IF;
	IF(max_objective_ps is null)THEN
		max_objective_ps := 0;
	END IF;
	IF(sum_ods_ps is null)THEN
		sum_ods_ps := 100;
	END IF;
	IF(sum_ods_ps = 0)THEN
		sum_ods_ps := 100;
	END IF;

	IF(max_objective_ps > 50)THEN
		msg := 'Objective should not have a % higer than 50';
		RAISE EXCEPTION '%', msg;
	ELSIF(v_objective_ps = 100) AND (sum_ods_ps = 100)THEN
		UPDATE employee_objectives SET approve_status = 'Completed'
		WHERE (employee_objective_id = CAST($1 as int));

		msg := 'Objectives Review Applied';	
	ELSIF(sum_ods_ps <> 100)THEN
		msg := 'Objective details % must add up to 100';
		RAISE EXCEPTION '%', msg;
	ELSE
		msg := 'Objective % must add up to 100';
		RAISE EXCEPTION '%', msg;
	END IF;

	return msg;
END;
$_$;


ALTER FUNCTION public.objectives_review(character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: open_periods(character varying, character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION open_periods(character varying, character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	v_org_id			integer;
	v_period_id			integer;
	msg					varchar(120);
BEGIN

	IF ($3 = '1') THEN
		UPDATE periods SET opened = true WHERE period_id = $1::int;
		msg := 'Period Opened';
	ELSIF ($3 = '2') THEN
		UPDATE periods SET closed = true WHERE period_id = $1::int;
		msg := 'Period Closed';
	ELSIF ($3 = '3') THEN
		UPDATE periods SET activated = true WHERE period_id = $1::int;
		msg := 'Period Activated';
	ELSIF ($3 = '4') THEN
		UPDATE periods SET activated = false WHERE period_id = $1::int;
		msg := 'Period De-activated';
	END IF;

	RETURN msg;
END;
$_$;


ALTER FUNCTION public.open_periods(character varying, character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: payroll_payable(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION payroll_payable(integer, integer) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	v_org_id				integer;
	v_org_name				varchar(50);
	v_org_client_id			integer;
	v_account_id			integer;
	v_entity_type_id		integer;
	v_bank_account_id		integer;
	reca					RECORD;
	msg						varchar(120);
BEGIN

	SELECT orgs.org_id, orgs.org_client_id, orgs.org_name INTO v_org_id, v_org_client_id, v_org_name
	FROM orgs INNER JOIN periods ON orgs.org_id = periods.org_id
	WHERE (periods.period_id = $1);
	
	IF(v_org_client_id is null)THEN
		SELECT account_id INTO v_account_id
		FROM default_accounts 
		WHERE (org_id = v_org_id) AND (use_key_id = 52);
		
		SELECT max(entity_type_id) INTO v_entity_type_id
		FROM entity_types
		WHERE (org_id = v_org_id) AND (use_key_id = 3);
		
		IF((v_account_id is not null) AND (v_entity_type_id is not null))THEN
			v_org_client_id := nextval('entitys_entity_id_seq');
			
			INSERT INTO entitys (entity_id, org_id, entity_type_id, account_id, entity_name, user_name, function_role, use_key_id)
			VALUES (v_org_client_id, v_org_id, v_entity_type_id, v_account_id, v_org_name, lower(trim(v_org_name)), 'supplier', 3);
		END IF;
	END IF;
	
	SELECT bank_account_id INTO v_bank_account_id
	FROM bank_accounts
	WHERE (org_id = v_org_id) AND (is_default = true);
	
	IF((v_org_client_id is not null) AND (v_bank_account_id is not null))THEN
		--- add transactions for banking payments	
		INSERT INTO transactions (transaction_type_id, transaction_status_id, entered_by, tx_type, 
			entity_id, bank_account_id, currency_id, org_id, ledger_type_id,
			exchange_rate, transaction_date, payment_date, transaction_amount, narrative)
		SELECT 21, 1, $2, -1, 
			v_org_client_id, v_bank_account_id, a.currency_id, a.org_id, 
			get_ledger_link(a.org_id, 1, a.pay_group_id, a.gl_payment_account, 'PAYROLL Payments ' || a.pay_group_name),
			a.exchange_rate, a.end_date, a.end_date, sum(a.b_banked),
			'PAYROLL Payments ' || a.pay_group_name
		FROM vw_ems a
		WHERE (a.period_id = $1)
		GROUP BY a.org_id, a.period_id, a.end_date, a.gl_payment_account, a.pay_group_id, a.currency_id, 
			a.exchange_rate, a.pay_group_name;

		--- add transactions for deduction remitance
		INSERT INTO transactions (transaction_type_id, transaction_status_id, entered_by, tx_type, 
			entity_id, bank_account_id, currency_id, org_id, ledger_type_id,
			exchange_rate, transaction_date, payment_date, transaction_amount, narrative)
		SELECT 21, 1, $2, -1, 
			v_org_client_id, v_bank_account_id, a.currency_id, a.org_id, 
			get_ledger_link(a.org_id, 2, a.adjustment_id, a.account_number, 'PAYROLL Deduction ' || a.adjustment_name),
			a.exchange_rate, a.end_date, a.end_date, sum(a.amount),
			'PAYROLL Deduction ' || a.adjustment_name
		FROM vw_employee_adjustments a
		WHERE (a.period_id = $1)
		GROUP BY a.currency_id, a.org_id, a.adjustment_id, a.account_number, a.adjustment_name, 
			a.exchange_rate, a.end_date;
			
		--- add transactions for tax remitance
		INSERT INTO transactions (transaction_type_id, transaction_status_id, entered_by, tx_type, 
			entity_id, bank_account_id, currency_id, org_id, ledger_type_id,
			exchange_rate, transaction_date, payment_date, transaction_amount, narrative)
		SELECT 21, 1, $2, -1, 
			v_org_client_id, v_bank_account_id, a.currency_id, a.org_id, 
			get_ledger_link(a.org_id, 3, a.tax_type_id, a.account_number, 'PAYROLL Tax ' || a.tax_type_name),
			a.exchange_rate, a.end_date, a.end_date, sum(a.amount + a.employer),
			'PAYROLL Tax ' || a.tax_type_name
		FROM vw_employee_tax_types a
		WHERE (a.period_id = $1)
		GROUP BY a.currency_id, a.org_id, a.tax_type_id, a.account_number, a.tax_type_name, 
			a.exchange_rate, a.end_date;
	END IF;
		
	RETURN msg;
END;
$_$;


ALTER FUNCTION public.payroll_payable(integer, integer) OWNER TO postgres;

--
-- Name: post_transaction(character varying, character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION post_transaction(character varying, character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	rec					RECORD;
	v_period_id			int;
	v_journal_id		int;
	msg					varchar(120);
BEGIN
	SELECT org_id, department_id, transaction_id, transaction_type_id, transaction_type_name as tx_name, 
		transaction_status_id, journal_id, gl_bank_account_id, currency_id, exchange_rate,
		transaction_date, transaction_amount, transaction_tax_amount, document_number, 
		credit_amount, debit_amount, entity_account_id, entity_name, approve_status, 
		ledger_account_id, tax_account_id, ledger_posting INTO rec
	FROM vw_transactions
	WHERE (transaction_id = CAST($1 as integer));

	v_period_id := get_open_period(rec.transaction_date);
	IF(v_period_id is null) THEN
		msg := 'No active period to post.';
		RAISE EXCEPTION 'No active period to post.';
	ELSIF(rec.journal_id is not null) THEN
		msg := 'Transaction previously Posted.';
		RAISE EXCEPTION 'Transaction previously Posted.';
	ELSIF(rec.transaction_status_id = 1) THEN
		msg := 'Transaction needs to be completed first.';
		RAISE EXCEPTION 'Transaction needs to be completed first.';
	ELSIF(rec.approve_status != 'Approved') THEN
		msg := 'Transaction is not yet approved.';
		RAISE EXCEPTION 'Transaction is not yet approved.';
	ELSIF((rec.ledger_account_id is not null) AND (rec.ledger_posting = false)) THEN
		msg := 'Transaction not for posting.';
		RAISE EXCEPTION 'Transaction not for posting.';
	ELSE
		v_journal_id := nextval('journals_journal_id_seq');
		INSERT INTO journals (journal_id, org_id, department_id, currency_id, period_id, exchange_rate, journal_date, narrative)
		VALUES (v_journal_id, rec.org_id, rec.department_id, rec.currency_id, v_period_id, rec.exchange_rate, rec.transaction_date, rec.tx_name || ' - posting for ' || rec.document_number);
		
		IF((rec.transaction_type_id = 7) or (rec.transaction_type_id = 8)) THEN
			INSERT INTO gls (org_id, journal_id, account_id, debit, credit, gl_narrative)
			VALUES (rec.org_id, v_journal_id, rec.entity_account_id, rec.debit_amount, rec.credit_amount, rec.tx_name || ' - ' || rec.entity_name);

			INSERT INTO gls (org_id, journal_id, account_id, debit, credit, gl_narrative)
			VALUES (rec.org_id, v_journal_id, rec.gl_bank_account_id, rec.credit_amount, rec.debit_amount, rec.tx_name || ' - ' || rec.entity_name);
		ELSIF((rec.transaction_type_id = 21) or (rec.transaction_type_id = 22)) THEN		
			INSERT INTO gls (org_id, journal_id, account_id, debit, credit, gl_narrative)
			VALUES (rec.org_id, v_journal_id, rec.gl_bank_account_id, rec.credit_amount, rec.debit_amount, rec.tx_name || ' - ' || rec.entity_name);
			
			IF(rec.transaction_tax_amount = 0)THEN
				INSERT INTO gls (org_id, journal_id, account_id, debit, credit, gl_narrative)
				VALUES (rec.org_id, v_journal_id, rec.ledger_account_id, rec.debit_amount, rec.credit_amount, rec.tx_name || ' - ' || rec.entity_name);
			ELSIF(rec.transaction_type_id = 21)THEN
				INSERT INTO gls (org_id, journal_id, account_id, debit, credit, gl_narrative)
				VALUES (rec.org_id, v_journal_id, rec.ledger_account_id, rec.debit_amount - rec.transaction_tax_amount, rec.credit_amount, rec.tx_name || ' - ' || rec.entity_name);
				
				INSERT INTO gls (org_id, journal_id, account_id, debit, credit, gl_narrative)
				VALUES (rec.org_id, v_journal_id, rec.tax_account_id, rec.transaction_tax_amount, 0, rec.tx_name || ' - ' || rec.entity_name);
			ELSE
				INSERT INTO gls (org_id, journal_id, account_id, debit, credit, gl_narrative)
				VALUES (rec.org_id, v_journal_id, rec.ledger_account_id, rec.debit_amount, rec.credit_amount - rec.transaction_tax_amount, rec.tx_name || ' - ' || rec.entity_name);
				
				INSERT INTO gls (org_id, journal_id, account_id, debit, credit, gl_narrative)
				VALUES (rec.org_id, v_journal_id, rec.tax_account_id, 0, rec.transaction_tax_amount, rec.tx_name || ' - ' || rec.entity_name);			
			END IF;
		ELSE
			INSERT INTO gls (org_id, journal_id, account_id, debit, credit, gl_narrative)
			VALUES (rec.org_id, v_journal_id, rec.entity_account_id, rec.debit_amount, rec.credit_amount, rec.tx_name || ' - ' || rec.entity_name);

			INSERT INTO gls (org_id, journal_id, account_id, debit, credit, gl_narrative)
			SELECT org_id, v_journal_id, trans_account_id, full_debit_amount, full_credit_amount, rec.tx_name || ' - ' || item_name
			FROM vw_transaction_details
			WHERE (transaction_id = rec.transaction_id) AND (full_amount > 0);

			INSERT INTO gls (org_id, journal_id, account_id, debit, credit, gl_narrative)
			SELECT org_id, v_journal_id, tax_account_id, tax_debit_amount, tax_credit_amount, rec.tx_name || ' - ' || item_name
			FROM vw_transaction_details
			WHERE (transaction_id = rec.transaction_id) AND (full_tax_amount > 0);
		END IF;

		UPDATE transactions SET journal_id = v_journal_id WHERE (transaction_id = rec.transaction_id);
		msg := process_journal(CAST(v_journal_id as varchar),'0','0');
	END IF;

	return msg;
END;
$_$;


ALTER FUNCTION public.post_transaction(character varying, character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: prev_acct(integer, date); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION prev_acct(integer, date) RETURNS real
    LANGUAGE sql
    AS $_$
    SELECT sum(gls.debit - gls.credit)
	FROM gls INNER JOIN journals ON gls.journal_id = journals.journal_id
	WHERE (gls.account_id = $1) AND (journals.posted = true) 
		AND (journals.journal_date < $2);
$_$;


ALTER FUNCTION public.prev_acct(integer, date) OWNER TO postgres;

--
-- Name: prev_balance(date); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION prev_balance(date) RETURNS real
    LANGUAGE sql
    AS $_$
    SELECT COALESCE(sum(transactions.exchange_rate * transactions.tx_type * transactions.transaction_amount), 0)::real
	FROM transactions
	WHERE (transactions.payment_date < $1) 
		AND (transactions.tx_type is not null);
$_$;


ALTER FUNCTION public.prev_balance(date) OWNER TO postgres;

--
-- Name: prev_base_acct(integer, date); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION prev_base_acct(integer, date) RETURNS real
    LANGUAGE sql
    AS $_$
    SELECT sum(gls.debit * journals.exchange_rate - gls.credit * journals.exchange_rate) 
	FROM gls INNER JOIN journals ON gls.journal_id = journals.journal_id
	WHERE (gls.account_id = $1) AND (journals.posted = true) 
		AND (journals.journal_date < $2);
$_$;


ALTER FUNCTION public.prev_base_acct(integer, date) OWNER TO postgres;

--
-- Name: prev_base_returns(date); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION prev_base_returns(date) RETURNS real
    LANGUAGE sql
    AS $_$
    SELECT COALESCE(sum(base_credit - base_debit), 0)
	FROM vw_gls
	WHERE (chat_type_id > 3) AND (posted = true) AND (journal_date < $1);
$_$;


ALTER FUNCTION public.prev_base_returns(date) OWNER TO postgres;

--
-- Name: prev_clear_balance(date); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION prev_clear_balance(date) RETURNS real
    LANGUAGE sql
    AS $_$
    SELECT COALESCE(sum(transactions.exchange_rate * transactions.tx_type * transactions.transaction_amount), 0)::real
	FROM transactions
	WHERE (transactions.payment_date < $1) AND (transactions.completed = true) 
		AND (transactions.is_cleared = true) AND (transactions.tx_type is not null);
$_$;


ALTER FUNCTION public.prev_clear_balance(date) OWNER TO postgres;

--
-- Name: prev_returns(date); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION prev_returns(date) RETURNS real
    LANGUAGE sql
    AS $_$
    SELECT COALESCE(sum(credit - debit), 0)
	FROM vw_gls
	WHERE (chat_type_id > 3) AND (posted = true) AND (journal_date < $1);
$_$;


ALTER FUNCTION public.prev_returns(date) OWNER TO postgres;

--
-- Name: process_bio_imports1(character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION process_bio_imports1(character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	v_org_id				integer;
	msg		 				varchar(120);
BEGIN

	SELECT org_id INTO v_org_id FROM entitys
	WHERE entity_id = $2::integer;

	INSERT INTO access_logs (access_log_id, entity_id, org_id, log_time, log_name, log_machine, log_access, log_id, log_area, log_in_out)
	SELECT bio_imports1_id, e.entity_id, v_org_id, to_timestamp(col1, 'MM/DD/YYYY hh:MI:SS pm'), col2, col4, col5, col6, col7, col10
	FROM bio_imports1 LEFT JOIN access_logs ON to_timestamp(bio_imports1.col1, 'MM/DD/YYYY hh:MI:SS pm') = access_logs.log_time
		LEFT JOIN employees as e ON trim(bio_imports1.col6) = trim(e.bio_metric_number)
	WHERE access_logs.access_log_id is null
	ORDER BY to_timestamp(col1, 'MM/DD/YYYY hh:MI:SS pm');

	DELETE FROM bio_imports1;

	INSERT INTO attendance (entity_id, org_id, attendance_date, time_in, time_out)
	SELECT entity_id, org_id, log_time::date, min(log_time::time), max(log_time::time)
	FROM access_logs
	WHERE (is_picked = false) AND (entity_id is not null)
	GROUP BY entity_id, org_id, log_time::date
	ORDER BY entity_id, log_time::date;

	UPDATE access_logs SET is_picked = true
	WHERE (is_picked = false) AND (entity_id is not null);

	msg := 'Uploaded the file';
	
	return msg;
END;
$_$;


ALTER FUNCTION public.process_bio_imports1(character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: process_journal(character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION process_journal(character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	rec RECORD;
	msg varchar(120);
BEGIN
	SELECT periods.start_date, periods.end_date, periods.opened, periods.closed, journals.journal_date, journals.posted, 
		sum(debit) as sum_debit, sum(credit) as sum_credit INTO rec
	FROM (periods INNER JOIN journals ON periods.period_id = journals.period_id)
		INNER JOIN gls ON journals.journal_id = gls.journal_id
	WHERE (journals.journal_id = CAST($1 as integer))
	GROUP BY periods.start_date, periods.end_date, periods.opened, periods.closed, journals.journal_date, journals.posted;

	IF(rec.posted = true) THEN
		msg := 'Journal previously Processed.';
	ELSIF((rec.start_date > rec.journal_date) OR (rec.end_date < rec.journal_date)) THEN
		msg := 'Journal date has to be within periods date.';
	ELSIF((rec.opened = false) OR (rec.closed = true)) THEN
		msg := 'Transaction period has to be opened and not closed.';
	ELSIF(rec.sum_debit <> rec.sum_credit) THEN
		msg := 'Cannot process Journal because credits do not equal debits.';
	ELSE
		UPDATE journals SET posted = true WHERE (journals.journal_id = CAST($1 as integer));
		msg := 'Journal Processed.';
	END IF;

	return msg;
END;
$_$;


ALTER FUNCTION public.process_journal(character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: process_ledger(character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION process_ledger(character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	rec						RECORD;
	v_period_id				integer;
	v_journal_id			integer;
	v_account_no			varchar(32);
	ledger_diff				real;
	msg						varchar(120);
BEGIN

	v_period_id := $1::int;

	SELECT periods.period_id, periods.is_posted, periods.opened, periods.closed, periods.end_date,
		orgs.org_id, orgs.currency_id, orgs.payroll_payable
		INTO rec
	FROM periods INNER JOIN orgs ON periods.org_id = orgs.org_id
	WHERE (periods.period_id = v_period_id);

	SELECT abs(sum(dr_amt) - sum(cr_amt)) INTO ledger_diff
	FROM vw_payroll_ledger
	WHERE (period_id = v_period_id);
	
	SELECT vw_payroll_ledger.gl_payroll_account INTO v_account_no
	FROM vw_payroll_ledger LEFT JOIN accounts ON (vw_payroll_ledger.gl_payroll_account = accounts.account_no::text)
		AND (vw_payroll_ledger.org_id = accounts.org_id)
	WHERE (vw_payroll_ledger.period_id = v_period_id) AND (accounts.account_id is null);
	
	IF(rec.is_posted = true)THEN
		msg := 'The payroll for this period is already posted';
	ELSIF(ledger_diff > 1) THEN
		msg := 'The ledger is not balanced';
	ELSIF((rec.opened = false) OR (rec.closed = true)) THEN
		msg := 'Transaction period has to be opened and not closed.';
	ELSIF(v_account_no is not null) THEN
		msg := 'Ensure the accounts match the ledger accounts';
	ELSE
		v_journal_id := nextval('journals_journal_id_seq');
		INSERT INTO journals (journal_id, org_id, currency_id, period_id, exchange_rate, journal_date, narrative)
		VALUES (v_journal_id, rec.org_id, rec.currency_id, rec.period_id, 1, rec.end_date, 'Payroll posting for ' || to_char(rec.end_date, 'MMM YYYY'));

		INSERT INTO gls (org_id, journal_id, account_id, debit, credit, gl_narrative)
		SELECT aa.org_id, v_journal_id, bb.account_id, aa.dr_amt, aa.cr_amt, aa.description
		FROM vw_payroll_ledger aa LEFT JOIN accounts bb ON (aa.gl_payroll_account = bb.account_no::text) AND (aa.org_id = bb.org_id)
		WHERE (aa.period_id = v_period_id);
		
		IF(rec.payroll_payable = true)THEN
			msg := payroll_payable(v_period_id, $2::integer);
		END IF;

		UPDATE periods SET is_posted = true
		WHERE (period_id = v_period_id);

		msg := 'Payroll Ledger Processed';
	END IF;

	RETURN msg;
END;
$_$;


ALTER FUNCTION public.process_ledger(character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: process_loans(character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION process_loans(character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	rec							RECORD;
	v_exchange_rate				real;
	v_employee_adjustment_id	integer;
	msg							varchar(120);
BEGIN
	
	FOR rec IN SELECT vw_loan_monthly.loan_month_id, vw_loan_monthly.loan_id, vw_loan_monthly.entity_id, vw_loan_monthly.period_id, 
		vw_loan_monthly.employee_adjustment_id, vw_loan_monthly.adjustment_id, vw_loan_monthly.loan_balance, 
		vw_loan_monthly.repayment, (vw_loan_monthly.interest_paid + vw_loan_monthly.penalty_paid) as total_interest,
		(vw_loan_monthly.repayment + vw_loan_monthly.interest_paid + vw_loan_monthly.penalty_paid) as total_deduction,
		employee_month.employee_month_id, employee_month.org_id, 
		employee_month.currency_id, employee_month.exchange_rate,
		adjustments.currency_id as adj_currency_id
		
		FROM vw_loan_monthly INNER JOIN employee_month ON (vw_loan_monthly.entity_id = employee_month.entity_id) AND (vw_loan_monthly.period_id = employee_month.period_id)
			INNER JOIN adjustments ON vw_loan_monthly.adjustment_id = adjustments.adjustment_id
		WHERE (vw_loan_monthly.period_id = CAST($1 as int)) 
	LOOP
	
		IF(rec.currency_id = rec.adj_currency_id)THEN
			v_exchange_rate := 1;
		ELSE
			v_exchange_rate := 1 / rec.exchange_rate;
		END IF;

		IF(rec.employee_adjustment_id is null)THEN
			v_employee_adjustment_id := nextval('employee_adjustments_employee_adjustment_id_seq');
			
			INSERT INTO employee_adjustments (employee_month_id, adjustment_id, adjustment_type, adjustment_factor,
				amount, balance, in_tax, org_id, exchange_rate, employee_adjustment_id)
			VALUES (rec.employee_month_id, rec.adjustment_id, 2, -1,
				rec.total_deduction, rec.loan_balance, false, rec.org_id, v_exchange_rate, v_employee_adjustment_id);

			UPDATE loan_monthly SET employee_adjustment_id = v_employee_adjustment_id
			WHERE (loan_month_id = rec.loan_month_id);
		ELSE
			UPDATE employee_adjustments SET amount = rec.total_deduction, balance = rec.loan_balance, exchange_rate = v_exchange_rate
			WHERE (employee_adjustment_id = rec.employee_adjustment_id);
		END IF;

	END LOOP;

	msg := 'Payroll Processed';

	RETURN msg;
END;
$_$;


ALTER FUNCTION public.process_loans(character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: process_payroll(character varying, character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION process_payroll(character varying, character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	rec 		RECORD;
	msg 		varchar(120);
BEGIN
	IF ($3 = '1') THEN
		UPDATE employee_adjustments SET tax_reduction_amount = 0 
		FROM employee_month 
		WHERE (employee_adjustments.employee_month_id = employee_month.employee_month_id) 
			AND (employee_month.period_id = CAST($1 as int));
	
		PERFORM updTax(employee_month_id, period_id)
		FROM employee_month
		WHERE (period_id = CAST($1 as int));

		msg := 'Payroll Processed';
	ELSIF ($3 = '2') THEN
		UPDATE periods SET entity_id = CAST($2 as int), approve_status = 'Completed'
		WHERE (period_id = CAST($1 as int));

		msg := 'Application for approval';
	ELSIF ($3 = '3') THEN
		UPDATE periods SET closed = true
		WHERE (period_id = CAST($1 as int));

		msg := 'Period closed';
	ELSIF ($3 = '4') THEN
		UPDATE periods SET closed = false
		WHERE (period_id = CAST($1 as int));

		msg := 'Period opened';
	END IF;

	return msg;
END;
$_$;


ALTER FUNCTION public.process_payroll(character varying, character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: process_pensions(character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION process_pensions(character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	rec							RECORD;
	adj							RECORD;
	v_period_id					integer;
	v_org_id					integer;
	v_employee_month_id			integer;
	v_employee_adjustment_id	integer;
	v_currency_id				integer;
	v_exchange_rate				real;
	a_exchange_rate				real;
	v_amount					real;
	msg							varchar(120);
BEGIN

	SELECT period_id, org_id INTO v_period_id, v_org_id
	FROM periods WHERE period_id = $1::int;
	
	FOR rec IN SELECT pension_id, entity_id, adjustment_id, contribution_id, 
		pension_company, pension_number, amount, use_formura, 
		employer_ps, employer_amount, employer_formural
		FROM pensions WHERE (active = true) AND (org_id = v_org_id) 
	LOOP
	
		SELECT employee_month_id, currency_id, exchange_rate 
			INTO v_employee_month_id, v_currency_id, v_exchange_rate
		FROM employee_month
		WHERE (period_id = v_period_id) AND (entity_id = rec.entity_id);
		
		--- Deduction
		SELECT employee_adjustment_id INTO v_employee_adjustment_id
		FROM employee_adjustments
		WHERE (employee_month_id = v_employee_month_id) AND (pension_id = rec.pension_id)
			AND (adjustment_id = rec.adjustment_id);
		
		SELECT adjustment_id, currency_id, org_id, adjustment_name, adjustment_type, 
			adjustment_order, earning_code, formural, monthly_update, in_payroll, 
			in_tax, visible, running_balance, reduce_balance, tax_reduction_ps, 
			tax_relief_ps, tax_max_allowed, account_number
		INTO adj
		FROM adjustments
		WHERE (adjustment_id = rec.adjustment_id);
		
		v_amount := 0;
		IF(rec.use_formura = true) AND (adj.formural is not null) AND (v_employee_month_id is not null) THEN
			EXECUTE 'SELECT ' || adj.formural || ' FROM employee_month WHERE employee_month_id = ' || v_employee_month_id
			INTO v_amount;
			IF(v_currency_id <> adj.currency_id)THEN
				v_amount := v_amount * v_exchange_rate;
			END IF;
		ELSIF(rec.amount > 0)THEN
			v_amount := rec.amount;
		END IF;
		
		a_exchange_rate := 1;
		IF(v_currency_id <> adj.currency_id)THEN
			a_exchange_rate := 1 / v_exchange_rate;
		END IF;
		
		IF(v_employee_adjustment_id is null) AND (v_employee_month_id is not null) THEN
			INSERT INTO employee_adjustments(employee_month_id, pension_id, org_id, 
				adjustment_id, adjustment_type, adjustment_factor, 
				in_payroll, in_tax, visible,
				exchange_rate, pay_date, amount)
			VALUES (v_employee_month_id, rec.pension_id, v_org_id,
				adj.adjustment_id, adj.adjustment_type, -1, 
				adj.in_payroll, adj.in_tax, adj.visible,
				a_exchange_rate, current_date, v_amount);
		ELSIF (v_employee_month_id is not null) THEN
			UPDATE employee_adjustments SET amount = v_amount, exchange_rate = a_exchange_rate
			WHERE employee_adjustment_id = v_employee_adjustment_id;
		END IF;
	
		--- Employer contribution
		IF((rec.employer_ps > 0) OR (rec.employer_amount > 0) OR (rec.employer_formural = true))THEN
			SELECT employee_adjustment_id INTO v_employee_adjustment_id
			FROM employee_adjustments
			WHERE (employee_month_id = v_employee_month_id) AND (pension_id = rec.pension_id)
				AND (adjustment_id = rec.contribution_id);
			
			SELECT adjustment_id, currency_id, org_id, adjustment_name, adjustment_type, 
				adjustment_order, earning_code, formural, monthly_update, in_payroll, 
				in_tax, visible, running_balance, reduce_balance, tax_reduction_ps, 
				tax_relief_ps, tax_max_allowed, account_number
			INTO adj
			FROM adjustments
			WHERE (adjustment_id = rec.contribution_id);
			
			a_exchange_rate := 1;
			IF(v_currency_id <> adj.currency_id)THEN
				a_exchange_rate := 1 / v_exchange_rate;
			END IF;
			
			v_amount := 0;
			IF(rec.employer_formural = true) AND (adj.formural is not null) AND (v_employee_month_id is not null) THEN
				EXECUTE 'SELECT ' || adj.formural || ' FROM employee_month WHERE employee_month_id = ' || v_employee_month_id
				INTO v_amount;
				IF(v_currency_id <> adj.currency_id)THEN
					v_amount := v_amount * v_exchange_rate;
				END IF;
			ELSIF(rec.employer_ps > 0)THEN
				v_amount := v_amount * rec.employer_ps / 100;
			ELSIF(rec.employer_amount > 0)THEN
				v_amount := rec.employer_amount;
			END IF;
			
			IF(v_employee_adjustment_id is null) AND (v_employee_month_id is not null) AND (v_amount > 0) THEN
				INSERT INTO employee_adjustments(employee_month_id, pension_id, org_id, 
					adjustment_id, adjustment_type, adjustment_factor, 
					in_payroll, in_tax, visible,
					exchange_rate, pay_date, amount)
				VALUES (v_employee_month_id, rec.pension_id, v_org_id,
					adj.adjustment_id, adj.adjustment_type, 1, 
					adj.in_payroll, adj.in_tax, adj.visible,
					a_exchange_rate, current_date, v_amount);
			ELSIF (v_employee_month_id is not null) THEN
				UPDATE employee_adjustments SET amount = v_amount, exchange_rate = a_exchange_rate
				WHERE employee_adjustment_id = v_employee_adjustment_id;
			END IF;
		END IF;
		
	END LOOP;

	msg := 'Pension Processed';

	RETURN msg;
END;
$_$;


ALTER FUNCTION public.process_pensions(character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: process_transaction(character varying, character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION process_transaction(character varying, character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	rec RECORD;
	bankacc INTEGER;
	msg varchar(120);
BEGIN
	SELECT org_id, transaction_id, transaction_type_id, transaction_status_id, transaction_amount INTO rec
	FROM transactions
	WHERE (transaction_id = CAST($1 as integer));

	IF(rec.transaction_status_id = 1) THEN
		msg := 'Transaction needs to be completed first.';
	ELSIF(rec.transaction_status_id = 2) THEN
		IF (($3 = '7') AND ($3 = '8')) THEN
			SELECT max(bank_account_id) INTO bankacc
			FROM bank_accounts WHERE (is_default = true);

			INSERT INTO transactions (org_id, department_id, entity_id, currency_id, transaction_type_id, transaction_date, bank_account_id, transaction_amount)
			SELECT transactions.org_id, transactions.department_id, transactions.entity_id, transactions.currency_id, 1, CURRENT_DATE, bankacc, 
				SUM(transaction_details.quantity * (transaction_details.amount + transaction_details.tax_amount))
			FROM transactions INNER JOIN transaction_details ON transactions.transaction_id = transaction_details.transaction_id
			WHERE (transactions.transaction_id = rec.transaction_id)
			GROUP BY transactions.transaction_id, transactions.entity_id;

			INSERT INTO transaction_links (org_id, transaction_id, transaction_to, amount)
			VALUES (rec.org_id, currval('transactions_transaction_id_seq'), rec.transaction_id, rec.transaction_amount);
		
			UPDATE transactions SET transaction_status_id = 3 WHERE transaction_id = rec.transaction_id;
		ELSE
			INSERT INTO transactions (org_id, department_id, entity_id, currency_id, transaction_type_id, transaction_date, order_number, payment_terms, job, narrative, details)
			SELECT org_id, department_id, entity_id, currency_id, CAST($3 as integer), CURRENT_DATE, order_number, payment_terms, job, narrative, details
			FROM transactions
			WHERE (transaction_id = rec.transaction_id);

			INSERT INTO transaction_details (org_id, transaction_id, account_id, item_id, quantity, amount, tax_amount, narrative, details)
			SELECT org_id, currval('transactions_transaction_id_seq'), account_id, item_id, quantity, amount, tax_amount, narrative, details
			FROM transaction_details
			WHERE (transaction_id = rec.transaction_id);

			INSERT INTO transaction_links (org_id, transaction_id, transaction_to, amount)
			VALUES (REC.org_id, currval('transactions_transaction_id_seq'), rec.transaction_id, rec.transaction_amount);

			UPDATE transactions SET transaction_status_id = 3 WHERE transaction_id = rec.transaction_id;
		END IF;
		msg := 'Transaction proccesed';
	ELSE
		msg := 'Transaction previously Processed.';
	END IF;

	return msg;
END;
$_$;


ALTER FUNCTION public.process_transaction(character varying, character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: sum_attendance_hours(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION sum_attendance_hours(integer, integer) RETURNS interval
    LANGUAGE sql
    AS $_$
	SELECT sum(COALESCE(mon_time_diff, '00:00:00'::interval) + COALESCE(tue_time_diff, '00:00:00'::interval) +
		COALESCE(wed_time_diff, '00:00:00'::interval) + COALESCE(thu_time_diff, '00:00:00'::interval) +
		COALESCE(fri_time_diff, '00:00:00'::interval) -
		((mon_count + tue_count + wed_count + thu_count + fri_count)::varchar || 'hours')::interval)
	FROM vw_week_attendance
	WHERE (vw_week_attendance.entity_id = $1) AND (vw_week_attendance.period_id = $2)
$_$;


ALTER FUNCTION public.sum_attendance_hours(integer, integer) OWNER TO postgres;

--
-- Name: upd_action(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION upd_action() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	wfid		INTEGER;
	reca		RECORD;
	tbid		INTEGER;
	iswf		BOOLEAN;
	add_flow	BOOLEAN;
BEGIN

	add_flow := false;
	IF(TG_OP = 'INSERT')THEN
		IF (NEW.approve_status = 'Completed')THEN
			add_flow := true;
		END IF;
	ELSE
		IF(OLD.approve_status = 'Draft') AND (NEW.approve_status = 'Completed')THEN
			add_flow := true;
		END IF;
	END IF;
	
	IF(add_flow = true)THEN
		wfid := nextval('workflow_table_id_seq');
		NEW.workflow_table_id := wfid;

		IF(TG_OP = 'UPDATE')THEN
			IF(OLD.workflow_table_id is not null)THEN
				INSERT INTO workflow_logs (org_id, table_name, table_id, table_old_id)
				VALUES (NEW.org_id, TG_TABLE_NAME, wfid, OLD.workflow_table_id);
			END IF;
		END IF;

		FOR reca IN SELECT workflows.workflow_id, workflows.table_name, workflows.table_link_field, workflows.table_link_id
			FROM workflows INNER JOIN entity_subscriptions ON workflows.source_entity_id = entity_subscriptions.entity_type_id
			WHERE (workflows.table_name = TG_TABLE_NAME) AND (entity_subscriptions.entity_id= NEW.entity_id) 
		LOOP
			iswf := false;
			IF(reca.table_link_field is null)THEN
				iswf := true;
			ELSE
				IF(TG_TABLE_NAME = 'entry_forms')THEN
					tbid := NEW.form_id;
				ELSIF(TG_TABLE_NAME = 'employee_leave')THEN
					tbid := NEW.leave_type_id;
				END IF;
				IF(tbid = reca.table_link_id)THEN
					iswf := true;
				END IF;
			END IF;

			IF(iswf = true)THEN
				INSERT INTO approvals (org_id, workflow_phase_id, table_name, table_id, org_entity_id, escalation_days, escalation_hours, approval_level, approval_narrative, to_be_done)
				SELECT org_id, workflow_phase_id, tg_table_name, wfid, new.entity_id, escalation_days, escalation_hours, approval_level, phase_narrative, 'Approve - ' || phase_narrative
				FROM vw_workflow_entitys
				WHERE (table_name = TG_TABLE_NAME) AND (entity_id = NEW.entity_id) AND (workflow_id = reca.workflow_id)
				ORDER BY approval_level, workflow_phase_id;

				UPDATE approvals SET approve_status = 'Completed' 
				WHERE (table_id = wfid) AND (approval_level = 1);
			END IF;
		END LOOP;
	END IF;

	RETURN NEW;
END;
$$;


ALTER FUNCTION public.upd_action() OWNER TO postgres;

--
-- Name: upd_approvals(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION upd_approvals() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	reca	RECORD;
	wfid	integer;
	vorgid	integer;
	vnotice	boolean;
	vadvice	boolean;
BEGIN

	SELECT notice, advice, org_id INTO vnotice, vadvice, vorgid
	FROM workflow_phases
	WHERE (workflow_phase_id = NEW.workflow_phase_id);

	IF (NEW.approve_status = 'Completed') THEN
		INSERT INTO sys_emailed (table_id, table_name, email_type, org_id)
		VALUES (NEW.approval_id, TG_TABLE_NAME, 1, vorgid);
	END IF;
	IF (NEW.approve_status = 'Approved') AND (vadvice = true) AND (NEW.forward_id is null) THEN
		INSERT INTO sys_emailed (table_id, table_name, email_type, org_id)
		VALUES (NEW.approval_id, TG_TABLE_NAME, 1, vorgid);
	END IF;
	IF (NEW.approve_status = 'Approved') AND (vnotice = true) AND (NEW.forward_id is null) THEN
		INSERT INTO sys_emailed (table_id, table_name, email_type, org_id)
		VALUES (NEW.approval_id, TG_TABLE_NAME, 2, vorgid);
	END IF;

	IF(TG_OP = 'INSERT') AND (NEW.forward_id is null) THEN
		INSERT INTO approval_checklists (approval_id, checklist_id, requirement, manditory, org_id)
		SELECT NEW.approval_id, checklist_id, requirement, manditory, org_id
		FROM checklists
		WHERE (workflow_phase_id = NEW.workflow_phase_id)
		ORDER BY checklist_number;
	END IF;

	RETURN NULL;
END;
$$;


ALTER FUNCTION public.upd_approvals() OWNER TO postgres;

--
-- Name: upd_approvals(character varying, character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION upd_approvals(character varying, character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	app_id		Integer;
	reca 		RECORD;
	recb		RECORD;
	recc		RECORD;
	recd		RECORD;

	min_level	Integer;
	mysql		varchar(240);
	msg 		varchar(120);
BEGIN
	app_id := CAST($1 as int);
	SELECT approvals.approval_id, approvals.org_id, approvals.table_name, approvals.table_id, 
		approvals.approval_level, approvals.review_advice,
		workflow_phases.workflow_phase_id, workflow_phases.workflow_id, workflow_phases.return_level INTO reca
	FROM approvals INNER JOIN workflow_phases ON approvals.workflow_phase_id = workflow_phases.workflow_phase_id
	WHERE (approvals.approval_id = app_id);

	SELECT count(approval_checklist_id) as cl_count INTO recc
	FROM approval_checklists
	WHERE (approval_id = app_id) AND (manditory = true) AND (done = false);

	SELECT orgs.org_id, transactions.transaction_type_id, orgs.enforce_budget,
		get_budgeted(transactions.transaction_id, transactions.transaction_date, transactions.department_id) as budget_var 
		INTO recd
	FROM orgs INNER JOIN transactions ON orgs.org_id = transactions.org_id
	WHERE (transactions.workflow_table_id = reca.table_id);

	IF ($3 = '1') THEN
		UPDATE approvals SET approve_status = 'Completed', completion_date = now()
		WHERE approval_id = app_id;
		msg := 'Completed';
	ELSIF ($3 = '2') AND (recc.cl_count <> 0) THEN
		msg := 'There are manditory checklist that must be checked first.';
	ELSIF (recd.transaction_type_id = 5) AND (recd.enforce_budget = true) AND (recd.budget_var < 0) THEN
		msg := 'You need a budget to approve the expenditure.';
	ELSIF ($3 = '2') AND (recc.cl_count = 0) THEN
		UPDATE approvals SET approve_status = 'Approved', action_date = now(), app_entity_id = CAST($2 as int)
		WHERE approval_id = app_id;

		SELECT min(approvals.approval_level) INTO min_level
		FROM approvals INNER JOIN workflow_phases ON approvals.workflow_phase_id = workflow_phases.workflow_phase_id
		WHERE (approvals.table_id = reca.table_id) AND (approvals.approve_status = 'Draft')
			AND (workflow_phases.advice = false);
		
		IF(min_level is null)THEN
			mysql := 'UPDATE ' || reca.table_name || ' SET approve_status = ' || quote_literal('Approved') 
			|| ', action_date = now()'
			|| ' WHERE workflow_table_id = ' || reca.table_id;
			EXECUTE mysql;

			INSERT INTO sys_emailed (table_id, table_name, email_type)
			VALUES (reca.table_id, 'vw_workflow_approvals', 1);
			
			FOR recb IN SELECT workflow_phase_id, advice, notice
			FROM workflow_phases
			WHERE (workflow_id = reca.workflow_id) AND (approval_level >= reca.approval_level) LOOP
				IF (recb.advice = true) THEN
					UPDATE approvals SET approve_status = 'Approved', action_date = now(), completion_date = now()
					WHERE (workflow_phase_id = recb.workflow_phase_id) AND (table_id = reca.table_id);
				END IF;
			END LOOP;
		ELSE
			FOR recb IN SELECT workflow_phase_id, advice, notice
			FROM workflow_phases
			WHERE (workflow_id = reca.workflow_id) AND (approval_level <= min_level) LOOP
				IF (recb.advice = true) THEN
					UPDATE approvals SET approve_status = 'Approved', action_date = now(), completion_date = now()
					WHERE (workflow_phase_id = recb.workflow_phase_id) 
						AND (approve_status = 'Draft') AND (table_id = reca.table_id);
				ELSE
					UPDATE approvals SET approve_status = 'Completed', completion_date = now()
					WHERE (workflow_phase_id = recb.workflow_phase_id) 
						AND (approve_status = 'Draft') AND (table_id = reca.table_id);
				END IF;
			END LOOP;
		END IF;
		msg := 'Approved';
	ELSIF ($3 = '3') THEN
		UPDATE approvals SET approve_status = 'Rejected',  action_date = now(), app_entity_id = CAST($2 as int)
		WHERE approval_id = app_id;

		mysql := 'UPDATE ' || reca.table_name || ' SET approve_status = ' || quote_literal('Rejected') 
		|| ', action_date = now()'
		|| ' WHERE workflow_table_id = ' || reca.table_id;
		EXECUTE mysql;

		INSERT INTO sys_emailed (table_id, table_name, email_type, org_id)
		VALUES (reca.table_id, 'vw_workflow_approvals', 2, reca.org_id);
		msg := 'Rejected';
	ELSIF ($3 = '4') AND (reca.return_level = 0) THEN
		UPDATE approvals SET approve_status = 'Review',  action_date = now(), app_entity_id = CAST($2 as int)
		WHERE approval_id = app_id;

		mysql := 'UPDATE ' || reca.table_name || ' SET approve_status = ' || quote_literal('Draft')
		|| ', action_date = now()'
		|| ' WHERE workflow_table_id = ' || reca.table_id;
		EXECUTE mysql;

		msg := 'Forwarded for review';
	ELSIF ($3 = '4') AND (reca.return_level <> 0) THEN
		UPDATE approvals SET approve_status = 'Review',  action_date = now(), app_entity_id = CAST($2 as int)
		WHERE approval_id = app_id;

		INSERT INTO approvals (org_id, workflow_phase_id, table_name, table_id, org_entity_id, escalation_days, escalation_hours, approval_level, approval_narrative, to_be_done, approve_status)
		SELECT org_id, workflow_phase_id, reca.table_name, reca.table_id, CAST($2 as int), escalation_days, escalation_hours, approval_level, phase_narrative, reca.review_advice, 'Completed'
		FROM vw_workflow_entitys
		WHERE (workflow_id = reca.workflow_id) AND (approval_level = reca.return_level)
		ORDER BY workflow_phase_id;

		UPDATE approvals SET approve_status = 'Draft' WHERE approval_id = app_id;

		msg := 'Forwarded to owner for review';
	END IF;

	RETURN msg;
END;
$_$;


ALTER FUNCTION public.upd_approvals(character varying, character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: upd_budget_lines(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION upd_budget_lines() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	accountid 	INTEGER;
BEGIN

	IF(NEW.income_budget = true)THEN
		SELECT sales_account_id INTO accountid
		FROM items
		WHERE (item_id = NEW.item_id);
	ELSE
		SELECT purchase_account_id INTO accountid
		FROM items
		WHERE (item_id = NEW.item_id);
	END IF;

	IF(NEW.account_id is null) THEN
		NEW.account_id = accountid;
	END IF;

	RETURN NEW;
END;
$$;


ALTER FUNCTION public.upd_budget_lines() OWNER TO postgres;

--
-- Name: upd_checklist(character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION upd_checklist(character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	cl_id		Integer;
	reca 		RECORD;
	recc 		RECORD;
	msg 		varchar(120);
BEGIN
	cl_id := CAST($1 as int);

	SELECT approval_checklist_id, approval_id, checklist_id, requirement, manditory, done INTO reca
	FROM approval_checklists
	WHERE (approval_checklist_id = cl_id);

	IF ($3 = '1') THEN
		UPDATE approval_checklists SET done = true WHERE (approval_checklist_id = cl_id);

		SELECT count(approval_checklist_id) as cl_count INTO recc
		FROM approval_checklists
		WHERE (approval_id = reca.approval_id) AND (manditory = true) AND (done = false);
		msg := 'Checklist done.';

		IF(recc.cl_count = 0) THEN
			msg := upd_approvals(CAST(reca.approval_id as varchar(12)), $2, '2');
		END IF;
	ELSIF ($3 = '2') THEN
		UPDATE approval_checklists SET done = false WHERE (approval_checklist_id = cl_id);
		msg := 'Checklist not done.';
	END IF;

	RETURN msg;
END;
$_$;


ALTER FUNCTION public.upd_checklist(character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: upd_complete_form(character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION upd_complete_form(character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	msg varchar(120);
BEGIN
	IF ($3 = '1') THEN
		UPDATE entry_forms SET approve_status = 'Completed', completion_date = now()
		WHERE (entry_form_id = CAST($1 as int));
		msg := 'Completed the form';
	ELSIF ($3 = '2') THEN
		UPDATE entry_forms SET approve_status = 'Approved', action_date = now()
		WHERE (entry_form_id = CAST($1 as int));
		msg := 'Approved the form';
	ELSIF ($3 = '3') THEN
		UPDATE entry_forms SET approve_status = 'Rejected', action_date = now()
		WHERE (entry_form_id = CAST($1 as int));
		msg := 'Rejected the form';
	END IF;

	return msg;
END;
$_$;


ALTER FUNCTION public.upd_complete_form(character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: upd_employee_adjustments(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION upd_employee_adjustments() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	rec 		RECORD;
	entityid 	integer;
	periodid 	integer;
BEGIN
	SELECT monthly_update, running_balance INTO rec
	FROM adjustments WHERE adjustment_id = NEW.Adjustment_ID;

	SELECT entity_id, period_id INTO entityid, periodid
	FROM employee_month WHERE employee_month_id = NEW.employee_month_id;

	IF(rec.running_balance = true) AND (NEW.balance is not null)THEN
		UPDATE default_adjustments SET balance = NEW.balance
		WHERE (entity_id = entityid) AND (adjustment_id = NEW.adjustment_id);
	END IF;

	IF(TG_OP = 'UPDATE')THEN
		IF (OLD.amount <> NEW.amount)THEN
			IF(rec.monthly_update = true)THEN
				UPDATE default_adjustments SET amount = NEW.amount 
				WHERE (entity_id = entityid) AND (adjustment_id = NEW.adjustment_id);
			END IF;

			PERFORM updTax(employee_month_id, Period_id)
			FROM employee_month
			WHERE (period_id = periodid);
		END IF;
	ELSE
		PERFORM updTax(employee_month_id, Period_id)
		FROM employee_month
		WHERE (period_id = periodid);
	END IF;

	RETURN NULL;
END;
$$;


ALTER FUNCTION public.upd_employee_adjustments() OWNER TO postgres;

--
-- Name: upd_employee_month(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION upd_employee_month() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
	INSERT INTO employee_tax_types (org_id, employee_month_id, tax_type_id, tax_identification, additional, amount, employer, in_tax, exchange_rate)
	SELECT NEW.org_id, NEW.employee_month_id, default_tax_types.tax_type_id, default_tax_types.tax_identification, 
		Default_Tax_Types.Additional, 0, 0, Tax_Types.In_Tax,
		(CASE WHEN Tax_Types.currency_id = NEW.currency_id THEN 1 ELSE 1 / NEW.exchange_rate END)
	FROM Default_Tax_Types INNER JOIN Tax_Types ON Default_Tax_Types.Tax_Type_id = Tax_Types.Tax_Type_id
	WHERE (Default_Tax_Types.active = true) AND (Default_Tax_Types.entity_ID = NEW.entity_ID);

	INSERT INTO employee_adjustments (org_id, employee_month_id, adjustment_id, amount, adjustment_type, in_payroll, in_tax, visible, adjustment_factor, 
		balance, tax_relief_amount, exchange_rate, narrative)
	SELECT NEW.org_id, NEW.employee_month_id, default_adjustments.adjustment_id, default_adjustments.amount,
		adjustments.adjustment_type, adjustments.in_payroll, adjustments.in_tax, adjustments.visible,
		(CASE WHEN adjustments.adjustment_type = 2 THEN -1 ELSE 1 END),
		(CASE WHEN (adjustments.running_balance = true) AND (adjustments.reduce_balance = false) THEN (default_adjustments.balance + default_adjustments.amount)
			WHEN (adjustments.running_balance = true) AND (adjustments.reduce_balance = true) THEN (default_adjustments.balance - default_adjustments.amount) END),
		(default_adjustments.amount * adjustments.tax_relief_ps / 100),
		(CASE WHEN adjustments.currency_id = NEW.currency_id THEN 1 ELSE 1 / NEW.exchange_rate END),
		narrative
	FROM default_adjustments INNER JOIN adjustments ON default_adjustments.adjustment_id = adjustments.adjustment_id
	WHERE ((default_adjustments.final_date is null) OR (default_adjustments.final_date > current_date))
		AND (default_adjustments.active = true) AND (default_adjustments.entity_id = NEW.entity_id);

	INSERT INTO advance_deductions (org_id, amount, employee_month_id)
	SELECT NEW.org_id, (Amount / Pay_Period), NEW.Employee_Month_ID
	FROM employee_advances INNER JOIN employee_month ON employee_advances.employee_month_id = employee_month.employee_month_id
	WHERE (employee_month.entity_id = NEW.entity_id) AND (employee_advances.pay_period > 0) AND (employee_advances.completed = false)
		AND (employee_advances.pay_upto >= current_date);
		
	INSERT INTO project_staff_costs (org_id, employee_month_id, project_id, project_role, payroll_ps, staff_cost, tax_cost)
	SELECT NEW.org_id, NEW.employee_month_id, 
		project_staff.project_id, project_staff.project_role, project_staff.payroll_ps, project_staff.staff_cost, project_staff.tax_cost
	FROM project_staff
	WHERE (project_staff.entity_id = NEW.entity_id) AND (project_staff.monthly_cost = true);
	
	INSERT INTO employee_banking (org_id, employee_month_id, bank_branch_id, currency_id, 
		bank_account, amount, 
		exchange_rate)
	SELECT NEW.org_id, NEW.employee_month_id, bank_branch_id, currency_id,
		bank_account, amount,
		(CASE WHEN default_banking.currency_id = NEW.currency_id THEN 1 ELSE 1 / NEW.exchange_rate END)
	FROM default_banking 
	WHERE (default_banking.entity_id = NEW.entity_id) AND (default_banking.active = true)
		AND (amount > 0);

	RETURN NULL;
END;
$$;


ALTER FUNCTION public.upd_employee_month() OWNER TO postgres;

--
-- Name: upd_employee_per_diem(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION upd_employee_per_diem() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	v_period_id			integer;
	v_tax_limit			real;
BEGIN
	SELECT periods.period_id, periods.per_diem_tax_limit INTO v_period_id, v_tax_limit
	FROM employee_month INNER JOIN periods ON employee_month.period_id = periods.period_id
	WHERE employee_month_id = NEW.employee_month_id;
	
	IF(NEW.days_travelled  is null)THEN
		NEW.days_travelled := NEW.return_date - NEW.travel_date;
	END IF;

	IF(NEW.cash_paid = 0) THEN
		NEW.cash_paid := NEW.per_diem;
	END IF;
	IF(NEW.tax_amount = 0) THEN
		NEW.full_amount := (NEW.per_diem - (v_tax_limit * NEW.days_travelled * 0.3)) / 0.7;
		NEW.tax_amount := NEW.full_amount - (v_tax_limit * NEW.days_travelled);
	END IF;

	RETURN NEW;
END;
$$;


ALTER FUNCTION public.upd_employee_per_diem() OWNER TO postgres;

--
-- Name: upd_gls(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION upd_gls() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	isposted BOOLEAN;
BEGIN
	SELECT posted INTO isposted
	FROM journals 
	WHERE (journal_id = NEW.journal_id);

	IF (isposted = true) THEN
		RAISE EXCEPTION '% Journal is already posted no changes are allowed.', NEW.journal_id;
	END IF;

	RETURN NEW;
END;
$$;


ALTER FUNCTION public.upd_gls() OWNER TO postgres;

--
-- Name: upd_objective_details(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION upd_objective_details() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	v_objective_ps				real;
	sum_ods_ps					real;
BEGIN
	
	SELECT objective_ps INTO v_objective_ps
	FROM objectives
	WHERE (objective_id = NEW.objective_id);
	SELECT sum(ods_ps) INTO sum_ods_ps
	FROM objective_details
	WHERE (objective_id = NEW.objective_id) AND (ods_ps is not null) AND (ln_objective_detail_id is null);
		
	IF(sum_ods_ps > v_objective_ps)THEN
		RAISE EXCEPTION 'The % objective details are more than the overall objective details', '%';
	END IF;

	RETURN NEW;
END;
$$;


ALTER FUNCTION public.upd_objective_details() OWNER TO postgres;

--
-- Name: upd_reviews(character varying, character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION upd_reviews(character varying, character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	v_approve_status	varchar(16);
	v_eo_id				integer;
	v_jr_id				integer;
	v_ep_id				integer;

	msg 				varchar(120);
BEGIN

	IF($3 = '1') THEN
		UPDATE employee_objectives SET approve_status = 'Draft'
		WHERE employee_objective_id	= CAST($1 as integer);

		msg := 'Objective Reviews opened';
	ELSIF($3 = '2') THEN
		UPDATE job_reviews SET approve_status = 'Draft'
		WHERE job_review_id	= CAST($1 as integer);

		msg := 'Perfomance Reviews opened';
	ELSIF($3 = '3') THEN
		v_eo_id := CAST($1 as int);
		SELECT approve_status INTO v_approve_status
		FROM employee_objectives WHERE employee_objective_id = v_eo_id;
		
		SELECT evaluation_point_id INTO v_ep_id
		FROM evaluation_points INNER JOIN objectives ON evaluation_points.objective_id = objectives.objective_id
		WHERE (objectives.employee_objective_id = v_eo_id);
		
		IF(v_ep_id is not null)THEN
			msg := 'You need to delete the objectives linked to the review';
		ELSIF(v_approve_status = 'Draft')THEN
			DELETE FROM objective_details WHERE objective_id IN
			(SELECT objective_id FROM objectives WHERE employee_objective_id = v_eo_id);
			DELETE FROM objectives WHERE employee_objective_id = v_eo_id;
			DELETE FROM employee_objectives WHERE employee_objective_id = v_eo_id;
		
			msg := 'Delete objective defination';
		ELSE
			msg := 'You can only delete a draft objective defination.';
		END IF;
	ELSIF($3 = '4') THEN
		v_jr_id := CAST($1 as int);
		SELECT approve_status INTO v_approve_status
		FROM job_reviews WHERE job_review_id = v_jr_id;
		
		IF(v_approve_status = 'Draft')THEN
			DELETE FROM evaluation_points WHERE job_review_id = v_jr_id;
			DELETE FROM job_reviews WHERE job_review_id = v_jr_id;
		
			msg := 'Delete job review';
		ELSE
			msg := 'You can only delete a draft job review.';
		END IF;
	END IF;

	return msg;
END;
$_$;


ALTER FUNCTION public.upd_reviews(character varying, character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: upd_transaction_details(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION upd_transaction_details() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	statusID 	INTEGER;
	journalID 	INTEGER;
	v_for_sale	BOOLEAN;
	accountid 	INTEGER;
	taxrate 	REAL;
BEGIN
	SELECT transactions.transaction_status_id, transactions.journal_id, transaction_types.for_sales
		INTO statusID, journalID, v_for_sale
	FROM transaction_types INNER JOIN transactions ON transaction_types.transaction_type_id = transactions.transaction_type_id
	WHERE (transaction_id = NEW.transaction_id);

	IF ((statusID > 1) OR (journalID is not null)) THEN
		RAISE EXCEPTION 'Transaction is already posted no changes are allowed.';
	END IF;

	IF(v_for_sale = true)THEN
		SELECT items.sales_account_id, tax_types.tax_rate INTO accountid, taxrate
		FROM tax_types INNER JOIN items ON tax_types.tax_type_id = items.tax_type_id
		WHERE (items.item_id = NEW.item_id);
	ELSE
		SELECT items.purchase_account_id, tax_types.tax_rate INTO accountid, taxrate
		FROM tax_types INNER JOIN items ON tax_types.tax_type_id = items.tax_type_id
		WHERE (items.item_id = NEW.item_id);
	END IF;

	NEW.tax_amount := NEW.amount * taxrate / 100;
	IF(accountid is not null)THEN
		NEW.account_id := accountid;
	END IF;

	RETURN NEW;
END;
$$;


ALTER FUNCTION public.upd_transaction_details() OWNER TO postgres;

--
-- Name: upd_trx_ledger(character varying, character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION upd_trx_ledger(character varying, character varying, character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
DECLARE
	msg							varchar(120);
BEGIN
	
	IF ($3 = '1') THEN
		UPDATE transactions SET for_processing = true WHERE transaction_id = $1::integer;
		msg := 'Opened for processing';
	ELSIF ($3 = '2') THEN
		UPDATE transactions SET for_processing = false WHERE transaction_id = $1::integer;
		msg := 'Closed for processing';
	ELSIF ($3 = '3') THEN
		UPDATE transactions  SET payment_date = current_date, completed = true
		WHERE transaction_id = $1::integer AND completed = false;
		msg := 'Completed';
	ELSIF ($3 = '4') THEN
		UPDATE transactions  SET is_cleared = true WHERE transaction_id = $1::integer;
		msg := 'Cleared for posting ';
	END IF;

	RETURN msg;
END;
$_$;


ALTER FUNCTION public.upd_trx_ledger(character varying, character varying, character varying, character varying) OWNER TO postgres;

--
-- Name: upd_work_rates(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION upd_work_rates() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN

	INSERT INTO work_rate_changes (work_rate_id, org_id, work_rate_name, work_rate_code, work_rate,
		weight_rate, overtime_rate, special_rate)
	VALUES (NEW.work_rate_id, NEW.org_id, NEW.work_rate_name, NEW.work_rate_code, NEW.work_rate,
		NEW.weight_rate, NEW.overtime_rate, NEW.special_rate);	

	RETURN NULL;
END;
$$;


ALTER FUNCTION public.upd_work_rates() OWNER TO postgres;

--
-- Name: updtax(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION updtax(integer, integer) RETURNS double precision
    LANGUAGE plpgsql
    AS $_$
DECLARE
	reca 				RECORD;
	income 				REAL;
	tax 				REAL;
	InsuranceRelief 	REAL;
BEGIN

	FOR reca IN SELECT employee_tax_types.employee_tax_type_id, employee_tax_types.tax_type_id, period_tax_types.formural,
			 period_tax_types.employer, period_tax_types.employer_ps
		FROM employee_tax_types INNER JOIN period_tax_types ON (employee_tax_types.tax_type_id = period_tax_types.tax_type_id)
		WHERE (employee_month_id = $1) AND (Period_Tax_Types.Period_ID = $2)
		ORDER BY Period_Tax_Types.Tax_Type_order 
	LOOP

		EXECUTE 'SELECT ' || reca.formural || ' FROM employee_tax_types WHERE employee_tax_type_id = ' || reca.employee_tax_type_id 
		INTO tax;

		UPDATE employee_tax_types SET amount = tax, employer = reca.employer + (tax * reca.employer_ps / 100)
		WHERE employee_tax_type_id = reca.employee_tax_type_id;
	END LOOP;

	RETURN tax;
END;
$_$;


ALTER FUNCTION public.updtax(integer, integer) OWNER TO postgres;



--
-- Name: access_logs; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE access_logs (
    access_log_id integer NOT NULL,
    entity_id integer,
    org_id integer,
    log_time timestamp without time zone,
    log_name character varying(50),
    log_machine character varying(50),
    log_access character varying(50),
    log_id character varying(50),
    log_area character varying(50),
    log_in_out character varying(50),
    is_picked boolean DEFAULT false,
    narrative character varying(240)
);


ALTER TABLE public.access_logs OWNER TO postgres;

--
-- Name: account_class; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE account_class (
    account_class_id integer NOT NULL,
    account_class_no integer NOT NULL,
    org_id integer,
    chat_type_id integer NOT NULL,
    chat_type_name character varying(50) NOT NULL,
    account_class_name character varying(120) NOT NULL,
    details text
);


ALTER TABLE public.account_class OWNER TO postgres;

--
-- Name: account_class_account_class_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE account_class_account_class_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.account_class_account_class_id_seq OWNER TO postgres;

--
-- Name: account_class_account_class_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE account_class_account_class_id_seq OWNED BY account_class.account_class_id;


--
-- Name: account_types; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE account_types (
    account_type_id integer NOT NULL,
    account_type_no integer NOT NULL,
    account_class_id integer,
    org_id integer,
    account_type_name character varying(120) NOT NULL,
    details text
);


ALTER TABLE public.account_types OWNER TO postgres;

--
-- Name: account_types_account_type_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE account_types_account_type_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.account_types_account_type_id_seq OWNER TO postgres;

--
-- Name: account_types_account_type_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE account_types_account_type_id_seq OWNED BY account_types.account_type_id;


--
-- Name: accounts; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE accounts (
    account_id integer NOT NULL,
    account_no integer NOT NULL,
    account_type_id integer,
    org_id integer,
    account_name character varying(120) NOT NULL,
    is_header boolean DEFAULT false NOT NULL,
    is_active boolean DEFAULT true NOT NULL,
    details text
);


ALTER TABLE public.accounts OWNER TO postgres;

--
-- Name: accounts_account_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE accounts_account_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.accounts_account_id_seq OWNER TO postgres;

--
-- Name: accounts_account_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE accounts_account_id_seq OWNED BY accounts.account_id;


--
-- Name: address; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE address (
    address_id integer NOT NULL,
    address_type_id integer,
    sys_country_id character(2),
    org_id integer,
    address_name character varying(120),
    table_name character varying(32),
    table_id integer,
    post_office_box character varying(50),
    postal_code character varying(12),
    premises character varying(120),
    street character varying(120),
    town character varying(50),
    phone_number character varying(150),
    extension character varying(15),
    mobile character varying(150),
    fax character varying(150),
    email character varying(120),
    website character varying(120),
    is_default boolean,
    first_password character varying(32),
    details text,
    company_name character varying(150),
    position_held character varying(150)
);


ALTER TABLE public.address OWNER TO postgres;

--
-- Name: address_address_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE address_address_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.address_address_id_seq OWNER TO postgres;

--
-- Name: address_address_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE address_address_id_seq OWNED BY address.address_id;


--
-- Name: address_types; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE address_types (
    address_type_id integer NOT NULL,
    org_id integer,
    address_type_name character varying(50)
);


ALTER TABLE public.address_types OWNER TO postgres;

--
-- Name: address_types_address_type_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE address_types_address_type_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.address_types_address_type_id_seq OWNER TO postgres;

--
-- Name: address_types_address_type_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE address_types_address_type_id_seq OWNED BY address_types.address_type_id;


--
-- Name: adjustment_effects; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE adjustment_effects (
    adjustment_effect_id integer NOT NULL,
    adjustment_effect_name character varying(50) NOT NULL
);


ALTER TABLE public.adjustment_effects OWNER TO postgres;

--
-- Name: adjustments; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE adjustments (
    adjustment_id integer NOT NULL,
    currency_id integer,
    adjustment_effect_id integer,
    org_id integer,
    adjustment_name character varying(50) NOT NULL,
    adjustment_type integer NOT NULL,
    adjustment_order integer DEFAULT 0 NOT NULL,
    earning_code integer,
    formural character varying(430),
    default_amount real DEFAULT 0 NOT NULL,
    monthly_update boolean DEFAULT true NOT NULL,
    in_payroll boolean DEFAULT true NOT NULL,
    in_tax boolean DEFAULT true NOT NULL,
    visible boolean DEFAULT true NOT NULL,
    running_balance boolean DEFAULT false NOT NULL,
    reduce_balance boolean DEFAULT false NOT NULL,
    tax_reduction_ps double precision DEFAULT 0 NOT NULL,
    tax_relief_ps double precision DEFAULT 0 NOT NULL,
    tax_max_allowed double precision DEFAULT 0 NOT NULL,
    account_number character varying(32),
    details text
);


ALTER TABLE public.adjustments OWNER TO postgres;

--
-- Name: adjustments_adjustment_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE adjustments_adjustment_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.adjustments_adjustment_id_seq OWNER TO postgres;

--
-- Name: adjustments_adjustment_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE adjustments_adjustment_id_seq OWNED BY adjustments.adjustment_id;


--
-- Name: advance_deductions; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE advance_deductions (
    advance_deduction_id integer NOT NULL,
    employee_month_id integer NOT NULL,
    org_id integer,
    pay_date date DEFAULT ('now'::text)::date NOT NULL,
    amount double precision NOT NULL,
    exchange_rate real DEFAULT 1 NOT NULL,
    in_payroll boolean DEFAULT true NOT NULL,
    narrative character varying(240)
);


ALTER TABLE public.advance_deductions OWNER TO postgres;

--
-- Name: advance_deductions_advance_deduction_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE advance_deductions_advance_deduction_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.advance_deductions_advance_deduction_id_seq OWNER TO postgres;

--
-- Name: advance_deductions_advance_deduction_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE advance_deductions_advance_deduction_id_seq OWNED BY advance_deductions.advance_deduction_id;


--
-- Name: amortisation; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE amortisation (
    amortisation_id integer NOT NULL,
    org_id integer,
    asset_id integer,
    amortisation_year integer,
    asset_value real,
    amount real,
    posted boolean DEFAULT false NOT NULL,
    details text
);


ALTER TABLE public.amortisation OWNER TO postgres;

--
-- Name: amortisation_amortisation_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE amortisation_amortisation_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.amortisation_amortisation_id_seq OWNER TO postgres;

--
-- Name: amortisation_amortisation_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE amortisation_amortisation_id_seq OWNED BY amortisation.amortisation_id;


--
-- Name: applicants; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE applicants (
    entity_id integer NOT NULL,
    disability_id integer,
    currency_id integer,
    org_id integer,
    person_title character varying(7),
    surname character varying(50) NOT NULL,
    first_name character varying(50) NOT NULL,
    middle_name character varying(50),
    applicant_email character varying(50) NOT NULL,
    applicant_phone character varying(50),
    date_of_birth date,
    gender character varying(1),
    nationality character(2),
    marital_status character varying(2),
    picture_file character varying(32),
    identity_card character varying(50),
    language character varying(320),
    previous_salary real,
    expected_salary real,
    how_you_heard character varying(320),
    created timestamp without time zone DEFAULT now(),
    field_of_study text,
    interests text,
    objective text,
    details text
);


ALTER TABLE public.applicants OWNER TO postgres;

--
-- Name: applications; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE applications (
    application_id integer NOT NULL,
    intake_id integer,
    contract_type_id integer,
    contract_status_id integer,
    entity_id integer,
    employee_id integer,
    currency_id integer,
    org_id integer,
    contract_date date,
    contract_start date,
    contract_close date,
    contract_period integer,
    contract_terms text,
    initial_salary real,
    notice_email boolean DEFAULT false NOT NULL,
    application_date timestamp without time zone DEFAULT now(),
    approve_status character varying(16) DEFAULT 'Draft'::character varying NOT NULL,
    workflow_table_id integer,
    action_date timestamp without time zone,
    short_listed integer DEFAULT 0 NOT NULL,
    previous_salary real,
    expected_salary real,
    exchange_rate real DEFAULT 1,
    review_rating integer,
    applicant_comments text,
    review text
);


ALTER TABLE public.applications OWNER TO postgres;

--
-- Name: applications_application_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE applications_application_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.applications_application_id_seq OWNER TO postgres;

--
-- Name: applications_application_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE applications_application_id_seq OWNED BY applications.application_id;


--
-- Name: approval_checklists; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE approval_checklists (
    approval_checklist_id integer NOT NULL,
    approval_id integer NOT NULL,
    checklist_id integer NOT NULL,
    org_id integer,
    requirement text,
    manditory boolean DEFAULT false NOT NULL,
    done boolean DEFAULT false NOT NULL,
    narrative character varying(320)
);


ALTER TABLE public.approval_checklists OWNER TO postgres;

--
-- Name: approval_checklists_approval_checklist_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE approval_checklists_approval_checklist_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.approval_checklists_approval_checklist_id_seq OWNER TO postgres;

--
-- Name: approval_checklists_approval_checklist_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE approval_checklists_approval_checklist_id_seq OWNED BY approval_checklists.approval_checklist_id;


--
-- Name: approvals; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE approvals (
    approval_id integer NOT NULL,
    workflow_phase_id integer NOT NULL,
    org_entity_id integer NOT NULL,
    app_entity_id integer,
    org_id integer,
    approval_level integer DEFAULT 1 NOT NULL,
    escalation_days integer DEFAULT 0 NOT NULL,
    escalation_hours integer DEFAULT 3 NOT NULL,
    escalation_time timestamp without time zone DEFAULT now() NOT NULL,
    forward_id integer,
    table_name character varying(64),
    table_id integer,
    application_date timestamp without time zone DEFAULT now() NOT NULL,
    completion_date timestamp without time zone,
    action_date timestamp without time zone,
    approve_status character varying(16) DEFAULT 'Draft'::character varying NOT NULL,
    approval_narrative character varying(240),
    to_be_done text,
    what_is_done text,
    review_advice text,
    details text
);


ALTER TABLE public.approvals OWNER TO postgres;

--
-- Name: approvals_approval_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE approvals_approval_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.approvals_approval_id_seq OWNER TO postgres;

--
-- Name: approvals_approval_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE approvals_approval_id_seq OWNED BY approvals.approval_id;


--
-- Name: aptitude_grades; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE aptitude_grades (
    aptitude_grade_id integer NOT NULL,
    aptitude_test_id integer,
    user_id integer,
    graded_by integer,
    org_id integer,
    date_taken date,
    date_graded date,
    grade integer,
    review_comment text
);


ALTER TABLE public.aptitude_grades OWNER TO postgres;

--
-- Name: aptitude_grades_aptitude_grade_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE aptitude_grades_aptitude_grade_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.aptitude_grades_aptitude_grade_id_seq OWNER TO postgres;

--
-- Name: aptitude_grades_aptitude_grade_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE aptitude_grades_aptitude_grade_id_seq OWNED BY aptitude_grades.aptitude_grade_id;


--
-- Name: aptitude_ongoing; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE aptitude_ongoing (
    aptitude_ongoing_id integer NOT NULL,
    aptitude_test_id integer,
    user_id character(30)
);


ALTER TABLE public.aptitude_ongoing OWNER TO postgres;

--
-- Name: aptitude_ongoing_aptitude_ongoing_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE aptitude_ongoing_aptitude_ongoing_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.aptitude_ongoing_aptitude_ongoing_id_seq OWNER TO postgres;

--
-- Name: aptitude_ongoing_aptitude_ongoing_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE aptitude_ongoing_aptitude_ongoing_id_seq OWNED BY aptitude_ongoing.aptitude_ongoing_id;


--
-- Name: aptitude_tests; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE aptitude_tests (
    aptitude_test_id integer NOT NULL,
    org_id integer,
    aptitude_test_name character(50) NOT NULL,
    display boolean,
    pass_mark integer,
    test_objectives text NOT NULL,
    sample_jsp text NOT NULL,
    sample_js text,
    sample_css text,
    sample_sql text
);


ALTER TABLE public.aptitude_tests OWNER TO postgres;

--
-- Name: aptitude_tests_aptitude_test_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE aptitude_tests_aptitude_test_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.aptitude_tests_aptitude_test_id_seq OWNER TO postgres;

--
-- Name: aptitude_tests_aptitude_test_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE aptitude_tests_aptitude_test_id_seq OWNED BY aptitude_tests.aptitude_test_id;


--
-- Name: asset_movement; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE asset_movement (
    asset_movement_id integer NOT NULL,
    org_id integer,
    asset_id integer,
    department_id integer,
    date_aquired date,
    date_left date,
    details text
);


ALTER TABLE public.asset_movement OWNER TO postgres;

--
-- Name: asset_movement_asset_movement_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE asset_movement_asset_movement_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.asset_movement_asset_movement_id_seq OWNER TO postgres;

--
-- Name: asset_movement_asset_movement_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE asset_movement_asset_movement_id_seq OWNED BY asset_movement.asset_movement_id;


--
-- Name: asset_types; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE asset_types (
    asset_type_id integer NOT NULL,
    org_id integer,
    asset_type_name character varying(50) NOT NULL,
    depreciation_rate real DEFAULT 10 NOT NULL,
    asset_account integer,
    depreciation_account integer,
    accumulated_account integer,
    valuation_account integer,
    disposal_account integer,
    details text
);


ALTER TABLE public.asset_types OWNER TO postgres;

--
-- Name: asset_types_asset_type_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE asset_types_asset_type_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.asset_types_asset_type_id_seq OWNER TO postgres;

--
-- Name: asset_types_asset_type_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE asset_types_asset_type_id_seq OWNED BY asset_types.asset_type_id;


--
-- Name: asset_valuations; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE asset_valuations (
    asset_valuation_id integer NOT NULL,
    org_id integer,
    asset_id integer,
    valuation_year integer,
    asset_value real DEFAULT 0 NOT NULL,
    value_change real DEFAULT 0 NOT NULL,
    posted boolean DEFAULT false NOT NULL,
    details text
);


ALTER TABLE public.asset_valuations OWNER TO postgres;

--
-- Name: asset_valuations_asset_valuation_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE asset_valuations_asset_valuation_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.asset_valuations_asset_valuation_id_seq OWNER TO postgres;

--
-- Name: asset_valuations_asset_valuation_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE asset_valuations_asset_valuation_id_seq OWNED BY asset_valuations.asset_valuation_id;


--
-- Name: assets; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE assets (
    asset_id integer NOT NULL,
    org_id integer,
    asset_type_id integer,
    item_id integer,
    asset_name character varying(50),
    asset_serial character varying(50),
    purchase_date date NOT NULL,
    purchase_value real NOT NULL,
    disposal_amount real,
    disposal_date date,
    disposal_posting boolean DEFAULT false NOT NULL,
    lost boolean DEFAULT false NOT NULL,
    stolen boolean DEFAULT false NOT NULL,
    tag_number character varying(50),
    asset_location character varying(50),
    asset_condition character varying(50),
    asset_acquisition character varying(50),
    details text
);


ALTER TABLE public.assets OWNER TO postgres;

--
-- Name: assets_asset_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE assets_asset_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.assets_asset_id_seq OWNER TO postgres;

--
-- Name: assets_asset_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE assets_asset_id_seq OWNED BY assets.asset_id;


--
-- Name: attendance; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE attendance (
    attendance_id integer NOT NULL,
    entity_id integer,
    org_id integer,
    attendance_date date,
    time_in time without time zone,
    time_out time without time zone,
    details text
);


ALTER TABLE public.attendance OWNER TO postgres;

--
-- Name: attendance_attendance_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE attendance_attendance_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.attendance_attendance_id_seq OWNER TO postgres;

--
-- Name: attendance_attendance_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE attendance_attendance_id_seq OWNED BY attendance.attendance_id;


--
-- Name: bank_accounts; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE bank_accounts (
    bank_account_id integer NOT NULL,
    org_id integer,
    bank_branch_id integer,
    account_id integer,
    currency_id integer,
    bank_account_name character varying(120),
    bank_account_number character varying(50),
    narrative character varying(240),
    is_default boolean DEFAULT false NOT NULL,
    is_active boolean DEFAULT true NOT NULL,
    details text
);


ALTER TABLE public.bank_accounts OWNER TO postgres;

--
-- Name: bank_accounts_bank_account_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE bank_accounts_bank_account_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.bank_accounts_bank_account_id_seq OWNER TO postgres;

--
-- Name: bank_accounts_bank_account_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE bank_accounts_bank_account_id_seq OWNED BY bank_accounts.bank_account_id;


--
-- Name: bank_branch; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE bank_branch (
    bank_branch_id integer NOT NULL,
    bank_id integer,
    org_id integer,
    bank_branch_name character varying(50) NOT NULL,
    bank_branch_code character varying(50),
    narrative character varying(240)
);


ALTER TABLE public.bank_branch OWNER TO postgres;

--
-- Name: bank_branch_bank_branch_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE bank_branch_bank_branch_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.bank_branch_bank_branch_id_seq OWNER TO postgres;

--
-- Name: bank_branch_bank_branch_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE bank_branch_bank_branch_id_seq OWNED BY bank_branch.bank_branch_id;


--
-- Name: banks; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE banks (
    bank_id integer NOT NULL,
    sys_country_id character(2),
    org_id integer,
    bank_name character varying(50) NOT NULL,
    bank_code character varying(25),
    swift_code character varying(25),
    sort_code character varying(25),
    narrative character varying(240)
);


ALTER TABLE public.banks OWNER TO postgres;

--
-- Name: banks_bank_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE banks_bank_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.banks_bank_id_seq OWNER TO postgres;

--
-- Name: banks_bank_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE banks_bank_id_seq OWNED BY banks.bank_id;


--
-- Name: bidders; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE bidders (
    bidder_id integer NOT NULL,
    org_id integer,
    tender_id integer,
    entity_id integer,
    bidder_name character varying(120),
    tender_amount real,
    bind_bond character varying(120),
    bind_bond_amount real,
    return_date date,
    points real,
    is_awarded boolean NOT NULL,
    award_reference character varying(32),
    details text
);


ALTER TABLE public.bidders OWNER TO postgres;

--
-- Name: bidders_bidder_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE bidders_bidder_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.bidders_bidder_id_seq OWNER TO postgres;

--
-- Name: bidders_bidder_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE bidders_bidder_id_seq OWNED BY bidders.bidder_id;


--
-- Name: bio_imports1; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE bio_imports1 (
    bio_imports1_id integer NOT NULL,
    org_id integer,
    col1 character varying(50),
    col2 character varying(50),
    col3 character varying(50),
    col4 character varying(50),
    col5 character varying(50),
    col6 character varying(50),
    col7 character varying(50),
    col8 character varying(50),
    col9 character varying(50),
    col10 character varying(50),
    col11 character varying(50),
    is_picked boolean DEFAULT false
);


ALTER TABLE public.bio_imports1 OWNER TO postgres;

--
-- Name: bio_imports1_bio_imports1_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE bio_imports1_bio_imports1_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.bio_imports1_bio_imports1_id_seq OWNER TO postgres;

--
-- Name: bio_imports1_bio_imports1_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE bio_imports1_bio_imports1_id_seq OWNED BY bio_imports1.bio_imports1_id;


--
-- Name: budget_lines; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE budget_lines (
    budget_line_id integer NOT NULL,
    org_id integer,
    budget_id integer,
    period_id integer,
    account_id integer,
    item_id integer,
    transaction_id integer,
    spend_type integer DEFAULT 0 NOT NULL,
    quantity integer DEFAULT 1 NOT NULL,
    amount real DEFAULT 0 NOT NULL,
    tax_amount real DEFAULT 0 NOT NULL,
    income_budget boolean DEFAULT false NOT NULL,
    narrative character varying(240),
    details text
);


ALTER TABLE public.budget_lines OWNER TO postgres;

--
-- Name: budget_lines_budget_line_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE budget_lines_budget_line_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.budget_lines_budget_line_id_seq OWNER TO postgres;

--
-- Name: budget_lines_budget_line_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE budget_lines_budget_line_id_seq OWNED BY budget_lines.budget_line_id;


--
-- Name: budgets; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE budgets (
    budget_id integer NOT NULL,
    org_id integer,
    fiscal_year_id integer,
    department_id integer,
    link_budget_id integer,
    entity_id integer,
    budget_type integer DEFAULT 1 NOT NULL,
    budget_name character varying(50),
    application_date timestamp without time zone DEFAULT now(),
    approve_status character varying(16) DEFAULT 'Draft'::character varying NOT NULL,
    workflow_table_id integer,
    action_date timestamp without time zone,
    details text
);


ALTER TABLE public.budgets OWNER TO postgres;

--
-- Name: budgets_budget_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE budgets_budget_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.budgets_budget_id_seq OWNER TO postgres;

--
-- Name: budgets_budget_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE budgets_budget_id_seq OWNED BY budgets.budget_id;


--
-- Name: career_development; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE career_development (
    career_development_id integer NOT NULL,
    org_id integer,
    career_development_name character varying(50) NOT NULL,
    details text
);


ALTER TABLE public.career_development OWNER TO postgres;

--
-- Name: career_development_career_development_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE career_development_career_development_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.career_development_career_development_id_seq OWNER TO postgres;

--
-- Name: career_development_career_development_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE career_development_career_development_id_seq OWNED BY career_development.career_development_id;


--
-- Name: case_types; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE case_types (
    case_type_id integer NOT NULL,
    org_id integer,
    case_type_name character varying(50),
    details text
);


ALTER TABLE public.case_types OWNER TO postgres;

--
-- Name: case_types_case_type_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE case_types_case_type_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.case_types_case_type_id_seq OWNER TO postgres;

--
-- Name: case_types_case_type_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE case_types_case_type_id_seq OWNED BY case_types.case_type_id;


--
-- Name: casual_application; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE casual_application (
    casual_application_id integer NOT NULL,
    department_id integer,
    casual_category_id integer,
    entity_id integer,
    org_id integer,
    "position" integer DEFAULT 1 NOT NULL,
    work_duration integer DEFAULT 1 NOT NULL,
    approved_pay_rate real,
    approve_status character varying(16) DEFAULT 'draft'::character varying NOT NULL,
    workflow_table_id integer,
    application_date timestamp without time zone DEFAULT now(),
    action_date timestamp without time zone,
    details text
);


ALTER TABLE public.casual_application OWNER TO postgres;

--
-- Name: casual_application_casual_application_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE casual_application_casual_application_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.casual_application_casual_application_id_seq OWNER TO postgres;

--
-- Name: casual_application_casual_application_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE casual_application_casual_application_id_seq OWNED BY casual_application.casual_application_id;


--
-- Name: casual_category; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE casual_category (
    casual_category_id integer NOT NULL,
    org_id integer,
    casual_category_name character varying(50),
    details text
);


ALTER TABLE public.casual_category OWNER TO postgres;

--
-- Name: casual_category_casual_category_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE casual_category_casual_category_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.casual_category_casual_category_id_seq OWNER TO postgres;

--
-- Name: casual_category_casual_category_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE casual_category_casual_category_id_seq OWNED BY casual_category.casual_category_id;


--
-- Name: casuals; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE casuals (
    casual_id integer NOT NULL,
    entity_id integer,
    casual_application_id integer,
    org_id integer,
    start_date date,
    end_date date,
    duration integer,
    pay_rate real,
    amount_paid real,
    paid boolean DEFAULT false NOT NULL,
    approve_status character varying(16) DEFAULT 'draft'::character varying NOT NULL,
    workflow_table_id integer,
    application_date timestamp without time zone DEFAULT now(),
    action_date timestamp without time zone,
    details text
);


ALTER TABLE public.casuals OWNER TO postgres;

--
-- Name: casuals_casual_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE casuals_casual_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.casuals_casual_id_seq OWNER TO postgres;

--
-- Name: casuals_casual_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE casuals_casual_id_seq OWNED BY casuals.casual_id;


--
-- Name: checklists; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE checklists (
    checklist_id integer NOT NULL,
    workflow_phase_id integer NOT NULL,
    org_id integer,
    checklist_number integer,
    manditory boolean DEFAULT false NOT NULL,
    requirement text,
    details text
);


ALTER TABLE public.checklists OWNER TO postgres;

--
-- Name: checklists_checklist_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE checklists_checklist_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.checklists_checklist_id_seq OWNER TO postgres;

--
-- Name: checklists_checklist_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE checklists_checklist_id_seq OWNED BY checklists.checklist_id;


--
-- Name: claim_details; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE claim_details (
    claim_detail_id integer NOT NULL,
    claim_id integer,
    currency_id integer,
    org_id integer,
    nature_of_expence character varying(50),
    receipt_number character varying(50),
    amount real NOT NULL,
    exchange_rate real DEFAULT 1 NOT NULL,
    expense_code character varying(50),
    details text
);


ALTER TABLE public.claim_details OWNER TO postgres;

--
-- Name: claim_details_claim_detail_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE claim_details_claim_detail_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.claim_details_claim_detail_id_seq OWNER TO postgres;

--
-- Name: claim_details_claim_detail_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE claim_details_claim_detail_id_seq OWNED BY claim_details.claim_detail_id;


--
-- Name: claim_types; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE claim_types (
    claim_type_id integer NOT NULL,
    adjustment_id integer,
    org_id integer,
    claim_type_name character varying(50),
    details text
);


ALTER TABLE public.claim_types OWNER TO postgres;

--
-- Name: claim_types_claim_type_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE claim_types_claim_type_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.claim_types_claim_type_id_seq OWNER TO postgres;

--
-- Name: claim_types_claim_type_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE claim_types_claim_type_id_seq OWNED BY claim_types.claim_type_id;


--
-- Name: claims; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE claims (
    claim_id integer NOT NULL,
    claim_type_id integer,
    entity_id integer,
    employee_adjustment_id integer,
    org_id integer,
    claim_date date NOT NULL,
    in_payroll boolean DEFAULT false NOT NULL,
    narrative character varying(250),
    process_claim boolean DEFAULT false NOT NULL,
    process_date date,
    application_date timestamp without time zone DEFAULT now(),
    approve_status character varying(16) DEFAULT 'Draft'::character varying NOT NULL,
    workflow_table_id integer,
    action_date timestamp without time zone,
    details text
);


ALTER TABLE public.claims OWNER TO postgres;

--
-- Name: claims_claim_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE claims_claim_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.claims_claim_id_seq OWNER TO postgres;

--
-- Name: claims_claim_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE claims_claim_id_seq OWNED BY claims.claim_id;


--
-- Name: contract_status; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE contract_status (
    contract_status_id integer NOT NULL,
    org_id integer,
    contract_status_name character varying(50) NOT NULL,
    details text
);


ALTER TABLE public.contract_status OWNER TO postgres;

--
-- Name: contract_status_contract_status_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE contract_status_contract_status_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.contract_status_contract_status_id_seq OWNER TO postgres;

--
-- Name: contract_status_contract_status_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE contract_status_contract_status_id_seq OWNED BY contract_status.contract_status_id;


--
-- Name: contract_types; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE contract_types (
    contract_type_id integer NOT NULL,
    org_id integer,
    notice_period integer DEFAULT 30 NOT NULL,
    contract_type_name character varying(50) NOT NULL,
    contract_text text,
    details text
);


ALTER TABLE public.contract_types OWNER TO postgres;

--
-- Name: contract_types_contract_type_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE contract_types_contract_type_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.contract_types_contract_type_id_seq OWNER TO postgres;

--
-- Name: contract_types_contract_type_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE contract_types_contract_type_id_seq OWNED BY contract_types.contract_type_id;


--
-- Name: contracts; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE contracts (
    contract_id integer NOT NULL,
    org_id integer,
    bidder_id integer,
    contract_name character varying(320) NOT NULL,
    contract_date date,
    contract_end date,
    contract_amount real,
    contract_tax real,
    details text
);


ALTER TABLE public.contracts OWNER TO postgres;

--
-- Name: contracts_contract_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE contracts_contract_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.contracts_contract_id_seq OWNER TO postgres;

--
-- Name: contracts_contract_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE contracts_contract_id_seq OWNED BY contracts.contract_id;


--
-- Name: currency; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE currency (
    currency_id integer NOT NULL,
    currency_name character varying(50),
    currency_symbol character varying(3),
    org_id integer
);


ALTER TABLE public.currency OWNER TO postgres;

--
-- Name: currency_currency_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE currency_currency_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.currency_currency_id_seq OWNER TO postgres;

--
-- Name: currency_currency_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE currency_currency_id_seq OWNED BY currency.currency_id;


--
-- Name: currency_rates; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE currency_rates (
    currency_rate_id integer NOT NULL,
    currency_id integer,
    org_id integer,
    exchange_date date DEFAULT ('now'::text)::date NOT NULL,
    exchange_rate real DEFAULT 1 NOT NULL
);


ALTER TABLE public.currency_rates OWNER TO postgres;

--
-- Name: currency_rates_currency_rate_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE currency_rates_currency_rate_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.currency_rates_currency_rate_id_seq OWNER TO postgres;

--
-- Name: currency_rates_currency_rate_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE currency_rates_currency_rate_id_seq OWNED BY currency_rates.currency_rate_id;


--
-- Name: cv_projects; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE cv_projects (
    cv_projectid integer NOT NULL,
    entity_id integer,
    org_id integer,
    cv_project_name character varying(240),
    cv_project_date date NOT NULL,
    details text
);


ALTER TABLE public.cv_projects OWNER TO postgres;

--
-- Name: cv_projects_cv_projectid_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE cv_projects_cv_projectid_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.cv_projects_cv_projectid_seq OWNER TO postgres;

--
-- Name: cv_projects_cv_projectid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE cv_projects_cv_projectid_seq OWNED BY cv_projects.cv_projectid;


--
-- Name: cv_seminars; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE cv_seminars (
    cv_seminar_id integer NOT NULL,
    entity_id integer,
    org_id integer,
    cv_seminar_name character varying(240),
    cv_seminar_date date NOT NULL,
    details text
);


ALTER TABLE public.cv_seminars OWNER TO postgres;

--
-- Name: cv_seminars_cv_seminar_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE cv_seminars_cv_seminar_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.cv_seminars_cv_seminar_id_seq OWNER TO postgres;

--
-- Name: cv_seminars_cv_seminar_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE cv_seminars_cv_seminar_id_seq OWNED BY cv_seminars.cv_seminar_id;


--
-- Name: day_works; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE day_works (
    day_work_id integer NOT NULL,
    period_id integer NOT NULL,
    entity_id integer,
    farm_field_id integer,
    org_id integer,
    batch_ref character varying(50),
    work_date date,
    work_start time without time zone,
    work_end time without time zone,
    farm_weight real,
    factory_weight real,
    details text
);


ALTER TABLE public.day_works OWNER TO postgres;

--
-- Name: day_works_day_work_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE day_works_day_work_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.day_works_day_work_id_seq OWNER TO postgres;

--
-- Name: day_works_day_work_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE day_works_day_work_id_seq OWNED BY day_works.day_work_id;


--
-- Name: default_accounts; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE default_accounts (
    default_account_id integer NOT NULL,
    account_id integer,
    use_key_id integer NOT NULL,
    org_id integer,
    narrative character varying(240)
);


ALTER TABLE public.default_accounts OWNER TO postgres;

--
-- Name: default_accounts_default_account_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE default_accounts_default_account_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.default_accounts_default_account_id_seq OWNER TO postgres;

--
-- Name: default_accounts_default_account_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE default_accounts_default_account_id_seq OWNED BY default_accounts.default_account_id;


--
-- Name: default_adjustments; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE default_adjustments (
    default_adjustment_id integer NOT NULL,
    entity_id integer,
    adjustment_id integer,
    org_id integer,
    amount double precision DEFAULT 0 NOT NULL,
    balance double precision DEFAULT 0 NOT NULL,
    final_date date,
    active boolean DEFAULT true,
    narrative character varying(240)
);


ALTER TABLE public.default_adjustments OWNER TO postgres;

--
-- Name: default_adjustments_default_adjustment_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE default_adjustments_default_adjustment_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.default_adjustments_default_adjustment_id_seq OWNER TO postgres;

--
-- Name: default_adjustments_default_adjustment_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE default_adjustments_default_adjustment_id_seq OWNED BY default_adjustments.default_adjustment_id;


--
-- Name: default_banking; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE default_banking (
    default_banking_id integer NOT NULL,
    entity_id integer,
    bank_branch_id integer,
    currency_id integer,
    org_id integer,
    amount double precision DEFAULT 0 NOT NULL,
    ps_amount double precision DEFAULT 0 NOT NULL,
    final_date date,
    active boolean DEFAULT true,
    bank_account character varying(64),
    narrative character varying(240)
);


ALTER TABLE public.default_banking OWNER TO postgres;

--
-- Name: default_banking_default_banking_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE default_banking_default_banking_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.default_banking_default_banking_id_seq OWNER TO postgres;

--
-- Name: default_banking_default_banking_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE default_banking_default_banking_id_seq OWNED BY default_banking.default_banking_id;


--
-- Name: default_tax_types; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE default_tax_types (
    default_tax_type_id integer NOT NULL,
    entity_id integer,
    tax_type_id integer,
    org_id integer,
    tax_identification character varying(50),
    narrative character varying(240),
    additional double precision DEFAULT 0 NOT NULL,
    active boolean DEFAULT true
);


ALTER TABLE public.default_tax_types OWNER TO postgres;

--
-- Name: default_tax_types_default_tax_type_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE default_tax_types_default_tax_type_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.default_tax_types_default_tax_type_id_seq OWNER TO postgres;

--
-- Name: default_tax_types_default_tax_type_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE default_tax_types_default_tax_type_id_seq OWNED BY default_tax_types.default_tax_type_id;


--
-- Name: define_phases; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE define_phases (
    define_phase_id integer NOT NULL,
    project_type_id integer,
    entity_type_id integer,
    org_id integer,
    define_phase_name character varying(240),
    define_phase_time real DEFAULT 0 NOT NULL,
    define_phase_cost real DEFAULT 0 NOT NULL,
    phase_order integer DEFAULT 0 NOT NULL,
    details text
);


ALTER TABLE public.define_phases OWNER TO postgres;

--
-- Name: define_phases_define_phase_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE define_phases_define_phase_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.define_phases_define_phase_id_seq OWNER TO postgres;

--
-- Name: define_phases_define_phase_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE define_phases_define_phase_id_seq OWNED BY define_phases.define_phase_id;


--
-- Name: define_tasks; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE define_tasks (
    define_task_id integer NOT NULL,
    define_phase_id integer,
    org_id integer,
    define_task_name character varying(240) NOT NULL,
    narrative character varying(120),
    details text
);


ALTER TABLE public.define_tasks OWNER TO postgres;

--
-- Name: define_tasks_define_task_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE define_tasks_define_task_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.define_tasks_define_task_id_seq OWNER TO postgres;

--
-- Name: define_tasks_define_task_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE define_tasks_define_task_id_seq OWNED BY define_tasks.define_task_id;


--
-- Name: department_roles; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE department_roles (
    department_role_id integer NOT NULL,
    department_id integer,
    ln_department_role_id integer,
    jobs_category_id integer,
    org_id integer,
    department_role_name character varying(240) NOT NULL,
    active boolean DEFAULT true NOT NULL,
    job_description text,
    job_requirements text,
    duties text,
    performance_measures text,
    details text
);


ALTER TABLE public.department_roles OWNER TO postgres;

--
-- Name: department_roles_department_role_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE department_roles_department_role_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.department_roles_department_role_id_seq OWNER TO postgres;

--
-- Name: department_roles_department_role_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE department_roles_department_role_id_seq OWNED BY department_roles.department_role_id;


--
-- Name: departments; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE departments (
    department_id integer NOT NULL,
    ln_department_id integer,
    org_id integer,
    department_name character varying(120),
    department_account character varying(50),
    function_code character varying(50),
    active boolean DEFAULT true NOT NULL,
    petty_cash boolean DEFAULT false NOT NULL,
    cost_center boolean DEFAULT true NOT NULL,
    revenue_center boolean DEFAULT true NOT NULL,
    description text,
    duties text,
    reports text,
    details text
);


ALTER TABLE public.departments OWNER TO postgres;

--
-- Name: departments_department_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE departments_department_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.departments_department_id_seq OWNER TO postgres;

--
-- Name: departments_department_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE departments_department_id_seq OWNED BY departments.department_id;


--
-- Name: disability; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE disability (
    disability_id integer NOT NULL,
    org_id integer,
    disability_name character varying(240) NOT NULL
);


ALTER TABLE public.disability OWNER TO postgres;

--
-- Name: disability_disability_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE disability_disability_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.disability_disability_id_seq OWNER TO postgres;

--
-- Name: disability_disability_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE disability_disability_id_seq OWNED BY disability.disability_id;


--
-- Name: education; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE education (
    education_id integer NOT NULL,
    entity_id integer,
    education_class_id integer,
    org_id integer,
    date_from date NOT NULL,
    date_to date,
    name_of_school character varying(240),
    examination_taken character varying(240),
    grades_obtained character varying(50),
    certificate_number character varying(50),
    details text
);


ALTER TABLE public.education OWNER TO postgres;

--
-- Name: education_class; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE education_class (
    education_class_id integer NOT NULL,
    org_id integer,
    education_class_name character varying(50),
    details text
);


ALTER TABLE public.education_class OWNER TO postgres;

--
-- Name: education_class_education_class_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE education_class_education_class_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.education_class_education_class_id_seq OWNER TO postgres;

--
-- Name: education_class_education_class_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE education_class_education_class_id_seq OWNED BY education_class.education_class_id;


--
-- Name: education_education_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE education_education_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.education_education_id_seq OWNER TO postgres;

--
-- Name: education_education_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE education_education_id_seq OWNED BY education.education_id;


--
-- Name: employee_adjustments; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE employee_adjustments (
    employee_adjustment_id integer NOT NULL,
    employee_month_id integer NOT NULL,
    adjustment_id integer NOT NULL,
    pension_id integer,
    org_id integer,
    adjustment_type integer,
    adjustment_factor integer DEFAULT 1 NOT NULL,
    pay_date date DEFAULT ('now'::text)::date NOT NULL,
    amount double precision NOT NULL,
    balance double precision,
    paid_amount double precision DEFAULT 0 NOT NULL,
    exchange_rate real DEFAULT 1 NOT NULL,
    tax_reduction_amount double precision DEFAULT 0 NOT NULL,
    tax_relief_amount double precision DEFAULT 0 NOT NULL,
    in_payroll boolean DEFAULT true NOT NULL,
    in_tax boolean DEFAULT true NOT NULL,
    visible boolean DEFAULT true NOT NULL,
    narrative character varying(240)
);


ALTER TABLE public.employee_adjustments OWNER TO postgres;

--
-- Name: employee_adjustments_employee_adjustment_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE employee_adjustments_employee_adjustment_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.employee_adjustments_employee_adjustment_id_seq OWNER TO postgres;

--
-- Name: employee_adjustments_employee_adjustment_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE employee_adjustments_employee_adjustment_id_seq OWNED BY employee_adjustments.employee_adjustment_id;


--
-- Name: employee_advances; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE employee_advances (
    employee_advance_id integer NOT NULL,
    employee_month_id integer,
    currency_id integer,
    entity_id integer NOT NULL,
    org_id integer,
    pay_date date DEFAULT ('now'::text)::date NOT NULL,
    pay_upto date NOT NULL,
    pay_period integer DEFAULT 3 NOT NULL,
    amount double precision NOT NULL,
    payment_amount double precision NOT NULL,
    exchange_rate real DEFAULT 1 NOT NULL,
    in_payroll boolean DEFAULT false NOT NULL,
    completed boolean DEFAULT false NOT NULL,
    application_date timestamp without time zone DEFAULT now(),
    approve_status character varying(16) DEFAULT 'Draft'::character varying NOT NULL,
    workflow_table_id integer,
    action_date timestamp without time zone,
    narrative character varying(240),
    details text
);


ALTER TABLE public.employee_advances OWNER TO postgres;

--
-- Name: employee_advances_employee_advance_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE employee_advances_employee_advance_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.employee_advances_employee_advance_id_seq OWNER TO postgres;

--
-- Name: employee_advances_employee_advance_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE employee_advances_employee_advance_id_seq OWNED BY employee_advances.employee_advance_id;


--
-- Name: employee_banking; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE employee_banking (
    employee_banking_id integer NOT NULL,
    employee_month_id integer NOT NULL,
    bank_branch_id integer,
    currency_id integer,
    org_id integer,
    amount double precision DEFAULT 0 NOT NULL,
    exchange_rate real DEFAULT 1 NOT NULL,
    active boolean DEFAULT true,
    bank_account character varying(64),
    narrative character varying(240)
);


ALTER TABLE public.employee_banking OWNER TO postgres;

--
