Skip to content

Instantly share code, notes, and snippets.

@joshwiens

joshwiens/pgrestore_dw_errors.sh

Last active July 16, 2018 05:46
Show Gist options
  • Save joshwiens/81d064c0c3645d9afb68643b5beb3bc4 to your computer and use it in GitHub Desktop.
Save joshwiens/81d064c0c3645d9afb68643b5beb3bc4 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
pgrestore_dw_errors.sh
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 10063; 0 0 COMMENT EXTENSION plpgsql
pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of extension plpgsql
Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
pg_restore: [archiver (db)] Error from TOC entry 1; 3079 17188 EXTENSION plv8
pg_restore: [archiver (db)] could not execute query: ERROR: could not open extension control file "/share/extension/plv8.control": No such file or directory
Command was: CREATE EXTENSION IF NOT EXISTS plv8 WITH SCHEMA pg_catalog;
pg_restore: [archiver (db)] Error from TOC entry 10064; 0 0 COMMENT EXTENSION plv8
pg_restore: [archiver (db)] could not execute query: ERROR: extension "plv8" does not exist
Command was: COMMENT ON EXTENSION plv8 IS 'PL/JavaScript (v8) trusted procedural language';
pg_restore: [archiver (db)] Error from TOC entry 559; 1255 18115 FUNCTION p_get_metric_by_sequence_json(jsonb, integer) dw_db_admin
pg_restore: [archiver (db)] could not execute query: ERROR: language "plv8" does not exist
Command was: CREATE FUNCTION public.p_get_metric_by_sequence_json(i_metrics jsonb, i_metric_sequence integer) RETURNS jsonb
LANGUAGE plv8
AS $$
for (var i in i_metrics) {
if (i_metrics[i].metric_sequence == i_metric_sequence) {
return i_metrics[i];
}
}
return null;
$$;
pg_restore: [archiver (db)] could not execute query: ERROR: function public.p_get_metric_by_sequence_json(jsonb, integer) does not exist
Command was: ALTER FUNCTION public.p_get_metric_by_sequence_json(i_metrics jsonb, i_metric_sequence integer) OWNER TO dw_db_admin;
pg_restore: [archiver (db)] Error from TOC entry 549; 1255 17203 FUNCTION wavg_accum(double precision[], double precision, double precision) dw_db_admin
pg_restore: [archiver (db)] could not execute query: ERROR: language "plv8" does not exist
Command was: CREATE FUNCTION public.wavg_accum(state double precision[], val double precision, weight double precision) RETURNS double precision[]
LANGUAGE plv8
AS $$
if ( val === null || weight === null ) {
return state;
}
state[0] = state[0] + weight;
state[1] = state[1] + ( val * weight );
return state;
$$;
pg_restore: [archiver (db)] could not execute query: ERROR: function public.wavg_accum(double precision[], double precision, double precision) does not exist
Command was: ALTER FUNCTION public.wavg_accum(state double precision[], val double precision, weight double precision) OWNER TO dw_db_admin;
pg_restore: [archiver (db)] Error from TOC entry 2278; 1255 17535 AGGREGATE els_wavg(double precision, double precision) dw_db_admin
pg_restore: [archiver (db)] could not execute query: ERROR: function public.wavg_accum(double precision[], double precision, double precision) does not exist
Command was: CREATE AGGREGATE public.els_wavg(double precision, double precision) (
SFUNC = public.wavg_accum,
STYPE = double precision[],
INITCOND = '{0,0}',
FINALFUNC = public.float8_wavg
);
pg_restore: [archiver (db)] could not execute query: ERROR: aggregate public.els_wavg(double precision, double precision) does not exist
Command was: ALTER AGGREGATE public.els_wavg(double precision, double precision) OWNER TO dw_db_admin;
pg_restore: [archiver (db)] Error from TOC entry 2277; 1255 17205 AGGREGATE wavg(double precision, double precision) dw_db_admin
pg_restore: [archiver (db)] could not execute query: ERROR: function public.wavg_accum(double precision[], double precision, double precision) does not exist
Command was: CREATE AGGREGATE public.wavg(double precision, double precision) (
SFUNC = public.wavg_accum,
STYPE = double precision[],
INITCOND = '{0,0}',
FINALFUNC = public.float8_wavg
);
pg_restore: [archiver (db)] could not execute query: ERROR: aggregate public.wavg(double precision, double precision) does not exist
Command was: ALTER AGGREGATE public.wavg(double precision, double precision) OWNER TO dw_db_admin;
pg_restore: [archiver (db)] Error from TOC entry 530; 1259 51578 VIEW dv_els_detail dw_db_admin
pg_restore: [archiver (db)] could not execute query: ERROR: function public.wavg(double precision, double precision) does not exist
LINE 28: round((public.wavg((i.els)::double precision, (i.els_hou...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
Command was: CREATE VIEW public.dv_els_detail AS
SELECT i.client_id,
i.client_name,
i.facility_id,
i.facility_name,
s.shift_id,
s.shift_name,
d.department_id,
d.department_name,
c.category_id,
c.category_name,
i.item_date,
i.employee_id,
i.employee_type,
i.employee_code,
i.last_name,
i.first_name,
i.supervisor_id,
i.supervisor_name,
round(sum(i.direct_hours), 4) AS direct_hours,
round(sum(i.els_hours), 4) AS els_hours,
round(sum(i.indirect_hours), 4) AS indirect_hours,
round(sum(i.billable_hours), 4) AS billable_hours,
round(sum(i.direct_missing_hours), 4) AS direct_missing_hours,
round(sum(i.indirect_missing_hours), 4) AS indirect_missing_hours,
round(sum((COALESCE(i.billable_hours, (0)::numeric) + COALESCE(i.direct_hours, (0)::numeric))), 4) AS productive_hours,
round(sum(((i.els * i.els_hours) / (100)::numeric)), 4) AS earned_hours,
round((public.wavg((i.els)::double precision, (i.els_hours)::double precision))::numeric, 4) AS els
FROM (((( SELECT c_1.client_id,
c_1.client_name,
f.facility_id,
f.facility_name,
i_1.shift_dim_id,
i_1.department_dim_id,
i_1.category_dim_id,
i_1.item_date,
sum(
CASE
WHEN (NOT i_1.ismissing_time) THEN i_1.direct_hours
ELSE (0)::numeric
END) AS direct_hours,
i_1.els_hours,
i_1.els,
sum(
CASE
WHEN (NOT i_1.ismissing_time) THEN i_1.indirect_hours
ELSE (0)::numeric
END) AS indirect_hours,
sum(i_1.billable_hours) AS billable_hours,
sum(
CASE
WHEN i_1.ismissing_time THEN i_1.direct_hours
ELSE (0)::numeric
END) AS direct_missing_hours,
sum(
CASE
WHEN i_1.ismissing_time THEN i_1.indirect_hours
ELSE (0)::numeric
END) AS indirect_missing_hours,
sup.employee_id AS supervisor_id,
(((sup.last_name)::text || ', '::text) || (sup.first_name)::text) AS supervisor_name,
e.employee_id,
e.last_name,
e.first_name,
e.employee_code,
CASE
WHEN ((e.employee_type)::text = 'F'::text) THEN 'Full Time'::text
WHEN ((e.employee_type)::text = 'T'::text) THEN 'Temp'::text
WHEN ((e.employee_type)::text = 'C'::text) THEN 'Contract'::text
WHEN ((e.employee_type)::text = 'D'::text) THEN 'Day Labor'::text
ELSE NULL::text
END AS employee_type,
sum(i_1.indirect_cost) AS indirect_cost,
sum(i_1.indirect_missing_cost) AS indirect_missing_cost
FROM ((((( SELECT item_cost_fact.item_date,
item_cost_fact.client_dim_id,
item_cost_fact.facility_dim_id,
item_cost_fact.department_dim_id,
item_cost_fact.process_dim_id,
item_cost_fact.category_dim_id,
item_cost_fact.shift_dim_id,
item_cost_fact.employee_dim_id,
item_cost_fact.supervisor_dim_id,
item_cost_fact.ismissing_time,
item_cost_fact.els,
item_cost_fact.els_hours,
item_cost_fact.direct_hours,
item_cost_fact.indirect_hours,
item_cost_fact.billable_hours,
CASE
WHEN (item_cost_fact.ismissing_time = false) THEN ((COALESCE(item_cost_fact.indirect_base_pay, (0)::numeric) + COALESCE(item_cost_fact.indirect_bonus_pay, (0)::numeric)) + COALESCE(item_cost_fact.indirect_overtime_pay, (0)::numeric))
ELSE (0)::numeric
END AS indirect_cost,
CASE
WHEN (item_cost_fact.ismissing_time = true) THEN ((COALESCE(item_cost_fact.indirect_base_pay, (0)::numeric) + COALESCE(item_cost_fact.indirect_bonus_pay, (0)::numeric)) + COALESCE(item_cost_fact.indirect_overtime_pay, (0)::numeric))
ELSE (0)::numeric
END AS indirect_missing_cost
FROM public.item_cost_fact) i_1
LEFT JOIN public.employee_dim e ON ((e.employee_dim_id = i_1.employee_dim_id)))
LEFT JOIN public.supervisor_dim sup ON ((sup.supervisor_dim_id = i_1.supervisor_dim_id)))
LEFT JOIN public.client_dim c_1 ON ((c_1.client_dim_id = i_1.client_dim_id)))
LEFT JOIN public.facility_dim f ON ((f.facility_dim_id = i_1.facility_dim_id)))
GROUP BY c_1.client_id, c_1.client_name, f.facility_id, f.facility_name, i_1.shift_dim_id, i_1.department_dim_id, i_1.category_dim_id, i_1.item_date, i_1.els, i_1.els_hours, sup.employee_id,sup.first_name, sup.last_name, e.employee_id, e.last_name, e.first_name, e.employee_code, e.employee_type) i
LEFT JOIN public.shift_dim s ON ((s.shift_dim_id = i.shift_dim_id)))
LEFT JOIN public.department_dim d ON ((d.department_dim_id = i.department_dim_id)))
LEFT JOIN public.category_dim c ON ((c.category_dim_id = i.category_dim_id)))
GROUP BY i.client_id, i.client_name, i.facility_id, i.facility_name, s.shift_id, s.shift_name, d.department_id, d.department_name, c.category_id, c.category_name, i.item_date, i.employee_id, i.employee_type, i.employee_code, i.last_name, i.first_name, i.supervisor_id, i.supervisor_name;
pg_restore: [archiver (db)] could not execute query: ERROR: relation "public.dv_els_detail" does not exist
Command was: ALTER TABLE public.dv_els_detail OWNER TO dw_db_admin;
pg_restore: [archiver (db)] Error from TOC entry 371; 1259 28374 VIEW dv_employee_day_hours dw_db_admin
pg_restore: [archiver (db)] could not execute query: ERROR: function public.wavg(double precision, double precision) does not exist
LINE 28: round((public.wavg((i.els)::double precision, (i.els_hou...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
Command was: CREATE VIEW public.dv_employee_day_hours AS
SELECT i.client_id,
i.client_name,
i.facility_id,
i.facility_name,
s.shift_id,
s.shift_name,
i.item_date,
i.employee_id,
i.employee_type,
i.employee_code,
i.last_name,
i.first_name,
i.supervisor_id,
i.supervisor_name,
round(max(i.daily_hours), 4) AS daily_hours,
round(max(i.timecard_hours), 4) AS timecard_hours,
round((sum(COALESCE(i.project_hours, (0)::numeric)) + max(COALESCE(i.missing_hours, (0)::numeric))), 4) AS total_hours,
round(sum(i.direct_hours), 4) AS direct_hours,
round(sum(i.els_hours), 4) AS els_hours,
round(sum(i.indirect_hours), 4) AS indirect_hours,
round(sum(i.billable_hours), 4) AS billable_hours,
round(sum(i.direct_missing_hours), 4) AS direct_missing_hours,
round(sum(i.indirect_missing_hours), 4) AS indirect_missing_hours,
round(max(COALESCE(i.missing_hours, (0)::numeric)), 4) AS missing_hours,
round(sum((COALESCE(i.billable_hours, (0)::numeric) + COALESCE(i.direct_hours, (0)::numeric))), 4) AS productive_hours,
round(sum(((i.els * i.els_hours) / (100)::numeric)), 4) AS earned_hours,
round((public.wavg((i.els)::double precision, (i.els_hours)::double precision))::numeric, 4) AS els,
max(i.period_els) AS period_els,
max(i.lifetime_els) AS lifetime_els,
round(avg(i.qls), 4) AS qls,
CASE
WHEN (max(i.daily_hours) > (0)::numeric) THEN round(((sum((COALESCE(i.billable_hours, (0)::numeric) + COALESCE(i.direct_hours, (0)::numeric))) / max(i.daily_hours)) * (100)::numeric), 4)
ELSE NULL::numeric
END AS utilization,
CASE
WHEN (max(i.daily_hours) > (0)::numeric) THEN round((((sum(((i.els * i.els_hours) / (100)::numeric)) + COALESCE(sum(i.billable_hours), (0)::numeric)) / max(i.daily_hours)) * (100)::numeric), 4)
ELSE NULL::numeric
END AS effectiveness,
sum(i.regular_hours) AS regular_hours,
sum(i.daily_overtime) AS overtime_hours,
max(i.hourly_wage) AS hourly_wage,
sum(i.base_pay) AS base_pay,
sum(i.bonus_pay) AS bonus_pay,
sum(i.overtime_pay) AS overtime_pay,
round(sum(i.indirect_cost), 4) AS indirect_cost,
round(sum(i.indirect_missing_cost), 4) AS indirect_missing_cost,
round(sum(i.missing_hours_cost), 4) AS missing_hours_cost,
round(sum(i.overtime_cost), 4) AS overtime_cost
FROM (( SELECT c.client_id,
c.client_name,
f.facility_id,
f.facility_name,
i_1.shift_dim_id,
i_1.item_date,
ed.daily_hours,
ed.timecard_hours,
ed.project_hours,
sum(
CASE
WHEN (NOT i_1.ismissing_time) THEN i_1.direct_hours
ELSE (0)::numeric
END) AS direct_hours,
ed.day_els_hours AS els_hours,
sum(
CASE
WHEN (NOT i_1.ismissing_time) THEN i_1.indirect_hours
ELSE (0)::numeric
END) AS indirect_hours,
sum(i_1.billable_hours) AS billable_hours,
sum(
CASE
WHEN i_1.ismissing_time THEN i_1.direct_hours
ELSE (0)::numeric
END) AS direct_missing_hours,
sum(
CASE
WHEN i_1.ismissing_time THEN i_1.indirect_hours
ELSE (0)::numeric
END) AS indirect_missing_hours,
ed.missing_hours,
ed.regular_hours,
ed.daily_overtime,
sup.employee_id AS supervisor_id,
(((sup.last_name)::text || ', '::text) || (sup.first_name)::text) AS supervisor_name,
e.employee_id,
e.last_name,
e.first_name,
e.employee_code,
CASE
WHEN ((e.employee_type)::text = 'F'::text) THEN 'Full Time'::text
WHEN ((e.employee_type)::text = 'T'::text) THEN 'Temp'::text
WHEN ((e.employee_type)::text = 'C'::text) THEN 'Contract'::text
WHEN ((e.employee_type)::text = 'D'::text) THEN 'Day Labor'::text
ELSE NULL::text
END AS employee_type,
ed.day_els AS els,
ed.period_els,
ed.lifetime_els,
ed.day_qls AS qls,
ed.hourly_wage,
ed.base_pay,
ed.bonus_pay,
ed.overtime_pay,
ed.missing_hours_cost,
ed.overtime_cost,
sum(i_1.indirect_cost) AS indirect_cost,
sum(i_1.indirect_missing_cost) AS indirect_missing_cost
FROM (((((( SELECT ic.item_date,
ic.client_dim_id,
ic.facility_dim_id,
ic.department_dim_id,
ic.process_dim_id,
ic.category_dim_id,
ic.shift_dim_id,
ic.employee_dim_id,
ic.supervisor_dim_id,
ic.isdirect,
ic.isindirect,
ic.isbillable,
ic.ismissing_time,
ic.els,
ic.els_hours,
ic.direct_hours,
ic.indirect_hours,
ic.billable_hours,
CASE
WHEN (ic.ismissing_time = false) THEN ((COALESCE(ic.indirect_base_pay, (0)::numeric) + COALESCE(ic.indirect_bonus_pay, (0)::numeric)) + COALESCE(ic.indirect_overtime_pay, (0)::numeric))
ELSE (0)::numeric
END AS indirect_cost,
CASE
WHEN (ic.ismissing_time = true) THEN ((COALESCE(ic.indirect_base_pay, (0)::numeric) + COALESCE(ic.indirect_bonus_pay, (0)::numeric)) + COALESCE(ic.indirect_overtime_pay, (0)::numeric))
ELSE (0)::numeric
END AS indirect_missing_cost
FROM public.item_cost_fact ic) i_1
LEFT JOIN public.employee_day_fact ed ON (((ed.employee_dim_id = i_1.employee_dim_id) AND (ed.work_date = i_1.item_date))))
LEFT JOIN public.employee_dim e ON ((e.employee_dim_id = ed.employee_dim_id)))
LEFT JOIN public.supervisor_dim sup ON ((sup.supervisor_dim_id = ed.supervisor_dim_id)))
LEFT JOIN public.client_dim c ON ((c.client_dim_id = i_1.client_dim_id)))
LEFT JOIN public.facility_dim f ON ((f.facility_dim_id = i_1.facility_dim_id)))
GROUP BY c.client_id, c.client_name, f.facility_id, f.facility_name, i_1.shift_dim_id, i_1.item_date, ed.daily_hours, ed.project_hours, ed.timecard_hours, ed.missing_hours, ed.pos_missing_hours, ed.neg_missing_hours, ed.day_els_hours, ed.day_els, ed.day_qls, ed.period_els, ed.lifetime_els, ed.regular_hours, ed.daily_overtime, ed.hourly_wage, ed.base_pay, ed.bonus_pay, ed.overtime_pay, ed.missing_hours_cost, ed.overtime_cost, sup.employee_id, sup.first_name, sup.last_name, e.employee_id, e.last_name, e.first_name, e.employee_code, e.employee_type) i
LEFT JOIN public.shift_dim s ON ((s.shift_dim_id = i.shift_dim_id)))
GROUP BY i.client_id, i.client_name, i.facility_id, i.facility_name, s.shift_id, s.shift_name, i.item_date, i.employee_id, i.employee_type, i.employee_code, i.last_name, i.first_name, i.supervisor_id, i.supervisor_name;
pg_restore: [archiver (db)] could not execute query: ERROR: relation "public.dv_employee_day_hours" does not exist
Command was: ALTER TABLE public.dv_employee_day_hours OWNER TO dw_db_admin;
pg_restore: [archiver (db)] Error from TOC entry 10062; 0 0 ACL SCHEMA public dw_db_admin
pg_restore: [archiver (db)] could not execute query: ERROR: role "rdsadmin" does not exist
Command was: REVOKE ALL ON SCHEMA public FROM rdsadmin;
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT ALL ON SCHEMA public TO dw_db_admin;
GRANT ALL ON SCHEMA public TO PUBLIC;
GRANT USAGE ON SCHEMA public TO reports;
pg_restore: [archiver (db)] Error from TOC entry 10071; 0 0 ACL FUNCTION p_get_metric_by_sequence_json(i_metrics jsonb, i_metric_sequence integer) dw_db_admin
pg_restore: [archiver (db)] could not execute query: ERROR: function public.p_get_metric_by_sequence_json(jsonb, integer) does not exist
Command was: GRANT ALL ON FUNCTION public.p_get_metric_by_sequence_json(i_metrics jsonb, i_metric_sequence integer) TO reports;
pg_restore: [archiver (db)] Error from TOC entry 10085; 0 0 ACL FUNCTION wavg_accum(state double precision[], val double precision, weight double precision) dw_db_admin
pg_restore: [archiver (db)] could not execute query: ERROR: function public.wavg_accum(double precision[], double precision, double precision) does not exist
Command was: GRANT ALL ON FUNCTION public.wavg_accum(state double precision[], val double precision, weight double precision) TO reports;
pg_restore: [archiver (db)] Error from TOC entry 10087; 0 0 ACL FUNCTION els_wavg(double precision, double precision) dw_db_admin
pg_restore: [archiver (db)] could not execute query: ERROR: function public.els_wavg(double precision, double precision) does not exist
Command was: GRANT ALL ON FUNCTION public.els_wavg(double precision, double precision) TO reports;
pg_restore: [archiver (db)] Error from TOC entry 10090; 0 0 ACL FUNCTION wavg(double precision, double precision) dw_db_admin
pg_restore: [archiver (db)] could not execute query: ERROR: function public.wavg(double precision, double precision) does not exist
Command was: GRANT ALL ON FUNCTION public.wavg(double precision, double precision) TO reports;
pg_restore: [archiver (db)] Error from TOC entry 10104; 0 0 ACL TABLE dv_els_detail dw_db_admin
pg_restore: [archiver (db)] could not execute query: ERROR: relation "public.dv_els_detail" does not exist
Command was: GRANT SELECT ON TABLE public.dv_els_detail TO dw_read_only;
GRANT SELECT ON TABLE public.dv_els_detail TO reports;
pg_restore: [archiver (db)] Error from TOC entry 10106; 0 0 ACL TABLE dv_employee_day_hours dw_db_admin
pg_restore: [archiver (db)] could not execute query: ERROR: relation "public.dv_employee_day_hours" does not exist
Command was: GRANT SELECT ON TABLE public.dv_employee_day_hours TO dw_read_only;
GRANT SELECT ON TABLE public.dv_employee_day_hours TO reports;
Raw
pgrestore_em_errors.sh
pg_restore: [archiver (db)] could not execute query: ERROR: function public.p_calculate_linear_interpolation(json, numeric) does not exist
Command was: ALTER FUNCTION public.p_calculate_linear_interpolation(i_config json, i_x numeric) OWNER TO lp;
pg_restore: [archiver (db)] Error from TOC entry 2894; 1255 35777691 AGGREGATE els_wavg(double precision, double precision) lp
pg_restore: [archiver (db)] could not execute query: ERROR: function public.els_wavg_accum(double precision[], double precision, double precision) does not exist
Command was: CREATE AGGREGATE public.els_wavg(els double precision, hours double precision) (
SFUNC = public.els_wavg_accum,
STYPE = double precision[],
INITCOND = '{0,0}',
FINALFUNC = public.float8_wavg
);
pg_restore: [archiver (db)] could not execute query: ERROR: aggregate public.els_wavg(double precision, double precision) does not exist
Command was: ALTER AGGREGATE public.els_wavg(els double precision, hours double precision) OWNER TO lp;
pg_restore: [archiver (db)] Error from TOC entry 665; 1259 160223042 VIEW dv_dept_day_hours lp
pg_restore: [archiver (db)] could not execute query: ERROR: function public.els_wavg(double precision, double precision) does not exist
LINE 27: public.els_wavg((ie.els_value)::double precision...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
Command was: CREATE VIEW public.dv_dept_day_hours AS
SELECT i.client_id,
c.name AS client_name,
i.facility_id,
i.facility_name,
i.department_id,
i.department_name,
i.item_date,
round(sum(i.direct_hours), 4) AS direct_hours,
round(sum(i.indirect_hours), 4) AS indirect_hours,
round(sum(i.billable_hours), 2) AS billable_hours,
round(sum(i.els_hours), 4) AS els_hours,
round((public.els_wavg(i.els, (i.els_hours)::double precision))::numeric, 4) AS els,
round(sum(i.direct_missing_hours), 4) AS direct_missing_hours,
round(sum(i.indirect_missing_hours), 4) AS indirect_missing_hours
FROM (( SELECT i_1.client_id,
i_1.facility_id,
i_1.facility_name,
i_1.department_id,
i_1.department_name,
i_1.item_date,
sum(i_1.total_hours) AS total_hours,
sum(i_1.direct_hours) AS direct_hours,
sum(i_1.indirect_hours) AS indirect_hours,
sum(i_1.billable_hours) AS billable_hours,
sum(i_1.els_hours) AS els_hours,
public.els_wavg((ie.els_value)::double precision, (i_1.els_hours)::double precision) AS els,
sum(i_1.direct_missing_hours) AS direct_missing_hours,
sum(i_1.indirect_missing_hours) AS indirect_missing_hours
FROM ((( SELECT i_2.client_id,
i_2.facility_id,
f.facility_name,
sum(
CASE
WHEN (NOT i_2.isindirect) THEN i_2.hours
ELSE NULL::numeric
END) AS item_direct_hours,
sum(i_2.hours) AS total_hours,
i_2.missing_time_factor,
sum(
CASE
WHEN (i_2.isdirect AND (NOT i_2.ismissing_time)) THEN i_2.hours
ELSE (0)::numeric
END) AS direct_hours,
sum(
CASE
WHEN (i_2.isindirect AND (NOT i_2.ismissing_time)) THEN i_2.hours
ELSE (0)::numeric
END) AS indirect_hours,
sum(
CASE
WHEN (i_2.isbillable AND (NOT i_2.ismissing_time)) THEN i_2.hours
ELSE (0)::numeric
END) AS billable_hours,
sum(
CASE
WHEN ((NOT i_2.isindirect) AND i_2.ismissing_time) THEN i_2.hours
ELSE (0)::numeric
END) AS direct_missing_hours,
sum(
CASE
WHEN ((i_2.isindirect AND (NOT i_2.isbillable)) AND i_2.ismissing_time) THEN i_2.hours
ELSE (0)::numeric
END) AS indirect_missing_hours,
sum(
CASE
WHEN ((NOT i_2.isindirect) AND (i_2.hours > (0)::numeric)) THEN i_2.hours
ELSE (0)::numeric
END) AS els_hours,
i_2.isindirect,
i_2.item_date,
i_2.item_id,
i_2.department_id,
d.department_name
FROM ((( SELECT i_3.client_id,
i_3.facility_id,
i_3.department_id,
i_3.item_date,
i_3.item_id,
(COALESCE(t.hours, (ts.effective_hours)::double precision, (0)::double precision))::numeric AS hours,
eh.missing_time_factor,
p.ismissing_time,
(i_3.isindirect AND ii.isbillable) AS isbillable,
(i_3.isindirect AND (NOT ii.isbillable)) AS isindirect,
((NOT i_3.isindirect) AND (NOT (d_1.laborstandard_id = 5))) AS isdirect
FROM (((((((((public.item i_3
JOIN public.item_summary ii ON ((((ii.client_id = i_3.client_id) AND (ii.item_id = i_3.item_id)) AND ii.isactive)))
JOIN public.facility_period pp ON ((pp.facility_period_id = ii.facility_period_id)))
JOIN public.facility_archive f_1 ON ((f_1.facility_archive_id = ii.facility_archive_id)))
JOIN public.department_archive d_1 ON ((d_1.department_archive_id = ii.department_archive_id)))
JOIN public.item_employee t ON (((t.client_id = i_3.client_id) AND (t.item_id = i_3.item_id))))
LEFT JOIN public.item_employee_summary ts ON (((ts.item_id = i_3.item_id) AND (ts.employee_id = t.employee_id))))
JOIN public.employee_hours eh ON ((((eh.facility_id = i_3.facility_id) AND (eh.employee_id = t.employee_id)) AND (eh.item_date = i_3.item_date))))
JOIN public.employee_archive e ON (((e.employee_id = t.employee_id) AND (e.employee_archive_id = eh.employee_archive_id))))
LEFT JOIN public.process_archive p ON ((p.process_archive_id = ii.process_archive_id)))
WHERE i_3.isactive) i_2
LEFT JOIN public.facility_archive f ON ((((f.facility_id = i_2.facility_id) AND (f.effective_date = ( SELECT max(facility_archive.effective_date) AS max
FROM public.facility_archive
WHERE ((facility_archive.facility_id = f.facility_id) AND facility_archive.isarchive_active)))) AND f.isarchive_active)))
LEFT JOIN public.department_archive d ON ((((d.department_id = i_2.department_id) AND (d.effective_date = ( SELECT max(department_archive.effective_date) AS max
FROM public.department_archive
WHERE ((department_archive.department_id = i_2.department_id) AND department_archive.isarchive_active)))) AND d.isarchive_active)))
GROUP BY i_2.client_id, i_2.facility_id, f.facility_name, i_2.isindirect, i_2.item_date, i_2.item_id, i_2.department_id, d.department_name, i_2.missing_time_factor) i_1
LEFT JOIN public.item_els ie ON ((ie.item_id = i_1.item_id)))
LEFT JOIN public.facility_settings fs ON ((fs.facility_id = i_1.facility_id)))
GROUP BY i_1.client_id, i_1.facility_id, i_1.facility_name, i_1.item_date, i_1.department_id, i_1.department_name) i
JOIN public.client c ON ((c.client_id = i.client_id)))
GROUP BY i.client_id, c.name, i.facility_id, i.facility_name, i.department_id, i.department_name, i.item_date
ORDER BY i.item_date;
pg_restore: [archiver (db)] could not execute query: ERROR: relation "public.dv_dept_day_hours" does not exist
Command was: ALTER TABLE public.dv_dept_day_hours OWNER TO lp;
pg_restore: [archiver (db)] Error from TOC entry 666; 1259 160223047 VIEW dv_employee_day_hours lp
pg_restore: [archiver (db)] could not execute query: ERROR: function public.els_wavg(double precision, double precision) does not exist
LINE 109: public.els_wavg((
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
Command was: CREATE VIEW public.dv_employee_day_hours AS
SELECT i.client_id,
c.name AS client_name,
i.facility_id,
i.facility_name,
i.shift_id,
i.shift_name,
i.item_date,
i.employee_id,
i.employee_type,
i.employee_code,
i.last_name,
i.first_name,
i.supervisor_id,
i.supervisor_name,
round(max(i.daily_hours), 4) AS daily_hours,
round(max(i.timecard_hours), 4) AS timecard_hours,
round((sum(i.total_hours))::numeric, 4) AS total_hours,
round((sum(i.direct_hours))::numeric, 4) AS direct_hours,
round((sum(i.els_hours))::numeric, 4) AS els_hours,
round((sum(i.indirect_hours))::numeric, 4) AS indirect_hours,
round((sum(i.billable_hours))::numeric, 4) AS billable_hours,
round((sum(i.direct_missing_hours))::numeric, 4) AS direct_missing_hours,
round((sum(i.indirect_missing_hours))::numeric, 4) AS indirect_missing_hours,
round((max(i.pos_missing_hours))::numeric, 4) AS pos_missing_hours,
round((min(i.neg_missing_hours))::numeric, 4) AS neg_missing_hours,
round(max(COALESCE(i.missing_hours, (0)::numeric)), 4) AS missing_hours,
round((sum((COALESCE(i.billable_hours, (0)::double precision) + COALESCE(i.direct_hours, (0)::double precision))))::numeric, 4) AS productive_hours,
round((sum(((i.els * i.els_hours) / (100)::double precision)))::numeric, 4) AS earned_hours,
round((public.els_wavg(i.els, i.els_hours))::numeric, 4) AS els,
round(avg(q.qls_value), 4) AS qls,
CASE
WHEN (max(i.daily_hours) > (0)::numeric) THEN round((((sum((COALESCE(i.billable_hours, (0)::double precision) + COALESCE(i.direct_hours, (0)::double precision))) / (max(i.daily_hours))::double precision) * (100)::double precision))::numeric, 4)
ELSE NULL::numeric
END AS utilization,
CASE
WHEN (max(i.daily_hours) > (0)::numeric) THEN round(((((sum(((i.els * i.els_hours) / (100)::double precision)) + COALESCE(sum(i.billable_hours), (0)::double precision)) / (max(i.daily_hours))::double precision) * (100)::double precision))::numeric, 4)
ELSE NULL::numeric
END AS effectiveness
FROM (((( SELECT i_1.client_id,
i_1.facility_id,
i_1.facility_name,
i_1.shift_id,
i_1.shift_name,
i_1.item_date,
i_1.employee_id,
h.daily_hours,
h.timecard_hours,
h.missing_hours,
h.supervisor_employee_id AS supervisor_id,
((esp.last_name)::text || COALESCE((', '::text || (esp.first_name)::text), ''::text)) AS supervisor_name,
e.employee_code,
CASE
WHEN (e.employee_type = 'F'::bpchar) THEN 'Full Time'::text
WHEN (e.employee_type = 'T'::bpchar) THEN 'Temp'::text
WHEN (e.employee_type = 'C'::bpchar) THEN 'Contract'::text
WHEN (e.employee_type = 'D'::bpchar) THEN 'Day Labor'::text
ELSE NULL::text
END AS employee_type,
per.last_name,
per.first_name,
sum(i_1.hours) AS total_hours,
sum(
CASE
WHEN ((NOT i_1.isindirect) AND (NOT i_1.ismissing_time)) THEN i_1.hours
ELSE (0)::double precision
END) AS direct_hours,
sum(
CASE
WHEN ((i_1.isindirect AND (NOT i_1.isbillable)) AND (NOT i_1.ismissing_time)) THEN i_1.hours
ELSE (0)::double precision
END) AS indirect_hours,
sum(
CASE
WHEN (i_1.isindirect AND i_1.isbillable) THEN i_1.hours
ELSE (0)::double precision
END) AS billable_hours,
sum(
CASE
WHEN ((NOT i_1.isindirect) AND i_1.ismissing_time) THEN i_1.hours
ELSE (0)::double precision
END) AS direct_missing_hours,
sum(
CASE
WHEN ((i_1.isindirect AND (NOT i_1.isbillable)) AND i_1.ismissing_time) THEN i_1.hours
ELSE (0)::double precision
END) AS indirect_missing_hours,
CASE
WHEN (h.missing_hours > (0)::numeric) THEN (h.missing_hours)::double precision
ELSE sum(
CASE
WHEN (i_1.ismissing_time AND (i_1.hours > (0)::double precision)) THEN i_1.hours
ELSE (0)::double precision
END)
END AS pos_missing_hours,
CASE
WHEN (h.missing_hours < (0)::numeric) THEN (h.missing_hours)::double precision
ELSE sum(
CASE
WHEN (i_1.ismissing_time AND (i_1.hours < (0)::double precision)) THEN i_1.hours
ELSE (0)::double precision
END)
END AS neg_missing_hours,
sum(
CASE
WHEN ((NOT i_1.isindirect) AND (i_1.hours > (0)::double precision)) THEN i_1.hours
ELSE (0)::double precision
END) AS els_hours,
public.els_wavg((
CASE
WHEN (NOT i_1.isindirect) THEN (ie.els_value * COALESCE(h.missing_time_factor, 1.0))
ELSE NULL::numeric
END)::double precision,
CASE
WHEN ((NOT i_1.isindirect) AND (i_1.hours > (0)::double precision)) THEN i_1.hours
ELSE NULL::double precision
END) AS els
FROM ((((((( SELECT i_2.client_id,
i_2.facility_id,
i_2.shift_id,
i_2.process_id,
i_2.item_id,
i_2.item_date,
t.employee_id,
COALESCE((ts.effective_hours)::double precision, t.hours) AS hours,
i_2.isindirect,
ii.isbillable,
i_2.isautomatic_entry,
pa.ismissing_time,
pa.process_name,
fa.facility_name,
sa.shift_name
FROM ((((((public.item i_2
JOIN public.item_summary ii ON ((((ii.item_id = i_2.item_id) AND (ii.client_id = i_2.client_id)) AND ii.isactive)))
JOIN public.item_employee t ON ((((t.client_id = i_2.client_id) AND (t.item_id = i_2.item_id)) AND t.isactive)))
LEFT JOIN public.item_employee_summary ts ON (((ts.item_id = i_2.item_id) AND (ts.employee_id = t.employee_id))))
JOIN public.process_archive pa ON ((pa.process_archive_id = ii.process_archive_id)))
LEFT JOIN public.facility_archive fa ON ((fa.facility_archive_id = ii.facility_archive_id)))
LEFT JOIN public.shift_archive sa ON ((sa.shift_archive_id = ii.shift_archive_id)))
WHERE i_2.isactive) i_1
JOIN public.employee_hours h ON (((((h.client_id = i_1.client_id) AND (h.facility_id = i_1.facility_id)) AND (h.employee_id = i_1.employee_id)) AND (h.item_date = i_1.item_date))))
JOIN public.employee_archive e ON ((e.employee_archive_id = h.employee_archive_id)))
LEFT JOIN public.item_els ie ON ((ie.item_id = i_1.item_id)))
LEFT JOIN public.person per ON ((per.person_id = e.person_id)))
LEFT JOIN public.employee_archive ese ON ((((ese.employee_id = h.supervisor_employee_id) AND (ese.effective_date = ( SELECT max(employee_archive.effective_date) AS max
FROM public.employee_archive
WHERE ((employee_archive.employee_id = ese.employee_id) AND employee_archive.isarchive_active)))) AND ese.isarchive_active)))
LEFT JOIN public.person esp ON ((esp.person_id = ese.person_id)))
GROUP BY i_1.client_id, i_1.facility_id, i_1.facility_name, i_1.shift_id, i_1.shift_name, i_1.process_id, i_1.process_name, i_1.employee_id, h.missing_time_factor, h.daily_hours, h.timecard_hours, h.missing_hours, i_1.item_date, h.supervisor_employee_id, per.last_name, per.first_name, e.employee_code, e.employee_type, esp.last_name, esp.first_name) i
JOIN public.client c ON ((c.client_id = i.client_id)))
LEFT JOIN public.v_facility_default_period fpq ON (((((fpq.client_id = i.client_id) AND (fpq.facility_id = i.facility_id)) AND (fpq.period_type = 'QLS'::bpchar)) AND ((i.item_date >= fpq.period_start) AND (i.item_date <= fpq.period_end)))))
LEFT JOIN public.facility_period_qls q ON (((q.facility_period_id = fpq.facility_period_id) AND (q.employee_id = i.employee_id))))
GROUP BY i.client_id, c.name, i.facility_id, i.facility_name, i.shift_id, i.shift_name, i.item_date, i.employee_id, i.employee_type, i.employee_code, i.last_name, i.first_name, i.supervisor_id, i.supervisor_name;
pg_restore: [archiver (db)] could not execute query: ERROR: relation "public.dv_employee_day_hours" does not exist
Command was: ALTER TABLE public.dv_employee_day_hours OWNER TO lp;
pg_restore: [archiver (db)] Error from TOC entry 662; 1259 160223027 VIEW dv_facility_day_hours lp
pg_restore: [archiver (db)] could not execute query: ERROR: function public.els_wavg(double precision, double precision) does not exist
LINE 84: ... WHEN (NOT i_2.isindirect) THEN public.els...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
Command was: CREATE VIEW public.dv_facility_day_hours AS
SELECT i.client_id,
i.client_name,
i.facility_id,
i.facility_name,
i.item_date,
round((sum(COALESCE(i.els_hours, (0)::double precision)))::numeric, 4) AS els_hours,
round((sum(COALESCE(i.direct_hours, (0)::double precision)))::numeric, 4) AS direct_hours,
round((sum(COALESCE(i.indirect_hours, (0)::double precision)))::numeric, 4) AS indirect_hours,
round((sum(COALESCE(i.billable_hours, (0)::double precision)))::numeric, 4) AS billable_hours,
round((sum(COALESCE(i.earned_hours, (0)::double precision)))::numeric, 4) AS earned_hours,
round((sum((COALESCE(i.billable_hours, (0)::double precision) + COALESCE(i.direct_hours, (0)::double precision))))::numeric, 4) AS productive_hours,
round((public.els_wavg(i.els, i.els_hours))::numeric, 4) AS els,
CASE
WHEN (eh.daily_hours > (0)::numeric) THEN round((((sum((COALESCE(i.billable_hours, (0)::double precision) + COALESCE(i.direct_hours, (0)::double precision))) / (eh.daily_hours)::double precision) * (100)::double precision))::numeric, 4)
ELSE NULL::numeric
END AS utilization,
CASE
WHEN (eh.daily_hours > (0)::numeric) THEN round((((sum((COALESCE(i.earned_hours, (0)::double precision) + COALESCE(i.billable_hours, (0)::double precision))) / (eh.daily_hours)::double precision) * (100)::double precision))::numeric, 4)
ELSE NULL::numeric
END AS effectiveness,
round((sum(COALESCE(i.direct_missing_hours, (0)::double precision)))::numeric, 4) AS direct_missing_hours,
round((sum(COALESCE(i.indirect_missing_hours, (0)::double precision)))::numeric, 4) AS indirect_missing_hours,
round(eh.timecard_hours, 4) AS timecard_hours,
round(eh.daily_hours, 4) AS daily_hours,
round(eh.project_hours, 4) AS project_hours,
round(eh.missing_hours, 4) AS missing_hours
FROM (( SELECT i_1.client_id,
c.name AS client_name,
i_1.facility_id,
i_1.facility_name,
i_1.process_id,
i_1.process_name,
i_1.item_date,
i_1.employee_id,
sum(i_1.total_hours) AS total_hours,
sum(i_1.els_hours) AS els_hours,
sum(i_1.direct_hours) AS direct_hours,
sum(i_1.indirect_hours) AS indirect_hours,
sum(i_1.billable_hours) AS billable_hours,
sum(i_1.direct_missing_hours) AS direct_missing_hours,
sum(i_1.indirect_missing_hours) AS indirect_missing_hours,
sum(((i_1.els * i_1.els_hours) / (100)::double precision)) AS earned_hours,
public.els_wavg(i_1.els, i_1.els_hours) AS els
FROM (( SELECT i_2.client_id,
i_2.facility_id,
i_2.facility_name,
i_2.process_id,
i_2.process_name,
i_2.item_date,
i_2.employee_id,
sum(i_2.hours) AS total_hours,
sum(
CASE
WHEN ((NOT i_2.isindirect) AND (i_2.hours > (0)::double precision)) THEN i_2.hours
ELSE (0)::double precision
END) AS els_hours,
sum(
CASE
WHEN (((NOT i_2.isindirect) AND (NOT i_2.ismissing_time)) AND (i_2.hours > (0)::double precision)) THEN i_2.hours
ELSE (0)::double precision
END) AS direct_hours,
sum(
CASE
WHEN ((i_2.isindirect AND (NOT i_2.isbillable)) AND (NOT i_2.ismissing_time)) THEN i_2.hours
ELSE (0)::double precision
END) AS indirect_hours,
sum(
CASE
WHEN ((i_2.isindirect AND (NOT i_2.isbillable)) AND i_2.ismissing_time) THEN i_2.hours
ELSE (0)::double precision
END) AS indirect_missing_hours,
sum(
CASE
WHEN (i_2.isindirect AND i_2.isbillable) THEN i_2.hours
ELSE (0)::double precision
END) AS billable_hours,
sum(
CASE
WHEN (((NOT i_2.isindirect) AND i_2.ismissing_time) AND (i_2.hours > (0)::double precision)) THEN i_2.hours
ELSE (0)::double precision
END) AS direct_missing_hours,
CASE
WHEN (NOT i_2.isindirect) THEN public.els_wavg(((ie.els_value * COALESCE(h.missing_time_factor, 1.0)))::double precision,
CASE
WHEN ((NOT i_2.isindirect) AND (i_2.hours > (0)::double precision)) THEN i_2.hours
ELSE (0)::double precision
END)
ELSE NULL::double precision
END AS els
FROM (((( SELECT i_3.client_id,
i_3.facility_id,
i_3.process_id,
i_3.item_id,
i_3.item_date,
t.employee_id,
COALESCE((ts.effective_hours)::double precision, t.hours) AS hours,
i_3.isindirect,
ii.isbillable,
i_3.isautomatic_entry,
pa.ismissing_time,
pa.process_name,
fa.facility_name
FROM (((((public.item i_3
JOIN public.item_summary ii ON ((((ii.item_id = i_3.item_id) AND (ii.client_id = i_3.client_id)) AND ii.isactive)))
JOIN public.item_employee t ON ((((t.client_id = i_3.client_id) AND (t.item_id = i_3.item_id)) AND t.isactive)))
LEFT JOIN public.item_employee_summary ts ON (((ts.item_id = i_3.item_id) AND (ts.employee_id = t.employee_id))))
JOIN public.process_archive pa ON ((pa.process_archive_id = ii.process_archive_id)))
LEFT JOIN public.facility_archive fa ON ((fa.facility_archive_id = ii.facility_archive_id)))
WHERE i_3.isactive) i_2
JOIN public.employee_hours h ON (((((h.client_id = i_2.client_id) AND (h.facility_id = i_2.facility_id)) AND (h.employee_id = i_2.employee_id)) AND (h.item_date = i_2.item_date))))
JOIN public.employee_archive e ON ((e.employee_archive_id = h.employee_archive_id)))
LEFT JOIN public.item_els ie ON ((ie.item_id = i_2.item_id)))
GROUP BY i_2.client_id, i_2.facility_id, i_2.facility_name, i_2.isautomatic_entry, i_2.ismissing_time, i_2.process_id, i_2.process_name, i_2.isindirect, i_2.isbillable, i_2.employee_id, h.missing_time_factor, h.timecard_hours, i_2.item_date) i_1
JOIN public.client c ON ((c.client_id = i_1.client_id)))
GROUP BY i_1.client_id, c.name, i_1.facility_id, i_1.facility_name, i_1.process_id, i_1.process_name, i_1.employee_id, i_1.item_date) i
LEFT JOIN ( SELECT eh_1.client_id,
eh_1.facility_id,
eh_1.item_date,
sum(eh_1.timecard_hours) AS timecard_hours,
sum(eh_1.daily_hours) AS daily_hours,
sum(eh_1.project_hours) AS project_hours,
sum(eh_1.missing_hours) AS missing_hours
FROM public.employee_hours eh_1
WHERE (eh_1.daily_hours > (0)::numeric)
GROUP BY eh_1.client_id, eh_1.facility_id, eh_1.item_date) eh ON ((((i.client_id = eh.client_id) AND (i.facility_id = eh.facility_id)) AND (i.item_date = eh.item_date))))
GROUP BY i.client_id, i.client_name, i.facility_id, i.facility_name, i.item_date, eh.timecard_hours, eh.project_hours, eh.daily_hours, eh.missing_hours
ORDER BY i.item_date;
pg_restore: [archiver (db)] could not execute query: ERROR: relation "public.dv_facility_day_hours" does not exist
Command was: ALTER TABLE public.dv_facility_day_hours OWNER TO lp;
pg_restore: [archiver (db)] Error from TOC entry 663; 1259 160223032 VIEW dv_facility_day_hours_qls lp
pg_restore: [archiver (db)] could not execute query: ERROR: function public.els_wavg(double precision, double precision) does not exist
LINE 88: ... WHEN (NOT i_2.isindirect) THEN public.els...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
Command was: CREATE VIEW public.dv_facility_day_hours_qls AS
SELECT i.client_id,
i.client_name,
i.facility_id,
i.facility_name,
i.item_date,
round((sum(COALESCE(i.els_hours, (0)::double precision)))::numeric, 4) AS els_hours,
round((sum(COALESCE(i.direct_hours, (0)::double precision)))::numeric, 4) AS direct_hours,
round((sum(COALESCE(i.indirect_hours, (0)::double precision)))::numeric, 4) AS indirect_hours,
round((sum(COALESCE(i.billable_hours, (0)::double precision)))::numeric, 4) AS billable_hours,
round((sum(COALESCE(i.earned_hours, (0)::double precision)))::numeric, 4) AS earned_hours,
round((sum((COALESCE(i.billable_hours, (0)::double precision) + COALESCE(i.direct_hours, (0)::double precision))))::numeric, 4) AS productive_hours,
round((public.els_wavg(i.els, i.els_hours))::numeric, 4) AS els,
( SELECT round((avg(q.qls_value))::numeric, 4) AS round
FROM (public.qls q
JOIN public.qls_employee qt ON ((qt.qls_id = q.qls_id)))
WHERE (((q.client_id = i.client_id) AND (q.facility_id = i.facility_id)) AND (q.qls_date = i.item_date))) AS qls,
CASE
WHEN (eh.daily_hours > (0)::numeric) THEN round((((sum((COALESCE(i.billable_hours, (0)::double precision) + COALESCE(i.direct_hours, (0)::double precision))) / (eh.daily_hours)::double precision) * (100)::double precision))::numeric, 4)
ELSE NULL::numeric
END AS utilization,
CASE
WHEN (eh.daily_hours > (0)::numeric) THEN round((((sum((COALESCE(i.earned_hours, (0)::double precision) + COALESCE(i.billable_hours, (0)::double precision))) / (eh.daily_hours)::double precision) * (100)::double precision))::numeric, 4)
ELSE NULL::numeric
END AS effectiveness,
round((sum(COALESCE(i.direct_missing_hours, (0)::double precision)))::numeric, 4) AS direct_missing_hours,
round((sum(COALESCE(i.indirect_missing_hours, (0)::double precision)))::numeric, 4) AS indirect_missing_hours,
round(eh.timecard_hours, 4) AS timecard_hours,
round(eh.daily_hours, 4) AS daily_hours,
round(eh.project_hours, 4) AS project_hours,
round(eh.missing_hours, 4) AS missing_hours
FROM (( SELECT i_1.client_id,
c.name AS client_name,
i_1.facility_id,
i_1.facility_name,
i_1.process_id,
i_1.process_name,
i_1.item_date,
i_1.employee_id,
sum(i_1.total_hours) AS total_hours,
sum(i_1.els_hours) AS els_hours,
sum(i_1.direct_hours) AS direct_hours,
sum(i_1.indirect_hours) AS indirect_hours,
sum(i_1.billable_hours) AS billable_hours,
sum(i_1.direct_missing_hours) AS direct_missing_hours,
sum(i_1.indirect_missing_hours) AS indirect_missing_hours,
sum(((i_1.els * i_1.els_hours) / (100)::double precision)) AS earned_hours,
public.els_wavg(i_1.els, i_1.els_hours) AS els
FROM (( SELECT i_2.client_id,
i_2.facility_id,
i_2.facility_name,
i_2.process_id,
i_2.process_name,
i_2.item_date,
i_2.employee_id,
sum(i_2.hours) AS total_hours,
sum(
CASE
WHEN ((NOT i_2.isindirect) AND (i_2.hours > (0)::double precision)) THEN i_2.hours
ELSE (0)::double precision
END) AS els_hours,
sum(
CASE
WHEN (((NOT i_2.isindirect) AND (NOT i_2.ismissing_time)) AND (i_2.hours > (0)::double precision)) THEN i_2.hours
ELSE (0)::double precision
END) AS direct_hours,
sum(
CASE
WHEN ((i_2.isindirect AND (NOT i_2.isbillable)) AND (NOT i_2.ismissing_time)) THEN i_2.hours
ELSE (0)::double precision
END) AS indirect_hours,
sum(
CASE
WHEN ((i_2.isindirect AND (NOT i_2.isbillable)) AND i_2.ismissing_time) THEN i_2.hours
ELSE (0)::double precision
END) AS indirect_missing_hours,
sum(
CASE
WHEN (i_2.isindirect AND i_2.isbillable) THEN i_2.hours
ELSE (0)::double precision
END) AS billable_hours,
sum(
CASE
WHEN (((NOT i_2.isindirect) AND i_2.ismissing_time) AND (i_2.hours > (0)::double precision)) THEN i_2.hours
ELSE (0)::double precision
END) AS direct_missing_hours,
CASE
WHEN (NOT i_2.isindirect) THEN public.els_wavg(((ie.els_value * COALESCE(h.missing_time_factor, 1.0)))::double precision,
CASE
WHEN ((NOT i_2.isindirect) AND (i_2.hours > (0)::double precision)) THEN i_2.hours
ELSE (0)::double precision
END)
ELSE NULL::double precision
END AS els
FROM (((( SELECT i_3.client_id,
i_3.facility_id,
i_3.process_id,
i_3.item_id,
i_3.item_date,
t.employee_id,
COALESCE((ts.effective_hours)::double precision, t.hours) AS hours,
i_3.isindirect,
ii.isbillable,
i_3.isautomatic_entry,
pa.ismissing_time,
pa.process_name,
fa.facility_name
FROM (((((public.item i_3
JOIN public.item_summary ii ON ((((ii.item_id = i_3.item_id) AND (ii.client_id = i_3.client_id)) AND ii.isactive)))
JOIN public.item_employee t ON ((((t.client_id = i_3.client_id) AND (t.item_id = i_3.item_id)) AND t.isactive)))
LEFT JOIN public.item_employee_summary ts ON (((ts.item_id = i_3.item_id) AND (ts.employee_id = t.employee_id))))
JOIN public.process_archive pa ON ((pa.process_archive_id = ii.process_archive_id)))
LEFT JOIN public.facility_archive fa ON ((fa.facility_archive_id = ii.facility_archive_id)))
WHERE i_3.isactive) i_2
JOIN public.employee_hours h ON (((((h.client_id = i_2.client_id) AND (h.facility_id = i_2.facility_id)) AND (h.employee_id = i_2.employee_id)) AND (h.item_date = i_2.item_date))))
JOIN public.employee_archive e ON ((e.employee_archive_id = h.employee_archive_id)))
LEFT JOIN public.item_els ie ON ((ie.item_id = i_2.item_id)))
GROUP BY i_2.client_id, i_2.facility_id, i_2.facility_name, i_2.isautomatic_entry, i_2.ismissing_time, i_2.process_id, i_2.process_name, i_2.isindirect, i_2.isbillable, i_2.employee_id, h.missing_time_factor, h.timecard_hours, i_2.item_date) i_1
JOIN public.client c ON ((c.client_id = i_1.client_id)))
GROUP BY i_1.client_id, c.name, i_1.facility_id, i_1.facility_name, i_1.process_id, i_1.process_name, i_1.employee_id, i_1.item_date) i
LEFT JOIN ( SELECT eh_1.client_id,
eh_1.facility_id,
eh_1.item_date,
sum(eh_1.timecard_hours) AS timecard_hours,
sum(eh_1.daily_hours) AS daily_hours,
sum(eh_1.project_hours) AS project_hours,
sum(eh_1.missing_hours) AS missing_hours
FROM public.employee_hours eh_1
WHERE (eh_1.daily_hours > (0)::numeric)
GROUP BY eh_1.client_id, eh_1.facility_id, eh_1.item_date) eh ON ((((i.client_id = eh.client_id) AND (i.facility_id = eh.facility_id)) AND (i.item_date = eh.item_date))))
GROUP BY i.client_id, i.client_name, i.facility_id, i.facility_name, i.item_date, eh.timecard_hours, eh.project_hours, eh.daily_hours, eh.missing_hours
ORDER BY i.item_date;
pg_restore: [archiver (db)] could not execute query: ERROR: relation "public.dv_facility_day_hours_qls" does not exist
Command was: ALTER TABLE public.dv_facility_day_hours_qls OWNER TO lp;
pg_restore: [archiver (db)] Error from TOC entry 664; 1259 160223037 VIEW dv_shift_day_hours lp
pg_restore: [archiver (db)] could not execute query: ERROR: function public.els_wavg(double precision, double precision) does not exist
LINE 93: ... WHEN (NOT i_2.isindirect) THEN public.els...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
Command was: CREATE VIEW public.dv_shift_day_hours AS
SELECT i.client_id,
i.client_name,
i.facility_id,
i.facility_name,
i.shift_id,
i.shift_name,
i.item_date,
round(sum(i.daily_hours), 4) AS daily_hours,
round(sum(i.missing_hours), 4) AS missing_hours,
round((sum(i.total_hours))::numeric, 4) AS total_hours,
round((sum(i.direct_hours))::numeric, 4) AS direct_hours,
round((sum(i.indirect_hours))::numeric, 4) AS indirect_hours,
round((sum(i.billable_hours))::numeric, 4) AS billable_hours,
round((sum((COALESCE(i.billable_hours, (0)::double precision) + COALESCE(i.direct_hours, (0)::double precision))))::numeric, 4) AS productive_hours,
round((sum(i.earned_hours))::numeric, 4) AS earned_hours,
round((sum(i.els_hours))::numeric, 4) AS els_hours,
round((public.els_wavg(i.els, i.els_hours))::numeric, 4) AS els,
round(avg(i.qls), 4) AS qls,
CASE
WHEN (sum(i.daily_hours) > (0)::numeric) THEN round((((sum((COALESCE(i.billable_hours, (0)::double precision) + COALESCE(i.direct_hours, (0)::double precision))) / (sum(i.daily_hours))::double precision) * (100)::double precision))::numeric, 4)
ELSE NULL::numeric
END AS utilization,
CASE
WHEN (sum(i.daily_hours) > (0)::numeric) THEN round(((((sum(i.earned_hours) + COALESCE(sum(i.billable_hours), (0)::double precision)) / (sum(i.daily_hours))::double precision) * (100)::double precision))::numeric, 4)
ELSE NULL::numeric
END AS effectiveness
FROM ( SELECT i_1.client_id,
c.name AS client_name,
i_1.facility_id,
i_1.facility_name,
i_1.shift_id,
i_1.shift_name,
i_1.item_date,
i_1.employee_id,
max(i_1.daily_hours) AS daily_hours,
max(i_1.missing_hours) AS missing_hours,
sum(i_1.total_hours) AS total_hours,
sum(i_1.direct_hours) AS direct_hours,
sum(i_1.indirect_hours) AS indirect_hours,
sum(i_1.billable_hours) AS billable_hours,
sum(i_1.direct_missing_hours) AS direct_missing_hours,
sum(i_1.els_hours) AS els_hours,
sum(i_1.indirect_missing_hours) AS indirect_missing_hours,
sum(((i_1.els * i_1.els_hours) / (100)::double precision)) AS earned_hours,
public.els_wavg(i_1.els, i_1.els_hours) AS els,
avg(i_1.qls) AS qls
FROM (( SELECT i_2.client_id,
i_2.facility_id,
i_2.facility_name,
i_2.shift_id,
i_2.shift_name,
i_2.item_date,
i_2.employee_id,
h.daily_hours,
sum(i_2.hours) AS total_hours,
sum(h.missing_hours) AS missing_hours,
sum(
CASE
WHEN ((i_2.isindirect AND (NOT i_2.isbillable)) AND (NOT i_2.ismissing_time)) THEN i_2.hours
ELSE (0)::double precision
END) AS indirect_hours,
sum(
CASE
WHEN ((i_2.isindirect AND (NOT i_2.isbillable)) AND i_2.ismissing_time) THEN i_2.hours
ELSE (0)::double precision
END) AS indirect_missing_hours,
sum(
CASE
WHEN (i_2.isindirect AND i_2.isbillable) THEN i_2.hours
ELSE (0)::double precision
END) AS billable_hours,
sum(
CASE
WHEN ((NOT i_2.isindirect) AND (NOT i_2.ismissing_time)) THEN i_2.hours
ELSE (0)::double precision
END) AS direct_hours,
sum(
CASE
WHEN ((NOT i_2.isindirect) AND i_2.ismissing_time) THEN i_2.hours
ELSE (0)::double precision
END) AS direct_missing_hours,
sum(
CASE
WHEN ((NOT i_2.isindirect) AND (i_2.hours > (0)::double precision)) THEN i_2.hours
ELSE (0)::double precision
END) AS els_hours,
( SELECT round((avg(q.qls_value))::numeric, 4) AS round
FROM (public.qls q
JOIN public.qls_employee qt ON ((qt.qls_id = q.qls_id)))
WHERE (((q.client_id = i_2.client_id) AND (q.facility_id = i_2.facility_id)) AND (q.qls_date = i_2.item_date))) AS qls,
CASE
WHEN (NOT i_2.isindirect) THEN public.els_wavg(((ie.els_value * COALESCE(h.missing_time_factor, 1.0)))::double precision, i_2.hours)
ELSE NULL::double precision
END AS els
FROM ((( SELECT i_3.client_id,
i_3.facility_id,
i_3.shift_id,
i_3.process_id,
i_3.item_id,
i_3.item_date,
t.employee_id,
COALESCE((ts.effective_hours)::double precision, t.hours) AS hours,
i_3.isindirect,
ii.isbillable,
i_3.isautomatic_entry,
pa.ismissing_time,
pa.process_name,
fa.facility_name,
sa.shift_name
FROM ((((((public.item i_3
JOIN public.item_summary ii ON ((((ii.item_id = i_3.item_id) AND (ii.client_id = i_3.client_id)) AND ii.isactive)))
JOIN public.item_employee t ON ((((t.client_id = i_3.client_id) AND (t.item_id = i_3.item_id)) AND t.isactive)))
LEFT JOIN public.item_employee_summary ts ON (((ts.item_id = i_3.item_id) AND (ts.employee_id = t.employee_id))))
JOIN public.process_archive pa ON ((pa.process_archive_id = ii.process_archive_id)))
LEFT JOIN public.facility_archive fa ON ((fa.facility_archive_id = ii.facility_archive_id)))
LEFT JOIN public.shift_archive sa ON ((sa.shift_archive_id = ii.shift_archive_id)))
WHERE i_3.isactive) i_2
JOIN public.employee_hours h ON (((((h.client_id = i_2.client_id) AND (h.facility_id = i_2.facility_id)) AND (h.employee_id = i_2.employee_id)) AND (h.item_date = i_2.item_date))))
LEFT JOIN public.item_els ie ON ((ie.item_id = i_2.item_id)))
GROUP BY i_2.client_id, i_2.facility_id, i_2.facility_name, i_2.shift_id, i_2.shift_name, i_2.isautomatic_entry, i_2.ismissing_time, i_2.process_id, i_2.process_name, i_2.isindirect,i_2.isbillable, i_2.employee_id, h.missing_time_factor, h.daily_hours, h.missing_hours, h.timecard_hours, h.project_hours, i_2.item_date) i_1
JOIN public.client c ON ((c.client_id = i_1.client_id)))
GROUP BY i_1.client_id, c.name, i_1.facility_id, i_1.facility_name, i_1.shift_id, i_1.shift_name, i_1.employee_id, i_1.item_date) i
GROUP BY i.client_id, i.client_name, i.facility_id, i.facility_name, i.shift_id, i.shift_name, i.item_date;
pg_restore: [archiver (db)] could not execute query: ERROR: relation "public.dv_shift_day_hours" does not exist
Command was: ALTER TABLE public.dv_shift_day_hours OWNER TO lp;
pg_restore: [archiver (db)] Error from TOC entry 552; 1259 19168 VIEW vs_els_daily lp
pg_restore: [archiver (db)] could not execute query: ERROR: function public.els_wavg(double precision, double precision) does not exist
LINE 11: (round((public.els_wavg((i.els_value)::double precision,...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
Command was: CREATE VIEW public.vs_els_daily AS
SELECT i.client_id,
i.facility_id,
i.employee_id,
i.item_date,
(round(sum((i.direct_hours -
CASE
WHEN (i.direct_missing_hours > (0)::numeric) THEN i.direct_missing_hours
ELSE (0)::numeric
END)), 4))::double precision AS direct_hours,
(round((public.els_wavg((i.els_value)::double precision, (i.direct_hours)::double precision))::numeric, 4))::double precision AS els,
round(sum(i.direct_hours), 4) AS els_hours
FROM ( SELECT i_1.client_id,
i_1.facility_id,
i_1.item_date,
t.employee_id,
(
CASE
WHEN (COALESCE((ts.effective_hours)::double precision, t.hours) > (0)::double precision) THEN COALESCE((ts.effective_hours)::double precision, t.hours)
ELSE ((0)::numeric)::double precision
END)::numeric AS direct_hours,
(
CASE
WHEN pa.ismissing_time THEN COALESCE((ts.effective_hours)::double precision, t.hours)
ELSE ((0)::numeric)::double precision
END)::numeric AS direct_missing_hours,
(ie.els_value * COALESCE(h.missing_time_factor, 1.0)) AS els_value
FROM ((((((public.item i_1
JOIN public.item_summary ii ON ((((ii.client_id = i_1.client_id) AND (ii.item_id = i_1.item_id)) AND ii.isactive)))
JOIN public.item_employee t ON ((((t.client_id = i_1.client_id) AND (t.item_id = i_1.item_id)) AND t.isactive)))
LEFT JOIN public.item_employee_summary ts ON (((ts.item_id = t.item_id) AND (ts.employee_id = t.employee_id))))
LEFT JOIN public.process_archive pa ON ((pa.process_archive_id = ii.process_archive_id)))
LEFT JOIN public.employee_hours h ON (((((h.client_id = i_1.client_id) AND (h.facility_id = i_1.facility_id)) AND (h.employee_id = t.employee_id)) AND (h.item_date = i_1.item_date))))
LEFT JOIN public.item_els ie ON ((ie.item_id = i_1.item_id)))
WHERE (i_1.isactive AND ((NOT i_1.isindirect) AND (NOT ii.isbillable)))) i
GROUP BY i.client_id, i.facility_id, i.item_date, i.employee_id;
pg_restore: [archiver (db)] could not execute query: ERROR: relation "public.vs_els_daily" does not exist
Command was: ALTER TABLE public.vs_els_daily OWNER TO lp;
pg_restore: [archiver (db)] Error from TOC entry 553; 1259 19173 VIEW v_daily_labor_savings lp
pg_restore: [archiver (db)] could not execute query: ERROR: relation "public.vs_els_daily" does not exist
LINE 37: FROM (((((public.vs_els_daily de_1
^
Command was: CREATE VIEW public.v_daily_labor_savings AS
SELECT de.client_id,
de.facility_id,
de.item_date,
de.employee_id,
de.direct_hours,
de.els,
de.hourly_wage,
de.status_quo_els,
de.status_quo_indirect,
de.labor_burden,
de.bonus_load,
round((((de.els / de.status_quo_els) - (1)::numeric) * (100)::numeric), 2) AS productivity_increase,
round(((((de.direct_hours * (((de.els / de.status_quo_els) - (1)::numeric))::double precision) * de.hourly_wage) * (((1)::numeric + (de.labor_burden / (100)::numeric)))::double precision))::numeric, 2) AS labor_savings,
round((((de.direct_hours * de.hourly_wage) * (((1)::numeric + (de.labor_burden / (100)::numeric)))::double precision))::numeric, 2) AS burdened_labor_cost,
round(((de.direct_hours * ((de.els / de.status_quo_els))::double precision))::numeric, 2) AS status_quo_direct_hours,
round(((((de.direct_hours * ((de.els / de.status_quo_els))::double precision) * de.hourly_wage) * (((1)::numeric + (de.labor_burden / (100)::numeric)))::double precision))::numeric, 2) AS status_quo_burdened_labor_cost
FROM ( SELECT de_1.client_id,
de_1.facility_id,
de_1.item_date,
de_1.employee_id,
de_1.direct_hours,
round(((de_1.els * (
CASE
WHEN f.ismissinghours_els THEN
CASE
WHEN (h.timecard_hours > (0)::numeric) THEN round(LEAST((h.project_hours / h.timecard_hours), (1)::numeric), 4)
ELSE 1.0
END
ELSE (1)::numeric
END)::double precision))::numeric, 2) AS els,
fs.status_quo_els,
fs.status_quo_indirect,
fs.labor_burden,
fs.bonus_load,
e.hourly_wage
FROM (((((public.vs_els_daily de_1
LEFT JOIN public.employee_hours h ON ((((h.facility_id = de_1.facility_id) AND (h.employee_id = de_1.employee_id)) AND (h.item_date = de_1.item_date))))
JOIN public.facility_settings fs ON ((fs.facility_id = de_1.facility_id)))
JOIN public.facility_period fp ON ((((fp.facility_id = de_1.facility_id) AND ((de_1.item_date >= fp.period_start) AND (de_1.item_date <= fp.period_end))) AND (fp.period_type = 'ELS'::bpchar))))
JOIN public.employee_archive e ON (((((e.employee_id = de_1.employee_id) AND (e.effective_date = ( SELECT max(employee_archive.effective_date) AS max
FROM public.employee_archive
WHERE (((employee_archive.employee_id = de_1.employee_id) AND (employee_archive.effective_date <= fp.period_start)) AND employee_archive.isarchive_active)))) AND e.isactive) AND e.isarchive_active)))
LEFT JOIN public.facility_archive f ON (((((f.facility_id = de_1.facility_id) AND (f.effective_date = ( SELECT max(facility_archive.effective_date) AS max
FROM public.facility_archive
WHERE (((facility_archive.facility_id = de_1.facility_id) AND (facility_archive.effective_date <= fp.period_start)) AND facility_archive.isarchive_active)))) AND f.isactive) AND f.isarchive_active)))) de
GROUP BY de.client_id, de.facility_id, de.employee_id, de.item_date, de.els, de.direct_hours, de.status_quo_els, de.status_quo_indirect, de.labor_burden, de.bonus_load, de.hourly_wage
ORDER BY de.facility_id, de.item_date, de.employee_id;
pg_restore: [archiver (db)] could not execute query: ERROR: relation "public.v_daily_labor_savings" does not exist
Command was: ALTER TABLE public.v_daily_labor_savings OWNER TO lp;
pg_restore: [archiver (db)] Error from TOC entry 563; 1259 19222 VIEW vs_els_daily_learning lp
pg_restore: [archiver (db)] could not execute query: ERROR: function public.els_wavg(double precision, double precision) does not exist
LINE 60: (public.els_wavg(((ie.els_value * COALESCE(h.mis...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
Command was: CREATE VIEW public.vs_els_daily_learning AS
SELECT i.client_id,
i.facility_id,
i.employee_id,
i.item_date,
pp.period_start,
pp.period_end,
(sum((i.direct_hours -
CASE
WHEN (i.direct_missing_hours > (0)::numeric) THEN i.direct_missing_hours
ELSE (0)::numeric
END)))::double precision AS direct_hours,
public.els_wavg((i.els)::double precision, (i.direct_hours)::double precision) AS els,
round((
CASE
WHEN (sum(i.direct_hours) > (0)::numeric) THEN (sum((public.p_calculate_learningcurve(i.startup_percent, i.peak_percent, (
CASE
WHEN (emp.start_date IS NOT NULL) THEN (public.p_calculate_weekdays(emp.start_date, i.item_date))::bigint
ELSE ( SELECT count(*) AS count
FROM public.employee_hours
WHERE (((employee_hours.facility_id = i.facility_id) AND (employee_hours.employee_id = i.employee_id)) AND (employee_hours.item_date < i.item_date)))
END)::double precision, i.learn_ratio) * (i.direct_hours)::double precision)) / (sum(i.direct_hours))::double precision)
ELSE NULL::double precision
END)::numeric, 2) AS goal,
CASE
WHEN (sum(i.direct_hours) > (0)::numeric) THEN round(((sum((i.peak_percent * (i.direct_hours)::double precision)) / (sum(i.direct_hours))::double precision))::numeric, 2)
ELSE NULL::numeric
END AS peak_percent,
CASE
WHEN (sum(i.direct_hours) > (0)::numeric) THEN
CASE
WHEN (sum((i.peak_percent * (i.direct_hours)::double precision)) > (0)::double precision) THEN round(((round(((sum((public.p_calculate_learningcurve(i.startup_percent, i.peak_percent, (
CASE
WHEN (emp.start_date IS NOT NULL) THEN (public.p_calculate_weekdays(emp.start_date, i.item_date))::bigint
ELSE ( SELECT count(*) AS count
FROM public.employee_hours
WHERE (((employee_hours.facility_id = i.facility_id) AND (employee_hours.employee_id = i.employee_id)) AND (employee_hours.item_date < i.item_date)))
END)::double precision, i.learn_ratio) * (i.direct_hours)::double precision)) / (sum(i.direct_hours))::double precision))::numeric, 2) * (100)::numeric) / round(((sum((i.peak_percent *(i.direct_hours)::double precision)) / (sum(i.direct_hours))::double precision))::numeric, 2)), 2)
ELSE (0)::numeric
END
ELSE NULL::numeric
END AS learning_factor
FROM (((( SELECT i_1.client_id,
i_1.facility_id,
i_1.item_date,
i_1.employee_id,
i_1.startup_percent,
i_1.peak_percent,
i_1.learn_ratio,
round((sum(
CASE
WHEN ((NOT i_1.isindirect) AND (i_1.hours > (0)::double precision)) THEN i_1.hours
ELSE NULL::double precision
END))::numeric, 4) AS direct_hours,
round((sum(
CASE
WHEN ((NOT i_1.isindirect) AND i_1.ismissing_time) THEN i_1.hours
ELSE NULL::double precision
END))::numeric, 4) AS direct_missing_hours,
(public.els_wavg(((ie.els_value * COALESCE(h.missing_time_factor, 1.0)))::double precision,
CASE
WHEN ((NOT i_1.isindirect) AND (i_1.hours > (0)::double precision)) THEN i_1.hours
ELSE NULL::double precision
END))::numeric AS els
FROM ((( SELECT i_2.client_id,
i_2.facility_id,
i_2.item_date,
t.employee_id,
i_2.item_id,
COALESCE((ts.effective_hours)::double precision, t.hours) AS hours,
i_2.isindirect,
ii.isbillable,
pa.ismissing_time,
da.startup_percent,
da.peak_percent,
da.learn_ratio
FROM (((((public.item i_2
JOIN public.item_summary ii ON ((((ii.item_id = i_2.item_id) AND (ii.client_id = i_2.client_id)) AND ii.isactive)))
JOIN public.item_employee t ON ((((t.client_id = i_2.client_id) AND (t.item_id = i_2.item_id)) AND t.isactive)))
LEFT JOIN public.item_employee_summary ts ON (((ts.item_id = i_2.item_id) AND (ts.employee_id = t.employee_id))))
JOIN public.process_archive pa ON ((pa.process_archive_id = ii.process_archive_id)))
JOIN public.department_archive da ON ((da.department_archive_id = ii.department_archive_id)))
WHERE i_2.isactive) i_1
JOIN public.employee_hours h ON (((((h.client_id = i_1.client_id) AND (h.facility_id = i_1.facility_id)) AND (h.employee_id = i_1.employee_id)) AND (h.item_date = i_1.item_date))))
LEFT JOIN public.item_els ie ON ((ie.item_id = i_1.item_id)))
GROUP BY i_1.client_id, i_1.facility_id, i_1.employee_id, i_1.item_date, i_1.startup_percent, i_1.peak_percent, i_1.learn_ratio) i
JOIN public.facility_period_config ppc ON (((ppc.facility_id = i.facility_id) AND ppc.isdefault)))
JOIN public.facility_period pp ON (((((pp.facility_id = i.facility_id) AND (pp.facility_period_config_id = ppc.facility_period_config_id)) AND (pp.period_type = 'PAY'::bpchar)) AND ((i.item_date >= pp.period_start) AND (i.item_date <= pp.period_end)))))
JOIN public.employee_archive emp ON (((((emp.employee_id = i.employee_id) AND emp.isarchive_active) AND emp.isactive) AND (emp.effective_date = ( SELECT max(employee_archive.effective_date) AS max
FROM public.employee_archive
WHERE (((employee_archive.employee_id = emp.employee_id) AND (employee_archive.effective_date <= pp.period_start)) AND employee_archive.isarchive_active))))))
GROUP BY i.client_id, i.facility_id, i.item_date, i.employee_id, pp.period_start, pp.period_end
ORDER BY i.item_date, i.employee_id;
pg_restore: [archiver (db)] could not execute query: ERROR: relation "public.vs_els_daily_learning" does not exist
Command was: ALTER TABLE public.vs_els_daily_learning OWNER TO lp;
pg_restore: WARNING: no privileges could be revoked for "public"
pg_restore: WARNING: no privileges could be revoked for "public"
pg_restore: WARNING: no privileges were granted for "public"
pg_restore: WARNING: no privileges were granted for "public"
pg_restore: [archiver (db)] Error from TOC entry 7243; 0 0 ACL TABLE dv_dept_day_hours lp
pg_restore: [archiver (db)] could not execute query: ERROR: relation "public.dv_dept_day_hours" does not exist
Command was: REVOKE ALL ON TABLE public.dv_dept_day_hours FROM PUBLIC;
REVOKE ALL ON TABLE public.dv_dept_day_hours FROM lp;
GRANT ALL ON TABLE public.dv_dept_day_hours TO lp;
GRANT SELECT ON TABLE public.dv_dept_day_hours TO ims_qc;
pg_restore: [archiver (db)] Error from TOC entry 7247; 0 0 ACL TABLE dv_employee_day_hours lp
pg_restore: [archiver (db)] could not execute query: ERROR: relation "public.dv_employee_day_hours" does not exist
Command was: REVOKE ALL ON TABLE public.dv_employee_day_hours FROM PUBLIC;
REVOKE ALL ON TABLE public.dv_employee_day_hours FROM lp;
GRANT ALL ON TABLE public.dv_employee_day_hours TO lp;
GRANT SELECT ON TABLE public.dv_employee_day_hours TO ims_qc;
pg_restore: [archiver (db)] Error from TOC entry 7248; 0 0 ACL TABLE dv_facility_day_hours lp
pg_restore: [archiver (db)] could not execute query: ERROR: relation "public.dv_facility_day_hours" does not exist
Command was: REVOKE ALL ON TABLE public.dv_facility_day_hours FROM PUBLIC;
REVOKE ALL ON TABLE public.dv_facility_day_hours FROM lp;
GRANT ALL ON TABLE public.dv_facility_day_hours TO lp;
GRANT SELECT ON TABLE public.dv_facility_day_hours TO ims_qc;
pg_restore: [archiver (db)] Error from TOC entry 7251; 0 0 ACL TABLE dv_facility_day_hours_qls lp
pg_restore: [archiver (db)] could not execute query: ERROR: relation "public.dv_facility_day_hours_qls" does not exist
Command was: REVOKE ALL ON TABLE public.dv_facility_day_hours_qls FROM PUBLIC;
REVOKE ALL ON TABLE public.dv_facility_day_hours_qls FROM lp;
GRANT ALL ON TABLE public.dv_facility_day_hours_qls TO lp;
GRANT SELECT ON TABLE public.dv_facility_day_hours_qls TO ims_qc;
pg_restore: [archiver (db)] Error from TOC entry 7252; 0 0 ACL TABLE dv_shift_day_hours lp
pg_restore: [archiver (db)] could not execute query: ERROR: relation "public.dv_shift_day_hours" does not exist
Command was: REVOKE ALL ON TABLE public.dv_shift_day_hours FROM PUBLIC;
REVOKE ALL ON TABLE public.dv_shift_day_hours FROM lp;
GRANT ALL ON TABLE public.dv_shift_day_hours TO lp;
GRANT SELECT ON TABLE public.dv_shift_day_hours TO ims_qc;
pg_restore: [archiver (db)] Error from TOC entry 7416; 0 0 ACL TABLE vs_els_daily lp
pg_restore: [archiver (db)] could not execute query: ERROR: relation "public.vs_els_daily" does not exist
Command was: REVOKE ALL ON TABLE public.vs_els_daily FROM PUBLIC;
REVOKE ALL ON TABLE public.vs_els_daily FROM lp;
GRANT ALL ON TABLE public.vs_els_daily TO lp;
GRANT SELECT ON TABLE public.vs_els_daily TO ims_qc;
pg_restore: [archiver (db)] Error from TOC entry 7417; 0 0 ACL TABLE v_daily_labor_savings lp
pg_restore: [archiver (db)] could not execute query: ERROR: relation "public.v_daily_labor_savings" does not exist
Command was: REVOKE ALL ON TABLE public.v_daily_labor_savings FROM PUBLIC;
REVOKE ALL ON TABLE public.v_daily_labor_savings FROM lp;
GRANT ALL ON TABLE public.v_daily_labor_savings TO lp;
GRANT SELECT ON TABLE public.v_daily_labor_savings TO ims_qc;
pg_restore: [archiver (db)] Error from TOC entry 7430; 0 0 ACL TABLE vs_els_daily_learning lp
pg_restore: [archiver (db)] could not execute query: ERROR: relation "public.vs_els_daily_learning" does not exist
Command was: REVOKE ALL ON TABLE public.vs_els_daily_learning FROM PUBLIC;
REVOKE ALL ON TABLE public.vs_els_daily_learning FROM lp;
GRANT ALL ON TABLE public.vs_els_daily_learning TO lp;
GRANT SELECT ON TABLE public.vs_els_daily_learning TO ims_qc;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment