{"id":27431,"date":"2023-05-26T21:09:24","date_gmt":"2023-05-26T21:09:24","guid":{"rendered":"https:\/\/bcccog.org\/cloned\/?p=27431"},"modified":"2023-05-26T21:09:27","modified_gmt":"2023-05-26T21:09:27","slug":"payroll-table","status":"publish","type":"post","link":"https:\/\/bcccog.org\/?p=27431","title":{"rendered":"payroll table"},"content":{"rendered":"<style type=\"text\/css\"><\/style>\n<p class=\"wp-block-paragraph\">CREATE TABLE adjustment_effects (<br>adjustment_effect_id integer primary key,<br>adjustment_effect_name varchar(50) not null<br>);<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE TABLE adjustments (<br>adjustment_id serial primary key,<br>currency_id integer references currency,<br>adjustment_effect_id integer references adjustment_effects,<br>org_id integer references orgs,<br>adjustment_name varchar(50) not null,<br>adjustment_type integer not null,<br>adjustment_order integer default 0 not null,<br>earning_code integer,<br>formural varchar(430),<br>default_amount real default 0 not null,<br>monthly_update boolean default true not null,<br>in_payroll boolean default true not null,<br>in_tax boolean default true not null,<br>visible boolean default true not null,<br>running_balance boolean default false not null,<br>reduce_balance boolean default false not null,<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>tax_reduction_ps        float default 0 not null,\ntax_relief_ps           float default 0 not null,\ntax_max_allowed         float default 0 not null,\n\naccount_number          varchar(32),\ndetails                 text,\n\nUNIQUE(adjustment_name, org_id)<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">);<br>CREATE INDEX adjustments_currency_id ON adjustments(currency_id);<br>CREATE INDEX adjustments_adjustment_effect_id ON adjustments(adjustment_effect_id);<br>CREATE INDEX adjustments_org_id ON adjustments(org_id);<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE TABLE claim_types (<br>claim_type_id serial primary key,<br>adjustment_id integer references adjustments,<br>org_id integer references orgs,<br>claim_type_name varchar(50),<br>details text<br>);<br>CREATE INDEX claim_types_adjustment_id ON claim_types(adjustment_id);<br>CREATE INDEX claim_types_org_id ON claim_types(org_id);<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE TABLE default_adjustments (<br>default_adjustment_id serial primary key,<br>entity_id integer references entitys,<br>adjustment_id integer references adjustments,<br>org_id integer references orgs,<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>amount                  float default 0 not null,\nbalance                 float default 0 not null,\nfinal_date              date,\nactive                  boolean default true,\n\nNarrative               varchar(240)<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">);<br>CREATE INDEX default_adjustments_entity_id ON default_adjustments (entity_id);<br>CREATE INDEX default_adjustments_adjustment_id ON default_adjustments (adjustment_id);<br>CREATE INDEX default_adjustments_org_id ON default_adjustments(org_id);<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE TABLE default_banking (<br>default_banking_id serial primary key,<br>entity_id integer references entitys,<br>bank_branch_id integer references bank_branch,<br>currency_id integer references currency,<br>org_id integer references orgs,<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>amount                  float default 0 not null,\nps_amount               float default 0 not null,\nfinal_date              date,\nactive                  boolean default true,\n\nbank_account            varchar(64),\n\nNarrative               varchar(240)<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">);<br>CREATE INDEX default_banking_entity_id ON default_banking (entity_id);<br>CREATE INDEX default_banking_bank_branch_id ON default_banking (bank_branch_id);<br>CREATE INDEX default_banking_currency_id ON default_banking (currency_id);<br>CREATE INDEX default_banking_org_id ON default_banking(org_id);<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE TABLE pensions (<br>pension_id serial primary key,<br>entity_id integer references entitys,<br>adjustment_id integer references adjustments,<br>contribution_id integer references adjustments,<br>org_id integer references orgs,<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>pension_company         varchar(50) not null,\npension_number          varchar(50),\nactive                  boolean default true,\n\namount                  float default 0 not null,\nuse_formura             boolean default false not null,\n\nemployer_ps             float default 0 not null,\nemployer_amount         float default 0 not null,\nemployer_formural       boolean default false not null,\n\ndetails                 text<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">);<br>CREATE INDEX pension_entity_id ON pensions (entity_id);<br>CREATE INDEX pension_adjustment_id ON pensions (adjustment_id);<br>CREATE INDEX pension_contribution_id ON pensions (contribution_id);<br>CREATE INDEX pension_org_id ON pensions (org_id);<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE TABLE employee_month (<br>employee_month_id serial primary key,<br>entity_id integer references entitys not null,<br>period_id integer references periods not null,<br>bank_branch_id integer references bank_branch not null,<br>pay_group_id integer references pay_groups not null,<br>department_role_id integer references department_roles not null,<br>currency_id integer references currency,<br>org_id integer references orgs,<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>exchange_rate           real default 1 not null,\nbank_account            varchar(32),\nbasic_pay               float default 0 not null,\ndetails                 text,\nunique (entity_id, period_id)<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">);<br>CREATE INDEX employee_month_entity_id ON employee_month (entity_id);<br>CREATE INDEX employee_month_period_id ON employee_month (period_id);<br>CREATE INDEX employee_month_bank_branch_id ON employee_month (bank_branch_id);<br>CREATE INDEX employee_month_bank_pay_group_id ON employee_month (pay_group_id);<br>CREATE INDEX employee_month_currency_id ON employee_month (currency_id);<br>CREATE INDEX employee_month_org_id ON employee_month(org_id);<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE TABLE employee_tax_types (<br>employee_tax_type_id serial primary key,<br>employee_month_id integer references employee_month not null,<br>tax_type_id integer references tax_types not null,<br>org_id integer references orgs,<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>tax_identification      varchar(50),\nin_tax                  boolean not null default false,\namount                  float default 0 not null,\nadditional              float default 0 not null,\nemployer                float default 0 not null,\nexchange_rate           real default 1 not null,\n\nnarrative               varchar(240)<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">);<br>CREATE INDEX employee_tax_types_employee_month_id ON employee_tax_types (employee_month_id);<br>CREATE INDEX employee_tax_types_tax_type_id ON employee_tax_types (tax_type_id);<br>CREATE INDEX employee_tax_types_org_id ON employee_tax_types(org_id);<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE TABLE employee_advances (<br>employee_advance_id serial primary key,<br>employee_month_id integer references employee_month,<br>currency_id integer references currency,<br>entity_id integer not null references entitys,<br>org_id integer references orgs,<br>pay_date date default current_date not null,<br>pay_upto date not null,<br>pay_period integer default 3 not null,<br>amount float not null,<br>payment_amount float not null,<br>exchange_rate real default 1 not null,<br>in_payroll boolean not null default false,<br>completed boolean not null default false,<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>application_date        timestamp default now(),\napprove_status          varchar(16) default 'Draft' not null,\nworkflow_table_id       integer,\naction_date             timestamp,\n\nnarrative               varchar(240),\ndetails                 text<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">);<br>CREATE INDEX employee_advances_employee_month_id ON employee_advances (employee_month_id);<br>CREATE INDEX employee_advances_currency_id ON employee_advances (currency_id);<br>CREATE INDEX employee_advances_entity_id ON employee_advances (entity_id);<br>CREATE INDEX employee_advances_org_id ON employee_advances(org_id);<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE TABLE advance_deductions (<br>advance_deduction_id serial primary key,<br>employee_month_id integer references employee_month not null,<br>org_id integer references orgs,<br>pay_date date default current_date not null,<br>amount float not null,<br>exchange_rate real default 1 not null,<br>in_payroll boolean not null default true,<br>narrative varchar(240)<br>);<br>CREATE INDEX advance_deductions_employee_month_id ON advance_deductions (employee_month_id);<br>CREATE INDEX advance_deductions_org_id ON advance_deductions(org_id);<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE TABLE employee_adjustments (<br>employee_adjustment_id serial primary key,<br>employee_month_id integer references employee_month not null,<br>adjustment_id integer references adjustments not null,<br>pension_id integer references pensions,<br>org_id integer references orgs,<br>adjustment_type integer,<br>adjustment_factor integer default 1 not null,<br>pay_date date default current_date not null,<br>amount float not null,<br>balance float,<br>paid_amount float default 0 not null,<br>exchange_rate real default 1 not null,<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>tax_reduction_amount    float default 0 not null,\ntax_relief_amount       float default 0 not null,\n\nin_payroll              boolean not null default true,\nin_tax                  boolean not null default true,\nvisible                 boolean not null default true,\nnarrative               varchar(240)<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">);<br>CREATE INDEX employee_adjustments_employee_month_id ON employee_adjustments (employee_month_id);<br>CREATE INDEX employee_adjustments_adjustment_id ON employee_adjustments (adjustment_id);<br>CREATE INDEX employee_adjustments_pension_id ON employee_adjustments (pension_id);<br>CREATE INDEX employee_adjustments_org_id ON employee_adjustments(org_id);<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE TABLE claims (<br>claim_id serial primary key,<br>claim_type_id integer references claim_types,<br>entity_id integer references entitys,<br>employee_adjustment_id integer references employee_adjustments,<br>org_id integer references orgs,<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>claim_date              date not null,\nin_payroll              boolean not null default false,\nnarrative               varchar(250),\n\nprocess_claim           boolean not null default false,\nprocess_date            date,\n\napplication_date        timestamp default now(),\napprove_status          varchar(16) default 'Draft' not null,\nworkflow_table_id       integer,\naction_date             timestamp,\n\ndetails                 text<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">);<br>CREATE INDEX claims_claim_type_id ON claims(claim_type_id);<br>CREATE INDEX claims_entity_id ON claims(entity_id);<br>CREATE INDEX claims_employee_adjustment_id ON claims(employee_adjustment_id);<br>CREATE INDEX claims_org_id ON claims(org_id);<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE TABLE claim_details (<br>claim_detail_id serial primary key,<br>claim_id integer references claims,<br>currency_id integer references currency,<br>org_id integer references orgs,<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>nature_of_expence       varchar(50),\nreceipt_number          varchar(50),\namount                  real not null,\nexchange_rate           real default 1 not null,\nexpense_code            varchar(50),\n\ndetails                 text<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">);<br>CREATE INDEX claim_details_claim_id ON claim_details(claim_id);<br>CREATE INDEX claim_details_currency_id ON claim_details(currency_id);<br>CREATE INDEX claim_details_org_id ON claim_details(org_id);<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE TABLE employee_overtime (<br>employee_overtime_id serial primary key,<br>employee_month_id integer references employee_month not null,<br>org_id integer references orgs,<br>overtime_date date not null,<br>overtime float not null,<br>overtime_rate float not null,<br>application_date timestamp default now(),<br>approve_status varchar(16) default &#8216;draft&#8217; not null,<br>workflow_table_id integer,<br>action_date timestamp,<br>narrative varchar(240),<br>details text<br>);<br>CREATE INDEX employee_overtime_employee_month_id ON employee_overtime (employee_month_id);<br>CREATE INDEX employee_overtime_org_id ON employee_overtime(org_id);<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE TABLE employee_per_diem (<br>employee_per_diem_id serial primary key,<br>employee_month_id integer references employee_month not null,<br>currency_id integer references currency,<br>org_id integer references orgs,<br>travel_date date not null,<br>return_date date not null,<br>days_travelled integer not null,<br>per_diem float default 0 not null,<br>cash_paid float default 0 not null,<br>tax_amount float default 0 not null,<br>full_amount float default 0 not null,<br>exchange_rate real default 1 not null,<br>travel_to varchar(240),<br>post_account varchar(32),<br>application_date timestamp default now(),<br>approve_status varchar(16) default &#8216;draft&#8217; not null,<br>workflow_table_id integer,<br>action_date timestamp,<br>completed boolean default false not null,<br>details text<br>);<br>CREATE INDEX employee_per_diem_employee_month_id ON employee_per_diem (employee_month_id);<br>CREATE INDEX employee_per_diem_currency_id ON employee_per_diem (currency_id);<br>CREATE INDEX employee_per_diem_org_id ON employee_per_diem(org_id);<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE TABLE employee_banking (<br>employee_banking_id serial primary key,<br>employee_month_id integer references employee_month not null,<br>bank_branch_id integer references bank_branch,<br>currency_id integer references currency,<br>org_id integer references orgs,<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>amount                  float default 0 not null,\nexchange_rate           real default 1 not null,\nactive                  boolean default true,\n\nbank_account            varchar(64),\n\nNarrative               varchar(240)<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">);<br>CREATE INDEX employee_banking_employee_month_id ON employee_banking (employee_month_id);<br>CREATE INDEX employee_banking_bank_branch_id ON employee_banking (bank_branch_id);<br>CREATE INDEX employee_banking_currency_id ON employee_banking (currency_id);<br>CREATE INDEX employee_banking_org_id ON employee_banking(org_id);<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE VIEW vw_adjustments AS<br>SELECT currency.currency_id, currency.currency_name, currency.currency_symbol,<br>adjustments.org_id, adjustments.adjustment_id, adjustments.adjustment_name, adjustments.adjustment_type,<br>adjustments.adjustment_order, adjustments.earning_code, adjustments.formural, adjustments.monthly_update,<br>adjustments.in_payroll, adjustments.in_tax, adjustments.visible, adjustments.running_balance,<br>adjustments.reduce_balance, adjustments.tax_reduction_ps, adjustments.tax_relief_ps,<br>adjustments.tax_max_allowed, adjustments.account_number, adjustments.details<br>FROM adjustments INNER JOIN currency ON adjustments.currency_id = currency.currency_id;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE VIEW vw_claim_types AS<br>SELECT adjustments.adjustment_id, adjustments.adjustment_name,<br>claim_types.org_id, claim_types.claim_type_id, claim_types.claim_type_name, claim_types.details<br>FROM claim_types INNER JOIN adjustments ON claim_types.adjustment_id = adjustments.adjustment_id;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE VIEW vw_claims AS<br>SELECT claim_types.claim_type_id, claim_types.claim_type_name,<br>entitys.entity_id, entitys.entity_name,<br>claims.org_id, claims.claim_id, claims.claim_date, claims.narrative, claims.in_payroll,<br>claims.process_claim, claims.process_date,<br>claims.application_date, claims.approve_status, claims.workflow_table_id, claims.action_date,<br>claims.details<br>FROM claims INNER JOIN claim_types ON claims.claim_type_id = claim_types.claim_type_id<br>INNER JOIN entitys ON claims.entity_id = entitys.entity_id;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE VIEW vw_claim_details AS<br>SELECT vw_claims.claim_type_id, vw_claims.claim_type_name, vw_claims.entity_id, vw_claims.entity_name,<br>vw_claims.claim_id, vw_claims.claim_date, vw_claims.narrative, vw_claims.application_date,<br>vw_claims.approve_status, vw_claims.workflow_table_id, vw_claims.action_date,<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>    currency.currency_id, currency.currency_name, currency.currency_symbol,\n    claim_details.org_id, claim_details.claim_detail_id, claim_details.nature_of_expence, \n    claim_details.receipt_number, claim_details.amount, claim_details.exchange_rate, claim_details.expense_code, \n    claim_details.details\nFROM claim_details INNER JOIN vw_claims ON claim_details.claim_id = vw_claims.claim_id\n    INNER JOIN currency ON claim_details.currency_id = currency.currency_id;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE VIEW vw_default_adjustments AS<br>SELECT vw_adjustments.adjustment_id, vw_adjustments.adjustment_name, vw_adjustments.adjustment_type,<br>vw_adjustments.currency_id, vw_adjustments.currency_name, vw_adjustments.currency_symbol,<br>entitys.entity_id, entitys.entity_name,<br>default_adjustments.org_id, default_adjustments.default_adjustment_id, default_adjustments.amount, default_adjustments.active,<br>default_adjustments.final_date, default_adjustments.narrative<br>FROM default_adjustments INNER JOIN vw_adjustments ON default_adjustments.adjustment_id = vw_adjustments.adjustment_id<br>INNER JOIN entitys ON default_adjustments.entity_id = entitys.entity_id;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE VIEW vw_default_banking AS<br>SELECT entitys.entity_id, entitys.entity_name,<br>vw_bank_branch.bank_id, vw_bank_branch.bank_name, vw_bank_branch.bank_branch_id,<br>vw_bank_branch.bank_branch_name, vw_bank_branch.bank_branch_code,<br>currency.currency_id, currency.currency_name, currency.currency_symbol,<br>default_banking.org_id, default_banking.default_banking_id, default_banking.amount,<br>default_banking.ps_amount, default_banking.final_date, default_banking.active,<br>default_banking.bank_account, default_banking.narrative<br>FROM default_banking INNER JOIN entitys ON default_banking.entity_id = entitys.entity_id<br>INNER JOIN vw_bank_branch ON default_banking.bank_branch_id = vw_bank_branch.bank_branch_id<br>INNER JOIN currency ON default_banking.currency_id = currency.currency_id;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE VIEW vw_pensions AS<br>SELECT entitys.entity_id, entitys.entity_name,<br>adjustments.adjustment_id, adjustments.adjustment_name,<br>currency.currency_id, currency.currency_name, currency.currency_symbol,<br>pensions.contribution_id, contributions.adjustment_name as contribution_name,<br>pensions.org_id, pensions.pension_id, pensions.pension_company, pensions.pension_number,<br>pensions.amount, pensions.use_formura, pensions.employer_ps, pensions.employer_amount,<br>pensions.employer_formural, pensions.active, pensions.details<br>FROM pensions INNER JOIN entitys ON pensions.entity_id = entitys.entity_id<br>INNER JOIN adjustments ON pensions.adjustment_id = adjustments.adjustment_id<br>INNER JOIN adjustments as contributions ON pensions.contribution_id = contributions.adjustment_id<br>INNER JOIN currency ON adjustments.currency_id = currency.currency_id;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE OR REPLACE FUNCTION getAdjustment(int, int, int) RETURNS float AS $$<br>DECLARE<br>adjustment float;<br>BEGIN<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>IF ($3 = 1) THEN\n    SELECT SUM(exchange_rate * amount) INTO adjustment\n    FROM employee_adjustments\n    WHERE (Employee_Month_ID = $1) AND (adjustment_type = $2);\nELSIF ($3 = 2) THEN\n    SELECT SUM(exchange_rate * amount) INTO adjustment\n    FROM employee_adjustments\n    WHERE (Employee_Month_ID = $1) AND (adjustment_type = $2) AND (In_payroll = true) AND (Visible = true);\nELSIF ($3 = 3) THEN\n    SELECT SUM(exchange_rate * amount) INTO adjustment\n    FROM employee_adjustments\n    WHERE (Employee_Month_ID = $1) AND (adjustment_type = $2) AND (In_Tax = true);\nELSIF ($3 = 4) THEN\n    SELECT SUM(exchange_rate * amount) INTO adjustment\n    FROM employee_adjustments\n    WHERE (Employee_Month_ID = $1) AND (adjustment_type = $2) AND (In_payroll = true);\nELSIF ($3 = 5) THEN\n    SELECT SUM(exchange_rate * amount) INTO adjustment\n    FROM employee_adjustments\n    WHERE (Employee_Month_ID = $1) AND (adjustment_type = $2) AND (Visible = true);\nELSIF ($3 = 11) THEN\n    SELECT SUM(exchange_rate * (amount + additional)) INTO adjustment\n    FROM employee_tax_types\n    WHERE (Employee_Month_ID = $1);\nELSIF ($3 = 12) THEN\n    SELECT SUM(exchange_rate * (amount + additional)) INTO adjustment\n    FROM employee_tax_types\n    WHERE (Employee_Month_ID = $1) AND (In_Tax = true);\nELSIF ($3 = 14) THEN\n    SELECT SUM(exchange_rate * (amount + additional)) INTO adjustment\n    FROM employee_tax_types\n    WHERE (Employee_Month_ID = $1) AND (Tax_Type_ID = $2);\nELSIF ($3 = 21) THEN\n    SELECT SUM(exchange_rate * amount * adjustment_factor) INTO adjustment\n    FROM employee_adjustments\n    WHERE (employee_month_id = $1) AND (in_tax = true);\nELSIF ($3 = 22) THEN\n    SELECT SUM(exchange_rate * amount * adjustment_factor) INTO adjustment\n    FROM employee_adjustments\n    WHERE (Employee_Month_ID = $1) AND (In_payroll = true) AND (Visible = true);\nELSIF ($3 = 23) THEN\n    SELECT SUM(exchange_rate * amount * adjustment_factor) INTO adjustment\n    FROM employee_adjustments\n    WHERE (employee_month_id = $1) AND (in_tax = true) AND (adjustment_factor = 1);\nELSIF ($3 = 24) THEN\n    SELECT SUM(exchange_rate * tax_reduction_amount) INTO adjustment\n    FROM employee_adjustments\n    WHERE (employee_month_id = $1) AND (in_tax = true) AND (adjustment_factor = -1);\nELSIF ($3 = 25) THEN\n    SELECT SUM(exchange_rate * tax_relief_amount) INTO adjustment\n    FROM employee_adjustments\n    WHERE (employee_month_id = $1) AND (in_tax = true) AND (adjustment_factor = -1);\nELSIF ($3 = 26) THEN\n    SELECT SUM(exchange_rate * amount) INTO adjustment\n    FROM employee_adjustments\n    WHERE (employee_month_id = $1) AND (pension_id is not null) AND (adjustment_type = 2);\nELSIF ($3 = 27) THEN\n    SELECT SUM(employee_adjustments.exchange_rate * employee_adjustments.amount) INTO adjustment\n    FROM employee_adjustments INNER JOIN adjustments ON employee_adjustments.adjustment_id = adjustments.adjustment_id\n    WHERE (employee_adjustments.employee_month_id = $1) AND (adjustments.adjustment_effect_id = $2);\nELSIF ($3 = 28) THEN\n    SELECT SUM(employee_adjustments.exchange_rate * employee_adjustments.tax_relief_amount) INTO adjustment\n    FROM employee_adjustments INNER JOIN adjustments ON employee_adjustments.adjustment_id = adjustments.adjustment_id\n    WHERE (employee_adjustments.employee_month_id = $1) AND (adjustments.adjustment_effect_id = $2);\nELSIF ($3 = 31) THEN\n    SELECT SUM(overtime * overtime_rate) INTO adjustment\n    FROM employee_overtime\n    WHERE (Employee_Month_ID = $1) AND (approve_status = 'Approved');\nELSIF ($3 = 32) THEN\n    SELECT SUM(exchange_rate * tax_amount) INTO adjustment\n    FROM employee_per_diem\n    WHERE (Employee_Month_ID = $1) AND (approve_status = 'Approved');\nELSIF ($3 = 33) THEN\n    SELECT SUM(exchange_rate * (full_amount -  cash_paid)) INTO adjustment\n    FROM Employee_Per_Diem\n    WHERE (Employee_Month_ID = $1) AND (approve_status = 'Approved');\nELSIF ($3 = 34) THEN\n    SELECT SUM(exchange_rate * amount) INTO adjustment\n    FROM employee_advances\n    WHERE (Employee_Month_ID = $1) AND (in_payroll = true);\nELSIF ($3 = 35) THEN\n    SELECT SUM(exchange_rate * amount) INTO adjustment\n    FROM advance_deductions\n    WHERE (Employee_Month_ID = $1) AND (In_payroll = true);\nELSIF ($3 = 36) THEN\n    SELECT SUM(exchange_rate * paid_amount) INTO adjustment\n    FROM employee_adjustments\n    WHERE (Employee_Month_ID = $1) AND (In_payroll = true) AND (Visible = true);\nELSIF ($3 = 37) THEN\n    SELECT SUM(exchange_rate * tax_relief_amount) INTO adjustment\n    FROM employee_adjustments\n    WHERE (Employee_Month_ID = $1);\n\n    IF(adjustment IS NULL)THEN\n        adjustment := 0;\n    END IF;\nELSIF ($3 = 41) THEN\n    SELECT SUM(exchange_rate * amount) INTO adjustment\n    FROM employee_banking\n    WHERE (employee_month_id = $1);\nELSE\n    adjustment := 0;\nEND IF;\n\nIF(adjustment is null) THEN\n    adjustment := 0;\nEND IF;\n\nRETURN adjustment;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">END;<br>$$ LANGUAGE plpgsql;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE OR REPLACE FUNCTION getAdjustment(int, int) RETURNS float AS $$<br>DECLARE<br>adjustment float;<br>BEGIN<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>IF ($2 = 1) THEN\n    SELECT (Basic_Pay + getAdjustment(Employee_Month_ID, 4, 31) + getAdjustment(Employee_Month_ID, 4, 32) \n        + getAdjustment(Employee_Month_ID, 4, 23)) \n    INTO adjustment\n    FROM Employee_Month\n    WHERE (Employee_Month_ID = $1);\nELSIF ($2 = 2) THEN\n    SELECT (Basic_Pay + getAdjustment(Employee_Month_ID, 4, 31) + getAdjustment(Employee_Month_ID, 4, 32)\n        - getAdjustment(Employee_Month_ID, 4, 23) \n        - getAdjustment(Employee_Month_ID, 4, 12) - getAdjustment(Employee_Month_ID, 4, 24)) \n    INTO adjustment\n    FROM Employee_Month\n    WHERE (Employee_Month_ID = $1);\nELSIF ($2 = 3) THEN\n    SELECT (Basic_Pay + getAdjustment(Employee_Month_ID, 4, 31) + getAdjustment(Employee_Month_ID, 4, 32)) \n    INTO adjustment\n    FROM Employee_Month\n    WHERE (Employee_Month_ID = $1);\nELSE\n    adjustment := 0;\nEND IF;\n\nIF(adjustment is null) THEN\n    adjustment := 0;\nEND IF;\n\nRETURN adjustment;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">END;<br>$$ LANGUAGE plpgsql;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE OR REPLACE FUNCTION getAdvanceBalance(int, date) RETURNS float AS $$<br>DECLARE<br>advance FLOAT;<br>paid FLOAT;<br>BEGIN<br>SELECT SUM(Amount) INTO advance<br>FROM vw_employee_advances<br>WHERE (entity_id = $1) AND (start_date &lt;= $2) AND (approve_status = &#8216;Approved&#8217;);<br>IF (advance is null) THEN advance := 0; END IF;<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT SUM(Amount) INTO paid\nFROM vw_advance_deductions\nWHERE (entity_id = $1) AND (start_date &lt;= $2);\nIF (paid is null) THEN paid := 0; END IF;\n\nadvance := advance - paid;\n\nRETURN advance;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">END;<br>$$ LANGUAGE plpgsql;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE VIEW vw_employee_month AS<br>SELECT vw_periods.period_id, vw_periods.start_date, vw_periods.end_date, vw_periods.overtime_rate,<br>vw_periods.activated, vw_periods.closed, vw_periods.month_id, vw_periods.period_year, vw_periods.period_month,<br>vw_periods.quarter, vw_periods.semister, vw_periods.gl_payroll_account, vw_periods.is_posted,<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>    vw_bank_branch.bank_id, vw_bank_branch.bank_name, vw_bank_branch.bank_branch_id, \n    vw_bank_branch.bank_branch_name, vw_bank_branch.bank_branch_code,\n    pay_groups.pay_group_id, pay_groups.pay_group_name, pay_groups.gl_payment_account,\n    pay_groups.bank_header, pay_groups.bank_address,\n    vw_department_roles.department_id, vw_department_roles.department_name,\n    vw_department_roles.department_role_id, vw_department_roles.department_role_name, \n    entitys.entity_id, entitys.entity_name,\n    employees.employee_id, employees.surname, employees.first_name, employees.middle_name, employees.date_of_birth, \n    employees.gender, employees.nationality, employees.marital_status, employees.appointment_date, employees.exit_date, \n    employees.contract, employees.contract_period, employees.employment_terms, employees.identity_card,\n    (employees.Surname || ' ' || employees.First_name || ' ' || COALESCE(employees.Middle_name, '')) as employee_name,\n    currency.currency_id, currency.currency_name, currency.currency_symbol, employee_month.exchange_rate,\n\n    employee_month.org_id, employee_month.employee_month_id, employee_month.bank_account, employee_month.basic_pay, employee_month.details,\n    getAdjustment(employee_month.employee_month_id, 4, 31) as overtime,\n    getAdjustment(employee_month.employee_month_id, 1, 1) as full_allowance,\n    getAdjustment(employee_month.employee_month_id, 1, 2) as payroll_allowance,\n    getAdjustment(employee_month.employee_month_id, 1, 3) as tax_allowance,\n    getAdjustment(employee_month.employee_month_id, 2, 1) as full_deduction,\n    getAdjustment(employee_month.employee_month_id, 2, 2) as payroll_deduction,\n    getAdjustment(employee_month.employee_month_id, 2, 3) as tax_deduction,\n    getAdjustment(employee_month.employee_month_id, 3, 1) as full_expense,\n    getAdjustment(employee_month.employee_month_id, 3, 2) as payroll_expense,\n    getAdjustment(employee_month.employee_month_id, 3, 3) as tax_expense,\n    getAdjustment(employee_month.employee_month_id, 4, 11) as payroll_tax,\n    getAdjustment(employee_month.employee_month_id, 4, 12) as tax_tax,\n    getAdjustment(employee_month.employee_month_id, 4, 22) as net_Adjustment,\n    getAdjustment(employee_month.employee_month_id, 4, 33) as per_diem,\n    getAdjustment(employee_month.employee_month_id, 4, 34) as advance,\n    getAdjustment(employee_month.employee_month_id, 4, 35) as advance_deduction,\n    getAdjustment(employee_month.employee_month_id, 4, 41) as other_banks,\n    (employee_month.Basic_Pay + getAdjustment(employee_month.employee_month_id, 4, 31) + getAdjustment(employee_month.employee_month_id, 4, 22) \n    + getAdjustment(employee_month.employee_month_id, 4, 33) - getAdjustment(employee_month.employee_month_id, 4, 11)) as net_pay,\n    (employee_month.Basic_Pay + getAdjustment(employee_month.employee_month_id, 4, 31) + getAdjustment(employee_month.employee_month_id, 4, 22) \n    + getAdjustment(employee_month.employee_month_id, 4, 33) + getAdjustment(employee_month.employee_month_id, 4, 34)\n    - getAdjustment(employee_month.employee_month_id, 4, 11) - getAdjustment(employee_month.employee_month_id, 4, 35)\n    - getAdjustment(employee_month.employee_month_id, 4, 36)\n    - getAdjustment(employee_month.employee_month_id, 4, 41)) as banked,\n    (employee_month.Basic_Pay + getAdjustment(employee_month.employee_month_id, 4, 31) + getAdjustment(employee_month.employee_month_id, 1, 1) \n    + getAdjustment(employee_month.employee_month_id, 3, 1) + getAdjustment(employee_month.employee_month_id, 4, 33)) as cost\nFROM employee_month INNER JOIN vw_bank_branch ON employee_month.bank_branch_id = vw_bank_branch.bank_branch_id\n    INNER JOIN vw_periods ON employee_month.period_id = vw_periods.period_id\n    INNER JOIN pay_groups ON employee_month.pay_group_id = pay_groups.pay_group_id\n    INNER JOIN entitys ON employee_month.entity_id = entitys.entity_id\n    INNER JOIN vw_department_roles ON employee_month.department_role_id = vw_department_roles.department_role_id\n    INNER JOIN employees ON employee_month.entity_id = employees.entity_id\n    INNER JOIN currency ON employee_month.currency_id = currency.currency_id;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE VIEW vw_ems AS<br>SELECT em.org_id, em.period_id, em.start_date, em.end_date, em.overtime_rate, em.activated, em.closed, em.month_id,<br>em.period_year, em.period_month, em.quarter, em.semister, em.bank_header, em.bank_address,<br>em.gl_payroll_account, em.is_posted,<br>em.bank_id, em.bank_name, em.bank_branch_id, em.bank_branch_name, em.bank_branch_code,<br>em.pay_group_id, em.pay_group_name, em.gl_payment_account,<br>em.department_id, em.department_name, em.department_role_id, em.department_role_name,<br>em.entity_id, em.entity_name,<br>em.employee_id, em.surname, em.first_name, em.middle_name, em.date_of_birth, em.gender,<br>em.nationality, em.marital_status, em.appointment_date, em.exit_date, em.contract, em.contract_period,<br>em.employment_terms, em.identity_card, em.employee_name,<br>em.currency_id, em.currency_name, em.currency_symbol, em.exchange_rate,<br>em.employee_month_id, em.bank_account, em.basic_pay, em.details, em.overtime,<br>em.full_allowance, em.payroll_allowance, em.tax_allowance, em.full_deduction,<br>em.payroll_deduction, em.tax_deduction, em.full_expense, em.payroll_expense,<br>em.tax_expense, em.payroll_tax, em.tax_tax, em.net_adjustment, em.per_diem,<br>em.advance, em.advance_deduction, em.other_banks, em.net_pay, em.banked, em.cost,<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>    (em.basic_pay * em.exchange_rate) as b_basic_pay,\n    ((em.banked + em.other_banks) * em.exchange_rate) as b_banked\nFROM vw_employee_month em;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE VIEW vw_employee_month_list AS<br>SELECT vw_periods.period_id, vw_periods.start_date, vw_periods.end_date, vw_periods.overtime_rate,<br>vw_periods.activated, vw_periods.closed, vw_periods.month_id, vw_periods.period_year, vw_periods.period_month,<br>vw_periods.quarter, vw_periods.semister, vw_periods.gl_payroll_account, vw_periods.is_posted,<br>entitys.entity_id, entitys.entity_name,<br>pay_groups.pay_group_id, pay_groups.pay_group_name, pay_groups.gl_payment_account,<br>pay_groups.bank_header, pay_groups.bank_address,<br>employees.employee_id, employees.surname, employees.first_name, employees.middle_name, employees.date_of_birth,<br>employees.gender, employees.nationality, employees.marital_status, employees.appointment_date, employees.exit_date,<br>employees.contract, employees.contract_period, employees.employment_terms, employees.identity_card,<br>(employees.Surname || &#8216; &#8216; || employees.First_name || &#8216; &#8216; || COALESCE(employees.Middle_name, &#8221;)) as employee_name,<br>departments.department_id, departments.department_name, departments.department_account, departments.function_code,<br>department_roles.department_role_id, department_roles.department_role_name,<br>employee_month.org_id, employee_month.employee_month_id, employee_month.bank_account, employee_month.basic_pay,<br>employee_month.currency_id, employee_month.exchange_rate<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>FROM employee_month INNER JOIN vw_periods ON employee_month.period_id = vw_periods.period_id\n    INNER JOIN pay_groups ON employee_month.pay_group_id = pay_groups.pay_group_id\n    INNER JOIN entitys ON employee_month.entity_id = entitys.entity_id\n    INNER JOIN employees ON employee_month.entity_id = employees.entity_id\n    INNER JOIN department_roles ON employee_month.department_role_id = department_roles.department_role_id\n    INNER JOIN departments ON department_roles.department_id = departments.department_id;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE VIEW vw_employee_tax_types AS<br>SELECT eml.employee_month_id, eml.period_id, eml.start_date,<br>eml.month_id, eml.period_year, eml.period_month,<br>eml.end_date, eml.gl_payroll_account,<br>eml.entity_id, eml.entity_name, eml.employee_id, eml.identity_card,<br>eml.surname, eml.first_name, eml.middle_name, eml.date_of_birth,<br>eml.department_id, eml.department_name, eml.department_account, eml.function_code,<br>eml.department_role_id, eml.department_role_name,<br>tax_types.tax_type_id, tax_types.tax_type_name, tax_types.account_id, tax_types.tax_type_number,<br>tax_types.account_number, tax_types.employer_account,<br>employee_tax_types.org_id, employee_tax_types.employee_tax_type_id, employee_tax_types.tax_identification,<br>employee_tax_types.amount,<br>employee_tax_types.additional, employee_tax_types.employer, employee_tax_types.narrative,<br>currency.currency_id, currency.currency_name, currency.currency_symbol, employee_tax_types.exchange_rate,<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>    (employee_tax_types.exchange_rate * employee_tax_types.amount) as base_amount,\n    (employee_tax_types.exchange_rate * employee_tax_types.employer) as base_employer,\n    (employee_tax_types.exchange_rate * employee_tax_types.additional) as base_additional,\n\n    (employee_tax_types.exchange_rate * eml.exchange_rate * employee_tax_types.amount) as b_amount,\n    (employee_tax_types.exchange_rate * eml.exchange_rate * employee_tax_types.employer) as b_employer,\n    (employee_tax_types.exchange_rate * eml.exchange_rate * employee_tax_types.additional) as b_additional\n\nFROM employee_tax_types INNER JOIN vw_employee_month_list as eml ON employee_tax_types.employee_month_id = eml.employee_month_id\n    INNER JOIN tax_types ON (employee_tax_types.tax_type_id = tax_types.tax_type_id)\n    INNER JOIN currency ON tax_types.currency_id = currency.currency_id;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE VIEW vw_employee_tax_month AS<br>SELECT emp.period_id, emp.start_date, emp.end_date, emp.overtime_rate,<br>emp.activated, emp.closed, emp.month_id, emp.period_year, emp.period_month,<br>emp.quarter, emp.semister, emp.bank_header, emp.bank_address,<br>emp.gl_payroll_account, emp.is_posted,<br>emp.bank_id, emp.bank_name, emp.bank_branch_id,<br>emp.bank_branch_name, emp.bank_branch_code,<br>emp.pay_group_id, emp.pay_group_name, emp.department_id, emp.department_name,<br>emp.department_role_id, emp.department_role_name,<br>emp.entity_id, emp.entity_name,<br>emp.employee_id, emp.surname, emp.first_name, emp.middle_name, emp.date_of_birth,<br>emp.gender, emp.nationality, emp.marital_status, emp.appointment_date, emp.exit_date,<br>emp.contract, emp.contract_period, emp.employment_terms, emp.identity_card,<br>emp.employee_name,<br>emp.currency_id, emp.currency_name, emp.currency_symbol, emp.exchange_rate,<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>    emp.org_id, emp.employee_month_id, emp.bank_account, emp.basic_pay, emp.details,\n    emp.overtime, emp.full_allowance, emp.payroll_allowance, emp.tax_allowance,\n    emp.full_deduction, emp.payroll_deduction, emp.tax_deduction, emp.full_expense,\n    emp.payroll_expense, emp.tax_expense, emp.payroll_tax, emp.tax_tax,\n    emp.net_adjustment, emp.per_diem, emp.advance, emp.advance_deduction,\n    emp.net_pay, emp.banked, emp.cost,\n\n    tax_types.tax_type_id, tax_types.tax_type_name, tax_types.account_id, tax_types.use_key_id,\n    employee_tax_types.employee_tax_type_id, employee_tax_types.tax_identification, \n    employee_tax_types.amount, employee_tax_types.exchange_rate as tax_exchange_rate,\n    employee_tax_types.additional, employee_tax_types.employer, employee_tax_types.narrative,\n\n    (employee_tax_types.amount * employee_tax_types.exchange_rate) as tax_base_amount,\n    (employee_tax_types.amount * employee_tax_types.exchange_rate * emp.exchange_rate) as b_tax_amount\n\nFROM vw_employee_month as emp INNER JOIN employee_tax_types ON emp.employee_month_id = employee_tax_types.employee_month_id\n    INNER JOIN tax_types ON employee_tax_types.tax_type_id = tax_types.tax_type_id;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE VIEW vw_employee_advances AS<br>SELECT eml.employee_month_id, eml.period_id, eml.start_date, eml.end_date,<br>eml.month_id, eml.period_year, eml.period_month, eml.gl_payroll_account,<br>eml.entity_id, eml.entity_name, eml.employee_id,<br>employee_advances.org_id, employee_advances.employee_advance_id,<br>employee_advances.pay_date, employee_advances.pay_period,<br>employee_advances.Pay_upto, employee_advances.amount, employee_advances.in_payroll, employee_advances.completed,<br>employee_advances.approve_status, employee_advances.Action_date, employee_advances.narrative,<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>    (employee_advances.amount * eml.exchange_rate) as b_advance_amount\nFROM employee_advances INNER JOIN vw_employee_month_list as eml ON employee_advances.employee_month_id = eml.employee_month_id;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE VIEW vw_advance_deductions AS<br>SELECT eml.employee_month_id, eml.period_id, eml.start_date, eml.end_date,<br>eml.month_id, eml.period_year, eml.period_month, eml.gl_payroll_account,<br>eml.entity_id, eml.entity_name, eml.employee_id,<br>advance_deductions.org_id, advance_deductions.advance_deduction_id, advance_deductions.pay_date,<br>advance_deductions.amount, advance_deductions.in_payroll, advance_deductions.narrative,<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>    (advance_deductions.amount * eml.exchange_rate) as b_advance_deduction\nFROM advance_deductions INNER JOIN vw_employee_month_list as eml ON advance_deductions.employee_month_id = eml.employee_month_id;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE VIEW vw_advance_statement AS<br>(SELECT eml.employee_month_id, eml.period_id, eml.start_date,<br>eml.month_id, eml.period_year, eml.period_month,<br>eml.entity_id, eml.entity_name, eml.employee_id,<br>employee_advances.org_id, employee_advances.pay_date, employee_advances.in_payroll, employee_advances.narrative,<br>employee_advances.amount, cast(0 as real) as recovery<br>FROM employee_advances INNER JOIN vw_employee_month_list as eml ON employee_advances.employee_month_id = eml.employee_month_id<br>WHERE (employee_advances.approve_status = &#8216;Approved&#8217;))<br>UNION<br>(SELECT eml.employee_month_id, eml.period_id, eml.start_date,<br>eml.month_id, eml.period_year, eml.period_month,<br>eml.entity_id, eml.entity_name, eml.employee_id,<br>advance_deductions.org_id, advance_deductions.pay_date, advance_deductions.in_payroll, advance_deductions.narrative,<br>cast(0 as real), advance_deductions.amount<br>FROM advance_deductions INNER JOIN vw_employee_month_list as eml ON advance_deductions.employee_month_id = eml.employee_month_id);<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE VIEW vw_employee_adjustments AS<br>SELECT eml.employee_month_id, eml.period_id, eml.start_date,<br>eml.month_id, eml.period_year, eml.period_month,<br>eml.end_date,<br>eml.entity_id, eml.entity_name, eml.employee_id, eml.identity_card,<br>eml.department_id, eml.department_name, eml.department_account, eml.function_code,<br>eml.department_role_id, eml.department_role_name,<br>adjustments.adjustment_id, adjustments.adjustment_name, adjustments.adjustment_type, adjustments.account_number,<br>adjustments.earning_code,<br>currency.currency_id, currency.currency_name, currency.currency_symbol,<br>employee_adjustments.org_id, employee_adjustments.employee_adjustment_id, employee_adjustments.pay_date, employee_adjustments.amount,<br>employee_adjustments.in_payroll, employee_adjustments.in_tax, employee_adjustments.visible, employee_adjustments.exchange_rate,<br>employee_adjustments.paid_amount, employee_adjustments.balance, employee_adjustments.narrative,<br>employee_adjustments.tax_relief_amount,<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>    (employee_adjustments.exchange_rate * employee_adjustments.amount) as base_amount,  \n    (employee_adjustments.exchange_rate * eml.exchange_rate * employee_adjustments.amount) as b_amount,\n    (employee_adjustments.exchange_rate * eml.exchange_rate * employee_adjustments.paid_amount) as b_paid_amount\n\nFROM employee_adjustments INNER JOIN adjustments ON employee_adjustments.adjustment_id = adjustments.adjustment_id\n    INNER JOIN vw_employee_month_list as eml ON employee_adjustments.employee_month_id = eml.employee_month_id\n    INNER JOIN currency ON adjustments.currency_id = currency.currency_id;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE VIEW vw_employee_overtime AS<br>SELECT eml.employee_month_id, eml.period_id, eml.start_date,<br>eml.month_id, eml.period_year, eml.period_month,<br>eml.entity_id, eml.entity_name, eml.employee_id,<br>employee_overtime.org_id, employee_overtime.employee_overtime_id, employee_overtime.overtime_date, employee_overtime.overtime,<br>employee_overtime.overtime_rate, employee_overtime.narrative, employee_overtime.approve_status,<br>employee_overtime.Action_date, employee_overtime.details<br>FROM employee_overtime INNER JOIN vw_employee_month_list as eml ON employee_overtime.employee_month_id = eml.employee_month_id;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE VIEW vw_employee_per_diem AS<br>SELECT eml.employee_month_id, eml.period_id, eml.start_date,<br>eml.month_id, eml.period_year, eml.period_month,<br>eml.entity_id, eml.entity_name, eml.employee_id,<br>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,<br>employee_per_diem.per_diem, employee_per_diem.cash_paid, employee_per_diem.tax_amount, employee_per_diem.full_amount,<br>employee_per_diem.travel_to, employee_per_diem.approve_status, employee_per_diem.action_date,<br>employee_per_diem.completed, employee_per_diem.post_account, employee_per_diem.details,<br>(employee_per_diem.exchange_rate * employee_per_diem.tax_amount) as base_tax_amount,<br>(employee_per_diem.exchange_rate * employee_per_diem.full_amount) as base_full_amount,<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>    (employee_per_diem.exchange_rate * eml.exchange_rate * employee_per_diem.full_amount) as b_full_amount,\n    (employee_per_diem.exchange_rate * eml.exchange_rate * employee_per_diem.cash_paid) as b_cash_paid\nFROM employee_per_diem INNER JOIN vw_employee_month_list as eml ON employee_per_diem.employee_month_id = eml.employee_month_id;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE VIEW vw_employee_banking AS<br>SELECT eml.employee_month_id, eml.period_id, eml.start_date,<br>eml.month_id, eml.period_year, eml.period_month,<br>eml.entity_id, eml.entity_name, eml.employee_id,<br>eml.pay_group_id, eml.bank_Header, eml.bank_address,<br>vw_bank_branch.bank_id, vw_bank_branch.bank_name, vw_bank_branch.bank_branch_id,<br>vw_bank_branch.bank_branch_name, vw_bank_branch.bank_branch_code,<br>currency.currency_id, currency.currency_name, currency.currency_symbol,<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>    employee_banking.org_id, employee_banking.employee_banking_id, employee_banking.amount, \n    employee_banking.exchange_rate, employee_banking.active, employee_banking.bank_account,\n    employee_banking.narrative,\n\n    (employee_banking.exchange_rate * employee_banking.amount) as base_amount,\n    (employee_banking.exchange_rate * eml.exchange_rate * employee_banking.amount) as b_amount\nFROM employee_banking INNER JOIN vw_employee_month_list as eml ON employee_banking.employee_month_id = eml.employee_month_id\n    INNER JOIN vw_bank_branch ON employee_banking.bank_branch_id = vw_bank_branch.bank_branch_id\n    INNER JOIN currency ON employee_banking.currency_id = currency.currency_id;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE VIEW vw_pension_adjustments AS<br>SELECT c.period_id, c.start_date,<br>a.employee_adjustment_id, a.employee_month_id, a.adjustment_id, a.pension_id,<br>a.org_id, a.adjustment_type, a.adjustment_factor, a.pay_date, a.amount,<br>a.exchange_rate, a.in_payroll, a.in_tax, a.visible,<br>(a.amount * a.exchange_rate) as base_amount<br>FROM employee_adjustments as a INNER JOIN employee_month as b ON a.employee_month_id = b.employee_month_id<br>INNER JOIN periods as c ON b.period_id = c.period_id<br>WHERE (a.pension_id is not null);<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE VIEW vw_employee_pensions AS<br>SELECT a.entity_id, a.entity_name, a.adjustment_id, a.adjustment_name, a.contribution_id,<br>a.contribution_name, a.org_id, a.pension_id, a.pension_company, a.pension_number,<br>a.active, a.currency_id, a.currency_name, a.currency_symbol,<br>b.period_id, b.start_date, b.employee_month_id,<br>COALESCE(b.amount, 0) as amount,<br>COALESCE(b.base_amount, 0) as base_amount,<br>COALESCE(c.amount, 0) as employer_amount,<br>COALESCE(c.base_amount, 0) as employer_base_amount,<br>(b.amount + COALESCE(c.amount, 0)) as pension_amount,<br>(b.base_amount + COALESCE(c.base_amount, 0)) as pension_base_amount<br>FROM (vw_pensions as a INNER JOIN vw_pension_adjustments as b<br>ON (a.pension_id = b.pension_id) AND (a.adjustment_id = b.adjustment_id))<br>LEFT JOIN vw_pension_adjustments as c<br>ON (a.pension_id = c.pension_id) AND (a.contribution_id = c.adjustment_id)<br>AND (b.employee_month_id = c.employee_month_id);<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE VIEW vw_employee_per_diem_ledger AS<br>(SELECT a.org_id, a.period_id, a.travel_date, &#8216;Travel Cost&#8217; as description,<br>a.post_account, a.entity_name, a.b_full_amount as dr_amt, 0.0 as cr_amt<br>FROM vw_employee_per_diem a<br>WHERE (a.approve_status = &#8216;Approved&#8217;))<br>UNION<br>(SELECT a.org_id, a.period_id, a.travel_date, &#8216;Travel Payment&#8217; as description,<br>get_default_account(24, a.org_id)::varchar(32), a.entity_name, 0.0 as dr_amt, cash_paid as cr_amt<br>FROM vw_employee_per_diem a<br>WHERE (a.approve_status = &#8216;Approved&#8217;))<br>UNION<br>(SELECT a.org_id, a.period_id, a.travel_date, &#8216;Travel PAYE&#8217; as description,<br>get_default_account(25, a.org_id)::varchar(32), a.entity_name, 0.0 as dr_amt, (a.b_full_amount &#8211; a.b_cash_paid) as cr_amt<br>FROM vw_employee_per_diem a<br>WHERE (a.approve_status = &#8216;Approved&#8217;));<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE VIEW vw_payroll_ledger_trx AS<br>SELECT org_id, period_id, end_date, description, gl_payroll_account, entity_name, employee_id,<br>dr_amt, cr_amt<br>FROM<br>((SELECT a.org_id, a.period_id, a.end_date, &#8216;BASIC SALARY&#8217; as description,<br>a.gl_payroll_account, a.entity_name, a.employee_id,<br>a.b_basic_pay as dr_amt, &#8216;0.0&#8217;::real as cr_amt<br>FROM vw_ems a)<br>UNION<br>(SELECT a.org_id, a.period_id, a.end_date, &#8216;SALARY PAYMENTS&#8217;,<br>a.gl_payment_account, a.entity_name, a.employee_id,<br>&#8216;0.0&#8217;::real, a.b_banked<br>FROM vw_ems a)<br>UNION<br>(SELECT a.org_id, a.period_id, a.end_date, a.tax_type_name,<br>a.account_number, a.entity_name, a.employee_id,<br>&#8216;0.0&#8217;::real, (a.b_amount + a.b_additional + a.b_employer)<br>FROM vw_employee_tax_types a)<br>UNION<br>(SELECT a.org_id, a.period_id, a.end_date, &#8216;Employer &#8211; &#8216; || a.tax_type_name,<br>a.account_number, a.entity_name, a.employee_id,<br>a.b_employer, &#8216;0.0&#8217;::real<br>FROM vw_employee_tax_types a<br>WHERE (a.employer &lt;&gt; 0))<br>UNION<br>(SELECT a.org_id, a.period_id, a.end_date, a.adjustment_name, a.account_number,<br>a.entity_name, a.employee_id,<br>SUM(CASE WHEN a.adjustment_type = 1 THEN a.b_amount &#8211; a.b_paid_amount ELSE &#8216;0.0&#8217;::real END),<br>SUM(CASE WHEN a.adjustment_type = 2 THEN a.b_amount &#8211; a.b_paid_amount ELSE &#8216;0.0&#8217;::real END)<br>FROM vw_employee_adjustments a<br>WHERE (a.visible = true) AND (a.adjustment_type &lt; 3)<br>GROUP BY a.org_id, a.period_id, a.end_date, a.adjustment_name, a.account_number,<br>a.entity_name, a.employee_id)<br>UNION<br>(SELECT a.org_id, a.period_id, a.travel_date, &#8216;Transport&#8217; as description,<br>a.post_account, a.entity_name, a.employee_id,<br>(a.b_full_amount &#8211; a.b_cash_paid), &#8216;0.0&#8217;::real<br>FROM vw_employee_per_diem a<br>WHERE (a.approve_status = &#8216;Approved&#8217;))<br>UNION<br>(SELECT ea.org_id, ea.period_id, ea.end_date, &#8216;SALARY ADVANCE&#8217; as description,<br>ea.gl_payroll_account, ea.entity_name, ea.employee_id,<br>ea.b_advance_amount, &#8216;0.0&#8217;::real<br>FROM vw_employee_advances as ea<br>WHERE (ea.in_payroll = true))<br>UNION<br>(SELECT ead.org_id, ead.period_id, ead.end_date, &#8216;ADVANCE DEDUCTION&#8217; as description,<br>ead.gl_payroll_account, ead.entity_name, ead.employee_id,<br>&#8216;0.0&#8217;::real, ead.b_advance_deduction<br>FROM vw_advance_deductions as ead<br>WHERE (ead.in_payroll = true))) as b<br>ORDER BY gl_payroll_account desc, dr_amt desc, cr_amt desc;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE VIEW vw_payroll_ledger AS<br>SELECT org_id, period_id, end_date, description, gl_payroll_account, dr_amt, cr_amt<br>FROM<br>((SELECT a.org_id, a.period_id, a.end_date, &#8216;BASIC SALARY&#8217; as description, a.gl_payroll_account,<br>sum(a.b_basic_pay) as dr_amt, &#8216;0.0&#8217;::real as cr_amt<br>FROM vw_ems a<br>GROUP BY a.org_id, a.period_id, a.end_date, a.gl_payroll_account)<br>UNION<br>(SELECT a.org_id, a.period_id, a.end_date, &#8216;SALARY PAYMENTS&#8217;, a.gl_payment_account,<br>&#8216;0.0&#8217;::real, sum(a.b_banked)<br>FROM vw_ems a<br>GROUP BY a.org_id, a.period_id, a.end_date, a.gl_payment_account)<br>UNION<br>(SELECT a.org_id, a.period_id, a.end_date, a.tax_type_name, a.account_number,<br>&#8216;0.0&#8217;::real, sum(a.b_amount + a.b_additional + a.b_employer)<br>FROM vw_employee_tax_types a<br>GROUP BY a.org_id, a.period_id, a.end_date, a.tax_type_name, a.account_number)<br>UNION<br>(SELECT a.org_id, a.period_id, a.end_date, &#8216;Employer &#8211; &#8216; || a.tax_type_name, a.account_number,<br>sum(a.b_employer), &#8216;0.0&#8217;::real<br>FROM vw_employee_tax_types a<br>WHERE (a.employer &lt;&gt; 0)<br>GROUP BY a.org_id, a.period_id, a.end_date, a.tax_type_name, a.account_number)<br>UNION<br>(SELECT a.org_id, a.period_id, a.end_date, a.adjustment_name, a.account_number,<br>SUM(CASE WHEN a.adjustment_type = 1 THEN a.b_amount &#8211; a.b_paid_amount ELSE &#8216;0.0&#8217;::real END),<br>SUM(CASE WHEN a.adjustment_type = 2 THEN a.b_amount &#8211; a.b_paid_amount ELSE &#8216;0.0&#8217;::real END)<br>FROM vw_employee_adjustments a<br>WHERE (a.visible = true) AND (a.adjustment_type &lt; 3)<br>GROUP BY a.org_id, a.period_id, a.end_date, a.adjustment_name, a.account_number)<br>UNION<br>(SELECT a.org_id, a.period_id, a.travel_date, &#8216;Transport&#8217; as description, a.post_account,<br>sum(a.b_full_amount &#8211; a.b_cash_paid), &#8216;0.0&#8217;::real<br>FROM vw_employee_per_diem a<br>WHERE (a.approve_status = &#8216;Approved&#8217;)<br>GROUP BY a.org_id, a.period_id, a.travel_date, a.post_account)<br>UNION<br>(SELECT ea.org_id, ea.period_id, ea.end_date, &#8216;SALARY ADVANCE&#8217; as description, ea.gl_payroll_account,<br>sum(ea.b_advance_amount), &#8216;0.0&#8217;::real<br>FROM vw_employee_advances as ea<br>WHERE (ea.in_payroll = true)<br>GROUP BY ea.org_id, ea.period_id, ea.end_date, ea.gl_payroll_account)<br>UNION<br>(SELECT ead.org_id, ead.period_id, ead.end_date, &#8216;ADVANCE DEDUCTION&#8217; as description, ead.gl_payroll_account,<br>&#8216;0.0&#8217;::real, sum(ead.b_advance_deduction)<br>FROM vw_advance_deductions as ead<br>WHERE (ead.in_payroll = true)<br>GROUP BY ead.org_id, ead.period_id, ead.end_date, ead.gl_payroll_account)) as b<br>ORDER BY gl_payroll_account desc, dr_amt desc, cr_amt desc;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE VIEW vw_sun_ledger_trx AS<br>SELECT org_id, period_id, end_date, entity_id,<br>gl_payroll_account, description,<br>department_account, employee_id, function_code,<br>description2, round(amount::numeric, 1) as gl_amount, debit_credit,<br>(period_id::varchar || &#8216;.&#8217; || entity_id::varchar || &#8216;.&#8217; || COALESCE(gl_payroll_account, &#8221;)) as sun_ledger_id<br>FROM<br>((SELECT a.org_id, a.period_id, a.end_date, a.entity_id,<br>a.gl_payroll_account, &#8216;Payroll&#8217; as description,<br>d.department_account, a.employee_id, d.function_code,<br>to_char(a.start_date, &#8216;Month YYYY&#8217;) || &#8216; &#8211; Basic Pay&#8217; as description2,<br>a.basic_pay as amount, &#8216;D&#8217; as debit_credit<br>FROM vw_employee_month a INNER JOIN departments d ON a.department_id = d.department_id)<br>UNION<br>(SELECT a.org_id, a.period_id, a.end_date, a.entity_id,<br>a.employee_id, a.entity_name,<br>&#8221;, &#8221;, &#8221;,<br>to_char(a.start_date, &#8216;Month YYYY&#8217;) || &#8216; &#8211; Netpay&#8217; as description2,<br>net_pay as amount, &#8216;C&#8217; as debit_credit<br>FROM vw_employee_month a)<br>UNION<br>(SELECT a.org_id, a.period_id, a.end_date, a.entity_id,<br>a.account_number, a.adjustment_name,<br>a.department_account, a.employee_id, a.function_code,<br>to_char(a.start_date, &#8216;Month YYYY&#8217;) || &#8216; &#8211; &#8216; || a.adjustment_name as description2,<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>    sum(a.amount), 'D' as debit_credit\nFROM vw_employee_adjustments a\nWHERE (a.visible = true) AND (a.adjustment_type = 1)\nGROUP BY a.org_id, a.period_id, a.end_date, a.entity_id,\n    a.account_number, a.adjustment_name, \n    a.department_account, a.employee_id, a.function_code, a.start_date)\nUNION\n(SELECT a.org_id, a.period_id, a.end_date, a.entity_id,\n    a.account_number, a.adjustment_name, \n    a.department_account, a.employee_id, a.function_code,\n    to_char(a.start_date, 'Month YYYY') || ' - ' || a.adjustment_name as description2, \n\n    sum(a.amount), 'C' as debit_credit\nFROM vw_employee_adjustments a\nWHERE (a.visible = true) AND (a.adjustment_type = 2)\nGROUP BY a.org_id, a.period_id, a.end_date, a.entity_id,\n    a.account_number, a.adjustment_name, \n    a.department_account, a.employee_id, a.function_code,\n    a.start_date)\nUNION\n(SELECT a.org_id, a.period_id, a.end_date, a.entity_id,\n    a.account_number, a.tax_type_name,\n    a.department_account, a.employee_id, a.function_code,\n    to_char(a.start_date, 'Month YYYY') || ' - ' || a.tax_type_name || ' - Deduction',\n    (a.amount + a.additional + a.employer), 'C' as debit_credit\nFROM vw_employee_tax_types a)\nUNION\n(SELECT a.org_id, a.period_id, a.end_date, a.entity_id,\n    a.employer_account, a.tax_type_name,\n    a.department_account, a.employee_id, a.function_code,\n    to_char(a.start_date, 'Month YYYY') || ' - ' || a.tax_type_name || ' - Contribution',\n    a.employer, 'D' as debit_credit\nFROM vw_employee_tax_types a\nWHERE a.employer &gt; 0)\nUNION\n(SELECT a.org_id, a.period_id, a.end_date, a.entity_id,\n    a.employee_id, a.entity_name,\n    '', '', '',\n    to_char(a.start_date, 'Month YYYY') || ' - Payroll Banking' as description2, \n    banked as amount, 'D' as debit_credit\nFROM vw_employee_month a)\nUNION\n(SELECT a.org_id, a.period_id, a.end_date, a.entity_id,\n    a.gl_payment_account, 'Bank Account',\n    '', '', '',\n    to_char(a.start_date, 'Month YYYY') || ' - Payroll Banking' as description2, \n    banked as amount, 'C' as debit_credit\nFROM vw_employee_month a)) as b\nORDER BY gl_payroll_account desc, amount desc, debit_credit desc;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE TRIGGER upd_action BEFORE INSERT OR UPDATE ON employee_overtime<br>FOR EACH ROW EXECUTE PROCEDURE upd_action();<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE TRIGGER upd_action BEFORE INSERT OR UPDATE ON employee_per_diem<br>FOR EACH ROW EXECUTE PROCEDURE upd_action();<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE TRIGGER upd_action BEFORE INSERT OR UPDATE ON claims<br>FOR EACH ROW EXECUTE PROCEDURE upd_action();<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE OR REPLACE FUNCTION ins_taxes() RETURNS trigger AS $$<br>BEGIN<br>INSERT INTO default_tax_types (org_id, entity_id, tax_type_id)<br>SELECT NEW.org_id, NEW.entity_id, tax_type_id<br>FROM tax_types<br>WHERE (active = true) AND (org_id = NEW.org_id)<br>AND ((use_key_id = 11) OR (use_key_id = 12));<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>RETURN NULL;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">END;<br>$$ LANGUAGE plpgsql;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE TRIGGER ins_taxes AFTER INSERT ON employees<br>FOR EACH ROW EXECUTE PROCEDURE ins_taxes();<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE OR REPLACE FUNCTION get_formula_adjustment(int, int, real) RETURNS float AS $$<br>DECLARE<br>v_employee_month_id integer;<br>v_basic_pay float;<br>v_adjustment float;<br>BEGIN<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT employee_month.employee_month_id, employee_month.basic_pay INTO v_employee_month_id, v_basic_pay\nFROM employee_month\nWHERE (employee_month.employee_month_id = $1);\n\nIF ($2 = 1) THEN\n    v_adjustment := v_basic_pay * $3;\nELSE\n    v_adjustment := 0;\nEND IF;\n\nIF(v_adjustment is null) THEN\n    v_adjustment := 0;\nEND IF;\n\nRETURN v_adjustment;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">END;<br>$$ LANGUAGE plpgsql;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE OR REPLACE FUNCTION generate_payroll(varchar(12), varchar(12), varchar(12), varchar(12)) RETURNS varchar(120) AS $$<br>DECLARE<br>v_period_tax_type_id integer;<br>v_employee_month_id integer;<br>v_period_id integer;<br>v_org_id integer;<br>v_month_name varchar(50);<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>msg                         varchar(120);<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">BEGIN<br>SELECT period_id, org_id, to_char(start_date, &#8216;Month YYYY&#8217;) INTO v_period_id, v_org_id, v_month_name<br>FROM periods<br>WHERE (period_id = CAST($1 as integer));<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT period_tax_type_id INTO v_period_tax_type_id\nFROM period_tax_types\nWHERE (period_id = v_period_id) AND (org_id = v_org_id);\n\nSELECT employee_month_id INTO v_employee_month_id\nFROM employee_month\nWHERE (period_id = v_period_id) AND (org_id = v_org_id);\n\nIF(v_period_tax_type_id is null) AND (v_employee_month_id is null)THEN\n\n    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)\n    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\n    FROM tax_types\n    WHERE (active = true) AND (org_id = v_org_id);\n\n    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)\n    SELECT v_period_id, org_id, pay_group_id, entity_id, bank_branch_id, department_role_id, currency_id, bank_account, basic_salary\n    FROM employees\n    WHERE (employees.active = true) and (employees.org_id = v_org_id);\n\n    INSERT INTO loan_monthly (period_id, org_id, loan_id, interest_amount, interest_paid, repayment)\n    SELECT v_period_id, org_id, loan_id, (loan_balance * interest \/ 1200), (loan_balance * interest \/ 1200),\n        (CASE WHEN loan_balance &gt; monthly_repayment THEN monthly_repayment ELSE loan_balance END)\n    FROM vw_loans \n    WHERE (loan_balance &gt; 0) AND (approve_status = 'Approved') AND (reducing_balance =  true) AND (org_id = v_org_id);\n\n    INSERT INTO loan_monthly (period_id, org_id, loan_id, interest_amount, interest_paid, repayment)\n    SELECT v_period_id, org_id, loan_id, (principle * interest \/ 1200), (principle * interest \/ 1200),\n        (CASE WHEN loan_balance &gt; monthly_repayment THEN monthly_repayment ELSE loan_balance END)\n    FROM vw_loans \n    WHERE (loan_balance &gt; 0) AND (approve_status = 'Approved') AND (reducing_balance =  false) AND (org_id = v_org_id);\n\n    PERFORM updTax(employee_month_id, Period_id)\n    FROM employee_month\n    WHERE (period_id = v_period_id);\n\n    INSERT INTO sys_emailed (sys_email_id, table_id, table_name, narrative, org_id)\n    SELECT 7, entity_id, 'periods', v_month_name, v_org_id\n    FROM entity_subscriptions\n    WHERE entity_type_id = 6;\n\n    msg := 'Payroll Generated';\nELSE\n    msg := 'Payroll was previously Generated';\nEND IF;\n\nRETURN msg;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">END;<br>$$ LANGUAGE plpgsql;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE OR REPLACE FUNCTION ins_period_tax_types() RETURNS trigger AS $$<br>BEGIN<br>INSERT INTO period_tax_rates (org_id, period_tax_type_id, tax_range, tax_rate)<br>SELECT NEW.org_id, NEW.period_tax_type_id, tax_range, tax_rate<br>FROM tax_rates<br>WHERE (tax_type_id = NEW.tax_type_id);<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>RETURN NULL;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">END;<br>$$ LANGUAGE plpgsql;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE TRIGGER ins_period_tax_types AFTER INSERT ON period_tax_types<br>FOR EACH ROW EXECUTE PROCEDURE ins_period_tax_types();<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE OR REPLACE FUNCTION ins_employee_month() RETURNS trigger AS $$<br>BEGIN<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT exchange_rate INTO NEW.exchange_rate\nFROM currency_rates\nWHERE (currency_rate_id = \n    (SELECT MAX(currency_rate_id)\n    FROM currency_rates\n    WHERE (currency_id = NEW.currency_id) AND (org_id = NEW.org_id)\n        AND (exchange_date &lt; CURRENT_DATE)));\n\nIF(NEW.exchange_rate is null)THEN NEW.exchange_rate := 1; END IF;   \n\nRETURN NEW;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">END;<br>$$ LANGUAGE plpgsql;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE TRIGGER ins_employee_month BEFORE INSERT ON employee_month<br>FOR EACH ROW EXECUTE PROCEDURE ins_employee_month();<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE OR REPLACE FUNCTION upd_employee_month() RETURNS trigger AS $$<br>BEGIN<br>INSERT INTO employee_tax_types (org_id, employee_month_id, tax_type_id, tax_identification, additional, amount, employer, in_tax, exchange_rate)<br>SELECT NEW.org_id, NEW.employee_month_id, default_tax_types.tax_type_id, default_tax_types.tax_identification,<br>Default_Tax_Types.Additional, 0, 0, Tax_Types.In_Tax,<br>(CASE WHEN Tax_Types.currency_id = NEW.currency_id THEN 1 ELSE 1 \/ NEW.exchange_rate END)<br>FROM Default_Tax_Types INNER JOIN Tax_Types ON Default_Tax_Types.Tax_Type_id = Tax_Types.Tax_Type_id<br>WHERE (Default_Tax_Types.active = true) AND (Default_Tax_Types.entity_ID = NEW.entity_ID);<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>INSERT INTO employee_adjustments (org_id, employee_month_id, adjustment_id, amount, adjustment_type, in_payroll, in_tax, visible, adjustment_factor, \n    balance, tax_relief_amount, exchange_rate, narrative)\nSELECT NEW.org_id, NEW.employee_month_id, default_adjustments.adjustment_id, default_adjustments.amount,\n    adjustments.adjustment_type, adjustments.in_payroll, adjustments.in_tax, adjustments.visible,\n    (CASE WHEN adjustments.adjustment_type = 2 THEN -1 ELSE 1 END),\n    (CASE WHEN (adjustments.running_balance = true) AND (adjustments.reduce_balance = false) THEN (default_adjustments.balance + default_adjustments.amount)\n        WHEN (adjustments.running_balance = true) AND (adjustments.reduce_balance = true) THEN (default_adjustments.balance - default_adjustments.amount) END),\n    (default_adjustments.amount * adjustments.tax_relief_ps \/ 100),\n    (CASE WHEN adjustments.currency_id = NEW.currency_id THEN 1 ELSE 1 \/ NEW.exchange_rate END),\n    narrative\nFROM default_adjustments INNER JOIN adjustments ON default_adjustments.adjustment_id = adjustments.adjustment_id\nWHERE ((default_adjustments.final_date is null) OR (default_adjustments.final_date &gt; current_date))\n    AND (default_adjustments.active = true) AND (default_adjustments.entity_id = NEW.entity_id);\n\nINSERT INTO advance_deductions (org_id, amount, employee_month_id)\nSELECT NEW.org_id, (Amount \/ Pay_Period), NEW.Employee_Month_ID\nFROM employee_advances INNER JOIN employee_month ON employee_advances.employee_month_id = employee_month.employee_month_id\nWHERE (employee_month.entity_id = NEW.entity_id) AND (employee_advances.pay_period &gt; 0) AND (employee_advances.completed = false)\n    AND (employee_advances.pay_upto &gt;= current_date);\n\nINSERT INTO project_staff_costs (org_id, employee_month_id, project_id, project_role, payroll_ps, staff_cost, tax_cost)\nSELECT NEW.org_id, NEW.employee_month_id, \n    project_staff.project_id, project_staff.project_role, project_staff.payroll_ps, project_staff.staff_cost, project_staff.tax_cost\nFROM project_staff\nWHERE (project_staff.entity_id = NEW.entity_id) AND (project_staff.monthly_cost = true);\n\nINSERT INTO employee_banking (org_id, employee_month_id, bank_branch_id, currency_id, \n    bank_account, amount, \n    exchange_rate)\nSELECT NEW.org_id, NEW.employee_month_id, bank_branch_id, currency_id,\n    bank_account, amount,\n    (CASE WHEN default_banking.currency_id = NEW.currency_id THEN 1 ELSE 1 \/ NEW.exchange_rate END)\nFROM default_banking \nWHERE (default_banking.entity_id = NEW.entity_id) AND (default_banking.active = true)\n    AND (amount &gt; 0);\n\nRETURN NULL;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">END;<br>$$ LANGUAGE plpgsql;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE TRIGGER upd_employee_month AFTER INSERT ON employee_month<br>FOR EACH ROW EXECUTE PROCEDURE upd_employee_month();<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE OR REPLACE FUNCTION gettax(float, int) RETURNS float AS $$<br>DECLARE<br>reca RECORD;<br>tax REAL;<br>BEGIN<br>SELECT period_tax_type_id, formural, tax_relief, percentage, linear, in_tax, employer, employer_ps INTO reca<br>FROM period_tax_types<br>WHERE (period_tax_type_id = $2);<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>IF(reca.linear = true) THEN\n    SELECT SUM(CASE WHEN tax_range &lt; $1 \n    THEN (tax_rate \/ 100) * (tax_range - getTaxMin(tax_range, reca.period_tax_type_id)) \n    ELSE (tax_rate \/ 100) * ($1 - getTaxMin(tax_range, reca.period_tax_type_id)) END) INTO tax\n    FROM period_tax_rates \n    WHERE (getTaxMin(tax_range, reca.period_tax_type_id) &lt;= $1) AND (period_tax_type_id = reca.period_tax_type_id);\nELSIF(reca.linear = false) THEN \n    SELECT max(tax_rate) INTO tax\n    FROM period_tax_rates \n    WHERE (getTaxMin(tax_range, reca.period_tax_type_id) &lt; $1) AND (tax_range &gt;= $1) \n        AND (period_tax_type_id = reca.period_tax_type_id);\nEND IF;\n\nIF (tax is null) THEN\n    tax := 0;\nEND IF;\n\nIF (tax &gt; reca.tax_relief) THEN\n    tax := tax - reca.tax_relief;\nELSE\n    tax := 0;\nEND IF;\n\nRETURN tax;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">END;<br>$$ LANGUAGE plpgsql;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE OR REPLACE FUNCTION get_employee_tax(int, int) RETURNS float AS $$<br>DECLARE<br>v_employee_month_id integer;<br>v_period_tax_type_id integer;<br>v_exchange_rate real;<br>v_income real;<br>v_tax_relief real;<br>v_tax real;<br>BEGIN<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT employee_tax_types.employee_month_id, period_tax_types.period_tax_type_id, employee_tax_types.exchange_rate\n    INTO v_employee_month_id, v_period_tax_type_id, v_exchange_rate\nFROM employee_tax_types INNER JOIN employee_month ON employee_tax_types.employee_month_id = employee_month.employee_month_id\n    INNER JOIN period_tax_types ON (employee_month.period_id = period_tax_types.period_id)\n        AND (employee_tax_types.tax_type_id = period_tax_types.tax_type_id)\nWHERE (employee_tax_types.employee_tax_type_id  = $1);\n\nIF(v_exchange_rate = 0) THEN v_exchange_rate := 1; END IF;\n\nIF ($2 = 1) THEN\n    v_income := getAdjustment(v_employee_month_id, 1) \/ v_exchange_rate;\n    v_tax := getTax(v_income, v_period_tax_type_id);\n\nELSIF ($2 = 2) THEN\n    v_income := getAdjustment(v_employee_month_id, 2) \/ v_exchange_rate;\n    v_tax := getTax(v_income, v_period_tax_type_id) - getAdjustment(v_employee_month_id, 4, 25) \/ v_exchange_rate;\n\nELSIF ($2 = 3) THEN\n    v_income := getAdjustment(v_employee_month_id, 3) \/ v_exchange_rate;\n    v_tax := getTax(v_income, v_period_tax_type_id);\n\nELSIF ($2 = 4) THEN\n    v_income := getAdjustment(v_employee_month_id, 2) \/ v_exchange_rate;\n    v_tax_relief := getAdjustment(v_employee_month_id, 1) \/ 100;\n    if(v_tax_relief &lt; 16666.67) then v_tax_relief := 16666.67; end if;\n    v_tax_relief := v_tax_relief + getAdjustment(v_employee_month_id, 1) \/ 5;\n    v_income := v_income - v_tax_relief;\n    v_tax := getTax(v_income, v_period_tax_type_id) - getAdjustment(v_employee_month_id, 4, 25) \/ v_exchange_rate;\n\nELSE\n    v_tax := 0;\nEND IF;\n\nIF(v_tax is null) THEN\n    v_tax := 0;\nELSIF(v_tax &lt; 0) THEN\n    v_tax := 0;\nEND IF;\n\nRETURN v_tax;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">END;<br>$$ LANGUAGE plpgsql;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE OR REPLACE FUNCTION updtax(int, int) RETURNS float AS $$<br>DECLARE<br>reca RECORD;<br>income REAL;<br>tax REAL;<br>InsuranceRelief REAL;<br>BEGIN<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>FOR reca IN SELECT employee_tax_types.employee_tax_type_id, employee_tax_types.tax_type_id, period_tax_types.formural,\n         period_tax_types.employer, period_tax_types.employer_ps\n    FROM employee_tax_types INNER JOIN period_tax_types ON (employee_tax_types.tax_type_id = period_tax_types.tax_type_id)\n    WHERE (employee_month_id = $1) AND (Period_Tax_Types.Period_ID = $2)\n    ORDER BY Period_Tax_Types.Tax_Type_order \nLOOP\n\n    EXECUTE 'SELECT ' || reca.formural || ' FROM employee_tax_types WHERE employee_tax_type_id = ' || reca.employee_tax_type_id \n    INTO tax;\n\n    UPDATE employee_tax_types SET amount = tax, employer = reca.employer + (tax * reca.employer_ps \/ 100)\n    WHERE employee_tax_type_id = reca.employee_tax_type_id;\nEND LOOP;\n\nRETURN tax;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">END;<br>$$ LANGUAGE plpgsql;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE OR REPLACE FUNCTION process_payroll(varchar(12), varchar(12), varchar(12), varchar(12)) RETURNS varchar(120) AS $$<br>DECLARE<br>rec RECORD;<br>msg varchar(120);<br>BEGIN<br>IF ($3 = &#8216;1&#8217;) THEN<br>UPDATE employee_adjustments SET tax_reduction_amount = 0<br>FROM employee_month<br>WHERE (employee_adjustments.employee_month_id = employee_month.employee_month_id)<br>AND (employee_month.period_id = CAST($1 as int));<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>    PERFORM updTax(employee_month_id, period_id)\n    FROM employee_month\n    WHERE (period_id = CAST($1 as int));\n\n    msg := 'Payroll Processed';\nELSIF ($3 = '2') THEN\n    UPDATE periods SET entity_id = CAST($2 as int), approve_status = 'Completed'\n    WHERE (period_id = CAST($1 as int));\n\n    msg := 'Application for approval';\nELSIF ($3 = '3') THEN\n    UPDATE periods SET closed = true\n    WHERE (period_id = CAST($1 as int));\n\n    msg := 'Period closed';\nELSIF ($3 = '4') THEN\n    UPDATE periods SET closed = false\n    WHERE (period_id = CAST($1 as int));\n\n    msg := 'Period opened';\nEND IF;\n\nreturn msg;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">END;<br>$$ LANGUAGE plpgsql;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE OR REPLACE FUNCTION ins_employee_adjustments() RETURNS trigger AS $$<br>DECLARE<br>v_formural varchar(430);<br>v_tax_relief_ps float;<br>v_tax_reduction_ps float;<br>v_tax_max_allowed float;<br>BEGIN<br>IF((NEW.Amount = 0) AND (NEW.paid_amount &lt;&gt; 0))THEN<br>NEW.Amount = NEW.paid_amount \/ 0.7;<br>END IF;<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>IF(NEW.exchange_rate is null) THEN NEW.exchange_rate = 1; END IF;\nIF(NEW.exchange_rate = 0) THEN NEW.exchange_rate = 1; END IF;\n\nSELECT adjustment_type, formural INTO NEW.adjustment_type, v_formural\nFROM adjustments \nWHERE (adjustments.adjustment_id = NEW.adjustment_id);\n\nIF(NEW.adjustment_type = 2)THEN\n    NEW.adjustment_factor = -1;\nEND IF;\n\nIF(NEW.Amount = 0) and (v_formural is not null)THEN\n    EXECUTE 'SELECT ' || v_formural || ' FROM employee_month WHERE employee_month_id = ' || NEW.employee_month_id\n    INTO NEW.Amount;\n    NEW.Amount := NEW.Amount \/ NEW.exchange_rate;\nEND IF;\n\nIF(NEW.in_tax = true)THEN\n    SELECT tax_reduction_ps, tax_relief_ps, tax_max_allowed INTO v_tax_reduction_ps, v_tax_relief_ps, v_tax_max_allowed\n    FROM adjustments\n    WHERE (adjustments.adjustment_id = NEW.adjustment_id);\n\n    IF(v_tax_reduction_ps is null)THEN\n        NEW.tax_reduction_amount := 0;\n    ELSE\n        NEW.tax_reduction_amount := NEW.amount * v_tax_reduction_ps \/ 100;\n        NEW.tax_reduction_amount := NEW.tax_reduction_amount;\n    END IF;\n\n    IF(v_tax_relief_ps is null)THEN\n        NEW.tax_relief_amount := 0;\n    ELSE\n        NEW.tax_relief_amount := NEW.amount * v_tax_relief_ps \/ 100;\n        NEW.tax_relief_amount := NEW.tax_relief_amount;\n    END IF;\n\n    IF(v_tax_max_allowed is not null)THEN\n        IF(NEW.tax_reduction_amount &gt; v_tax_max_allowed)THEN\n            NEW.tax_reduction_amount := v_tax_max_allowed;\n        END IF;\n        IF(NEW.tax_relief_amount &gt; v_tax_max_allowed)THEN\n            NEW.tax_relief_amount := v_tax_max_allowed;\n        END IF;\n    END IF;\nELSE\n    NEW.tax_relief_amount := 0;\n    NEW.tax_reduction_amount := 0;\nEND IF;\n\nRETURN NEW;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">END;<br>$$ LANGUAGE plpgsql;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE TRIGGER ins_Employee_Adjustments BEFORE INSERT OR UPDATE ON Employee_Adjustments<br>FOR EACH ROW EXECUTE PROCEDURE ins_Employee_Adjustments();<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE OR REPLACE FUNCTION upd_employee_adjustments() RETURNS trigger AS $$<br>DECLARE<br>rec RECORD;<br>entityid integer;<br>periodid integer;<br>BEGIN<br>SELECT monthly_update, running_balance INTO rec<br>FROM adjustments WHERE adjustment_id = NEW.Adjustment_ID;<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT entity_id, period_id INTO entityid, periodid\nFROM employee_month WHERE employee_month_id = NEW.employee_month_id;\n\nIF(rec.running_balance = true) AND (NEW.balance is not null)THEN\n    UPDATE default_adjustments SET balance = NEW.balance\n    WHERE (entity_id = entityid) AND (adjustment_id = NEW.adjustment_id);\nEND IF;\n\nIF(TG_OP = 'UPDATE')THEN\n    IF (OLD.amount &lt;&gt; NEW.amount)THEN\n        IF(rec.monthly_update = true)THEN\n            UPDATE default_adjustments SET amount = NEW.amount \n            WHERE (entity_id = entityid) AND (adjustment_id = NEW.adjustment_id);\n        END IF;\n\n        PERFORM updTax(employee_month_id, Period_id)\n        FROM employee_month\n        WHERE (period_id = periodid);\n    END IF;\nELSE\n    PERFORM updTax(employee_month_id, Period_id)\n    FROM employee_month\n    WHERE (period_id = periodid);\nEND IF;\n\nRETURN NULL;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">END;<br>$$ LANGUAGE plpgsql;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE TRIGGER upd_employee_adjustments AFTER INSERT OR UPDATE ON employee_adjustments<br>FOR EACH ROW EXECUTE PROCEDURE upd_employee_adjustments();<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE OR REPLACE FUNCTION upd_employee_per_diem() RETURNS trigger AS $$<br>DECLARE<br>v_period_id integer;<br>v_tax_limit real;<br>BEGIN<br>SELECT periods.period_id, periods.per_diem_tax_limit INTO v_period_id, v_tax_limit<br>FROM employee_month INNER JOIN periods ON employee_month.period_id = periods.period_id<br>WHERE employee_month_id = NEW.employee_month_id;<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>IF(NEW.days_travelled  is null)THEN\n    NEW.days_travelled := NEW.return_date - NEW.travel_date;\nEND IF;\n\nIF(NEW.cash_paid = 0) THEN\n    NEW.cash_paid := NEW.per_diem;\nEND IF;\nIF(NEW.tax_amount = 0) THEN\n    NEW.full_amount := (NEW.per_diem - (v_tax_limit * NEW.days_travelled * 0.3)) \/ 0.7;\n    NEW.tax_amount := NEW.full_amount - (v_tax_limit * NEW.days_travelled);\nEND IF;\n\nRETURN NEW;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">END;<br>$$ LANGUAGE plpgsql;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE TRIGGER upd_Employee_Per_Diem BEFORE INSERT OR UPDATE ON Employee_Per_Diem<br>FOR EACH ROW EXECUTE PROCEDURE upd_Employee_Per_Diem();<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE OR REPLACE FUNCTION process_ledger(varchar(12), varchar(12), varchar(12)) RETURNS varchar(120) AS $$<br>DECLARE<br>rec RECORD;<br>v_period_id integer;<br>v_journal_id integer;<br>v_account_no varchar(32);<br>ledger_diff real;<br>msg varchar(120);<br>BEGIN<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>v_period_id := $1::int;\n\nSELECT periods.period_id, periods.is_posted, periods.opened, periods.closed, periods.end_date,\n    orgs.org_id, orgs.currency_id, orgs.payroll_payable\n    INTO rec\nFROM periods INNER JOIN orgs ON periods.org_id = orgs.org_id\nWHERE (periods.period_id = v_period_id);\n\nSELECT abs(sum(dr_amt) - sum(cr_amt)) INTO ledger_diff\nFROM vw_payroll_ledger\nWHERE (period_id = v_period_id);\n\nSELECT vw_payroll_ledger.gl_payroll_account INTO v_account_no\nFROM vw_payroll_ledger LEFT JOIN accounts ON (vw_payroll_ledger.gl_payroll_account = accounts.account_no::text)\n    AND (vw_payroll_ledger.org_id = accounts.org_id)\nWHERE (vw_payroll_ledger.period_id = v_period_id) AND (accounts.account_id is null);\n\nIF(rec.is_posted = true)THEN\n    msg := 'The payroll for this period is already posted';\nELSIF(ledger_diff &gt; 1) THEN\n    msg := 'The ledger is not balanced';\nELSIF((rec.opened = false) OR (rec.closed = true)) THEN\n    msg := 'Transaction period has to be opened and not closed.';\nELSIF(v_account_no is not null) THEN\n    msg := 'Ensure the accounts match the ledger accounts';\nELSE\n    v_journal_id := nextval('journals_journal_id_seq');\n    INSERT INTO journals (journal_id, org_id, currency_id, period_id, exchange_rate, journal_date, narrative)\n    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'));\n\n    INSERT INTO gls (org_id, journal_id, account_id, debit, credit, gl_narrative)\n    SELECT aa.org_id, v_journal_id, bb.account_id, aa.dr_amt, aa.cr_amt, aa.description\n    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)\n    WHERE (aa.period_id = v_period_id);\n\n    IF(rec.payroll_payable = true)THEN\n        msg := payroll_payable(v_period_id, $2::integer);\n    END IF;\n\n    UPDATE periods SET is_posted = true\n    WHERE (period_id = v_period_id);\n\n    msg := 'Payroll Ledger Processed';\nEND IF;\n\nRETURN msg;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">END;<br>$$ LANGUAGE plpgsql;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE OR REPLACE FUNCTION del_period(int) RETURNS varchar(120) AS $$<br>DECLARE<br>msg varchar(120);<br>BEGIN<br>DELETE FROM loan_monthly WHERE period_id = $1;<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DELETE FROM advance_deductions WHERE (employee_month_id IN (SELECT employee_month_id FROM employee_month WHERE period_id = $1));\nDELETE FROM employee_advances WHERE (employee_month_id IN (SELECT employee_month_id FROM employee_month WHERE period_id = $1));\nDELETE FROM employee_banking WHERE (employee_month_id IN (SELECT employee_month_id FROM employee_month WHERE period_id = $1));\nDELETE FROM employee_adjustments WHERE (employee_month_id IN (SELECT employee_month_id FROM employee_month WHERE period_id = $1));\nDELETE FROM employee_overtime WHERE (employee_month_id IN (SELECT employee_month_id FROM employee_month WHERE period_id = $1));\nDELETE FROM employee_tax_types WHERE (employee_month_id IN (SELECT employee_month_id FROM employee_month WHERE period_id = $1));\nDELETE FROM period_tax_rates WHERE (period_tax_type_id IN (SELECT period_tax_type_id FROM period_tax_types WHERE period_id = $1));\nDELETE FROM period_tax_types WHERE period_id = $1;\n\nDELETE FROM employee_month WHERE period_id = $1;\nDELETE FROM periods WHERE period_id = $1;\n\nmsg := 'Period Deleted';\n\nRETURN msg;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">END;<br>$$ LANGUAGE plpgsql;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE OR REPLACE FUNCTION increment_payroll(varchar(12), varchar(12), varchar(12), varchar(12)) RETURNS varchar(120) AS $$<br>DECLARE<br>v_entity_id integer;<br>v_pay_step_id integer;<br>v_pay_step integer;<br>v_next_step_id integer;<br>v_pay_scale_id integer;<br>v_currency_id integer;<br>v_pay_amount real;<br>msg varchar(120);<br>BEGIN<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>v_entity_id := CAST($1 as int);\n\nIF ($3 = '1') THEN\n    SELECT pay_scale_steps.pay_scale_step_id, pay_scale_steps.pay_amount, pay_scales.currency_id\n        INTO v_pay_step_id, v_pay_amount, v_currency_id\n    FROM employees INNER JOIN pay_scale_steps ON employees.pay_scale_step_id = pay_scale_steps.pay_scale_step_id\n        INNER JOIN pay_scales ON pay_scale_steps.pay_scale_id = pay_scales.pay_scale_id\n    WHERE employees.entity_id = v_entity_id;\n\n    IF((v_pay_amount is not null) AND (v_currency_id is not null))THEN\n        UPDATE employees SET basic_salary = v_pay_amount, currency_id = v_currency_id\n        WHERE entity_id = v_entity_id;\n    END IF;\n\n    msg := 'Updated the pay';\nELSIF ($3 = '2') THEN\n    SELECT pay_scale_steps.pay_scale_step_id, pay_scale_steps.pay_scale_id, pay_scale_steps.pay_step\n        INTO v_pay_step_id, v_pay_scale_id, v_pay_step\n    FROM employees INNER JOIN pay_scale_steps ON employees.pay_scale_step_id = pay_scale_steps.pay_scale_step_id\n    WHERE employees.entity_id = v_entity_id;\n\n    SELECT pay_scale_steps.pay_scale_step_id INTO v_next_step_id\n    FROM pay_scale_steps\n    WHERE (pay_scale_steps.pay_scale_id = v_pay_scale_id) AND (pay_scale_steps.pay_step = v_pay_step + 1);\n\n    IF(v_next_step_id is not null)THEN\n        UPDATE employees SET pay_scale_step_id = v_next_step_id\n        WHERE entity_id = v_entity_id;\n    END IF;\n\n    msg := 'Pay step incremented';\nEND IF;\n\nRETURN msg;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">END;<br>$$ LANGUAGE plpgsql;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE OR REPLACE FUNCTION add_adjustment(varchar(12), varchar(12), varchar(12), varchar(12)) RETURNS varchar(120) AS $$<br>DECLARE<br>adj RECORD;<br>rec RECORD;<br>v_period_id integer;<br>v_org_id integer;<br>v_adjustment_factor integer;<br>v_default_adjustment_id integer;<br>v_amount real;<br>msg varchar(120);<br>BEGIN<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT adjustment_id, adjustment_name, org_id, adjustment_type, formural, default_amount, in_payroll, in_tax, visible INTO adj\nFROM adjustments\nWHERE (adjustment_id = $1::integer);\n\nIF(adj.adjustment_type = 2)THEN\n    v_adjustment_factor := -1;\nELSE\n    v_adjustment_factor := 1;\nEND IF;\n\nIF ($3 = '1') THEN\n    SELECT max(period_id) INTO v_period_id\n    FROM periods\n    WHERE (closed = false) AND (org_id = adj.org_id);\n\n    FOR rec IN SELECT employee_month_id, exchange_rate\n        FROM employee_month \n        WHERE (period_id = v_period_id) \n    LOOP\n\n        IF(adj.formural is not null)THEN\n            EXECUTE 'SELECT ' || adj.formural || ' FROM employee_month WHERE employee_month_id = ' || rec.employee_month_id\n            INTO v_amount;\n        END IF;\n        IF(v_amount is null)THEN\n            v_amount := adj.default_amount;\n        END IF;\n\n        IF(v_amount is not null)THEN\n            INSERT INTO employee_adjustments (employee_month_id, adjustment_id, org_id,\n                adjustment_type, adjustment_factor, pay_date, amount,\n                exchange_rate, in_payroll, in_tax, visible)\n            VALUES(rec.employee_month_id, adj.adjustment_id, adj.org_id,\n                adj.adjustment_type, v_adjustment_factor, current_date, v_amount,\n            (1 \/ rec.exchange_rate), adj.in_payroll, adj.in_tax, adj.visible);\n        END IF;\n    END LOOP;\n    msg := 'Added ' || adj.adjustment_name || ' to month';\nELSIF ($3 = '2') THEN  \n    FOR rec IN SELECT entity_id\n        FROM employees\n        WHERE (active = true) AND (org_id = adj.org_id) \n    LOOP\n\n        SELECT default_adjustment_id INTO v_default_adjustment_id\n        FROM default_adjustments\n        WHERE (entity_id = rec.entity_id) AND (adjustment_id = adj.adjustment_id);\n\n        IF(v_default_adjustment_id is null)THEN\n            INSERT INTO default_adjustments (entity_id, adjustment_id, org_id,\n                amount, active)\n            VALUES (rec.entity_id, adj.adjustment_id, adj.org_id,\n                adj.default_amount, true);\n        END IF;\n    END LOOP;\n    msg := 'Added ' || adj.adjustment_name || ' to employees';\nEND IF;\n\nRETURN msg;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">END;<br>$$ LANGUAGE plpgsql;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE OR REPLACE FUNCTION process_pensions(varchar(12), varchar(12), varchar(12)) RETURNS varchar(120) AS $$<br>DECLARE<br>rec RECORD;<br>adj RECORD;<br>v_period_id integer;<br>v_org_id integer;<br>v_employee_month_id integer;<br>v_employee_adjustment_id integer;<br>v_currency_id integer;<br>v_exchange_rate real;<br>a_exchange_rate real;<br>v_amount real;<br>msg varchar(120);<br>BEGIN<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT period_id, org_id INTO v_period_id, v_org_id\nFROM periods WHERE period_id = $1::int;\n\nFOR rec IN SELECT pension_id, entity_id, adjustment_id, contribution_id, \n    pension_company, pension_number, amount, use_formura, \n    employer_ps, employer_amount, employer_formural\n    FROM pensions WHERE (active = true) AND (org_id = v_org_id) \nLOOP\n\n    SELECT employee_month_id, currency_id, exchange_rate \n        INTO v_employee_month_id, v_currency_id, v_exchange_rate\n    FROM employee_month\n    WHERE (period_id = v_period_id) AND (entity_id = rec.entity_id);\n\n    --- Deduction\n    SELECT employee_adjustment_id INTO v_employee_adjustment_id\n    FROM employee_adjustments\n    WHERE (employee_month_id = v_employee_month_id) AND (pension_id = rec.pension_id)\n        AND (adjustment_id = rec.adjustment_id);\n\n    SELECT adjustment_id, currency_id, org_id, adjustment_name, adjustment_type, \n        adjustment_order, earning_code, formural, monthly_update, in_payroll, \n        in_tax, visible, running_balance, reduce_balance, tax_reduction_ps, \n        tax_relief_ps, tax_max_allowed, account_number\n    INTO adj\n    FROM adjustments\n    WHERE (adjustment_id = rec.adjustment_id);\n\n    v_amount := 0;\n    IF(rec.use_formura = true) AND (adj.formural is not null) AND (v_employee_month_id is not null) THEN\n        EXECUTE 'SELECT ' || adj.formural || ' FROM employee_month WHERE employee_month_id = ' || v_employee_month_id\n        INTO v_amount;\n        IF(v_currency_id &lt;&gt; adj.currency_id)THEN\n            v_amount := v_amount * v_exchange_rate;\n        END IF;\n    ELSIF(rec.amount &gt; 0)THEN\n        v_amount := rec.amount;\n    END IF;\n\n    a_exchange_rate := 1;\n    IF(v_currency_id &lt;&gt; adj.currency_id)THEN\n        a_exchange_rate := 1 \/ v_exchange_rate;\n    END IF;\n\n    IF(v_employee_adjustment_id is null) AND (v_employee_month_id is not null) THEN\n        INSERT INTO employee_adjustments(employee_month_id, pension_id, org_id, \n            adjustment_id, adjustment_type, adjustment_factor, \n            in_payroll, in_tax, visible,\n            exchange_rate, pay_date, amount)\n        VALUES (v_employee_month_id, rec.pension_id, v_org_id,\n            adj.adjustment_id, adj.adjustment_type, -1, \n            adj.in_payroll, adj.in_tax, adj.visible,\n            a_exchange_rate, current_date, v_amount);\n    ELSIF (v_employee_month_id is not null) THEN\n        UPDATE employee_adjustments SET amount = v_amount, exchange_rate = a_exchange_rate\n        WHERE employee_adjustment_id = v_employee_adjustment_id;\n    END IF;\n\n    --- Employer contribution\n    IF((rec.employer_ps &gt; 0) OR (rec.employer_amount &gt; 0) OR (rec.employer_formural = true))THEN\n        SELECT employee_adjustment_id INTO v_employee_adjustment_id\n        FROM employee_adjustments\n        WHERE (employee_month_id = v_employee_month_id) AND (pension_id = rec.pension_id)\n            AND (adjustment_id = rec.contribution_id);\n\n        SELECT adjustment_id, currency_id, org_id, adjustment_name, adjustment_type, \n            adjustment_order, earning_code, formural, monthly_update, in_payroll, \n            in_tax, visible, running_balance, reduce_balance, tax_reduction_ps, \n            tax_relief_ps, tax_max_allowed, account_number\n        INTO adj\n        FROM adjustments\n        WHERE (adjustment_id = rec.contribution_id);\n\n        a_exchange_rate := 1;\n        IF(v_currency_id &lt;&gt; adj.currency_id)THEN\n            a_exchange_rate := 1 \/ v_exchange_rate;\n        END IF;\n\n        v_amount := 0;\n        IF(rec.employer_formural = true) AND (adj.formural is not null) AND (v_employee_month_id is not null) THEN\n            EXECUTE 'SELECT ' || adj.formural || ' FROM employee_month WHERE employee_month_id = ' || v_employee_month_id\n            INTO v_amount;\n            IF(v_currency_id &lt;&gt; adj.currency_id)THEN\n                v_amount := v_amount * v_exchange_rate;\n            END IF;\n        ELSIF(rec.employer_ps &gt; 0)THEN\n            v_amount := v_amount * rec.employer_ps \/ 100;\n        ELSIF(rec.employer_amount &gt; 0)THEN\n            v_amount := rec.employer_amount;\n        END IF;\n\n        IF(v_employee_adjustment_id is null) AND (v_employee_month_id is not null) AND (v_amount &gt; 0) THEN\n            INSERT INTO employee_adjustments(employee_month_id, pension_id, org_id, \n                adjustment_id, adjustment_type, adjustment_factor, \n                in_payroll, in_tax, visible,\n                exchange_rate, pay_date, amount)\n            VALUES (v_employee_month_id, rec.pension_id, v_org_id,\n                adj.adjustment_id, adj.adjustment_type, 1, \n                adj.in_payroll, adj.in_tax, adj.visible,\n                a_exchange_rate, current_date, v_amount);\n        ELSIF (v_employee_month_id is not null) THEN\n            UPDATE employee_adjustments SET amount = v_amount, exchange_rate = a_exchange_rate\n            WHERE employee_adjustment_id = v_employee_adjustment_id;\n        END IF;\n    END IF;\n\nEND LOOP;\n\nmsg := 'Pension Processed';\n\nRETURN msg;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">END;<br>$$ LANGUAGE plpgsql;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE TRIGGER upd_action BEFORE INSERT OR UPDATE ON employee_advances<br>FOR EACH ROW EXECUTE PROCEDURE upd_action();<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE OR REPLACE FUNCTION advance_aplication(varchar(12), varchar(12), varchar(12)) RETURNS varchar(120) AS $$<br>DECLARE<br>msg varchar(120);<br>BEGIN<br>msg := &#8216;Advance applied&#8217;;<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>UPDATE employee_advances SET approve_status = 'Completed'\nWHERE (employee_advance_id = CAST($1 as int)) AND (approve_status = 'Draft');\n\nreturn msg;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">END;<br>$$ LANGUAGE plpgsql;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE OR REPLACE FUNCTION ins_employee_advances() RETURNS trigger AS $$<br>DECLARE<br>v_period_id integer;<br>BEGIN<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>IF(NEW.pay_upto is null)THEN\n    NEW.pay_upto := current_date;\nEND IF;\nIF(NEW.payment_amount is null)THEN\n    NEW.payment_amount := NEW.amount;\n    NEW.pay_period := 1;\nEND IF;\n\nIF((NEW.approve_status = 'Approved') AND (OLD.approve_status = 'Completed'))THEN\n    SELECT max(period_id) INTO v_period_id\n    FROM periods\n    WHERE (closed = false);\n\n    SELECT max(employee_month_id) INTO NEW.employee_month_id\n    FROM employee_month\n    WHERE (period_id = v_period_id) AND (entity_id = NEW.entity_id);\n\n    IF(v_period_id is null)THEN\n        RAISE EXCEPTION 'You need to have the current period approved';\n    ELSIF(NEW.employee_month_id is null)THEN\n        RAISE EXCEPTION 'You need to have the staff in the current active month';\n    END IF;\nEND IF;\n\nRETURN NEW;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">END;<br>$$ LANGUAGE plpgsql;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE TRIGGER ins_employee_advances BEFORE INSERT OR UPDATE ON employee_advances<br>FOR EACH ROW EXECUTE PROCEDURE ins_employee_advances();<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CREATE OR REPLACE FUNCTION claims_aplication(varchar(12), varchar(12), varchar(12)) RETURNS varchar(120) AS $$<br>DECLARE<br>v_amount real;<br>msg varchar(120);<br>BEGIN<br>msg := &#8216;Advance applied&#8217;;<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT sum(amount) INTO v_amount\nFROM vw_claim_details\nWHERE (claim_id = $1::int);\n\nIF(v_amount is null)THEN\n    RAISE EXCEPTION 'You need to add claim details';\nEND IF;\n\nUPDATE claims SET approve_status = 'Completed'\nWHERE (claim_id = CAST($1 as int)) AND (approve_status = 'Draft');\n\nRETURN msg;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">END;<br>$$ LANGUAGE plpgsql;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-27431","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/bcccog.org\/index.php?rest_route=\/wp\/v2\/posts\/27431","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bcccog.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bcccog.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bcccog.org\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/bcccog.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=27431"}],"version-history":[{"count":1,"href":"https:\/\/bcccog.org\/index.php?rest_route=\/wp\/v2\/posts\/27431\/revisions"}],"predecessor-version":[{"id":27432,"href":"https:\/\/bcccog.org\/index.php?rest_route=\/wp\/v2\/posts\/27431\/revisions\/27432"}],"wp:attachment":[{"href":"https:\/\/bcccog.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=27431"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bcccog.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=27431"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bcccog.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=27431"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}