CREATE TABLE adjustment_effects (
	adjustment_effect_id	integer primary key,
	adjustment_effect_name	varchar(50) not null
);

CREATE TABLE adjustments (
	adjustment_id			serial primary key,
	currency_id				integer references currency,
	adjustment_effect_id	integer references adjustment_effects,
	org_id					integer references orgs,
	adjustment_name			varchar(50) not null,
	adjustment_type			integer not null,
	adjustment_order		integer default 0 not null,
	earning_code			integer,
	formural				varchar(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		float default 0 not null,
	tax_relief_ps			float default 0 not null,
	tax_max_allowed			float default 0 not null,

	account_number			varchar(32),
	details					text,
	
	UNIQUE(adjustment_name, org_id)
);
CREATE INDEX adjustments_currency_id ON adjustments(currency_id);
CREATE INDEX adjustments_adjustment_effect_id ON adjustments(adjustment_effect_id);
CREATE INDEX adjustments_org_id ON adjustments(org_id);

CREATE TABLE claim_types (
	claim_type_id			serial primary key,
	adjustment_id			integer references adjustments,
	org_id					integer references orgs,
	claim_type_name			varchar(50),
	details					text
);
CREATE INDEX claim_types_adjustment_id ON claim_types(adjustment_id);
CREATE INDEX claim_types_org_id ON claim_types(org_id);

CREATE TABLE default_adjustments (
	default_adjustment_id	serial primary key,
	entity_id				integer references entitys,
	adjustment_id			integer references adjustments,
	org_id					integer references orgs,
	
	amount					float default 0 not null,
	balance					float default 0 not null,
	final_date				date,
	active					boolean default true,

	Narrative				varchar(240)
);
CREATE INDEX default_adjustments_entity_id ON default_adjustments (entity_id);
CREATE INDEX default_adjustments_adjustment_id ON default_adjustments (adjustment_id);
CREATE INDEX default_adjustments_org_id ON default_adjustments(org_id);

CREATE TABLE default_banking (
	default_banking_id		serial primary key,
	entity_id				integer references entitys,
	bank_branch_id			integer references bank_branch,
	currency_id				integer references currency,
	org_id					integer references orgs,
	
	amount					float default 0 not null,
	ps_amount				float default 0 not null,
	final_date				date,
	active					boolean default true,
	
	bank_account			varchar(64),

	Narrative				varchar(240)
);
CREATE INDEX default_banking_entity_id ON default_banking (entity_id);
CREATE INDEX default_banking_bank_branch_id ON default_banking (bank_branch_id);
CREATE INDEX default_banking_currency_id ON default_banking (currency_id);
CREATE INDEX default_banking_org_id ON default_banking(org_id);

CREATE TABLE pensions (
	pension_id 				serial primary key,
	entity_id				integer references entitys,
	adjustment_id			integer references adjustments,
	contribution_id			integer references adjustments,
	org_id					integer references orgs,
	
	pension_company			varchar(50) not null,
	pension_number			varchar(50),
	active					boolean default true,
	
	amount					float default 0 not null,
	use_formura				boolean default false not null,
	
	employer_ps				float default 0 not null,
	employer_amount			float default 0 not null,
	employer_formural		boolean default false not null,
	
	details					text
);
CREATE INDEX pension_entity_id ON pensions (entity_id);
CREATE INDEX pension_adjustment_id ON pensions (adjustment_id);
CREATE INDEX pension_contribution_id ON pensions (contribution_id);
CREATE INDEX pension_org_id ON pensions (org_id);

CREATE TABLE employee_month (
	employee_month_id		serial primary key,
	entity_id				integer references entitys not null,
	period_id				integer references periods not null,
	bank_branch_id			integer references bank_branch not null,
	pay_group_id			integer references pay_groups not null,
	department_role_id		integer references department_roles not null,
	currency_id				integer references currency,
	org_id					integer references orgs,
	
	exchange_rate			real default 1 not null,
	bank_account			varchar(32),
	basic_pay				float default 0 not null,
	details					text,
	unique (entity_id, period_id)
);
CREATE INDEX employee_month_entity_id ON employee_month (entity_id);
CREATE INDEX employee_month_period_id ON employee_month (period_id);
CREATE INDEX employee_month_bank_branch_id ON employee_month (bank_branch_id);
CREATE INDEX employee_month_bank_pay_group_id ON employee_month (pay_group_id);
CREATE INDEX employee_month_currency_id ON employee_month (currency_id);
CREATE INDEX employee_month_org_id ON employee_month(org_id);

CREATE TABLE employee_tax_types (
	employee_tax_type_id	serial primary key,
	employee_month_id		integer references employee_month not null,
	tax_type_id				integer references tax_types not null,
	org_id					integer references orgs,
	
	tax_identification		varchar(50),
	in_tax					boolean not null default false,
	amount					float default 0 not null,
	additional				float default 0 not null,
	employer				float default 0 not null,
	exchange_rate			real default 1 not null,
	
	narrative				varchar(240)
);
CREATE INDEX employee_tax_types_employee_month_id ON employee_tax_types (employee_month_id);
CREATE INDEX employee_tax_types_tax_type_id ON employee_tax_types (tax_type_id);
CREATE INDEX employee_tax_types_org_id ON employee_tax_types(org_id);

CREATE TABLE employee_advances (
	employee_advance_id		serial primary key,
	employee_month_id		integer references employee_month,
	currency_id				integer references currency,
	entity_id				integer not null references entitys,
	org_id					integer references orgs,
	pay_date				date default current_date not null,
	pay_upto				date not null,
	pay_period				integer default 3 not null,
	amount					float not null,
	payment_amount			float not null,
	exchange_rate			real default 1 not null,
	in_payroll				boolean not null default false,
	completed				boolean not null default false,

	application_date		timestamp default now(),
	approve_status			varchar(16) default 'Draft' not null,
	workflow_table_id		integer,
	action_date				timestamp,

	narrative				varchar(240),
	details					text
);
CREATE INDEX employee_advances_employee_month_id ON employee_advances (employee_month_id);
CREATE INDEX employee_advances_currency_id ON employee_advances (currency_id);
CREATE INDEX employee_advances_entity_id ON employee_advances (entity_id);
CREATE INDEX employee_advances_org_id ON employee_advances(org_id);

CREATE TABLE advance_deductions (
	advance_deduction_id	serial primary key,
	employee_month_id		integer references employee_month not null,
	org_id					integer references orgs,
	pay_date				date default current_date not null,
	amount					float not null,
	exchange_rate			real default 1 not null,
	in_payroll				boolean not null default true,
	narrative				varchar(240)
);
CREATE INDEX advance_deductions_employee_month_id ON advance_deductions (employee_month_id);
CREATE INDEX advance_deductions_org_id ON advance_deductions(org_id);

CREATE TABLE employee_adjustments (
	employee_adjustment_id	serial primary key,
	employee_month_id		integer references employee_month not null,
	adjustment_id			integer references adjustments not null,
	pension_id				integer references pensions,
	org_id					integer references orgs,
	adjustment_type			integer,
	adjustment_factor		integer default 1 not null,
	pay_date				date default current_date not null,
	amount					float not null,
	balance					float,
	paid_amount				float default 0 not null,
	exchange_rate			real default 1 not null,

	tax_reduction_amount	float default 0 not null,
	tax_relief_amount		float default 0 not null,

	in_payroll				boolean not null default true,
	in_tax					boolean not null default true,
	visible					boolean not null default true,
	narrative				varchar(240)
);
CREATE INDEX employee_adjustments_employee_month_id ON employee_adjustments (employee_month_id);
CREATE INDEX employee_adjustments_adjustment_id ON employee_adjustments (adjustment_id);
CREATE INDEX employee_adjustments_pension_id ON employee_adjustments (pension_id);
CREATE INDEX employee_adjustments_org_id ON employee_adjustments(org_id);

CREATE TABLE claims (
	claim_id				serial primary key,
	claim_type_id			integer references claim_types,
	entity_id				integer references entitys,
	employee_adjustment_id	integer references employee_adjustments,
	org_id					integer references orgs,
	
	claim_date				date not null,
	in_payroll				boolean not null default false,
	narrative				varchar(250),
	
	process_claim			boolean not null default false,
	process_date			date,
	
	application_date		timestamp default now(),
	approve_status			varchar(16) default 'Draft' not null,
	workflow_table_id		integer,
	action_date				timestamp,
	
	details					text
);
CREATE INDEX claims_claim_type_id ON claims(claim_type_id);
CREATE INDEX claims_entity_id ON claims(entity_id);
CREATE INDEX claims_employee_adjustment_id ON claims(employee_adjustment_id);
CREATE INDEX claims_org_id ON claims(org_id);

CREATE TABLE claim_details (
	claim_detail_id			serial primary key,
	claim_id				integer references claims,
	currency_id				integer references currency,
	org_id					integer references orgs,
	
	nature_of_expence		varchar(50),
	receipt_number			varchar(50),
	amount					real not null,
	exchange_rate			real default 1 not null,
	expense_code			varchar(50),

	details					text
);
CREATE INDEX claim_details_claim_id ON claim_details(claim_id);
CREATE INDEX claim_details_currency_id ON claim_details(currency_id);
CREATE INDEX claim_details_org_id ON claim_details(org_id);

CREATE TABLE employee_overtime (
	employee_overtime_id	serial primary key,
	employee_month_id		integer references employee_month not null,
	org_id					integer references orgs,
	overtime_date			date not null,
	overtime				float not null,
	overtime_rate			float not null,
	application_date		timestamp default now(),
	approve_status			varchar(16) default 'draft' not null,
	workflow_table_id		integer,
	action_date				timestamp,
	narrative				varchar(240),
	details					text
);
CREATE INDEX employee_overtime_employee_month_id ON employee_overtime (employee_month_id);
CREATE INDEX employee_overtime_org_id ON employee_overtime(org_id);

CREATE TABLE employee_per_diem (
	employee_per_diem_id	serial primary key,
	employee_month_id		integer references employee_month not null,
	currency_id				integer references currency,
	org_id					integer references orgs,
	travel_date				date not null,
	return_date				date not null,
	days_travelled			integer not null,
	per_diem				float default 0 not null,
	cash_paid				float default 0 not null,
	tax_amount				float default 0 not null,
	full_amount				float default 0 not null,
	exchange_rate			real default 1 not null,
	travel_to				varchar(240),
	post_account			varchar(32),
	application_date		timestamp default now(),
	approve_status			varchar(16) default 'draft' not null,
	workflow_table_id		integer,
	action_date				timestamp,
	completed				boolean default false not null,
	details					text
);
CREATE INDEX employee_per_diem_employee_month_id ON employee_per_diem (employee_month_id);
CREATE INDEX employee_per_diem_currency_id ON employee_per_diem (currency_id);
CREATE INDEX employee_per_diem_org_id ON employee_per_diem(org_id);

CREATE TABLE employee_banking (
	employee_banking_id		serial primary key,
	employee_month_id		integer references employee_month not null,
	bank_branch_id			integer references bank_branch,
	currency_id				integer references currency,
	org_id					integer references orgs,
	
	amount					float default 0 not null,
	exchange_rate			real default 1 not null,
	active					boolean default true,
	
	bank_account			varchar(64),

	Narrative				varchar(240)
);
CREATE INDEX employee_banking_employee_month_id ON employee_banking (employee_month_id);
CREATE INDEX employee_banking_bank_branch_id ON employee_banking (bank_branch_id);
CREATE INDEX employee_banking_currency_id ON employee_banking (currency_id);
CREATE INDEX employee_banking_org_id ON employee_banking(org_id);

CREATE VIEW vw_adjustments AS
	SELECT currency.currency_id, currency.currency_name, currency.currency_symbol,
		adjustments.org_id, adjustments.adjustment_id, adjustments.adjustment_name, adjustments.adjustment_type, 
		adjustments.adjustment_order, adjustments.earning_code, adjustments.formural, adjustments.monthly_update, 
		adjustments.in_payroll, adjustments.in_tax, adjustments.visible, adjustments.running_balance, 
		adjustments.reduce_balance, adjustments.tax_reduction_ps, adjustments.tax_relief_ps, 
		adjustments.tax_max_allowed, adjustments.account_number, adjustments.details
	FROM adjustments INNER JOIN currency ON adjustments.currency_id = currency.currency_id;
		
CREATE VIEW vw_claim_types AS
	SELECT adjustments.adjustment_id, adjustments.adjustment_name, 
		claim_types.org_id, claim_types.claim_type_id, claim_types.claim_type_name, claim_types.details
	FROM claim_types INNER JOIN adjustments ON claim_types.adjustment_id = adjustments.adjustment_id;
	
CREATE VIEW vw_claims AS
	SELECT claim_types.claim_type_id, claim_types.claim_type_name, 
		entitys.entity_id, entitys.entity_name, 
		claims.org_id, claims.claim_id, claims.claim_date, claims.narrative, claims.in_payroll,
		claims.process_claim, claims.process_date,
		claims.application_date, claims.approve_status, claims.workflow_table_id, claims.action_date, 
		claims.details
	FROM claims INNER JOIN claim_types ON claims.claim_type_id = claim_types.claim_type_id
		INNER JOIN entitys ON claims.entity_id = entitys.entity_id;
		
CREATE VIEW vw_claim_details AS
	SELECT vw_claims.claim_type_id, vw_claims.claim_type_name, vw_claims.entity_id, vw_claims.entity_name, 
		vw_claims.claim_id, vw_claims.claim_date, vw_claims.narrative, vw_claims.application_date, 
		vw_claims.approve_status, vw_claims.workflow_table_id, vw_claims.action_date,

		currency.currency_id, currency.currency_name, currency.currency_symbol,
		claim_details.org_id, claim_details.claim_detail_id, claim_details.nature_of_expence, 
		claim_details.receipt_number, claim_details.amount, claim_details.exchange_rate, claim_details.expense_code, 
		claim_details.details
	FROM claim_details INNER JOIN vw_claims ON claim_details.claim_id = vw_claims.claim_id
		INNER JOIN currency ON claim_details.currency_id = currency.currency_id;

CREATE VIEW vw_default_adjustments AS
	SELECT vw_adjustments.adjustment_id, vw_adjustments.adjustment_name, vw_adjustments.adjustment_type, 
		vw_adjustments.currency_id, vw_adjustments.currency_name, vw_adjustments.currency_symbol,
		entitys.entity_id, entitys.entity_name,
		default_adjustments.org_id, default_adjustments.default_adjustment_id, default_adjustments.amount, default_adjustments.active,
		default_adjustments.final_date, default_adjustments.narrative
	FROM default_adjustments INNER JOIN vw_adjustments ON default_adjustments.adjustment_id = vw_adjustments.adjustment_id
		INNER JOIN entitys ON default_adjustments.entity_id = entitys.entity_id;

CREATE VIEW vw_default_banking AS
	SELECT entitys.entity_id, entitys.entity_name, 
		vw_bank_branch.bank_id, vw_bank_branch.bank_name, vw_bank_branch.bank_branch_id, 
		vw_bank_branch.bank_branch_name, vw_bank_branch.bank_branch_code,
		currency.currency_id, currency.currency_name, currency.currency_symbol,
		default_banking.org_id, default_banking.default_banking_id, default_banking.amount, 
		default_banking.ps_amount, default_banking.final_date, default_banking.active, 
		default_banking.bank_account, default_banking.narrative
	FROM default_banking INNER JOIN entitys ON default_banking.entity_id = entitys.entity_id
		INNER JOIN vw_bank_branch ON default_banking.bank_branch_id = vw_bank_branch.bank_branch_id
		INNER JOIN currency ON default_banking.currency_id = currency.currency_id;

CREATE VIEW vw_pensions AS
	SELECT entitys.entity_id, entitys.entity_name,
		adjustments.adjustment_id, adjustments.adjustment_name, 
		currency.currency_id, currency.currency_name, currency.currency_symbol,
		pensions.contribution_id, contributions.adjustment_name as contribution_name, 
		pensions.org_id, pensions.pension_id, pensions.pension_company, pensions.pension_number, 
		pensions.amount, pensions.use_formura, pensions.employer_ps, pensions.employer_amount, 
		pensions.employer_formural, pensions.active, pensions.details
	FROM pensions INNER JOIN entitys ON pensions.entity_id = entitys.entity_id
		INNER JOIN adjustments ON pensions.adjustment_id = adjustments.adjustment_id
		INNER JOIN adjustments as contributions ON pensions.contribution_id = contributions.adjustment_id
		INNER JOIN currency ON adjustments.currency_id = currency.currency_id;
	
	
CREATE OR REPLACE FUNCTION getAdjustment(int, int, int) RETURNS float 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;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION getAdjustment(int, int) RETURNS float 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;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION getAdvanceBalance(int, date) RETURNS float 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;
$$ LANGUAGE plpgsql;

CREATE VIEW vw_employee_month AS
	SELECT vw_periods.period_id, vw_periods.start_date, vw_periods.end_date, vw_periods.overtime_rate, 
		vw_periods.activated, vw_periods.closed, vw_periods.month_id, vw_periods.period_year, vw_periods.period_month,
		vw_periods.quarter, vw_periods.semister, vw_periods.gl_payroll_account, vw_periods.is_posted,
		
		vw_bank_branch.bank_id, vw_bank_branch.bank_name, vw_bank_branch.bank_branch_id, 
		vw_bank_branch.bank_branch_name, vw_bank_branch.bank_branch_code,
		pay_groups.pay_group_id, pay_groups.pay_group_name, pay_groups.gl_payment_account,
		pay_groups.bank_header, pay_groups.bank_address,
		vw_department_roles.department_id, vw_department_roles.department_name,
		vw_department_roles.department_role_id, vw_department_roles.department_role_name, 
		entitys.entity_id, entitys.entity_name,
		employees.employee_id, employees.surname, employees.first_name, employees.middle_name, employees.date_of_birth, 
		employees.gender, employees.nationality, employees.marital_status, employees.appointment_date, employees.exit_date, 
		employees.contract, employees.contract_period, employees.employment_terms, employees.identity_card,
		(employees.Surname || ' ' || employees.First_name || ' ' || COALESCE(employees.Middle_name, '')) as employee_name,
		currency.currency_id, currency.currency_name, currency.currency_symbol, employee_month.exchange_rate,
		
		employee_month.org_id, employee_month.employee_month_id, employee_month.bank_account, employee_month.basic_pay, employee_month.details,
		getAdjustment(employee_month.employee_month_id, 4, 31) as overtime,
		getAdjustment(employee_month.employee_month_id, 1, 1) as full_allowance,
		getAdjustment(employee_month.employee_month_id, 1, 2) as payroll_allowance,
		getAdjustment(employee_month.employee_month_id, 1, 3) as tax_allowance,
		getAdjustment(employee_month.employee_month_id, 2, 1) as full_deduction,
		getAdjustment(employee_month.employee_month_id, 2, 2) as payroll_deduction,
		getAdjustment(employee_month.employee_month_id, 2, 3) as tax_deduction,
		getAdjustment(employee_month.employee_month_id, 3, 1) as full_expense,
		getAdjustment(employee_month.employee_month_id, 3, 2) as payroll_expense,
		getAdjustment(employee_month.employee_month_id, 3, 3) as tax_expense,
		getAdjustment(employee_month.employee_month_id, 4, 11) as payroll_tax,
		getAdjustment(employee_month.employee_month_id, 4, 12) as tax_tax,
		getAdjustment(employee_month.employee_month_id, 4, 22) as net_Adjustment,
		getAdjustment(employee_month.employee_month_id, 4, 33) as per_diem,
		getAdjustment(employee_month.employee_month_id, 4, 34) as advance,
		getAdjustment(employee_month.employee_month_id, 4, 35) as advance_deduction,
		getAdjustment(employee_month.employee_month_id, 4, 41) as other_banks,
		(employee_month.Basic_Pay + getAdjustment(employee_month.employee_month_id, 4, 31) + getAdjustment(employee_month.employee_month_id, 4, 22) 
		+ getAdjustment(employee_month.employee_month_id, 4, 33) - getAdjustment(employee_month.employee_month_id, 4, 11)) as net_pay,
		(employee_month.Basic_Pay + getAdjustment(employee_month.employee_month_id, 4, 31) + getAdjustment(employee_month.employee_month_id, 4, 22) 
		+ getAdjustment(employee_month.employee_month_id, 4, 33) + getAdjustment(employee_month.employee_month_id, 4, 34)
		- getAdjustment(employee_month.employee_month_id, 4, 11) - getAdjustment(employee_month.employee_month_id, 4, 35)
		- getAdjustment(employee_month.employee_month_id, 4, 36)
		- getAdjustment(employee_month.employee_month_id, 4, 41)) as banked,
		(employee_month.Basic_Pay + getAdjustment(employee_month.employee_month_id, 4, 31) + getAdjustment(employee_month.employee_month_id, 1, 1) 
		+ getAdjustment(employee_month.employee_month_id, 3, 1) + getAdjustment(employee_month.employee_month_id, 4, 33)) as cost
	FROM employee_month INNER JOIN vw_bank_branch ON employee_month.bank_branch_id = vw_bank_branch.bank_branch_id
		INNER JOIN vw_periods ON employee_month.period_id = vw_periods.period_id
		INNER JOIN pay_groups ON employee_month.pay_group_id = pay_groups.pay_group_id
		INNER JOIN entitys ON employee_month.entity_id = entitys.entity_id
		INNER JOIN vw_department_roles ON employee_month.department_role_id = vw_department_roles.department_role_id
		INNER JOIN employees ON employee_month.entity_id = employees.entity_id
		INNER JOIN currency ON employee_month.currency_id = currency.currency_id;
		
CREATE VIEW vw_ems AS
	SELECT em.org_id, em.period_id, em.start_date, em.end_date, em.overtime_rate, em.activated, em.closed, em.month_id, 
		em.period_year, em.period_month, em.quarter, em.semister, em.bank_header, em.bank_address, 
		em.gl_payroll_account, em.is_posted, 
		em.bank_id, em.bank_name, em.bank_branch_id, em.bank_branch_name, em.bank_branch_code, 
		em.pay_group_id, em.pay_group_name, em.gl_payment_account,
		em.department_id, em.department_name, em.department_role_id, em.department_role_name, 
		em.entity_id, em.entity_name, 
		em.employee_id, em.surname, em.first_name, em.middle_name, em.date_of_birth, em.gender, 
		em.nationality, em.marital_status, em.appointment_date, em.exit_date, em.contract, em.contract_period, 
		em.employment_terms, em.identity_card, em.employee_name, 
		em.currency_id, em.currency_name, em.currency_symbol, em.exchange_rate, 
		em.employee_month_id, em.bank_account, em.basic_pay, em.details, em.overtime, 
		em.full_allowance, em.payroll_allowance, em.tax_allowance, em.full_deduction, 
		em.payroll_deduction, em.tax_deduction, em.full_expense, em.payroll_expense, 
		em.tax_expense, em.payroll_tax, em.tax_tax, em.net_adjustment, em.per_diem, 
		em.advance, em.advance_deduction, em.other_banks, em.net_pay, em.banked, em.cost,
		
		(em.basic_pay * em.exchange_rate) as b_basic_pay,
		((em.banked + em.other_banks) * em.exchange_rate) as b_banked
	FROM vw_employee_month em;

CREATE VIEW vw_employee_month_list AS
	SELECT vw_periods.period_id, vw_periods.start_date, vw_periods.end_date, vw_periods.overtime_rate, 
		vw_periods.activated, vw_periods.closed, vw_periods.month_id, vw_periods.period_year, vw_periods.period_month,
		vw_periods.quarter, vw_periods.semister, vw_periods.gl_payroll_account, vw_periods.is_posted, 
		entitys.entity_id, entitys.entity_name,
		pay_groups.pay_group_id, pay_groups.pay_group_name, pay_groups.gl_payment_account,
		pay_groups.bank_header, pay_groups.bank_address,
		employees.employee_id, employees.surname, employees.first_name, employees.middle_name, employees.date_of_birth, 
		employees.gender, employees.nationality, employees.marital_status, employees.appointment_date, employees.exit_date, 
		employees.contract, employees.contract_period, employees.employment_terms, employees.identity_card,
		(employees.Surname || ' ' || employees.First_name || ' ' || COALESCE(employees.Middle_name, '')) as employee_name,
		departments.department_id, departments.department_name, departments.department_account, departments.function_code,
		department_roles.department_role_id, department_roles.department_role_name,
		employee_month.org_id, employee_month.employee_month_id, employee_month.bank_account, employee_month.basic_pay,
		employee_month.currency_id, employee_month.exchange_rate
		
	FROM employee_month INNER JOIN vw_periods ON employee_month.period_id = vw_periods.period_id
		INNER JOIN pay_groups ON employee_month.pay_group_id = pay_groups.pay_group_id
		INNER JOIN entitys ON employee_month.entity_id = entitys.entity_id
		INNER JOIN employees ON employee_month.entity_id = employees.entity_id
		INNER JOIN department_roles ON employee_month.department_role_id = department_roles.department_role_id
		INNER JOIN departments ON department_roles.department_id = departments.department_id;

CREATE VIEW vw_employee_tax_types AS
	SELECT eml.employee_month_id, eml.period_id, eml.start_date, 
		eml.month_id, eml.period_year, eml.period_month,
		eml.end_date, eml.gl_payroll_account,
		eml.entity_id, eml.entity_name, eml.employee_id, eml.identity_card,
		eml.surname, eml.first_name, eml.middle_name, eml.date_of_birth, 
		eml.department_id, eml.department_name, eml.department_account, eml.function_code,
		eml.department_role_id, eml.department_role_name,
		tax_types.tax_type_id, tax_types.tax_type_name, tax_types.account_id, tax_types.tax_type_number,
		tax_types.account_number, tax_types.employer_account,
		employee_tax_types.org_id, employee_tax_types.employee_tax_type_id, employee_tax_types.tax_identification, 
		employee_tax_types.amount, 
		employee_tax_types.additional, employee_tax_types.employer, employee_tax_types.narrative,
		currency.currency_id, currency.currency_name, currency.currency_symbol, employee_tax_types.exchange_rate,
		
		(employee_tax_types.exchange_rate * employee_tax_types.amount) as base_amount,
		(employee_tax_types.exchange_rate * employee_tax_types.employer) as base_employer,
		(employee_tax_types.exchange_rate * employee_tax_types.additional) as base_additional,
		
		(employee_tax_types.exchange_rate * eml.exchange_rate * employee_tax_types.amount) as b_amount,
		(employee_tax_types.exchange_rate * eml.exchange_rate * employee_tax_types.employer) as b_employer,
		(employee_tax_types.exchange_rate * eml.exchange_rate * employee_tax_types.additional) as b_additional
				
	FROM employee_tax_types INNER JOIN vw_employee_month_list as eml ON employee_tax_types.employee_month_id = eml.employee_month_id
		INNER JOIN tax_types ON (employee_tax_types.tax_type_id = tax_types.tax_type_id)
		INNER JOIN currency ON tax_types.currency_id = currency.currency_id;
		
CREATE VIEW vw_employee_tax_month AS
	SELECT emp.period_id, emp.start_date, emp.end_date, emp.overtime_rate, 
		emp.activated, emp.closed, emp.month_id, emp.period_year, emp.period_month,
		emp.quarter, emp.semister, emp.bank_header, emp.bank_address,
		emp.gl_payroll_account, emp.is_posted,
		emp.bank_id, emp.bank_name, emp.bank_branch_id, 
		emp.bank_branch_name, emp.bank_branch_code,
		emp.pay_group_id, emp.pay_group_name, emp.department_id, emp.department_name,
		emp.department_role_id, emp.department_role_name, 
		emp.entity_id, emp.entity_name,
		emp.employee_id, emp.surname, emp.first_name, emp.middle_name, emp.date_of_birth, 
		emp.gender, emp.nationality, emp.marital_status, emp.appointment_date, emp.exit_date, 
		emp.contract, emp.contract_period, emp.employment_terms, emp.identity_card,
		emp.employee_name,
		emp.currency_id, emp.currency_name, emp.currency_symbol, emp.exchange_rate,
		
		emp.org_id, emp.employee_month_id, emp.bank_account, emp.basic_pay, emp.details,
		emp.overtime, emp.full_allowance, emp.payroll_allowance, emp.tax_allowance,
		emp.full_deduction, emp.payroll_deduction, emp.tax_deduction, emp.full_expense,
		emp.payroll_expense, emp.tax_expense, emp.payroll_tax, emp.tax_tax,
		emp.net_adjustment, emp.per_diem, emp.advance, emp.advance_deduction,
		emp.net_pay, emp.banked, emp.cost,
		
		tax_types.tax_type_id, tax_types.tax_type_name, tax_types.account_id, tax_types.use_key_id,
		employee_tax_types.employee_tax_type_id, employee_tax_types.tax_identification, 
		employee_tax_types.amount, employee_tax_types.exchange_rate as tax_exchange_rate,
		employee_tax_types.additional, employee_tax_types.employer, employee_tax_types.narrative,
		
		(employee_tax_types.amount * employee_tax_types.exchange_rate) as tax_base_amount,
		(employee_tax_types.amount * employee_tax_types.exchange_rate * emp.exchange_rate) as b_tax_amount

	FROM vw_employee_month as emp INNER JOIN employee_tax_types ON emp.employee_month_id = employee_tax_types.employee_month_id
		INNER JOIN tax_types ON employee_tax_types.tax_type_id = tax_types.tax_type_id;
	
CREATE VIEW vw_employee_advances AS
	SELECT eml.employee_month_id, eml.period_id, eml.start_date, eml.end_date,
		eml.month_id, eml.period_year, eml.period_month, eml.gl_payroll_account, 
		eml.entity_id, eml.entity_name, eml.employee_id,
		employee_advances.org_id, employee_advances.employee_advance_id, 
		employee_advances.pay_date, employee_advances.pay_period, 
		employee_advances.Pay_upto, employee_advances.amount, employee_advances.in_payroll, employee_advances.completed, 
		employee_advances.approve_status, employee_advances.Action_date, employee_advances.narrative,
		
		(employee_advances.amount * eml.exchange_rate) as b_advance_amount
	FROM employee_advances INNER JOIN vw_employee_month_list as eml ON employee_advances.employee_month_id = eml.employee_month_id;

CREATE VIEW vw_advance_deductions AS
	SELECT eml.employee_month_id, eml.period_id, eml.start_date, eml.end_date,
		eml.month_id, eml.period_year, eml.period_month, eml.gl_payroll_account, 
		eml.entity_id, eml.entity_name, eml.employee_id,
		advance_deductions.org_id, advance_deductions.advance_deduction_id, advance_deductions.pay_date, 
		advance_deductions.amount, advance_deductions.in_payroll, advance_deductions.narrative,
		
		(advance_deductions.amount * eml.exchange_rate) as b_advance_deduction
	FROM advance_deductions INNER JOIN vw_employee_month_list as eml ON advance_deductions.employee_month_id = eml.employee_month_id;

CREATE VIEW vw_advance_statement AS
	(SELECT eml.employee_month_id, eml.period_id, eml.start_date, 
		eml.month_id, eml.period_year, eml.period_month,
		eml.entity_id, eml.entity_name, eml.employee_id,
		employee_advances.org_id, employee_advances.pay_date, employee_advances.in_payroll, employee_advances.narrative,
		employee_advances.amount, cast(0 as real) as recovery
	FROM employee_advances INNER JOIN vw_employee_month_list as eml ON employee_advances.employee_month_id = eml.employee_month_id
	WHERE (employee_advances.approve_status = 'Approved'))
	UNION
	(SELECT eml.employee_month_id, eml.period_id, eml.start_date, 
		eml.month_id, eml.period_year, eml.period_month,
		eml.entity_id, eml.entity_name, eml.employee_id,
		advance_deductions.org_id, advance_deductions.pay_date, advance_deductions.in_payroll, advance_deductions.narrative, 
		cast(0 as real), advance_deductions.amount
	FROM advance_deductions INNER JOIN vw_employee_month_list as eml ON advance_deductions.employee_month_id = eml.employee_month_id);

CREATE VIEW vw_employee_adjustments AS
	SELECT eml.employee_month_id, eml.period_id, eml.start_date, 
		eml.month_id, eml.period_year, eml.period_month,
		eml.end_date, 
		eml.entity_id, eml.entity_name, eml.employee_id, eml.identity_card,
		eml.department_id, eml.department_name, eml.department_account, eml.function_code,
		eml.department_role_id, eml.department_role_name,
		adjustments.adjustment_id, adjustments.adjustment_name, adjustments.adjustment_type, adjustments.account_number, 
		adjustments.earning_code,
		currency.currency_id, currency.currency_name, currency.currency_symbol,
		employee_adjustments.org_id, employee_adjustments.employee_adjustment_id, employee_adjustments.pay_date, employee_adjustments.amount, 
		employee_adjustments.in_payroll, employee_adjustments.in_tax, employee_adjustments.visible, employee_adjustments.exchange_rate,
		employee_adjustments.paid_amount, employee_adjustments.balance, employee_adjustments.narrative,
		employee_adjustments.tax_relief_amount,
		
		(employee_adjustments.exchange_rate * employee_adjustments.amount) as base_amount,	
		(employee_adjustments.exchange_rate * eml.exchange_rate * employee_adjustments.amount) as b_amount,
		(employee_adjustments.exchange_rate * eml.exchange_rate * employee_adjustments.paid_amount) as b_paid_amount
		
	FROM employee_adjustments INNER JOIN adjustments ON employee_adjustments.adjustment_id = adjustments.adjustment_id
		INNER JOIN vw_employee_month_list as eml ON employee_adjustments.employee_month_id = eml.employee_month_id
		INNER JOIN currency ON adjustments.currency_id = currency.currency_id;

CREATE VIEW vw_employee_overtime AS
	SELECT eml.employee_month_id, eml.period_id, eml.start_date, 
		eml.month_id, eml.period_year, eml.period_month,
		eml.entity_id, eml.entity_name, eml.employee_id,
		employee_overtime.org_id, employee_overtime.employee_overtime_id, employee_overtime.overtime_date, employee_overtime.overtime, 
		employee_overtime.overtime_rate, employee_overtime.narrative, employee_overtime.approve_status, 
		employee_overtime.Action_date, employee_overtime.details
	FROM employee_overtime INNER JOIN vw_employee_month_list as eml ON employee_overtime.employee_month_id = eml.employee_month_id;

CREATE VIEW vw_employee_per_diem AS
	SELECT eml.employee_month_id, eml.period_id, eml.start_date, 
		eml.month_id, eml.period_year, eml.period_month,
		eml.entity_id, eml.entity_name, eml.employee_id,
		employee_per_diem.org_id, employee_per_diem.employee_per_diem_id, employee_per_diem.travel_date, employee_per_diem.return_date, employee_per_diem.days_travelled, 
		employee_per_diem.per_diem, employee_per_diem.cash_paid, employee_per_diem.tax_amount, employee_per_diem.full_amount,
		employee_per_diem.travel_to, employee_per_diem.approve_status, employee_per_diem.action_date, 
		employee_per_diem.completed, employee_per_diem.post_account, employee_per_diem.details,
		(employee_per_diem.exchange_rate * employee_per_diem.tax_amount) as base_tax_amount, 
		(employee_per_diem.exchange_rate *  employee_per_diem.full_amount) as base_full_amount,
		
		(employee_per_diem.exchange_rate * eml.exchange_rate * employee_per_diem.full_amount) as b_full_amount,
		(employee_per_diem.exchange_rate * eml.exchange_rate * employee_per_diem.cash_paid) as b_cash_paid
	FROM employee_per_diem INNER JOIN vw_employee_month_list as eml ON employee_per_diem.employee_month_id = eml.employee_month_id;
	
CREATE VIEW vw_employee_banking AS
	SELECT eml.employee_month_id, eml.period_id, eml.start_date, 
		eml.month_id, eml.period_year, eml.period_month,
		eml.entity_id, eml.entity_name, eml.employee_id,
		eml.pay_group_id, eml.bank_Header, eml.bank_address,
		vw_bank_branch.bank_id, vw_bank_branch.bank_name, vw_bank_branch.bank_branch_id, 
		vw_bank_branch.bank_branch_name, vw_bank_branch.bank_branch_code,
		currency.currency_id, currency.currency_name, currency.currency_symbol,
		
		employee_banking.org_id, employee_banking.employee_banking_id, employee_banking.amount, 
		employee_banking.exchange_rate, employee_banking.active, employee_banking.bank_account,
		employee_banking.narrative,
		
		(employee_banking.exchange_rate * employee_banking.amount) as base_amount,
		(employee_banking.exchange_rate * eml.exchange_rate * employee_banking.amount) as b_amount
	FROM employee_banking INNER JOIN vw_employee_month_list as eml ON employee_banking.employee_month_id = eml.employee_month_id
		INNER JOIN vw_bank_branch ON employee_banking.bank_branch_id = vw_bank_branch.bank_branch_id
		INNER JOIN currency ON employee_banking.currency_id = currency.currency_id;

CREATE VIEW vw_pension_adjustments AS
	SELECT c.period_id, c.start_date,
		a.employee_adjustment_id, a.employee_month_id, a.adjustment_id, a.pension_id, 
		a.org_id, a.adjustment_type, a.adjustment_factor, a.pay_date, a.amount, 
		a.exchange_rate, a.in_payroll, a.in_tax, a.visible,
		(a.amount * a.exchange_rate) as base_amount
	FROM employee_adjustments as a INNER JOIN employee_month as b ON a.employee_month_id = b.employee_month_id
		INNER JOIN periods as c ON b.period_id = c.period_id
	WHERE (a.pension_id is not null);

CREATE VIEW vw_employee_pensions AS
	SELECT a.entity_id, a.entity_name, a.adjustment_id, a.adjustment_name, a.contribution_id, 
		a.contribution_name, a.org_id, a.pension_id, a.pension_company, a.pension_number, 
		a.active, a.currency_id, a.currency_name, a.currency_symbol,
		b.period_id, b.start_date, b.employee_month_id, 
		COALESCE(b.amount, 0) as amount, 
		COALESCE(b.base_amount, 0) as base_amount,
		COALESCE(c.amount, 0) as employer_amount, 
		COALESCE(c.base_amount, 0) as employer_base_amount,
		(b.amount + COALESCE(c.amount, 0)) as pension_amount, 
		(b.base_amount + COALESCE(c.base_amount, 0)) as pension_base_amount
	FROM (vw_pensions as a INNER JOIN vw_pension_adjustments as b 
		ON (a.pension_id = b.pension_id) AND (a.adjustment_id = b.adjustment_id))
		LEFT JOIN vw_pension_adjustments as c
		ON (a.pension_id = c.pension_id) AND (a.contribution_id = c.adjustment_id)
		AND (b.employee_month_id = c.employee_month_id);

CREATE VIEW vw_employee_per_diem_ledger AS
	(SELECT a.org_id, a.period_id, a.travel_date, 'Travel Cost' as description, 
		a.post_account, a.entity_name, a.b_full_amount as dr_amt, 0.0 as cr_amt
	FROM vw_employee_per_diem a
	WHERE (a.approve_status = 'Approved'))
	UNION
	(SELECT a.org_id, a.period_id, a.travel_date, 'Travel Payment' as description, 
		get_default_account(24, a.org_id)::varchar(32), a.entity_name, 0.0 as dr_amt, cash_paid as cr_amt
	FROM vw_employee_per_diem a
	WHERE (a.approve_status = 'Approved'))
	UNION
	(SELECT  a.org_id, a.period_id, a.travel_date, 'Travel PAYE' as description, 
		get_default_account(25, a.org_id)::varchar(32), a.entity_name, 0.0 as dr_amt, (a.b_full_amount - a.b_cash_paid) as cr_amt
	FROM vw_employee_per_diem a
	WHERE (a.approve_status = 'Approved'));

CREATE VIEW vw_payroll_ledger_trx AS
	SELECT org_id, period_id, end_date, description, gl_payroll_account, entity_name, employee_id,
		dr_amt, cr_amt 
	FROM 
	((SELECT a.org_id, a.period_id, a.end_date, 'BASIC SALARY' as description, 
		a.gl_payroll_account, a.entity_name, a.employee_id,
		a.b_basic_pay as dr_amt, '0.0'::real as cr_amt
	FROM vw_ems a)
	UNION
	(SELECT a.org_id, a.period_id, a.end_date, 'SALARY PAYMENTS',
		a.gl_payment_account, a.entity_name, a.employee_id,
		'0.0'::real, a.b_banked 
	FROM vw_ems a)
	UNION
	(SELECT a.org_id, a.period_id, a.end_date, a.tax_type_name, 
		a.account_number, a.entity_name, a.employee_id,
		'0.0'::real, (a.b_amount + a.b_additional + a.b_employer) 
	FROM vw_employee_tax_types a)
	UNION
	(SELECT a.org_id, a.period_id, a.end_date, 'Employer - ' || a.tax_type_name, 
		a.account_number, a.entity_name, a.employee_id,
		a.b_employer, '0.0'::real
	FROM vw_employee_tax_types a
	WHERE (a.employer <> 0))
	UNION
	(SELECT a.org_id, a.period_id, a.end_date, a.adjustment_name, a.account_number, 
		a.entity_name, a.employee_id,
		SUM(CASE WHEN a.adjustment_type = 1 THEN a.b_amount - a.b_paid_amount ELSE '0.0'::real END),
		SUM(CASE WHEN a.adjustment_type = 2 THEN a.b_amount - a.b_paid_amount ELSE '0.0'::real END)
	FROM vw_employee_adjustments a
	WHERE (a.visible = true) AND (a.adjustment_type < 3)
	GROUP BY a.org_id, a.period_id, a.end_date, a.adjustment_name, a.account_number, 
		a.entity_name, a.employee_id)
	UNION
	(SELECT a.org_id, a.period_id, a.travel_date, 'Transport' as description, 
		a.post_account, a.entity_name, a.employee_id,
		(a.b_full_amount - a.b_cash_paid), '0.0'::real
	FROM vw_employee_per_diem a
	WHERE (a.approve_status = 'Approved'))
	UNION
	(SELECT ea.org_id, ea.period_id, ea.end_date, 'SALARY ADVANCE' as description, 
		ea.gl_payroll_account, ea.entity_name, ea.employee_id,
		ea.b_advance_amount, '0.0'::real
	FROM vw_employee_advances as ea
	WHERE (ea.in_payroll = true))
	UNION
	(SELECT ead.org_id, ead.period_id, ead.end_date, 'ADVANCE DEDUCTION' as description, 
		ead.gl_payroll_account, ead.entity_name, ead.employee_id,
		'0.0'::real, ead.b_advance_deduction
	FROM vw_advance_deductions as ead
	WHERE (ead.in_payroll = true))) as b
	ORDER BY gl_payroll_account desc, dr_amt desc, cr_amt desc;

CREATE VIEW vw_payroll_ledger AS
	SELECT org_id, period_id, end_date, description, gl_payroll_account, dr_amt, cr_amt 
	FROM 
	((SELECT a.org_id, a.period_id, a.end_date, 'BASIC SALARY' as description, a.gl_payroll_account, 
		sum(a.b_basic_pay) as dr_amt, '0.0'::real as cr_amt
	FROM vw_ems a
		GROUP BY a.org_id, a.period_id, a.end_date, a.gl_payroll_account)
	UNION
	(SELECT a.org_id, a.period_id, a.end_date, 'SALARY PAYMENTS', a.gl_payment_account, 
		'0.0'::real, sum(a.b_banked)
	FROM vw_ems a
		GROUP BY a.org_id, a.period_id, a.end_date, a.gl_payment_account)
	UNION
	(SELECT a.org_id, a.period_id, a.end_date, a.tax_type_name, a.account_number, 
		'0.0'::real, sum(a.b_amount + a.b_additional + a.b_employer)
	FROM vw_employee_tax_types a
		GROUP BY a.org_id, a.period_id, a.end_date, a.tax_type_name, a.account_number)
	UNION
	(SELECT a.org_id, a.period_id, a.end_date, 'Employer - ' || a.tax_type_name, a.account_number,
		sum(a.b_employer), '0.0'::real
	FROM vw_employee_tax_types a
	WHERE (a.employer <> 0)
		GROUP BY a.org_id, a.period_id, a.end_date, a.tax_type_name, a.account_number)
	UNION
	(SELECT a.org_id, a.period_id, a.end_date, a.adjustment_name, a.account_number,
		SUM(CASE WHEN a.adjustment_type = 1 THEN a.b_amount - a.b_paid_amount ELSE '0.0'::real END),
		SUM(CASE WHEN a.adjustment_type = 2 THEN a.b_amount - a.b_paid_amount ELSE '0.0'::real END)
	FROM vw_employee_adjustments a
	WHERE (a.visible = true) AND (a.adjustment_type < 3)
		GROUP BY a.org_id, a.period_id, a.end_date, a.adjustment_name, a.account_number)
	UNION
	(SELECT a.org_id, a.period_id, a.travel_date, 'Transport' as description, a.post_account, 
		sum(a.b_full_amount - a.b_cash_paid), '0.0'::real
	FROM vw_employee_per_diem a
	WHERE (a.approve_status = 'Approved')
		GROUP BY a.org_id, a.period_id, a.travel_date, a.post_account)
	UNION
	(SELECT ea.org_id, ea.period_id, ea.end_date, 'SALARY ADVANCE' as description, ea.gl_payroll_account,
		sum(ea.b_advance_amount), '0.0'::real
	FROM vw_employee_advances as ea
	WHERE (ea.in_payroll = true)
		GROUP BY ea.org_id, ea.period_id, ea.end_date, ea.gl_payroll_account)
	UNION
	(SELECT ead.org_id, ead.period_id, ead.end_date, 'ADVANCE DEDUCTION' as description, ead.gl_payroll_account, 
		'0.0'::real, sum(ead.b_advance_deduction)
	FROM vw_advance_deductions as ead
	WHERE (ead.in_payroll = true)
		GROUP BY ead.org_id, ead.period_id, ead.end_date, ead.gl_payroll_account)) as b
	ORDER BY gl_payroll_account desc, dr_amt desc, cr_amt desc;
	
CREATE VIEW vw_sun_ledger_trx AS
	SELECT org_id, period_id, end_date, entity_id,
		gl_payroll_account, description,
		department_account,  employee_id, function_code,
		description2, round(amount::numeric, 1) as gl_amount, debit_credit,
		(period_id::varchar || '.' || entity_id::varchar || '.' || COALESCE(gl_payroll_account, '')) as sun_ledger_id
	FROM
	((SELECT a.org_id, a.period_id, a.end_date, a.entity_id,
		a.gl_payroll_account, 'Payroll' as description, 
		d.department_account, a.employee_id, d.function_code,
		to_char(a.start_date, 'Month YYYY') || ' - Basic Pay' as description2, 
		a.basic_pay as amount, 'D' as debit_credit
	FROM vw_employee_month a INNER JOIN departments d ON a.department_id = d.department_id)
	UNION
	(SELECT a.org_id, a.period_id, a.end_date, a.entity_id,
		a.employee_id, a.entity_name,
		'', '', '',
		to_char(a.start_date, 'Month YYYY') || ' - Netpay' as description2, 
		net_pay as amount, 'C' as debit_credit
	FROM vw_employee_month a)
	UNION
	(SELECT a.org_id, a.period_id, a.end_date, a.entity_id,
		a.account_number, a.adjustment_name, 
		a.department_account, a.employee_id, a.function_code,
		to_char(a.start_date, 'Month YYYY') || ' - ' || a.adjustment_name as description2, 
			
		sum(a.amount), 'D' as debit_credit
	FROM vw_employee_adjustments a
	WHERE (a.visible = true) AND (a.adjustment_type = 1)
	GROUP BY a.org_id, a.period_id, a.end_date, a.entity_id,
		a.account_number, a.adjustment_name, 
		a.department_account, a.employee_id, a.function_code, a.start_date)
	UNION
	(SELECT a.org_id, a.period_id, a.end_date, a.entity_id,
		a.account_number, a.adjustment_name, 
		a.department_account, a.employee_id, a.function_code,
		to_char(a.start_date, 'Month YYYY') || ' - ' || a.adjustment_name as description2, 
			
		sum(a.amount), 'C' as debit_credit
	FROM vw_employee_adjustments a
	WHERE (a.visible = true) AND (a.adjustment_type = 2)
	GROUP BY a.org_id, a.period_id, a.end_date, a.entity_id,
		a.account_number, a.adjustment_name, 
		a.department_account, a.employee_id, a.function_code,
		a.start_date)
	UNION
	(SELECT a.org_id, a.period_id, a.end_date, a.entity_id,
		a.account_number, a.tax_type_name,
		a.department_account, a.employee_id, a.function_code,
		to_char(a.start_date, 'Month YYYY') || ' - ' || a.tax_type_name || ' - Deduction',
		(a.amount + a.additional + a.employer), 'C' as debit_credit
	FROM vw_employee_tax_types a)
	UNION
	(SELECT a.org_id, a.period_id, a.end_date, a.entity_id,
		a.employer_account, a.tax_type_name,
		a.department_account, a.employee_id, a.function_code,
		to_char(a.start_date, 'Month YYYY') || ' - ' || a.tax_type_name || ' - Contribution',
		a.employer, 'D' as debit_credit
	FROM vw_employee_tax_types a
	WHERE a.employer > 0)
	UNION
	(SELECT a.org_id, a.period_id, a.end_date, a.entity_id,
		a.employee_id, a.entity_name,
		'', '', '',
		to_char(a.start_date, 'Month YYYY') || ' - Payroll Banking' as description2, 
		banked as amount, 'D' as debit_credit
	FROM vw_employee_month a)
	UNION
	(SELECT a.org_id, a.period_id, a.end_date, a.entity_id,
		a.gl_payment_account, 'Bank Account',
		'', '', '',
		to_char(a.start_date, 'Month YYYY') || ' - Payroll Banking' as description2, 
		banked as amount, 'C' as debit_credit
	FROM vw_employee_month a)) as b
	ORDER BY gl_payroll_account desc, amount desc, debit_credit desc;
	

CREATE TRIGGER upd_action BEFORE INSERT OR UPDATE ON employee_overtime
    FOR EACH ROW EXECUTE PROCEDURE upd_action();

CREATE TRIGGER upd_action BEFORE INSERT OR UPDATE ON employee_per_diem
    FOR EACH ROW EXECUTE PROCEDURE upd_action();

CREATE TRIGGER upd_action BEFORE INSERT OR UPDATE ON claims
    FOR EACH ROW EXECUTE PROCEDURE upd_action();

CREATE OR REPLACE FUNCTION ins_taxes() RETURNS trigger 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;
$$ LANGUAGE plpgsql;

CREATE TRIGGER ins_taxes AFTER INSERT ON employees
    FOR EACH ROW EXECUTE PROCEDURE ins_taxes();
    

CREATE OR REPLACE FUNCTION get_formula_adjustment(int, int, real) RETURNS float 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;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION generate_payroll(varchar(12), varchar(12), varchar(12), varchar(12)) RETURNS varchar(120) 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;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION ins_period_tax_types() RETURNS trigger 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;
$$ LANGUAGE plpgsql;

CREATE TRIGGER ins_period_tax_types AFTER INSERT ON period_tax_types
    FOR EACH ROW EXECUTE PROCEDURE ins_period_tax_types();
    
CREATE OR REPLACE FUNCTION ins_employee_month() RETURNS trigger 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;
$$ LANGUAGE plpgsql;

CREATE TRIGGER ins_employee_month BEFORE INSERT ON employee_month
    FOR EACH ROW EXECUTE PROCEDURE ins_employee_month();

CREATE OR REPLACE FUNCTION upd_employee_month() RETURNS trigger 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;
$$ LANGUAGE plpgsql;

CREATE TRIGGER upd_employee_month AFTER INSERT ON employee_month
    FOR EACH ROW EXECUTE PROCEDURE upd_employee_month();

CREATE OR REPLACE FUNCTION gettax(float, int) RETURNS float 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;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION get_employee_tax(int, int) RETURNS float 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;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION updtax(int, int) RETURNS float 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;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION process_payroll(varchar(12), varchar(12), varchar(12), varchar(12)) RETURNS varchar(120) 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;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION ins_employee_adjustments() RETURNS trigger 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;
$$ LANGUAGE plpgsql;

CREATE TRIGGER ins_Employee_Adjustments BEFORE INSERT OR UPDATE ON Employee_Adjustments
    FOR EACH ROW EXECUTE PROCEDURE ins_Employee_Adjustments();

CREATE OR REPLACE FUNCTION upd_employee_adjustments() RETURNS trigger 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;
$$ LANGUAGE plpgsql;

CREATE TRIGGER upd_employee_adjustments AFTER INSERT OR UPDATE ON employee_adjustments
    FOR EACH ROW EXECUTE PROCEDURE upd_employee_adjustments();

CREATE OR REPLACE FUNCTION upd_employee_per_diem() RETURNS trigger 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;
$$ LANGUAGE plpgsql;

CREATE TRIGGER upd_Employee_Per_Diem BEFORE INSERT OR UPDATE ON Employee_Per_Diem
    FOR EACH ROW EXECUTE PROCEDURE upd_Employee_Per_Diem();

CREATE OR REPLACE FUNCTION process_ledger(varchar(12), varchar(12), varchar(12)) RETURNS varchar(120) 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;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION del_period(int) RETURNS varchar(120) 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;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION increment_payroll(varchar(12), varchar(12), varchar(12), varchar(12)) RETURNS varchar(120) 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;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION add_adjustment(varchar(12), varchar(12), varchar(12), varchar(12)) RETURNS varchar(120) 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;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION process_pensions(varchar(12), varchar(12), varchar(12)) RETURNS varchar(120) 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;
$$ LANGUAGE plpgsql;

CREATE TRIGGER upd_action BEFORE INSERT OR UPDATE ON employee_advances
    FOR EACH ROW EXECUTE PROCEDURE upd_action();
    
    
CREATE OR REPLACE FUNCTION advance_aplication(varchar(12), varchar(12), varchar(12)) RETURNS varchar(120) 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;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION ins_employee_advances() RETURNS trigger 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;
$$ LANGUAGE plpgsql;

CREATE TRIGGER ins_employee_advances BEFORE INSERT OR UPDATE ON employee_advances
    FOR EACH ROW EXECUTE PROCEDURE ins_employee_advances();
    
CREATE OR REPLACE FUNCTION claims_aplication(varchar(12), varchar(12), varchar(12)) RETURNS varchar(120) 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;
$$ LANGUAGE plpgsql;