ALTER TABLE address ADD company_name varchar(150); ALTER TABLE address ADD position_held varchar(150); CREATE TABLE disability ( disability_id serial primary key, org_id integer references orgs, disability_name varchar(240) not null ); CREATE INDEX disability_org_id ON disability(org_id); CREATE TABLE pay_scales ( pay_scale_id serial primary key, currency_id integer references currency, org_id integer references orgs, pay_scale_name varchar(32) not null, min_pay real, max_pay real, details text ); CREATE INDEX pay_scales_org_id ON pay_scales(org_id); CREATE INDEX pay_scales_currency_id ON pay_scales(currency_id); CREATE TABLE pay_scale_steps ( pay_scale_step_id serial primary key, pay_scale_id integer references pay_scales, org_id integer references orgs, pay_step integer not null, pay_amount real not null ); CREATE INDEX pay_scale_steps_pay_scale_id ON pay_scale_steps(pay_scale_id); CREATE INDEX pay_scale_steps_org_id ON pay_scale_steps(org_id); CREATE TABLE pay_scale_years ( pay_scale_year_id serial primary key, pay_scale_id integer references pay_scales, org_id integer references orgs, pay_year integer not null, pay_amount real not null ); CREATE INDEX pay_scale_years_pay_scale_id ON pay_scale_years(pay_scale_id); CREATE INDEX pay_scale_years_org_id ON pay_scale_years(org_id); CREATE TABLE pay_groups ( pay_group_id serial primary key, org_id integer references orgs, pay_group_name varchar(50), gl_payment_account varchar(16), bank_header text, bank_address text, Details text ); CREATE INDEX pay_groups_org_id ON pay_groups(org_id); CREATE TABLE locations ( location_id serial primary key, org_id integer references orgs, location_name varchar(50), details text ); CREATE INDEX locations_org_id ON locations(org_id); CREATE TABLE jobs_category ( jobs_category_id serial primary key, org_id integer references orgs, jobs_category varchar(50), details text ); CREATE INDEX jobs_category_org_id ON jobs_category(org_id); CREATE TABLE department_roles ( department_role_id serial primary key, department_id integer references departments, ln_department_role_id integer references department_roles, jobs_category_id integer references jobs_category, org_id integer references orgs, department_role_name varchar(240) not null, active boolean default true not null, job_description text, job_requirements text, duties text, performance_measures text, details text ); CREATE INDEX department_roles_department_id ON department_roles (department_id); CREATE INDEX department_roles_ln_department_role_id ON department_roles (ln_department_role_id); CREATE INDEX department_roles_jobs_category_id ON department_roles (jobs_category_id); CREATE INDEX department_roles_org_id ON department_roles(org_id); INSERT INTO department_roles (org_id, department_role_id, ln_department_role_id, department_id, department_role_name) VALUES (0, 0, 0, 0, 'Chair Person'); CREATE TABLE applicants ( entity_id integer references entitys primary key, disability_id integer references disability, currency_id integer references currency, org_id integer references orgs, person_title varchar(7), surname varchar(50) not null, first_name varchar(50) not null, middle_name varchar(50), applicant_email varchar(50) not null unique, applicant_phone varchar(50), date_of_birth date, gender varchar(1), nationality char(2) references sys_countrys, marital_status varchar(2), picture_file varchar(32), identity_card varchar(50), language varchar(320), previous_salary real, expected_salary real, how_you_heard varchar(320), created timestamp default current_timestamp, field_of_study text, interests text, objective text, details text ); CREATE INDEX applicants_disability_id ON applicants(disability_id); CREATE INDEX applicants_currency_id ON applicants(currency_id); CREATE INDEX applicants_org_id ON applicants(org_id); CREATE TABLE employees ( entity_id integer references entitys primary key, department_role_id integer not null references department_roles, bank_branch_id integer not null references bank_branch, disability_id integer references disability, employee_id varchar(12) not null, pay_scale_id integer not null references pay_scales, pay_scale_step_id integer references pay_scale_steps, pay_group_id integer not null references pay_groups, location_id integer not null references locations, currency_id integer not null references currency, org_id integer not null references orgs, person_title varchar(7), surname varchar(50) not null, first_name varchar(50) not null, middle_name varchar(50), date_of_birth date, dob_email date default '2016-01-01'::date, gender varchar(1), phone varchar(120), nationality char(2) not null references sys_countrys, nation_of_birth char(2) references sys_countrys, place_of_birth varchar(50), marital_status varchar(2), appointment_date date, current_appointment date, exit_date date, contract boolean default false not null, contract_period integer not null, employment_terms varchar(320), identity_card varchar(50), basic_salary real not null, bank_account varchar(32), picture_file varchar(32), active boolean default true not null, language varchar(320), desg_code varchar(16), inc_mth varchar(16), previous_sal_point varchar(16), current_sal_point varchar(16), halt_point varchar(16), bio_metric_number varchar(32), height real, weight real, blood_group varchar(3), allergies varchar(320), field_of_study text, interests text, objective text, details text, UNIQUE(org_id, employee_id) ); CREATE INDEX employees_department_role_id ON employees (department_role_id); CREATE INDEX employees_bank_branch_id ON employees (bank_branch_id); CREATE INDEX employees_disability_id ON employees (disability_id); CREATE INDEX employees_pay_scale_id ON employees (pay_scale_id); CREATE INDEX employees_pay_scale_step_id ON employees (pay_scale_step_id); CREATE INDEX employees_pay_group_id ON employees (pay_group_id); CREATE INDEX employees_location_id ON employees (location_id); CREATE INDEX employees_nationality ON employees (nationality); CREATE INDEX employees_nation_of_birth ON employees (nation_of_birth); CREATE INDEX employees_currency_id ON employees (currency_id); CREATE INDEX employees_org_id ON employees(org_id); CREATE TABLE education_class ( education_class_id serial primary key, org_id integer references orgs, education_class_name varchar(50), details text ); CREATE INDEX education_class_org_id ON education_class(org_id); CREATE TABLE education ( education_id serial primary key, entity_id integer references entitys, education_class_id integer references education_class, org_id integer references orgs, date_from date not null, date_to date, name_of_school varchar(240), examination_taken varchar(240), grades_obtained varchar(50), certificate_number varchar(50), details text ); CREATE INDEX education_entity_id ON education (entity_id); CREATE INDEX education_education_class_id ON education (education_class_id); CREATE INDEX education_org_id ON education(org_id); CREATE TABLE employment ( employment_id serial primary key, entity_id integer references entitys, org_id integer references orgs, date_from date not null, date_to date, employers_name varchar(240), position_held varchar(240), details text ); CREATE INDEX employment_entity_id ON employment (entity_id); CREATE INDEX employment_org_id ON employment(org_id); CREATE TABLE kin_types ( kin_type_id serial primary key, org_id integer references orgs, kin_type_name varchar(50), details text ); CREATE INDEX kin_types_org_id ON kin_types(org_id); CREATE TABLE kins ( kin_id serial primary key, entity_id integer references entitys, kin_type_id integer references kin_types, org_id integer references orgs, full_names varchar(120), date_of_birth date, identification varchar(50), relation varchar(50), emergency_contact boolean default false not null, beneficiary boolean default false not null, beneficiary_ps real, details text ); CREATE INDEX kins_entity_id ON kins (entity_id); CREATE INDEX kins_kin_type_id ON kins (kin_type_id); CREATE INDEX kins_org_id ON kins(org_id); CREATE TABLE cv_seminars ( cv_seminar_id serial primary key, entity_id integer references entitys, org_id integer references orgs, cv_seminar_name varchar(240), cv_seminar_date date not null, details text ); CREATE INDEX cv_seminars_entity_id ON cv_seminars (entity_id); CREATE INDEX cv_seminars_org_id ON cv_seminars(org_id); CREATE TABLE cv_projects ( cv_projectid serial primary key, entity_id integer references entitys, org_id integer references orgs, cv_project_name varchar(240), cv_project_date date not null, details text ); CREATE INDEX cv_projects_entity_id ON cv_projects (entity_id); CREATE INDEX cv_projects_org_id ON cv_projects(org_id); CREATE TABLE skill_category ( skill_category_id serial primary key, org_id integer references orgs, skill_category_name varchar(50) not null, details text ); CREATE INDEX skill_category_org_id ON skill_category(org_id); CREATE TABLE skill_types ( skill_type_id serial primary key, skill_category_id integer references skill_category, org_id integer references orgs, skill_type_name varchar(50) not null, basic varchar(50), intermediate varchar(50), advanced varchar(50), details text ); CREATE INDEX skill_types_skill_category_id ON skill_types (skill_category_id); CREATE INDEX skill_types_org_id ON skill_types(org_id); CREATE TABLE skills ( skill_id serial primary key, entity_id integer references entitys, skill_type_id integer references skill_types, org_id integer references orgs, state_skill varchar(50), skill_level integer default 1 not null, aquired boolean default false not null, training_date date, trained boolean default false not null, training_institution varchar(240), training_cost real, details text ); CREATE INDEX skills_entity_id ON skills (entity_id); CREATE INDEX skills_skill_type_id ON skills (skill_type_id); CREATE INDEX skills_org_id ON skills(org_id); CREATE TABLE identification_types ( identification_type_id serial primary key, org_id integer references orgs, identification_type_name varchar(50), details text ); CREATE INDEX identification_types_org_id ON identification_types(org_id); CREATE TABLE identifications ( identification_id serial primary key, entity_id integer references entitys, identification_type_id integer references identification_types, nationality char(2) not null references sys_countrys, org_id integer references orgs, identification varchar(64), is_active boolean default true not null, starting_from date, expiring_at date, place_of_issue varchar(50), details text ); CREATE INDEX identifications_entity_id ON identifications(entity_id); CREATE INDEX identifications_identification_type_id ON identifications(identification_type_id); CREATE INDEX identifications_nationality ON identifications(nationality); CREATE INDEX identifications_org_id ON identifications(org_id); CREATE TABLE casual_category ( casual_category_id serial primary key, org_id integer references orgs, casual_category_name varchar(50), details text ); CREATE INDEX casual_category_org_id ON casual_category(org_id); CREATE TABLE casual_application ( casual_application_id serial primary key, department_id integer references departments, casual_category_id integer references casual_category, entity_id integer references entitys, org_id integer references orgs, position integer default 1 not null, work_duration integer default 1 not null, approved_pay_rate real, approve_status varchar(16) default 'draft' not null, workflow_table_id integer, application_date timestamp default now(), action_date timestamp, details text ); CREATE INDEX casual_application_Department_id ON casual_application (Department_id); CREATE INDEX casual_application_category_id ON casual_application (casual_category_id); CREATE INDEX casual_application_entity_id ON casual_application (entity_id); CREATE INDEX casual_application_org_id ON casual_application(org_id); CREATE TABLE casuals ( casual_id serial primary key, entity_id integer references entitys, casual_application_id integer references casual_application, org_id integer references orgs, start_date date, end_date date, duration integer, pay_rate real, amount_paid real, paid boolean default false not null, approve_status varchar(16) default 'draft' not null, workflow_table_id integer, application_date timestamp default now(), action_date timestamp, details text ); CREATE INDEX casuals_entity_id ON casuals (entity_id); CREATE INDEX casuals_casual_application_id ON casuals (casual_application_id); CREATE INDEX casuals_org_id ON casuals(org_id); CREATE TABLE org_events ( org_event_id serial primary key, org_id integer references orgs, org_event_name varchar(50) not null, start_date date, end_date date, details text ); CREATE INDEX org_events_id ON org_events (org_id); CREATE TABLE leave_types ( leave_type_id serial primary key, org_id integer references orgs, leave_type_name varchar(50) not null, allowed_leave_days integer default 1 not null, leave_days_span integer default 1 not null, use_type integer default 0 not null, month_quota real default 0 not null, initial_days real default 0 not null, maximum_carry real default 0 not null, include_holiday boolean default false not null, include_mon boolean default true not null, include_tue boolean default true not null, include_wed boolean default true not null, include_thu boolean default true not null, include_fri boolean default true not null, include_sat boolean default false not null, include_sun boolean default false not null, details text ); CREATE INDEX leave_types_org_id ON leave_types(org_id); CREATE TABLE employee_leave_types ( employee_leave_type_id serial primary key, entity_id integer references entitys, leave_type_id integer references leave_types, org_id integer references orgs, leave_balance real default 0 not null, leave_starting date default current_date not null, details text ); CREATE INDEX employee_leave_types_entity_id ON employee_leave_types (entity_id); CREATE INDEX employee_leave_types_leave_type_id ON employee_leave_types (leave_type_id); CREATE INDEX employee_leave_types_org_id ON employee_leave_types (org_id); CREATE TABLE employee_leave ( employee_leave_id serial primary key, entity_id integer references entitys, contact_entity_id integer references entitys, leave_type_id integer references leave_types, org_id integer references orgs, leave_from date not null, leave_to date not null, leave_days real not null, start_half_day boolean default false not null, end_half_day boolean default false not null, special_request boolean default false not null, application_date timestamp default now(), approve_status varchar(16) default 'Draft' not null, workflow_table_id integer, action_date timestamp, completed boolean default false not null, narrative varchar(240), details text ); CREATE INDEX employee_leave_entity_id ON employee_leave (entity_id); CREATE INDEX employee_leave_contact_entity_id ON employee_leave (contact_entity_id); CREATE INDEX employee_leave_leave_type_id ON employee_leave (leave_type_id); CREATE INDEX employee_leave_org_id ON employee_leave(org_id); CREATE TABLE leave_work_days ( leave_work_day_id serial primary key, employee_leave_id integer references employee_leave, entity_id integer references entitys, org_id integer references orgs, work_date date not null, half_day boolean default false not null, approve_status varchar(16) default 'Draft' not null, workflow_table_id integer, application_date timestamp default now(), action_date timestamp, details text ); CREATE INDEX leave_work_days_employee_leave_id ON leave_work_days (employee_leave_id); CREATE INDEX leave_work_days_org_id ON leave_work_days(org_id); CREATE TABLE intake ( intake_id serial primary key, department_role_id integer references department_roles, pay_scale_id integer references pay_scales, pay_group_id integer references pay_groups, location_id integer references locations, org_id integer references orgs, opening_date date not null, closing_date date not null, positions int, contract boolean default false not null, contract_period integer not null, details text ); CREATE INDEX intake_department_role_id ON intake (department_role_id); CREATE INDEX intake_pay_scale_id ON intake (pay_scale_id); CREATE INDEX intake_pay_group_id ON intake (pay_group_id); CREATE INDEX intake_location_id ON intake (location_id); CREATE INDEX intake_org_id ON intake(org_id); CREATE TABLE contract_types ( contract_type_id serial primary key, org_id integer references orgs, notice_period integer default 30 not null, contract_type_name varchar(50) not null, contract_text text, details text ); CREATE INDEX contract_types_org_id ON contract_types(org_id); CREATE TABLE contract_status ( contract_status_id serial primary key, org_id integer references orgs, contract_status_name varchar(50) not null, details text ); CREATE INDEX contract_status_org_id ON contract_status(org_id); CREATE TABLE applications ( application_id serial primary key, intake_id integer references intake, contract_type_id integer references contract_types, contract_status_id integer references contract_status, entity_id integer references entitys, employee_id integer references employees, currency_id integer references currency, org_id integer references orgs, contract_date date, contract_start date, contract_close date, contract_period integer, contract_terms text, initial_salary real, notice_email boolean default false not null, application_date timestamp default now(), approve_status varchar(16) default 'Draft' not null, workflow_table_id integer, action_date timestamp, short_listed integer default 0 not null, previous_salary real, expected_salary real, exchange_rate real default 1, review_rating integer, applicant_comments text, review text ); CREATE INDEX applications_intake_id ON applications(intake_id); CREATE INDEX applications_contract_type_id ON applications(contract_type_id); CREATE INDEX applications_contract_status_id ON applications(contract_status_id); CREATE INDEX applications_entity_id ON applications(entity_id); CREATE INDEX applications_employee_id ON applications(employee_id); CREATE INDEX applications_currency_id ON applications(currency_id); CREATE INDEX applications_org_id ON applications(org_id); CREATE TABLE internships ( internship_id serial primary key, department_id integer references departments, org_id integer references orgs, opening_date date not null, closing_date date not null, positions int, location varchar(50), details text ); CREATE INDEX internships_department_id ON internships (department_id); CREATE INDEX internships_org_id ON internships(org_id); CREATE TABLE interns ( intern_id serial primary key, internship_id integer references internships, entity_id integer references entitys, org_id integer references orgs, payment_amount real, start_date date, end_date date, phone_mobile varchar(50), application_date timestamp default now(), approve_status varchar(16) default 'Draft' not null, workflow_table_id integer, action_date timestamp, applicant_comments text, review text, UNIQUE(internship_id, entity_id) ); CREATE INDEX interns_internship_id ON interns (internship_id); CREATE INDEX interns_entity_id ON interns (entity_id); CREATE INDEX interns_org_id ON interns(org_id); CREATE TABLE objective_types ( objective_type_id serial primary key, org_id integer references orgs, objective_type_name varchar(320) not null, details text ); CREATE INDEX objective_types_org_id ON objective_types(org_id); CREATE TABLE employee_objectives ( employee_objective_id serial primary key, entity_id integer references entitys, org_id integer references orgs, employee_objective_name varchar(320) not null, objective_date date not null, approve_status varchar(16) default 'Draft' not null, workflow_table_id integer, application_date timestamp default now(), action_date timestamp, supervisor_comments text, details text ); CREATE INDEX employee_objectives_entity_id ON employee_objectives(entity_id); CREATE INDEX employee_objectives_org_id ON employee_objectives(org_id); CREATE TABLE objectives ( objective_id serial primary key, employee_objective_id integer references employee_objectives, objective_type_id integer references objective_types, org_id integer references orgs, date_set date not null, objective_ps real, objective_name varchar(320) not null, objective_completed boolean default false not null, objective_maditory boolean default false not null, supervisor_comments text, details text ); CREATE INDEX objectives_employee_objective_id ON objectives(employee_objective_id); CREATE INDEX objectives_objective_type_id ON objectives(objective_type_id); CREATE INDEX objectives_org_id ON objectives(org_id); CREATE TABLE objective_details ( objective_detail_id serial primary key, objective_id integer references objectives, ln_objective_detail_id integer references objective_details, org_id integer references orgs, objective_detail_name varchar(320) not null, success_indicator text not null, achievements text, resources_required text, ods_ps real, ---- ods = objective details ods_points integer default 1 not null, ods_reviewer_points integer default 1 not null, target_date date, completed boolean default false not null, completion_date date, target_changes text, supervisor_comments text, details text ); CREATE INDEX objective_details_objective_id ON objective_details(objective_id); CREATE INDEX objective_details_ln_objective_detail_id ON objective_details(ln_objective_detail_id); CREATE INDEX objective_details_org_id ON objective_details(org_id); CREATE TABLE review_category ( review_category_id serial primary key, org_id integer references orgs, review_category_name varchar(320), rate_objectives boolean default true not null, details text ); CREATE INDEX review_category_org_id ON review_category(org_id); CREATE TABLE review_points ( review_point_id serial primary key, review_category_id integer references review_category, org_id integer references orgs, review_point_name varchar(50), review_points integer default 1 not null, details text ); CREATE INDEX review_points_review_category_id ON review_points (review_category_id); CREATE INDEX review_points_org_id ON review_points(org_id); CREATE TABLE job_reviews ( job_review_id serial primary key, entity_id integer references entitys, review_category_id integer references review_category, org_id integer references orgs, total_points integer, self_rating integer, supervisor_rating integer, review_date date not null, review_done boolean default false not null, approve_status varchar(16) default 'Draft' not null, workflow_table_id integer, application_date timestamp default now(), action_date timestamp, recomendation text, staff_comments text, reviewer_comments text, pl_comments text, details text ); CREATE INDEX job_reviews_entity_id ON job_reviews (entity_id); CREATE INDEX job_reviews_review_category_id ON job_reviews (review_category_id); CREATE INDEX job_reviews_org_id ON job_reviews(org_id); CREATE TABLE career_development ( career_development_id serial primary key, org_id integer references orgs, career_development_name varchar(50) not null, details text, UNIQUE(org_id, career_development_name) ); CREATE INDEX career_development_org_id ON career_development(org_id); CREATE TABLE evaluation_points ( evaluation_point_id serial primary key, job_review_id integer references job_reviews, review_point_id integer references review_points, objective_id integer references objectives, career_development_id integer references career_development, org_id integer references orgs, points integer default 1 not null, grade varchar(2), narrative text, reviewer_points integer default 1 not null, reviewer_grade varchar(2), reviewer_narrative text, details text, UNIQUE(org_id, job_review_id, review_point_id, objective_id, career_development_id) ); CREATE INDEX evaluation_points_job_review_id ON evaluation_points (job_review_id); CREATE INDEX evaluation_points_review_point_id ON evaluation_points (review_point_id); CREATE INDEX evaluation_points_objective_id ON evaluation_points (objective_id); CREATE INDEX evaluation_points_career_development_id ON evaluation_points(career_development_id); CREATE INDEX evaluation_points_org_id ON evaluation_points (org_id); CREATE TABLE case_types ( case_type_id serial primary key, org_id integer references orgs, case_type_name varchar(50), details text ); CREATE INDEX case_types_org_id ON case_types(org_id); CREATE TABLE employee_cases ( employee_case_id serial primary key, case_type_id integer references case_types, entity_id integer references entitys, org_id integer references orgs, narrative varchar(240), case_date date, complaint text, case_action text, completed boolean default false not null, details text ); CREATE INDEX employee_cases_case_type_id ON employee_cases (case_type_id); CREATE INDEX employee_cases_entity_id ON employee_cases (entity_id); CREATE INDEX employee_cases_org_id ON employee_cases(org_id); CREATE TABLE trainings ( training_id serial primary key, org_id integer references orgs, training_name varchar(50), start_date date, end_date date, training_cost real, completed boolean default false not null, application_date timestamp default now(), approve_status varchar(16) default 'Draft' not null, workflow_table_id integer, action_date timestamp, details text ); CREATE INDEX trainings_org_id ON trainings(org_id); CREATE TABLE employee_trainings ( employee_training_id serial primary key, training_id integer references trainings, entity_id integer references entitys, org_id integer references orgs, narrative varchar(240), completed boolean default false not null, application_date timestamp default now(), approve_status varchar(16) default 'Draft' not null, workflow_table_id integer, action_date timestamp, details text ); CREATE INDEX employee_trainings_training_id ON employee_trainings (training_id); CREATE INDEX employee_trainings_entity_id ON employee_trainings (entity_id); CREATE INDEX employee_trainings_org_id ON employee_trainings(org_id); ----------- Views CREATE VIEW vw_referees AS SELECT sys_countrys.sys_country_id, sys_countrys.sys_country_name, address.address_id, address.org_id, address.address_name, address.table_name, address.table_id, address.post_office_box, address.postal_code, address.premises, address.street, address.town, address.phone_number, address.extension, address.mobile, address.fax, address.email, address.is_default, address.website, address.company_name, address.position_held, address.details FROM address INNER JOIN sys_countrys ON address.sys_country_id = sys_countrys.sys_country_id WHERE (address.table_name = 'referees'); CREATE VIEW vw_department_roles AS SELECT departments.department_id, departments.department_name, departments.description as department_description, departments.duties as department_duties, ln_department_roles.department_role_name as parent_role_name, jobs_category.jobs_category_id, jobs_category.jobs_category, department_roles.org_id, department_roles.department_role_id, department_roles.ln_department_role_id, department_roles.department_role_name, department_roles.job_description, department_roles.job_requirements, department_roles.duties, department_roles.performance_measures, department_roles.active, department_roles.details FROM department_roles INNER JOIN departments ON department_roles.department_id = departments.department_id LEFT JOIN department_roles as ln_department_roles ON department_roles.ln_department_role_id = ln_department_roles.department_role_id LEFT JOIN jobs_category ON jobs_category.jobs_category_id = department_roles.jobs_category_id; CREATE VIEW vw_pay_scales AS SELECT currency.currency_id, currency.currency_name, currency.currency_symbol, pay_scales.org_id, pay_scales.pay_scale_id, pay_scales.pay_scale_name, pay_scales.min_pay, pay_scales.max_pay, pay_scales.details FROM pay_scales INNER JOIN currency ON pay_scales.currency_id = currency.currency_id; CREATE VIEW vw_pay_scale_steps AS SELECT currency.currency_id, currency.currency_name, currency.currency_symbol, pay_scales.pay_scale_id, pay_scales.pay_scale_name, pay_scale_steps.org_id, pay_scale_steps.pay_scale_step_id, pay_scale_steps.pay_step, pay_scale_steps.pay_amount, (pay_scales.pay_scale_name || '-' || currency.currency_symbol || '-' || pay_scale_steps.pay_step) as pay_step_name FROM pay_scale_steps INNER JOIN pay_scales ON pay_scale_steps.pay_scale_id = pay_scales.pay_scale_id INNER JOIN currency ON pay_scales.currency_id = currency.currency_id; CREATE VIEW vw_education_max AS SELECT education_class.education_class_id, education_class.education_class_name, education.org_id, education.education_id, education.entity_id, education.date_from, education.date_to, education.name_of_school, education.examination_taken, education.grades_obtained, education.certificate_number FROM (education_class INNER JOIN education ON education_class.education_class_id = education.education_class_id) INNER JOIN (SELECT education.entity_id, max(education.education_id) as max_education_id FROM education INNER JOIN (SELECT entity_id, max(education_class_id) as max_education_class_id FROM education GROUP BY entity_id) as a ON (education.entity_id = a.entity_id) AND (education.education_class_id = a.max_education_class_id) GROUP BY education.entity_id) as b ON (education.education_id = b.max_education_id); CREATE VIEW vw_employment_max AS SELECT employment.employment_id, employment.entity_id, employment.date_from, employment.date_to, employment.org_id, employment.employers_name, employment.position_held, age(COALESCE(employment.date_to, current_date), employment.date_from) as employment_duration, c.employment_experince FROM employment INNER JOIN (SELECT max(employment_id) as max_employment_id FROM employment INNER JOIN (SELECT entity_id, max(date_from) as max_date_from FROM employment GROUP BY entity_id) as a ON (employment.entity_id = a.entity_id) AND (employment.date_from = a.max_date_from) GROUP BY employment.entity_id) as b ON employment.employment_id = b.max_employment_id INNER JOIN (SELECT entity_id, sum(age(COALESCE(employment.date_to, current_date), employment.date_from)) as employment_experince FROM employment GROUP BY entity_id) as c ON employment.entity_id = c.entity_id; CREATE VIEW vw_applicants AS SELECT sys_countrys.sys_country_id, sys_countrys.sys_country_name, currency.currency_id, currency.currency_name, currency.currency_symbol, applicants.org_id, applicants.entity_id, applicants.surname, applicants.first_name, applicants.middle_name, applicants.date_of_birth, applicants.nationality, applicants.identity_card, applicants.language, applicants.objective, applicants.interests, applicants.picture_file, applicants.details, applicants.person_title, applicants.field_of_study, applicants.applicant_email, applicants.applicant_phone, applicants.previous_salary, applicants.expected_salary, (applicants.Surname || ' ' || applicants.First_name || ' ' || COALESCE(applicants.Middle_name, '')) as applicant_name, to_char(age(applicants.date_of_birth), 'YY') as applicant_age, (CASE WHEN applicants.gender = 'M' THEN 'Male' ELSE 'Female' END) as gender_name, (CASE WHEN applicants.marital_status = 'M' THEN 'Married' ELSE 'Single' END) as marital_status_name, vw_education_max.education_class_id, vw_education_max.education_class_name, vw_education_max.education_id, vw_education_max.date_from, vw_education_max.date_to, vw_education_max.name_of_school, vw_education_max.examination_taken, vw_education_max.grades_obtained, vw_education_max.certificate_number, vw_employment_max.employers_name, vw_employment_max.position_held, vw_employment_max.date_from as emp_date_from, vw_employment_max.date_to as emp_date_to, vw_employment_max.employment_duration, vw_employment_max.employment_experince, round((date_part('year', vw_employment_max.employment_duration) + date_part('month', vw_employment_max.employment_duration)/12)::numeric, 1) as emp_duration, round((date_part('year', vw_employment_max.employment_experince) + date_part('month', vw_employment_max.employment_experince)/12)::numeric, 1) as emp_experince FROM applicants INNER JOIN sys_countrys ON applicants.nationality = sys_countrys.sys_country_id LEFT JOIN vw_education_max ON applicants.entity_id = vw_education_max.entity_id LEFT JOIN vw_employment_max ON applicants.entity_id = vw_employment_max.entity_id LEFT JOIN currency ON applicants.currency_id = currency.currency_id; CREATE VIEW vw_employees AS SELECT vw_bank_branch.bank_id, vw_bank_branch.bank_name, vw_bank_branch.bank_branch_id, vw_bank_branch.bank_branch_name, vw_bank_branch.bank_branch_code, vw_department_roles.department_id, vw_department_roles.department_name, vw_department_roles.department_role_id, vw_department_roles.department_role_name, locations.location_id, locations.location_name, currency.currency_id, currency.currency_name, currency.currency_symbol, sys_countrys.sys_country_name, nob.sys_country_name as birth_nation_name, disability.disability_id, disability.disability_name, employees.org_id, employees.entity_id, employees.employee_id, employees.surname, employees.first_name, employees.middle_name, employees.person_title, employees.field_of_study, (employees.Surname || ' ' || employees.First_name || ' ' || COALESCE(employees.Middle_name, '')) as employee_name, employees.date_of_birth, employees.dob_email, employees.place_of_birth, employees.gender, employees.nationality, employees.nation_of_birth, employees.marital_status, employees.appointment_date, employees.exit_date, employees.contract, employees.contract_period, employees.employment_terms, employees.identity_card, employees.basic_salary, employees.bank_account, employees.language, employees.picture_file, employees.active, employees.height, employees.weight, employees.blood_group, employees.allergies, employees.phone, employees.objective, employees.interests, employees.details, to_char(age(employees.date_of_birth), 'YY') as employee_age, (CASE WHEN employees.gender = 'M' THEN 'Male' ELSE 'Female' END) as gender_name, (CASE WHEN employees.marital_status = 'M' THEN 'Married' ELSE 'Single' END) as marital_status_name, vw_education_max.education_class_name, vw_education_max.date_from, vw_education_max.date_to, vw_education_max.name_of_school, vw_education_max.examination_taken, vw_education_max.grades_obtained, vw_education_max.certificate_number FROM employees INNER JOIN vw_bank_branch ON employees.bank_branch_id = vw_bank_branch.bank_branch_id INNER JOIN vw_department_roles ON employees.department_role_id = vw_department_roles.department_role_id INNER JOIN locations ON employees.location_id = locations.location_id INNER JOIN currency ON employees.currency_id = currency.currency_id INNER JOIN sys_countrys ON employees.nationality = sys_countrys.sys_country_id LEFT JOIN sys_countrys as nob ON employees.nation_of_birth = nob.sys_country_id LEFT JOIN disability ON employees.disability_id = disability.disability_id LEFT JOIN vw_education_max ON employees.entity_id = vw_education_max.entity_id; CREATE VIEW vw_entity_employees AS SELECT entitys.entity_id, entitys.org_id, entitys.entity_type_id, entitys.entity_name, entitys.user_name, entitys.primary_email, entitys.super_user, entitys.entity_leader, entitys.function_role, entitys.date_enroled, entitys.is_active, entitys.entity_password, entitys.first_password, entitys.is_picked, employees.employee_id, employees.surname, employees.first_name, employees.middle_name, employees.date_of_birth, employees.dob_email, employees.gender, employees.nationality, employees.marital_status, employees.appointment_date, employees.exit_date, employees.contract, employees.contract_period, employees.employment_terms, employees.identity_card, employees.basic_salary, employees.bank_account, employees.language, employees.objective, employees.active FROM entitys INNER JOIN employees ON entitys.entity_id = employees.entity_id; CREATE VIEW vw_employee_periods AS SELECT aa.period_id, aa.start_date, aa.period_year, aa.period_month, aa.period_code, aa.week_start, EXTRACT(WEEK FROM aa.week_start) as p_week, b.org_id, b.entity_id, b.employee_id, b.active, (b.Surname || ' ' || b.First_name || ' ' || COALESCE(b.Middle_name, '')) as employee_name FROM (SELECT a.org_id, a.period_id, a.start_date, a.end_date, to_char(a.start_date, 'YYYY') as period_year, to_char(a.start_date, 'Month') as period_month, to_char(a.start_date, 'YYYYMM') as period_code, generate_series(a.start_date, a.end_date, interval '1 week') as week_start FROM periods a) aa INNER JOIN employees b ON aa.org_id = b.org_id; CREATE VIEW vw_education AS SELECT education_class.education_class_id, education_class.education_class_name, entitys.entity_id, entitys.entity_name, education.org_id, education.education_id, education.date_from, education.date_to, education.name_of_school, education.examination_taken, education.grades_obtained, education.certificate_number, education.details FROM education INNER JOIN education_class ON education.education_class_id = education_class.education_class_id INNER JOIN entitys ON education.entity_id = entitys.entity_id; CREATE VIEW vw_employment AS SELECT entitys.entity_id, entitys.entity_name, employment.employment_id, employment.date_from, employment.date_to, employment.org_id, employment.employers_name, employment.position_held, employment.details, age(COALESCE(employment.date_to, current_date), employment.date_from) as employment_duration FROM employment INNER JOIN entitys ON employment.entity_id = entitys.entity_id; CREATE VIEW vw_kins AS SELECT entitys.entity_id, entitys.entity_name, kin_types.kin_type_id, kin_types.kin_type_name, kins.org_id, kins.kin_id, kins.full_names, kins.date_of_birth, kins.identification, kins.relation, kins.emergency_contact, kins.beneficiary, kins.beneficiary_ps, kins.details FROM kins INNER JOIN entitys ON kins.entity_id = entitys.entity_id INNER JOIN kin_types ON kins.kin_type_id = kin_types.kin_type_id; CREATE VIEW vw_cv_seminars AS SELECT entitys.entity_id, entitys.entity_name, cv_seminars.cv_seminar_id, cv_seminars.cv_seminar_name, cv_seminars.org_id, cv_seminars.cv_seminar_date, cv_seminars.details FROM cv_seminars INNER JOIN entitys ON cv_seminars.entity_id = entitys.entity_id; CREATE VIEW vw_cv_projects AS SELECT entitys.entity_id, entitys.entity_name, cv_projects.cv_projectid, cv_projects.cv_project_name, cv_projects.org_id, cv_projects.cv_project_date, cv_projects.details FROM cv_projects INNER JOIN entitys ON cv_projects.entity_id = entitys.entity_id; CREATE VIEW vw_skill_types AS SELECT skill_category.skill_category_id, skill_category.skill_category_name, skill_types.skill_type_id, skill_types.org_id, skill_types.skill_type_name, skill_types.basic, skill_types.intermediate, skill_types.advanced, skill_types.details FROM skill_types INNER JOIN skill_category ON skill_types.skill_category_id = skill_category.skill_category_id; CREATE VIEW vw_skills AS SELECT vw_skill_types.skill_category_id, vw_skill_types.skill_category_name, vw_skill_types.skill_type_id, vw_skill_types.basic, vw_skill_types.intermediate, vw_skill_types.advanced, entitys.entity_id, entitys.entity_name, skills.skill_id, skills.skill_level, skills.aquired, skills.training_date, skills.org_id, skills.trained, skills.training_institution, skills.training_cost, skills.details, (CASE WHEN vw_skill_types.skill_type_id = 0 THEN skills.state_skill ELSE vw_skill_types.skill_type_name END) as skill_type_name, (CASE WHEN skill_level = 1 THEN 'Basic' WHEN skill_level = 2 THEN 'Intermediate' WHEN skill_level = 3 THEN 'Advanced' ELSE 'None' END) as skill_level_name, (CASE WHEN skill_level = 1 THEN vw_skill_types.Basic WHEN skill_level = 2 THEN vw_skill_types.Intermediate WHEN skill_level = 3 THEN vw_skill_types.Advanced ELSE 'None' END) as skill_level_details FROM skills INNER JOIN entitys ON skills.entity_id = entitys.entity_id INNER JOIN vw_skill_types ON skills.skill_type_id = vw_skill_types.skill_type_id; CREATE VIEW vw_identifications AS SELECT entitys.entity_id, entitys.entity_name, identification_types.identification_type_id, identification_types.identification_type_name, identifications.org_id, identifications.identification_id, identifications.identification, identifications.is_active, identifications.starting_from, identifications.expiring_at, identifications.place_of_issue, identifications.details FROM identifications INNER JOIN entitys ON identifications.entity_id = entitys.entity_id INNER JOIN identification_types ON identifications.identification_type_id = identification_types.identification_type_id; CREATE VIEW vw_casual_application AS SELECT casual_category.casual_category_id, casual_category.casual_category_name, departments.department_id, departments.department_name, casual_application.casual_application_id, casual_application.position, casual_application.org_id, casual_application.application_date, casual_application.approved_pay_rate, casual_application.approve_status, casual_application.action_date, casual_application.work_duration, casual_application.details FROM casual_application INNER JOIN casual_category ON casual_application.casual_category_id = casual_category.casual_category_id INNER JOIN departments ON casual_application.department_id = departments.department_id; CREATE VIEW vw_casuals AS SELECT vw_casual_application.casual_category_id, vw_casual_application.casual_category_name, vw_casual_application.department_id, vw_casual_application.department_name, vw_casual_application.casual_application_id, vw_casual_application.position, vw_casual_application.application_date, vw_casual_application.approved_pay_rate, vw_casual_application.approve_status as application_approve_status, vw_casual_application.action_date as application_action_date, vw_casual_application.work_duration, entitys.entity_id, entitys.entity_name, casuals.org_id, casuals.casual_id, casuals.start_date, casuals.end_date, casuals.duration, casuals.pay_rate, casuals.amount_paid, casuals.approve_status, casuals.action_date, casuals.paid, casuals.details FROM casuals INNER JOIN vw_casual_application ON casuals.casual_application_id = vw_casual_application.casual_application_id INNER JOIN entitys ON casuals.entity_id = entitys.entity_id; CREATE VIEW vw_employee_leave_types AS SELECT entitys.entity_id, entitys.entity_name, leave_types.leave_type_id, leave_types.leave_type_name, leave_types.allowed_leave_days, leave_types.leave_days_span, leave_types.use_type, leave_types.month_quota, leave_types.initial_days, leave_types.maximum_carry, leave_types.include_holiday, employee_leave_types.org_id, employee_leave_types.employee_leave_type_id, employee_leave_types.leave_balance, employee_leave_types.leave_starting, employee_leave_types.details FROM employee_leave_types INNER JOIN entitys ON employee_leave_types.entity_id = entitys.entity_id INNER JOIN leave_types ON employee_leave_types.leave_type_id = leave_types.leave_type_id; CREATE VIEW vw_employee_leave AS SELECT entitys.entity_id, entitys.entity_name, leave_types.leave_type_id, leave_types.leave_type_name, contact_entity.entity_name as contact_name, employee_leave.org_id, employee_leave.employee_leave_id, employee_leave.leave_from, employee_leave.leave_to, employee_leave.Start_Half_Day, employee_leave.End_Half_Day, employee_leave.approve_status, employee_leave.action_date, employee_leave.workflow_table_id, employee_leave.completed, employee_leave.Leave_days, employee_leave.narrative, employee_leave.details, employee_leave.special_request, (CASE WHEN employee_leave.Start_Half_Day = true THEN '02:00 PM'::time ELSE '08:00 AM'::time END) as activity_time, (CASE WHEN employee_leave.End_Half_Day = true THEN '02:00 PM'::time ELSE '05:00 PM'::time END) as finish_time, date_part('month', employee_leave.leave_from) as leave_month, to_char(employee_leave.leave_from, 'YYYY') as leave_year FROM employee_leave INNER JOIN entitys ON employee_leave.entity_id = entitys.entity_id INNER JOIN leave_types ON employee_leave.leave_type_id = leave_types.leave_type_id LEFT JOIN entitys as contact_entity ON employee_leave.contact_entity_id = contact_entity.entity_id; CREATE VIEW vw_leave_work_days AS SELECT vw_employee_leave.entity_id, vw_employee_leave.entity_name, vw_employee_leave.leave_type_id, vw_employee_leave.leave_type_name, vw_employee_leave.employee_leave_id, vw_employee_leave.leave_from, vw_employee_leave.leave_to, vw_employee_leave.Start_Half_Day, vw_employee_leave.End_Half_Day, leave_work_days.org_id, leave_work_days.leave_work_day_id, leave_work_days.work_date, leave_work_days.half_day, leave_work_days.application_date, leave_work_days.approve_status, leave_work_days.workflow_table_id, leave_work_days.action_date, leave_work_days.details FROM leave_work_days INNER JOIN vw_employee_leave ON leave_work_days.employee_leave_id = vw_employee_leave.employee_leave_id; CREATE VIEW vw_intake AS SELECT vw_department_roles.department_id, vw_department_roles.department_name, vw_department_roles.department_description, vw_department_roles.department_duties, vw_department_roles.department_role_id, vw_department_roles.department_role_name, vw_department_roles.parent_role_name, vw_department_roles.jobs_category_id, vw_department_roles.jobs_category, vw_department_roles.job_description, vw_department_roles.job_requirements, vw_department_roles.duties, vw_department_roles.performance_measures, locations.location_id, locations.location_name, pay_groups.pay_group_id, pay_groups.pay_group_name, pay_scales.pay_scale_id, pay_scales.pay_scale_name, orgs.org_name, orgs.details as org_detail, intake.org_id, intake.intake_id, intake.opening_date, intake.closing_date, intake.positions, intake.contract, intake.contract_period, intake.details, (vw_department_roles.department_name || ', ' || vw_department_roles.department_role_name || ', ' || to_char(intake.opening_date, 'YYYY, Mon')) as intake_disp FROM intake INNER JOIN vw_department_roles ON intake.department_role_id = vw_department_roles.department_role_id INNER JOIN locations ON intake.location_id = locations.location_id INNER JOIN pay_groups ON intake.pay_group_id = pay_groups.pay_group_id INNER JOIN pay_scales ON intake.pay_scale_id = pay_scales.pay_scale_id INNER JOIN orgs ON intake.org_id = orgs.org_id; CREATE VIEW vw_applications AS SELECT vw_intake.department_id, vw_intake.department_name, vw_intake.department_description, vw_intake.department_duties, vw_intake.department_role_id, vw_intake.department_role_name, vw_intake.parent_role_name, vw_intake.job_description, vw_intake.job_requirements, vw_intake.duties, vw_intake.performance_measures, vw_intake.intake_id, vw_intake.opening_date, vw_intake.closing_date, vw_intake.positions, vw_intake.org_name, vw_intake.org_detail, entitys.entity_id, entitys.entity_name, entitys.primary_email, currency.currency_id, currency.currency_name, currency.currency_symbol, applications.org_id, applications.application_id, applications.employee_id, applications.contract_date, applications.contract_close, applications.contract_start, applications.contract_period, applications.contract_terms, applications.initial_salary, applications.application_date, applications.approve_status, applications.workflow_table_id, applications.action_date, applications.applicant_comments, applications.review, applications.short_listed, applications.previous_salary, applications.expected_salary, applications.exchange_rate, applications.review_rating, vw_education_max.education_class_name, vw_education_max.date_from, vw_education_max.date_to, vw_education_max.name_of_school, vw_education_max.examination_taken, vw_education_max.grades_obtained, vw_education_max.certificate_number, vw_employment_max.employment_id, vw_employment_max.employers_name, vw_employment_max.position_held, vw_employment_max.date_from as emp_date_from, vw_employment_max.date_to as emp_date_to, vw_employment_max.employment_duration, vw_employment_max.employment_experince, round((date_part('year', vw_employment_max.employment_duration) + date_part('month', vw_employment_max.employment_duration)/12)::numeric, 1) as emp_duration, round((date_part('year', vw_employment_max.employment_experince) + date_part('month', vw_employment_max.employment_experince)/12)::numeric, 1) as emp_experince FROM applications INNER JOIN entitys ON applications.entity_id = entitys.entity_id INNER JOIN vw_intake ON applications.intake_id = vw_intake.intake_id LEFT JOIN vw_education_max ON entitys.entity_id = vw_education_max.entity_id LEFT JOIN vw_employment_max ON entitys.entity_id = vw_employment_max.entity_id LEFT JOIN currency ON applications.currency_id = currency.currency_id; CREATE VIEW vw_contracting AS SELECT vw_intake.department_id, vw_intake.department_name, vw_intake.department_description, vw_intake.department_duties, vw_intake.department_role_id, vw_intake.department_role_name, vw_intake.job_description, vw_intake.parent_role_name, vw_intake.job_requirements, vw_intake.duties, vw_intake.performance_measures, vw_intake.intake_id, vw_intake.opening_date, vw_intake.closing_date, vw_intake.positions, entitys.entity_id, entitys.entity_name, orgs.org_id, orgs.org_name, contract_types.contract_type_id, contract_types.contract_type_name, contract_types.notice_period, contract_types.contract_text, contract_status.contract_status_id, contract_status.contract_status_name, applications.application_id, applications.employee_id, applications.contract_date, applications.contract_close, applications.contract_start, applications.contract_period, applications.contract_terms, applications.initial_salary, applications.application_date, applications.approve_status, applications.workflow_table_id, applications.action_date, applications.applicant_comments, applications.review, applications.notice_email, (current_date - applications.contract_close) as days_end_contract, vw_education_max.education_class_name, vw_education_max.date_from, vw_education_max.date_to, vw_education_max.name_of_school, vw_education_max.examination_taken, vw_education_max.grades_obtained, vw_education_max.certificate_number, vw_employment_max.employment_id, vw_employment_max.employers_name, vw_employment_max.position_held, vw_employment_max.date_from as emp_date_from, vw_employment_max.date_to as emp_date_to, vw_employment_max.employment_duration, vw_employment_max.employment_experince, round((date_part('year', vw_employment_max.employment_duration) + date_part('month', vw_employment_max.employment_duration)/12)::numeric, 1) as emp_duration, round((date_part('year', vw_employment_max.employment_experince) + date_part('month', vw_employment_max.employment_experince)/12)::numeric, 1) as emp_experince FROM applications INNER JOIN entitys ON applications.employee_id = entitys.entity_id INNER JOIN orgs ON applications.org_id = orgs.org_id LEFT JOIN vw_intake ON applications.intake_id = vw_intake.intake_id LEFT JOIN contract_types ON applications.contract_type_id = contract_types.contract_type_id LEFT JOIN contract_status ON applications.contract_status_id = contract_status.contract_status_id LEFT JOIN vw_education_max ON entitys.entity_id = vw_education_max.entity_id LEFT JOIN vw_employment_max ON entitys.entity_id = vw_employment_max.entity_id; CREATE VIEW vw_internships AS SELECT departments.department_id, departments.department_name, internships.internship_id, internships.opening_date, orgs.org_id, orgs.org_name, orgs.details as org_details, internships.closing_date, internships.positions, internships.location, internships.details FROM internships INNER JOIN departments ON internships.department_id = departments.department_id INNER JOIN orgs ON internships.org_id = orgs.org_id; CREATE VIEW vw_interns AS SELECT entitys.entity_id, entitys.entity_name, entitys.primary_email, entitys.primary_telephone, vw_internships.department_id, vw_internships.department_name, vw_internships.org_name, vw_internships.org_details, vw_internships.internship_id, vw_internships.positions, vw_internships.opening_date, vw_internships.closing_date, interns.org_id, interns.intern_id, interns.payment_amount, interns.start_date, interns.end_date, interns.application_date, interns.approve_status, interns.action_date, interns.workflow_table_id, interns.phone_mobile, interns.applicant_comments, interns.review, vw_education_max.education_class_name, vw_education_max.date_from, vw_education_max.date_to, vw_education_max.name_of_school, vw_education_max.examination_taken, vw_education_max.grades_obtained, vw_education_max.certificate_number FROM interns INNER JOIN entitys ON interns.entity_id = entitys.entity_id INNER JOIN vw_internships ON interns.internship_id = vw_internships.internship_id LEFT JOIN vw_education_max ON entitys.entity_id = vw_education_max.entity_id; CREATE VIEW vw_employee_objectives AS SELECT entitys.entity_id, entitys.entity_name, employee_objectives.org_id, employee_objectives.employee_objective_id, employee_objectives.employee_objective_name, employee_objectives.objective_date, employee_objectives.approve_status, employee_objectives.workflow_table_id, employee_objectives.application_date, employee_objectives.action_date, employee_objectives.supervisor_comments, employee_objectives.details, EXTRACT(YEAR FROM employee_objectives.objective_date) as objective_year FROM employee_objectives INNER JOIN entitys ON employee_objectives.entity_id = entitys.entity_id; CREATE VIEW vw_objective_year AS SELECT vw_employee_objectives.org_id, vw_employee_objectives.objective_year FROM vw_employee_objectives WHERE vw_employee_objectives.objective_year is not null GROUP BY vw_employee_objectives.org_id, vw_employee_objectives.objective_year; CREATE VIEW vw_objectives AS SELECT vw_employee_objectives.entity_id, vw_employee_objectives.entity_name, vw_employee_objectives.employee_objective_id, vw_employee_objectives.employee_objective_name, vw_employee_objectives.objective_date, vw_employee_objectives.approve_status, vw_employee_objectives.workflow_table_id, vw_employee_objectives.application_date, vw_employee_objectives.action_date, vw_employee_objectives.supervisor_comments, objective_types.objective_type_id, objective_types.objective_type_name, objectives.org_id, objectives.objective_id, objectives.date_set, objectives.objective_ps, objectives.objective_name, objectives.objective_completed, objectives.details FROM objectives INNER JOIN vw_employee_objectives ON objectives.employee_objective_id = vw_employee_objectives.employee_objective_id INNER JOIN objective_types ON objectives.objective_type_id = objective_types.objective_type_id; CREATE VIEW vw_objective_details AS SELECT vw_objectives.entity_id, vw_objectives.entity_name, vw_objectives.employee_objective_id, vw_objectives.employee_objective_name, vw_objectives.objective_date, vw_objectives.approve_status, vw_objectives.workflow_table_id, vw_objectives.application_date, vw_objectives.action_date, vw_objectives.supervisor_comments, vw_objectives.objective_type_id, vw_objectives.objective_type_name, vw_objectives.objective_id, vw_objectives.date_set, vw_objectives.objective_ps, vw_objectives.objective_name, vw_objectives.objective_completed, objective_details.org_id, objective_details.objective_detail_id, objective_details.ln_objective_detail_id, objective_details.objective_detail_name, objective_details.success_indicator, objective_details.achievements, objective_details.resources_required, objective_details.target_date, objective_details.completed, objective_details.completion_date, objective_details.ods_ps, objective_details.ods_points, objective_details.ods_reviewer_points, objective_details.details FROM objective_details INNER JOIN vw_objectives ON objective_details.objective_id = vw_objectives.objective_id; CREATE VIEW vw_review_points AS SELECT review_category.review_category_id, review_category.review_category_name, review_category.details as review_category_details, review_points.org_id, review_points.review_point_id, review_points.review_point_name, review_points.review_points, review_points.details FROM review_points INNER JOIN review_category ON review_points.review_category_id = review_category.review_category_id; CREATE VIEW vw_job_reviews AS SELECT entitys.entity_id, entitys.entity_name, review_category.review_category_id, review_category.review_category_name, review_category.rate_objectives, job_reviews.org_id, job_reviews.job_review_id, job_reviews.total_points, job_reviews.review_date, job_reviews.review_done, job_reviews.approve_status, job_reviews.workflow_table_id, job_reviews.application_date, job_reviews.action_date, job_reviews.recomendation, job_reviews.reviewer_comments, job_reviews.pl_comments, job_reviews.details, EXTRACT(YEAR FROM job_reviews.review_date) as review_year FROM job_reviews INNER JOIN entitys ON job_reviews.entity_id = entitys.entity_id INNER JOIN review_category ON job_reviews.review_category_id = review_category.review_category_id; CREATE VIEW vw_review_year AS SELECT vw_job_reviews.org_id, vw_job_reviews.review_year FROM vw_job_reviews WHERE vw_job_reviews.review_year is not null GROUP BY vw_job_reviews.org_id, vw_job_reviews.review_year; CREATE VIEW vw_all_job_reviews AS SELECT a.org_id, a.review_year, a.entity_id, a.employee_id, a.employee_name, b.job_review_id, b.total_points, b.approve_status FROM (SELECT vw_review_year.review_year, employees.org_id, employees.entity_id, employees.employee_id, (employees.Surname || ' ' || employees.First_name || ' ' || COALESCE(employees.Middle_name, '')) as employee_name FROM vw_review_year INNER JOIN employees ON vw_review_year.org_id = employees.org_id WHERE employees.active = true) as a LEFT JOIN (SELECT job_review_id, total_points, approve_status, entity_id, review_year FROM vw_job_reviews) as b ON (a.entity_id = b.entity_id) AND (a.review_year = b.review_year); CREATE VIEW vw_evaluation_points AS SELECT vw_job_reviews.entity_id, vw_job_reviews.entity_name, vw_job_reviews.review_category_id, vw_job_reviews.review_category_name, vw_job_reviews.rate_objectives, vw_job_reviews.job_review_id, vw_job_reviews.total_points, vw_job_reviews.review_date, vw_job_reviews.review_done, vw_job_reviews.recomendation, vw_job_reviews.reviewer_comments, vw_job_reviews.pl_comments, vw_job_reviews.approve_status, vw_job_reviews.workflow_table_id, vw_job_reviews.application_date, vw_job_reviews.action_date, vw_review_points.review_point_id, vw_review_points.review_point_name, vw_review_points.review_points, evaluation_points.org_id, evaluation_points.evaluation_point_id, evaluation_points.points, evaluation_points.grade, evaluation_points.reviewer_points, evaluation_points.reviewer_grade, evaluation_points.reviewer_narrative, evaluation_points.narrative, evaluation_points.details FROM evaluation_points INNER JOIN vw_job_reviews ON evaluation_points.job_review_id = vw_job_reviews.job_review_id INNER JOIN vw_review_points ON evaluation_points.review_point_id = vw_review_points.review_point_id; CREATE VIEW vw_evaluation_objectives AS SELECT vw_job_reviews.entity_id, vw_job_reviews.entity_name, vw_job_reviews.review_category_id, vw_job_reviews.review_category_name, vw_job_reviews.rate_objectives, vw_job_reviews.job_review_id, vw_job_reviews.total_points, vw_job_reviews.review_date, vw_job_reviews.review_done, vw_job_reviews.recomendation, vw_job_reviews.reviewer_comments, vw_job_reviews.pl_comments, vw_job_reviews.approve_status, vw_job_reviews.workflow_table_id, vw_job_reviews.application_date, vw_job_reviews.action_date, vw_objectives.objective_type_id, vw_objectives.objective_type_name, vw_objectives.objective_id, vw_objectives.date_set, vw_objectives.objective_ps, vw_objectives.objective_name, vw_objectives.objective_completed, vw_objectives.details as objective_details, evaluation_points.org_id, evaluation_points.evaluation_point_id, evaluation_points.points, evaluation_points.reviewer_points, evaluation_points.reviewer_narrative, evaluation_points.narrative, evaluation_points.details FROM evaluation_points INNER JOIN vw_job_reviews ON evaluation_points.job_review_id = vw_job_reviews.job_review_id INNER JOIN vw_objectives ON evaluation_points.objective_id = vw_objectives.objective_id; CREATE VIEW vw_review_reporting AS SELECT entitys.entity_id, entitys.entity_name, rpt.entity_id as rpt_id, rpt.entity_name as rpt_name, reporting.reporting_id, reporting.date_from, reporting.date_to, reporting.primary_report, reporting.is_active, reporting.ps_reporting, reporting.reporting_level, job_reviews.job_review_id, job_reviews.total_points, job_reviews.org_id, job_reviews.review_date, job_reviews.review_done, job_reviews.approve_status, job_reviews.workflow_table_id, job_reviews.application_date, job_reviews.action_date, job_reviews.recomendation, job_reviews.reviewer_comments, job_reviews.pl_comments, EXTRACT(YEAR FROM job_reviews.review_date) as review_year FROM reporting INNER JOIN entitys ON reporting.entity_id = entitys.entity_id INNER JOIN entitys as rpt ON reporting.report_to_id = rpt.entity_id INNER JOIN job_reviews ON reporting.entity_id = job_reviews.entity_id; CREATE VIEW vw_career_development AS SELECT vw_job_reviews.entity_id, vw_job_reviews.entity_name, vw_job_reviews.job_review_id, vw_job_reviews.total_points, vw_job_reviews.review_date, vw_job_reviews.review_done, vw_job_reviews.recomendation, vw_job_reviews.reviewer_comments, vw_job_reviews.pl_comments, vw_job_reviews.approve_status, vw_job_reviews.workflow_table_id, vw_job_reviews.application_date, vw_job_reviews.action_date, career_development.career_development_id, career_development.career_development_name, career_development.details as career_development_details, evaluation_points.org_id, evaluation_points.evaluation_point_id, evaluation_points.points, evaluation_points.reviewer_points, evaluation_points.reviewer_narrative, evaluation_points.narrative, evaluation_points.details FROM evaluation_points INNER JOIN vw_job_reviews ON evaluation_points.job_review_id = vw_job_reviews.job_review_id INNER JOIN career_development ON evaluation_points.career_development_id = career_development.career_development_id; CREATE VIEW vw_employee_cases AS SELECT case_types.case_type_id, case_types.case_type_name, entitys.entity_id, entitys.entity_name, employee_cases.org_id, employee_cases.employee_case_id, employee_cases.narrative, employee_cases.case_date, employee_cases.complaint, employee_cases.case_action, employee_cases.Completed, employee_cases.details FROM employee_cases INNER JOIN case_types ON employee_cases.case_type_id = case_types.case_type_id INNER JOIN entitys ON employee_cases.entity_id = entitys.entity_id; CREATE VIEW vw_employee_trainings AS SELECT entitys.entity_id, entitys.entity_name, trainings.training_id, trainings.training_name, trainings.training_cost, employee_trainings.org_id, employee_trainings.employee_training_id, employee_trainings.narrative, employee_trainings.completed, employee_trainings.application_date, employee_trainings.approve_status, employee_trainings.action_date, employee_trainings.details FROM employee_trainings INNER JOIN entitys ON employee_trainings.entity_id = entitys.entity_id INNER JOIN trainings ON employee_trainings.training_id = trainings.training_id; CREATE VIEW vw_intern_evaluations AS SELECT vw_applicants.entity_id, vw_applicants.sys_country_name, vw_applicants.applicant_name, vw_applicants.applicant_age, vw_applicants.gender_name, vw_applicants.marital_status_name, vw_applicants.language, vw_applicants.objective, vw_applicants.interests, education.date_from, education.date_to, education.name_of_school, education.examination_taken, vw_internships.department_id, vw_internships.department_name, vw_internships.internship_id, vw_internships.positions, vw_internships.opening_date, vw_internships.closing_date, interns.intern_id, interns.payment_amount, interns.start_date, interns.end_date, interns.application_date, interns.approve_status, interns.action_date, interns.workflow_table_id, interns.applicant_comments, interns.review FROM vw_applicants JOIN education ON vw_applicants.entity_id = education.entity_id JOIN interns ON interns.entity_id = vw_applicants.entity_id JOIN vw_internships ON interns.internship_id = vw_internships.internship_id JOIN (SELECT education.entity_id, max(education.education_class_id) AS mx_class_id FROM education WHERE education.entity_id IS NOT NULL GROUP BY education.entity_id) a ON education.entity_id = a.entity_id AND education.education_class_id = a.mx_class_id WHERE education.education_class_id > 6 ORDER BY vw_applicants.entity_id; ------- Functions CREATE OR REPLACE FUNCTION get_review_entity(varchar(16)) RETURNS integer AS $$ SELECT entity_id FROM job_reviews WHERE (job_review_id = CAST($1 as int)); $$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION get_review_category(varchar(16)) RETURNS integer AS $$ SELECT review_category_id FROM job_reviews WHERE (job_review_id = CAST($1 as int)); $$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION get_default_country(int) RETURNS char(2) AS $$ SELECT default_country_id::varchar(2) FROM orgs WHERE (org_id = $1); $$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION get_default_currency(int) RETURNS int AS $$ SELECT currency_id FROM orgs WHERE (org_id = $1); $$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION ins_applicants() RETURNS trigger AS $$ DECLARE v_org_id integer; v_entity_id integer; v_entity_type_id integer; BEGIN IF (TG_OP = 'INSERT') THEN IF(NEW.entity_id IS NULL) THEN SELECT entity_id INTO v_entity_id FROM entitys WHERE (trim(lower(user_name)) = trim(lower(NEW.applicant_email))); IF(v_entity_id is null)THEN v_org_id := NEW.org_id; IF(v_org_id is null)THEN SELECT min(org_id) INTO v_org_id FROM orgs WHERE (is_default = true); END IF; SELECT entity_type_id INTO v_entity_type_id FROM entity_types WHERE (org_id = v_org_id) AND (use_key_id = 4); NEW.entity_id := nextval('entitys_entity_id_seq'); INSERT INTO entitys (entity_id, org_id, entity_type_id, use_key_id, entity_name, User_name, primary_email, primary_telephone, function_role) VALUES (NEW.entity_id, v_org_id, v_entity_type_id, 4, (NEW.Surname || ' ' || NEW.First_name || ' ' || COALESCE(NEW.Middle_name, '')), lower(NEW.applicant_email), lower(NEW.applicant_email), NEW.applicant_phone, 'applicant'); ELSE RAISE EXCEPTION 'The username exists use a different one or reset password for the current one'; END IF; END IF; INSERT INTO sys_emailed (sys_email_id, table_id, table_name) VALUES (1, NEW.entity_id, 'applicant'); ELSIF (TG_OP = 'UPDATE') THEN UPDATE entitys SET entity_name = (NEW.Surname || ' ' || NEW.First_name || ' ' || COALESCE(NEW.Middle_name, '')) WHERE entity_id = NEW.entity_id; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER ins_applicants BEFORE INSERT OR UPDATE ON applicants FOR EACH ROW EXECUTE PROCEDURE ins_applicants(); CREATE OR REPLACE FUNCTION ins_employees() RETURNS trigger AS $$ DECLARE v_entity_type_id integer; v_use_type integer; v_org_sufix varchar(4); v_first_password varchar(12); v_user_count integer; v_user_name varchar(120); BEGIN IF (TG_OP = 'INSERT') THEN IF(NEW.entity_id IS NULL) THEN SELECT org_sufix INTO v_org_sufix FROM orgs WHERE (org_id = NEW.org_id); IF(v_org_sufix is null)THEN v_org_sufix := ''; END IF; NEW.entity_id := nextval('entitys_entity_id_seq'); IF(NEW.employee_id is null) THEN NEW.employee_id := NEW.entity_id; END IF; SELECT entity_type_id INTO v_entity_type_id FROM entity_types WHERE (org_id = NEW.org_id) AND (use_key_id = 1); v_first_password := first_password(); v_user_name := lower(v_org_sufix || '.' || NEW.First_name || '.' || NEW.Surname); SELECT count(entity_id) INTO v_user_count FROM entitys WHERE (org_id = NEW.org_id) AND (user_name = v_user_name); IF(v_user_count > 0) THEN v_user_name := v_user_name || v_user_count::varchar; END IF; INSERT INTO entitys (entity_id, org_id, entity_type_id, use_key_id, entity_name, user_name, function_role, first_password, entity_password) VALUES (NEW.entity_id, NEW.org_id, v_entity_type_id, 1, (NEW.Surname || ' ' || NEW.First_name || ' ' || COALESCE(NEW.Middle_name, '')), v_user_name, 'staff', v_first_password, md5(v_first_password)); INSERT INTO sys_emailed (org_id, sys_email_id, table_id, table_name) SELECT org_id, sys_email_id, NEW.entity_id, 'entitys' FROM sys_emails WHERE (use_type = 3) AND (org_id = NEW.org_id); END IF; v_use_type := 2; IF(NEW.gender = 'M')THEN v_use_type := 3; END IF; INSERT INTO employee_leave_types (entity_id, org_id, leave_type_id, leave_balance) SELECT NEW.entity_id, NEW.org_id, leave_type_id, 0 FROM leave_types WHERE (org_id = NEW.org_id) AND ((use_type = 1) OR (use_type = v_use_type)); ELSIF (TG_OP = 'UPDATE') THEN UPDATE entitys SET entity_name = (NEW.Surname || ' ' || NEW.First_name || ' ' || COALESCE(NEW.Middle_name, '')) WHERE entity_id = NEW.entity_id; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER ins_employees BEFORE INSERT OR UPDATE ON employees FOR EACH ROW EXECUTE PROCEDURE ins_employees(); CREATE OR REPLACE FUNCTION insf_leave_types() RETURNS trigger AS $$ BEGIN IF(NEW.use_type = 1)THEN INSERT INTO employee_leave_types (entity_id, org_id, leave_type_id, leave_balance) SELECT entity_id, NEW.org_id, NEW.leave_type_id, 0 FROM employees WHERE (org_id = NEW.org_id) AND (active = true); ELSIF(NEW.use_type = 2)THEN INSERT INTO employee_leave_types (entity_id, org_id, leave_type_id, leave_balance) SELECT entity_id, NEW.org_id, NEW.leave_type_id, 0 FROM employees WHERE (org_id = NEW.org_id) AND (active = true) AND (gender = 'F'); ELSIF(NEW.use_type = 1)THEN INSERT INTO employee_leave_types (entity_id, org_id, leave_type_id, leave_balance) SELECT entity_id, NEW.org_id, NEW.leave_type_id, 0 FROM employees WHERE (org_id = NEW.org_id) AND (active = true) AND (gender = 'M'); END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; CREATE TRIGGER insf_leave_types AFTER INSERT ON leave_types FOR EACH ROW EXECUTE PROCEDURE insf_leave_types(); CREATE OR REPLACE FUNCTION ins_employee_leave() RETURNS trigger AS $$ BEGIN IF(NEW.leave_to < NEW.leave_from)THEN RAISE EXCEPTION 'Check on your leave dates.'; END IF; IF(NEW.start_half_day = true) AND (NEW.end_half_day = true) AND (NEW.leave_to = NEW.leave_from)THEN RAISE EXCEPTION 'The leave half days cannot be done on the same day'; END IF; IF(NEW.approve_status = 'Draft') OR (NEW.leave_days is null)THEN NEW.leave_days := get_leave_days(NEW.leave_from, NEW.leave_to, NEW.leave_type_id); IF(NEW.start_half_day = true)THEN NEW.leave_days := NEW.leave_days - 0.5; END IF; IF(NEW.end_half_day = true)THEN NEW.leave_days := NEW.leave_days - 0.5; END IF; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER ins_employee_leave BEFORE INSERT OR UPDATE ON employee_leave FOR EACH ROW EXECUTE PROCEDURE ins_employee_leave(); CREATE OR REPLACE FUNCTION ins_leave_work_days() RETURNS trigger AS $$ BEGIN SELECT entity_id INTO NEW.entity_id FROM employee_leave WHERE (employee_leave_id = NEW.employee_leave_id); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER ins_leave_work_days BEFORE INSERT ON leave_work_days FOR EACH ROW EXECUTE PROCEDURE ins_leave_work_days(); CREATE OR REPLACE FUNCTION ins_employee_leave_types() RETURNS trigger AS $$ DECLARE reca RECORD; v_months integer; BEGIN SELECT allowed_leave_days, month_quota, initial_days, maximum_carry INTO reca FROM leave_types WHERE (leave_type_id = NEW.leave_type_id); IF(reca.month_quota > 0)THEN v_months := EXTRACT(MONTH FROM NEW.leave_starting) - 1; NEW.leave_balance := reca.month_quota * v_months * -1; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER ins_employee_leave_types BEFORE INSERT ON employee_leave_types FOR EACH ROW EXECUTE PROCEDURE ins_employee_leave_types(); CREATE OR REPLACE FUNCTION ins_job_reviews() RETURNS trigger AS $$ BEGIN INSERT INTO evaluation_points (job_review_id, org_id, review_point_id) SELECT NEW.job_review_id, NEW.org_id, review_point_id FROM review_points WHERE (review_category_id = NEW.review_category_id); INSERT INTO evaluation_points (job_review_id, org_id, objective_id) SELECT NEW.job_review_id, NEW.org_id, objectives.objective_id FROM objectives INNER JOIN employee_objectives ON objectives.employee_objective_id = employee_objectives.employee_objective_id WHERE (employee_objectives.entity_id = NEW.entity_id) AND (objectives.objective_completed = false); INSERT INTO evaluation_points (job_review_id, org_id, career_development_id) SELECT NEW.job_review_id, NEW.org_id, career_development_id FROM career_development WHERE (org_id = NEW.org_id); RETURN NULL; END; $$ LANGUAGE plpgsql; CREATE TRIGGER ins_job_reviews AFTER INSERT ON job_reviews FOR EACH ROW EXECUTE PROCEDURE ins_job_reviews(); CREATE OR REPLACE FUNCTION ins_applications(varchar(12), varchar(12), varchar(12)) RETURNS varchar(120) AS $$ DECLARE v_entity_id integer; v_application_id integer; v_sys_email_id integer; v_address integer; c_education_id integer; c_referees integer; c_files integer; reca RECORD; msg varchar(120); BEGIN SELECT application_id INTO v_application_id FROM applications WHERE (intake_id = $1::int) AND (entity_id = $2::int); SELECT org_id, entity_id, currency_id, previous_salary, expected_salary INTO reca FROM applicants WHERE (entity_id = $2::int); v_entity_id := reca.entity_id; IF(reca.entity_id is null) THEN SELECT org_id, entity_id, currency_id, basic_salary as previous_salary, basic_salary as expected_salary INTO reca FROM employees WHERE (entity_id = $2::int); v_entity_id := reca.entity_id; END IF; SELECT count(address_id) INTO v_address FROM vw_address WHERE (table_name = 'applicant') AND (is_default = true) AND (table_id = v_entity_id); IF(v_address is null) THEN v_address = 0; END IF; SELECT count(education_id) INTO c_education_id FROM education WHERE (entity_id = v_entity_id); IF(c_education_id is null) THEN c_education_id = 0; END IF; SELECT count(address_id) INTO c_referees FROM vw_referees WHERE (table_id = v_entity_id); IF(c_referees is null) THEN c_referees = 0; END IF; SELECT count(sys_file_id) INTO c_files FROM sys_files WHERE (table_id = v_entity_id); IF(c_files is null) THEN c_files = 0; END IF; IF v_application_id is not null THEN msg := 'There is another application for the post.'; RAISE EXCEPTION '%', msg; ELSIF (reca.previous_salary is null) OR (reca.expected_salary is null) THEN msg := 'Kindly indicate your previous and expected salary'; RAISE EXCEPTION '%', msg; ELSIF (v_address < 1) THEN msg := 'You need to have at least one full address added'; RAISE EXCEPTION '%', msg; ELSIF (c_education_id < 2) THEN msg := 'You need to have at least two education levels added'; RAISE EXCEPTION '%', msg; ELSIF (c_referees < 3) THEN msg := 'You need to have at least three referees added'; RAISE EXCEPTION '%', msg; ELSIF (c_files < 2) THEN msg := 'CV and Cover Letter MUST be uploaded'; RAISE EXCEPTION '%', msg; ELSE v_application_id := nextval('applications_application_id_seq'); INSERT INTO applications (application_id, intake_id, org_id, entity_id, currency_id, previous_salary, expected_salary, approve_status) VALUES (v_application_id, $1::int, reca.org_id, reca.entity_id, reca.currency_id, reca.previous_salary, reca.expected_salary, 'Completed'); SELECT sys_email_id INTO v_sys_email_id FROM sys_emails WHERE (use_type = 10) AND (org_id = reca.org_id); INSERT INTO sys_emailed (sys_email_id, org_id, table_id, table_name, email_type) VALUES (v_sys_email_id, reca.org_id, v_application_id, 'applications', 10); msg := 'Added Job application'; END IF; return msg; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION ins_interns(varchar(12), varchar(12), varchar(12)) RETURNS varchar(120) AS $$ DECLARE v_intern_id integer; v_org_id integer; v_sys_email_id integer; msg varchar(120); BEGIN SELECT intern_id INTO v_intern_id FROM interns WHERE (internship_id = $1::int) AND (entity_id = $2::int); SELECT org_id INTO v_org_id FROM internships WHERE (internship_id = $1::int); IF v_intern_id is null THEN v_intern_id := nextval('interns_intern_id_seq'); INSERT INTO interns (intern_id, org_id, internship_id, entity_id, approve_status) VALUES (v_intern_id, v_org_id, $1::int, $2::int, 'Completed'); SELECT sys_email_id INTO v_sys_email_id FROM sys_emails WHERE (use_type = 11) AND (org_id = v_org_id); INSERT INTO sys_emailed (sys_email_id, org_id, table_id, table_name, email_type) VALUES (v_sys_email_id, v_org_id, v_intern_id, 'interns', 11); msg := 'Added internship application'; ELSE msg := 'There is another application for the internship.'; END IF; return msg; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION ins_applications() RETURNS trigger AS $$ DECLARE typeid integer; BEGIN IF ((NEW.entity_id is null) AND (NEW.employee_id is not null)) THEN NEW.entity_id := NEW.employee_id; NEW.approve_status := 'Completed'; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER ins_applications BEFORE INSERT ON applications FOR EACH ROW EXECUTE PROCEDURE ins_applications(); CREATE TRIGGER upd_action BEFORE INSERT OR UPDATE ON employee_leave FOR EACH ROW EXECUTE PROCEDURE upd_action(); CREATE TRIGGER upd_action BEFORE INSERT OR UPDATE ON leave_work_days FOR EACH ROW EXECUTE PROCEDURE upd_action(); CREATE TRIGGER upd_action BEFORE UPDATE ON applications FOR EACH ROW EXECUTE PROCEDURE upd_action(); CREATE TRIGGER upd_action BEFORE INSERT OR UPDATE ON casual_application FOR EACH ROW EXECUTE PROCEDURE upd_action(); CREATE TRIGGER upd_action BEFORE INSERT OR UPDATE ON casuals FOR EACH ROW EXECUTE PROCEDURE upd_action(); CREATE TRIGGER upd_action BEFORE INSERT OR UPDATE ON interns FOR EACH ROW EXECUTE PROCEDURE upd_action(); CREATE TRIGGER upd_action BEFORE INSERT OR UPDATE ON employee_objectives FOR EACH ROW EXECUTE PROCEDURE upd_action(); CREATE TRIGGER upd_action BEFORE INSERT OR UPDATE ON job_reviews FOR EACH ROW EXECUTE PROCEDURE upd_action(); CREATE OR REPLACE FUNCTION upd_action() RETURNS trigger AS $$ DECLARE wfid INTEGER; reca RECORD; tbid INTEGER; iswf BOOLEAN; add_flow BOOLEAN; BEGIN add_flow := false; IF(TG_OP = 'INSERT')THEN IF (NEW.approve_status = 'Completed')THEN add_flow := true; END IF; ELSE IF(OLD.approve_status = 'Draft') AND (NEW.approve_status = 'Completed')THEN add_flow := true; END IF; END IF; IF(add_flow = true)THEN wfid := nextval('workflow_table_id_seq'); NEW.workflow_table_id := wfid; IF(TG_OP = 'UPDATE')THEN IF(OLD.workflow_table_id is not null)THEN INSERT INTO workflow_logs (org_id, table_name, table_id, table_old_id) VALUES (NEW.org_id, TG_TABLE_NAME, wfid, OLD.workflow_table_id); END IF; END IF; FOR reca IN SELECT workflows.workflow_id, workflows.table_name, workflows.table_link_field, workflows.table_link_id FROM workflows INNER JOIN entity_subscriptions ON workflows.source_entity_id = entity_subscriptions.entity_type_id WHERE (workflows.table_name = TG_TABLE_NAME) AND (entity_subscriptions.entity_id= NEW.entity_id) LOOP iswf := false; IF(reca.table_link_field is null)THEN iswf := true; ELSE IF(TG_TABLE_NAME = 'entry_forms')THEN tbid := NEW.form_id; ELSIF(TG_TABLE_NAME = 'employee_leave')THEN tbid := NEW.leave_type_id; END IF; IF(tbid = reca.table_link_id)THEN iswf := true; END IF; END IF; IF(iswf = true)THEN INSERT INTO approvals (org_id, workflow_phase_id, table_name, table_id, org_entity_id, escalation_days, escalation_hours, approval_level, approval_narrative, to_be_done) SELECT org_id, workflow_phase_id, tg_table_name, wfid, new.entity_id, escalation_days, escalation_hours, approval_level, phase_narrative, 'Approve - ' || phase_narrative FROM vw_workflow_entitys WHERE (table_name = TG_TABLE_NAME) AND (entity_id = NEW.entity_id) AND (workflow_id = reca.workflow_id) ORDER BY approval_level, workflow_phase_id; UPDATE approvals SET approve_status = 'Completed' WHERE (table_id = wfid) AND (approval_level = 1); END IF; END LOOP; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION get_leave_taken(integer, integer) RETURNS real AS $$ SELECT COALESCE(sum(leave_days), 0) FROM employee_leave WHERE (approve_status = 'Approved') AND (to_char(leave_from, 'YYYY') = to_char(current_date, 'YYYY')) AND (entity_id = $1) AND (leave_type_id = $2); $$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION get_leave_balance(integer, integer) RETURNS real AS $$ DECLARE reca RECORD; v_months integer; v_leave_starting date; v_leave_carryover real; v_leave_balance real; v_leave_days real; v_leave_work_days real; v_leave_initial real; v_year_leave real; BEGIN SELECT allowed_leave_days, month_quota, initial_days, maximum_carry INTO reca FROM leave_types WHERE (leave_type_id = $2); SELECT leave_balance, leave_starting INTO v_leave_carryover, v_leave_starting FROM employee_leave_types WHERE (entity_id = $1) AND (leave_type_id = $2); IF(v_leave_carryover is null) THEN v_leave_carryover := 0; END IF; IF(v_leave_carryover > reca.maximum_carry) THEN v_leave_carryover := reca.maximum_carry; END IF; IF(v_leave_starting is null) THEN v_leave_starting := current_date; END IF; v_months := EXTRACT(MONTH FROM CURRENT_TIMESTAMP) - 1; v_leave_balance := reca.initial_days + reca.month_quota * v_months; if(reca.month_quota = 0)THEN v_leave_balance := reca.allowed_leave_days; END IF; IF(reca.maximum_carry = 0)THEN SELECT sum(employee_leave.leave_days) INTO v_leave_days FROM employee_leave WHERE (entity_id = $1) AND (leave_type_id = $2) AND (approve_status <> 'Rejected') AND (approve_status <> 'Draft') AND (EXTRACT(YEAR FROM leave_from) = EXTRACT(YEAR FROM now())); IF(v_leave_days is null) THEN v_leave_days := 0; END IF; SELECT SUM(CASE WHEN leave_work_days.half_day = true THEN 0.5 ELSE 1 END) INTO v_leave_work_days FROM leave_work_days INNER JOIN employee_leave ON leave_work_days.employee_leave_id = employee_leave.employee_leave_id WHERE (employee_leave.entity_id = $1) AND (employee_leave.leave_type_id = $2) AND (leave_work_days.approve_status = 'Approved') AND (EXTRACT(YEAR FROM employee_leave.leave_from) = EXTRACT(YEAR FROM now())); IF(v_leave_work_days is null) THEN v_leave_work_days := 0; END IF; v_leave_days := v_leave_days - v_leave_work_days; IF(v_leave_balance > reca.allowed_leave_days) THEN v_leave_balance := reca.allowed_leave_days; END IF; v_leave_balance := v_leave_balance - v_leave_days; ELSE SELECT sum(employee_leave.leave_days) INTO v_leave_days FROM employee_leave WHERE (entity_id = $1) AND (leave_type_id = $2) AND (approve_status <> 'Rejected') AND (approve_status <> 'Draft'); IF(v_leave_days is null) THEN v_leave_days := 0; END IF; SELECT sum(employee_leave.leave_days) INTO v_year_leave FROM employee_leave WHERE (entity_id = $1) AND (leave_type_id = $2) AND (approve_status <> 'Rejected') AND (approve_status <> 'Draft') AND (EXTRACT(YEAR FROM leave_from) = EXTRACT(YEAR FROM now())); IF(v_year_leave is null) THEN v_year_leave := 0; END IF; SELECT SUM(CASE WHEN leave_work_days.half_day = true THEN 0.5 ELSE 1 END) INTO v_leave_work_days FROM leave_work_days INNER JOIN employee_leave ON leave_work_days.employee_leave_id = employee_leave.employee_leave_id WHERE (employee_leave.entity_id = $1) AND (employee_leave.leave_type_id = $2) AND (leave_work_days.approve_status = 'Approved'); IF(v_leave_work_days is null) THEN v_leave_work_days := 0; END IF; v_leave_days := v_leave_days - v_leave_work_days; v_leave_initial := v_leave_carryover + (EXTRACT(YEAR FROM now()) - EXTRACT(YEAR FROM v_leave_starting)) * reca.allowed_leave_days; IF(EXTRACT(MONTH FROM v_leave_starting) > 1)THEN v_leave_initial := v_leave_carryover + (EXTRACT(YEAR FROM now()) - EXTRACT(YEAR FROM v_leave_starting) - 1) * reca.allowed_leave_days; IF(reca.month_quota = 0)THEN v_leave_initial := v_leave_initial + (13 - EXTRACT(MONTH FROM v_leave_starting)) * reca.month_quota; ELSE v_leave_initial := v_leave_initial + reca.allowed_leave_days; END IF; END IF; v_leave_initial := v_leave_initial - (v_leave_days - v_year_leave); IF(v_leave_initial > reca.maximum_carry) THEN v_leave_initial := reca.maximum_carry; END IF; v_leave_balance := v_leave_initial + v_leave_balance - v_year_leave; END IF; RETURN v_leave_balance; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION get_leave_approved_balance(integer, integer) RETURNS real AS $$ DECLARE reca RECORD; v_months integer; v_leave_starting date; v_leave_carryover real; v_leave_balance real; v_leave_days real; v_leave_work_days real; v_leave_initial real; v_year_leave real; BEGIN SELECT allowed_leave_days, month_quota, initial_days, maximum_carry INTO reca FROM leave_types WHERE (leave_type_id = $2); SELECT leave_balance, leave_starting INTO v_leave_carryover, v_leave_starting FROM employee_leave_types WHERE (entity_id = $1) AND (leave_type_id = $2); IF(v_leave_carryover is null) THEN v_leave_carryover := 0; END IF; IF(v_leave_carryover > reca.maximum_carry) THEN v_leave_carryover := reca.maximum_carry; END IF; IF(v_leave_starting is null) THEN v_leave_starting := current_date; END IF; v_months := EXTRACT(MONTH FROM CURRENT_TIMESTAMP) - 1; v_leave_balance := reca.initial_days + reca.month_quota * v_months; if(reca.month_quota = 0)THEN v_leave_balance := reca.allowed_leave_days; END IF; IF(reca.maximum_carry = 0)THEN SELECT sum(employee_leave.leave_days) INTO v_leave_days FROM employee_leave WHERE (entity_id = $1) AND (leave_type_id = $2) AND (approve_status = 'Approved') AND (EXTRACT(YEAR FROM leave_from) = EXTRACT(YEAR FROM now())); IF(v_leave_days is null) THEN v_leave_days := 0; END IF; SELECT SUM(CASE WHEN leave_work_days.half_day = true THEN 0.5 ELSE 1 END) INTO v_leave_work_days FROM leave_work_days INNER JOIN employee_leave ON leave_work_days.employee_leave_id = employee_leave.employee_leave_id WHERE (employee_leave.entity_id = $1) AND (employee_leave.leave_type_id = $2) AND (leave_work_days.approve_status = 'Approved') AND (EXTRACT(YEAR FROM employee_leave.leave_from) = EXTRACT(YEAR FROM now())); IF(v_leave_work_days is null) THEN v_leave_work_days := 0; END IF; v_leave_days := v_leave_days - v_leave_work_days; IF(v_leave_balance > reca.allowed_leave_days) THEN v_leave_balance := reca.allowed_leave_days; END IF; v_leave_balance := v_leave_balance - v_leave_days; ELSE SELECT sum(employee_leave.leave_days) INTO v_leave_days FROM employee_leave WHERE (entity_id = $1) AND (leave_type_id = $2) AND (approve_status = 'Approved'); IF(v_leave_days is null) THEN v_leave_days := 0; END IF; SELECT sum(employee_leave.leave_days) INTO v_year_leave FROM employee_leave WHERE (entity_id = $1) AND (leave_type_id = $2) AND (approve_status = 'Approved') AND (EXTRACT(YEAR FROM leave_from) = EXTRACT(YEAR FROM now())); IF(v_year_leave is null) THEN v_year_leave := 0; END IF; SELECT SUM(CASE WHEN leave_work_days.half_day = true THEN 0.5 ELSE 1 END) INTO v_leave_work_days FROM leave_work_days INNER JOIN employee_leave ON leave_work_days.employee_leave_id = employee_leave.employee_leave_id WHERE (employee_leave.entity_id = $1) AND (employee_leave.leave_type_id = $2) AND (leave_work_days.approve_status = 'Approved'); IF(v_leave_work_days is null) THEN v_leave_work_days := 0; END IF; v_leave_days := v_leave_days - v_leave_work_days; v_leave_initial := v_leave_carryover + (EXTRACT(YEAR FROM now()) - EXTRACT(YEAR FROM v_leave_starting)) * reca.allowed_leave_days; IF(EXTRACT(MONTH FROM v_leave_starting) > 1)THEN v_leave_initial := v_leave_carryover + (EXTRACT(YEAR FROM now()) - EXTRACT(YEAR FROM v_leave_starting) - 1) * reca.allowed_leave_days; IF(reca.month_quota = 0)THEN v_leave_initial := v_leave_initial + (13 - EXTRACT(MONTH FROM v_leave_starting)) * reca.month_quota; ELSE v_leave_initial := v_leave_initial + reca.allowed_leave_days; END IF; END IF; v_leave_initial := v_leave_initial - (v_leave_days - v_year_leave); IF(v_leave_initial > reca.maximum_carry) THEN v_leave_initial := reca.maximum_carry; END IF; v_leave_balance := v_leave_initial + v_leave_balance - v_year_leave; END IF; RETURN v_leave_balance; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION leave_aplication(varchar(12), varchar(12), varchar(12)) RETURNS varchar(120) AS $$ DECLARE v_leave_balance real; v_leave_overlap integer; v_approve_status varchar(16); v_table_id integer; rec RECORD; msg varchar(120); BEGIN msg := 'Leave applied'; SELECT leave_types.leave_days_span, employee_leave.entity_id, employee_leave.leave_type_id, employee_leave.leave_days, employee_leave.leave_from, employee_leave.leave_to, employee_leave.contact_entity_id, employee_leave.narrative INTO rec FROM leave_types INNER JOIN employee_leave ON leave_types.leave_type_id = employee_leave.leave_type_id WHERE (employee_leave.employee_leave_id = CAST($1 as int)); v_leave_balance := get_leave_balance(rec.entity_id, rec.leave_type_id); SELECT count(employee_leave_id) INTO v_leave_overlap FROM employee_leave WHERE (entity_id = rec.entity_id) AND ((approve_status = 'Completed') OR (approve_status = 'Approved')) AND (((leave_from, leave_to) OVERLAPS (rec.leave_from, rec.leave_to)) = true); SELECT approve_status INTO v_approve_status FROM employee_leave WHERE (employee_leave_id = CAST($1 as int)); IF(rec.contact_entity_id is null)THEN RAISE EXCEPTION 'You must enter a contact person.'; ELSIF(v_approve_status <> 'Draft')THEN msg := 'Your application is not a draft.'; RAISE EXCEPTION '%', msg; ELSIF(rec.leave_days > rec.leave_days_span)THEN msg := 'Days applied for excced the span allowed'; RAISE EXCEPTION '%', msg; ELSIF(v_leave_balance <= 0) THEN msg := 'You do not have enough days to apply for this leave'; RAISE EXCEPTION '%', msg; ELSIF(v_leave_overlap > 0) THEN msg := 'You have applied for overlaping leave days'; RAISE EXCEPTION '%', msg; ELSE UPDATE employee_leave SET approve_status = 'Completed' WHERE (employee_leave_id = CAST($1 as int)); SELECT workflow_table_id INTO v_table_id FROM employee_leave WHERE (employee_leave_id = CAST($1 as int)); UPDATE approvals SET approval_narrative = rec.narrative WHERE (table_name = 'employee_leave') AND (table_id = v_table_id); END IF; return msg; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION leave_special(varchar(12), varchar(12), varchar(12), varchar(12)) RETURNS varchar(120) AS $$ DECLARE v_leave_overlap integer; v_approve_status varchar(16); rec RECORD; msg varchar(120); BEGIN SELECT approve_status INTO v_approve_status FROM employee_leave WHERE (employee_leave_id = CAST($1 as int)); SELECT leave_types.leave_days_span, employee_leave.entity_id, employee_leave.leave_type_id, employee_leave.leave_days, employee_leave.leave_from, employee_leave.leave_to INTO rec FROM leave_types INNER JOIN employee_leave ON leave_types.leave_type_id = employee_leave.leave_type_id WHERE (employee_leave.employee_leave_id = CAST($1 as int)); IF(v_approve_status <> 'Draft')THEN msg := 'Your application is not a draft.'; RAISE EXCEPTION '%', msg; ELSIF($3 = '1') THEN SELECT count(employee_leave_id) INTO v_leave_overlap FROM employee_leave WHERE (entity_id = rec.entity_id) AND ((approve_status = 'Completed') OR (approve_status = 'Approved')) AND (((leave_from, leave_to) OVERLAPS (rec.leave_from, rec.leave_to)) = true); IF(v_leave_overlap > 0) THEN msg := 'You have applied for overlaping leave days'; RAISE EXCEPTION '%', msg; ELSE UPDATE employee_leave SET special_request = true WHERE (employee_leave_id = CAST($1 as int)); msg := 'Special request send to HR'; END IF; ELSIF($3 = '2') THEN UPDATE employee_leave SET approve_status = 'Completed' WHERE (employee_leave_id = CAST($1 as int)); msg := 'Leave applied'; ELSIF($3 = '3') THEN UPDATE employee_leave SET approve_status = 'Rejected', action_date = now() WHERE (employee_leave_id = CAST($1 as int)); msg := 'Leave rejected'; END IF; return msg; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION job_review_check(varchar(12), varchar(12), varchar(12)) RETURNS varchar(120) AS $$ DECLARE rec RECORD; v_approve_status varchar(16); v_rate_objectives boolean; v_self_rating integer; v_objective_ps real; sum_ods_ps real; v_point_check integer; msg varchar(120); BEGIN SELECT sum(objectives.objective_ps) INTO v_objective_ps FROM objectives INNER JOIN evaluation_points ON evaluation_points.objective_id = objectives.objective_id WHERE (evaluation_points.job_review_id = CAST($1 as int)); SELECT sum(ods_ps) INTO sum_ods_ps FROM objective_details INNER JOIN evaluation_points ON evaluation_points.objective_id = objective_details.objective_id WHERE (evaluation_points.job_review_id = CAST($1 as int)); SELECT evaluation_points.evaluation_point_id INTO v_point_check FROM objectives INNER JOIN evaluation_points ON evaluation_points.objective_id = objectives.objective_id WHERE (evaluation_points.job_review_id = CAST($1 as int)) AND (objectives.objective_ps > 0) AND (evaluation_points.points = 0); SELECT job_reviews.self_rating, review_category.rate_objectives, job_reviews.approve_status INTO v_self_rating, v_rate_objectives, v_approve_status FROM job_reviews INNER JOIN review_category ON job_reviews.review_category_id = review_category.review_category_id WHERE (job_reviews.job_review_id = $1::int); IF(v_self_rating is null) THEN v_self_rating := 0; END IF; IF(sum_ods_ps is null)THEN sum_ods_ps := 100; END IF; IF(sum_ods_ps = 0)THEN sum_ods_ps := 100; END IF; IF(v_rate_objectives = false)THEN v_objective_ps := 100; sum_ods_ps := 100; END IF; IF(v_approve_status <> 'Draft')THEN msg := 'The review is already submitted'; ELSIF(v_objective_ps <> 100)THEN msg := 'Objective % must add up to 100'; RAISE EXCEPTION '%', msg; ELSIF(sum_ods_ps <> 100)THEN msg := 'Objective details % must add up to 100'; RAISE EXCEPTION '%', msg; ELSIF(v_self_rating = 0) AND (v_rate_objectives = true)THEN msg := 'Indicate your self rating'; RAISE EXCEPTION '%', msg; ELSIF(v_point_check is not null)THEN msg := 'All objective evaluations points must be between 1 to 4'; RAISE EXCEPTION '%', msg; ELSE UPDATE job_reviews SET approve_status = 'Completed' WHERE (job_review_id = CAST($1 as int)); msg := 'Review Applied'; END IF; RETURN msg; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION objectives_review(varchar(12), varchar(12), varchar(12)) RETURNS varchar(120) AS $$ DECLARE v_objective_ps real; max_objective_ps real; sum_ods_ps real; rec RECORD; msg varchar(120); BEGIN SELECT sum(objectives.objective_ps) INTO v_objective_ps FROM objectives WHERE (objectives.employee_objective_id = CAST($1 as int)); SELECT max(objectives.objective_ps) INTO max_objective_ps FROM objectives WHERE (objectives.employee_objective_id = CAST($1 as int)); SELECT sum(objective_details.ods_ps) INTO sum_ods_ps FROM objective_details INNER JOIN objectives ON objective_details.objective_id = objectives.objective_id WHERE (objectives.employee_objective_id = CAST($1 as int)); IF(v_objective_ps is null)THEN v_objective_ps := 0; END IF; IF(max_objective_ps is null)THEN max_objective_ps := 0; END IF; IF(sum_ods_ps is null)THEN sum_ods_ps := 100; END IF; IF(sum_ods_ps = 0)THEN sum_ods_ps := 100; END IF; IF(max_objective_ps > 50)THEN msg := 'Objective should not have a % higer than 50'; RAISE EXCEPTION '%', msg; ELSIF(v_objective_ps = 100) AND (sum_ods_ps = 100)THEN UPDATE employee_objectives SET approve_status = 'Completed' WHERE (employee_objective_id = CAST($1 as int)); msg := 'Objectives Review Applied'; ELSIF(sum_ods_ps <> 100)THEN msg := 'Objective details % must add up to 100'; RAISE EXCEPTION '%', msg; ELSE msg := 'Objective % must add up to 100'; RAISE EXCEPTION '%', msg; END IF; return msg; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION get_leave_days(date, date, integer) RETURNS real AS $$ DECLARE v_day date; v_holiday integer; v_holidays integer; rec RECORD; ans real; BEGIN ans := 0.0; v_day := $1; SELECT leave_type_id, allowed_leave_days, leave_days_span, month_quota, initial_days, maximum_carry, include_holiday, include_mon, include_tue, include_wed, include_thu, include_fri, include_sat, include_sun INTO rec FROM leave_types WHERE (leave_type_id = $3); v_holidays := 0; LOOP IF(EXTRACT(isodow FROM v_day)::integer = 1) AND (rec.include_mon = true) THEN ans := ans + 1; END IF; IF(EXTRACT(isodow FROM v_day)::integer = 2) AND (rec.include_tue = true) THEN ans := ans + 1; END IF; IF(EXTRACT(isodow FROM v_day)::integer = 3) AND (rec.include_wed = true) THEN ans := ans + 1; END IF; IF(EXTRACT(isodow FROM v_day)::integer = 4) AND (rec.include_thu = true) THEN ans := ans + 1; END IF; IF(EXTRACT(isodow FROM v_day)::integer = 5) AND (rec.include_fri = true) THEN ans := ans + 1; END IF; IF(EXTRACT(isodow FROM v_day)::integer = 6) AND (rec.include_sat = true) THEN ans := ans + 1; END IF; IF(EXTRACT(isodow FROM v_day)::integer = 7) AND (rec.include_sun = true) THEN ans := ans + 1; END IF; IF(rec.include_holiday = false)THEN SELECT count(holiday_id) INTO v_holiday FROM holidays WHERE (holiday_date = v_day); v_holidays := v_holidays + v_holiday; END IF; v_day := v_day + 1; EXIT WHEN $2 < v_day; END LOOP; ans := ans - CAST(v_holidays as real); return ans; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION add_shortlist(varchar(12), varchar(12), varchar(12), varchar(12)) RETURNS varchar(120) AS $$ DECLARE msg varchar(120); BEGIN IF ($3 = '1') THEN UPDATE applications SET short_listed = 1 WHERE application_id = CAST($1 as int); msg := 'Added to short list'; ELSIF ($3 = '2') THEN UPDATE applications SET short_listed = 0 WHERE application_id = CAST($1 as int); msg := 'Removed from short list'; END IF; return msg; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION add_employee(varchar(12), varchar(12), varchar(12)) RETURNS varchar(120) AS $$ DECLARE v_application_id integer; v_entity_id integer; v_employee_id integer; msg varchar(120); BEGIN v_application_id := CAST($1 as int); SELECT employees.entity_id, applications.employee_id INTO v_entity_id, v_employee_id FROM applications LEFT JOIN employees ON applications.entity_id = employees.entity_id WHERE (application_id = v_application_id); IF(v_employee_id is null) AND (v_entity_id is null)THEN INSERT INTO employees (org_id, currency_id, bank_branch_id, department_role_id, pay_scale_id, pay_group_id, location_id, person_title, surname, first_name, middle_name, date_of_birth, gender, nationality, marital_status, picture_file, identity_card, language, interests, objective, contract, appointment_date, current_appointment, contract_period, basic_salary) SELECT orgs.org_id, orgs.currency_id, 0, intake.department_role_id, intake.pay_scale_id, intake.pay_group_id, intake.location_id, applicants.person_title, applicants.surname, applicants.first_name, applicants.middle_name, applicants.date_of_birth, applicants.gender, applicants.nationality, applicants.marital_status, applicants.picture_file, applicants.identity_card, applicants.language, applicants.interests, applicants.objective, intake.contract, applications.contract_date, applications.contract_start, applications.contract_period, applications.initial_salary FROM orgs INNER JOIN applicants ON orgs.org_id = applicants.org_id INNER JOIN applications ON applicants.entity_id = applications.entity_id INNER JOIN intake ON applications.intake_id = intake.intake_id WHERE (applications.application_id = v_application_id); UPDATE applications SET employee_id = currval('entitys_entity_id_seq'), approve_status = 'Completed' WHERE (application_id = v_application_id); msg := 'Employee added'; ELSIF(v_employee_id is null)THEN UPDATE applications SET employee_id = v_employee_id, department_role_id = intake.department_role_id, pay_scale_id = intake.pay_scale_id, pay_group_id = intake.pay_group_id, location_id = intake.location_id, approve_status = 'Completed' FROM intake WHERE (applications.intake_id = intake.intake_id) AND (applications.application_id = v_application_id); msg := 'Employee details updated'; ELSE msg := 'Employeed already added to the system'; END IF; return msg; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION get_approval_date(integer) RETURNS date AS $$ DECLARE v_workflow_table_id integer; v_date date; BEGIN v_workflow_table_id := $1; SELECT action_date INTO v_date FROM approvals WHERE (approvals.table_id = v_workflow_table_id) AND (approvals.workflow_phase_id = 6); return v_date; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION get_approver(integer) RETURNS varchar(120) AS $$ DECLARE v_workflow_table_id integer; v_approver varchar(120); BEGIN v_approver :=''; v_workflow_table_id := $1; SELECT entitys.entity_name INTO v_approver FROM entitys INNER JOIN approvals ON entitys.entity_id = approvals.app_entity_id WHERE (approvals.table_id = v_workflow_table_id) AND (approvals.workflow_phase_id = 6); return v_approver; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION insa_employee_objectives() RETURNS trigger AS $$ BEGIN INSERT INTO objectives (employee_objective_id, org_id, objective_type_id, date_set, objective_ps, objective_name, objective_maditory) VALUES (NEW.employee_objective_id, NEW.org_id, 1, current_date, 0, 'Community service', true); RETURN null; END; $$ LANGUAGE plpgsql; CREATE TRIGGER insa_employee_objectives AFTER INSERT ON employee_objectives FOR EACH ROW EXECUTE PROCEDURE insa_employee_objectives(); CREATE OR REPLACE FUNCTION ins_objectives() RETURNS trigger AS $$ DECLARE sum_objective_ps real; sum_ods_ps real; BEGIN SELECT sum(objective_ps) INTO sum_objective_ps FROM objectives WHERE (employee_objective_id = NEW.employee_objective_id); SELECT sum(ods_ps) INTO sum_ods_ps FROM objective_details WHERE (objective_id = NEW.objective_id) AND (ods_ps is not null); IF(sum_objective_ps > 100)THEN RAISE EXCEPTION 'Your % objectives are more than 100', '%'; END IF; IF(sum_ods_ps > NEW.objective_ps)THEN RAISE EXCEPTION 'The % objective details are more than the overall objective details', '%'; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER ins_objectives AFTER INSERT OR UPDATE ON objectives FOR EACH ROW EXECUTE PROCEDURE ins_objectives(); CREATE OR REPLACE FUNCTION ins_objective_details() RETURNS trigger AS $$ DECLARE v_objective_ps real; sum_ods_ps real; BEGIN IF(NEW.ln_objective_detail_id is not null)THEN SELECT objective_id INTO NEW.objective_id FROM objective_details WHERE objective_detail_id = NEW.ln_objective_detail_id; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER ins_objective_details BEFORE INSERT OR UPDATE ON objective_details FOR EACH ROW EXECUTE PROCEDURE ins_objective_details(); CREATE OR REPLACE FUNCTION upd_objective_details() RETURNS trigger AS $$ DECLARE v_objective_ps real; sum_ods_ps real; BEGIN SELECT objective_ps INTO v_objective_ps FROM objectives WHERE (objective_id = NEW.objective_id); SELECT sum(ods_ps) INTO sum_ods_ps FROM objective_details WHERE (objective_id = NEW.objective_id) AND (ods_ps is not null) AND (ln_objective_detail_id is null); IF(sum_ods_ps > v_objective_ps)THEN RAISE EXCEPTION 'The % objective details are more than the overall objective details', '%'; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER upd_objective_details AFTER INSERT OR UPDATE ON objective_details FOR EACH ROW EXECUTE PROCEDURE upd_objective_details(); CREATE OR REPLACE FUNCTION upd_reviews(varchar(12), varchar(12), varchar(12), varchar(12)) RETURNS varchar(120) AS $$ DECLARE v_approve_status varchar(16); v_eo_id integer; v_jr_id integer; v_ep_id integer; msg varchar(120); BEGIN IF($3 = '1') THEN UPDATE employee_objectives SET approve_status = 'Draft' WHERE employee_objective_id = CAST($1 as integer); msg := 'Objective Reviews opened'; ELSIF($3 = '2') THEN UPDATE job_reviews SET approve_status = 'Draft' WHERE job_review_id = CAST($1 as integer); msg := 'Perfomance Reviews opened'; ELSIF($3 = '3') THEN v_eo_id := CAST($1 as int); SELECT approve_status INTO v_approve_status FROM employee_objectives WHERE employee_objective_id = v_eo_id; SELECT evaluation_point_id INTO v_ep_id FROM evaluation_points INNER JOIN objectives ON evaluation_points.objective_id = objectives.objective_id WHERE (objectives.employee_objective_id = v_eo_id); IF(v_ep_id is not null)THEN msg := 'You need to delete the objectives linked to the review'; ELSIF(v_approve_status = 'Draft')THEN DELETE FROM objective_details WHERE objective_id IN (SELECT objective_id FROM objectives WHERE employee_objective_id = v_eo_id); DELETE FROM objectives WHERE employee_objective_id = v_eo_id; DELETE FROM employee_objectives WHERE employee_objective_id = v_eo_id; msg := 'Delete objective defination'; ELSE msg := 'You can only delete a draft objective defination.'; END IF; ELSIF($3 = '4') THEN v_jr_id := CAST($1 as int); SELECT approve_status INTO v_approve_status FROM job_reviews WHERE job_review_id = v_jr_id; IF(v_approve_status = 'Draft')THEN DELETE FROM evaluation_points WHERE job_review_id = v_jr_id; DELETE FROM job_reviews WHERE job_review_id = v_jr_id; msg := 'Delete job review'; ELSE msg := 'You can only delete a draft job review.'; END IF; END IF; return msg; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION getComputedReviewPoints(v_type integer, v_job_review_id integer) RETURNS double precision AS $$ DECLARE v_points double precision; BEGIN IF(v_type = 1) THEN SELECT SUM((vw_evaluation_objectives.objective_ps/100) * vw_evaluation_objectives.points) INTO v_points FROM job_reviews INNER JOIN vw_evaluation_objectives ON job_reviews.job_review_id = vw_evaluation_objectives.job_review_id WHERE (job_reviews.job_review_id =v_job_review_id) AND (EXTRACT(YEAR FROM vw_evaluation_objectives.date_set) = EXTRACT(YEAR FROM job_reviews.review_date)); ELSE SELECT SUM((vw_evaluation_objectives.objective_ps/100) * vw_evaluation_objectives.reviewer_points) INTO v_points FROM job_reviews INNER JOIN vw_evaluation_objectives ON job_reviews.job_review_id = vw_evaluation_objectives.job_review_id WHERE (job_reviews.job_review_id = v_job_review_id) AND (EXTRACT(YEAR FROM vw_evaluation_objectives.date_set) = EXTRACT(YEAR FROM job_reviews.review_date)); END IF; IF(v_points is null) THEN v_points := 0; END IF; RETURN v_points; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION emailed_dob(integer, varchar(64)) RETURNS varchar(120) AS $$ DECLARE v_org_id integer; v_entity_name varchar(120); BEGIN SELECT org_id, entity_name INTO v_org_id, v_entity_name FROM entitys WHERE (entity_id = $2::int); INSERT INTO sys_emailed (sys_email_id, org_id, email_type, narrative) VALUES (9, 0, 9, 'Its birthday for ' || v_entity_name); UPDATE employees SET dob_email = current_date WHERE (entity_id = $2::int); RETURN 'Done'; END; $$ LANGUAGE plpgsql; CREATE FUNCTION emailed_contract(integer, varchar(64)) RETURNS varchar(120) AS $$ BEGIN UPDATE applications SET notice_email = true WHERE (application_id = $2::int); RETURN 'Done'; END; $$ LANGUAGE plpgsql;