33 lines
602 B
PL/PgSQL
33 lines
602 B
PL/PgSQL
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 and j.clm_total - coalesce (p.total_payments,0) > 0;
|
|
|
|
|
|
END
|
|
$function$;
|