35 lines
753 B
PL/PgSQL
35 lines
753 B
PL/PgSQL
-- Could not auto-generate a down migration.
|
|
-- Please write an appropriate down migration for the SQL below:
|
|
-- CREATE OR REPLACE FUNCTION public.jobs_ar_summary ()
|
|
-- RETURNS SETOF job_ar_schema
|
|
-- LANGUAGE plpgsql
|
|
-- STABLE
|
|
-- AS $function$
|
|
-- BEGIN
|
|
--
|
|
-- RETURN query
|
|
-- select
|
|
-- j.id,
|
|
-- j.ro_number,
|
|
-- j.clm_total,
|
|
-- coalesce (p.total_payments,0) as total_payments,
|
|
-- j.clm_total - coalesce (p.total_payments,0) as balance,
|
|
-- j.date_invoiced
|
|
-- from
|
|
-- jobs j
|
|
-- left join (
|
|
-- select
|
|
-- p.jobid,
|
|
-- coalesce (sum(p.amount),0) as total_payments
|
|
-- from
|
|
-- payments p
|
|
-- group by
|
|
-- p.jobid
|
|
-- ) p on
|
|
-- j.id = p.jobid
|
|
-- where j.remove_from_ar = false and j.date_invoiced is not null;
|
|
--
|
|
--
|
|
-- END
|
|
-- $function$;
|