AP vs AR and Regular vs Credit Memo Multiplier Functions

The purpose of this page is to give you a postgresql database function that will determine the appropriate sign of a given Invoice or Payment when listing documents of different types together. Example, select * from c_invoice will give you almost all amounts as positive; however, some are AP and some are AR. Some are regular invoices and others are Credit Memos. The below functions allow you to easily determine the multiplier sign.

CREATE OR REPLACE FUNCTION doctypemultiplier (numeric) RETURNS numeric AS $$
-- used to determine a Document Type is a credit memo
Select CASE
 WHEN charat(docbasetype::character varying, 3)::text = 'C'::text THEN (-1.0)
 ELSE 1.0 END
from c_doctype
where c_doctype_id = $1
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION doctypemultiplierap (numeric) RETURNS numeric AS $$
-- used to determine a Document Type is a AP (-) or AR (+)
Select CASE
 WHEN charat(docbasetype::character varying, 2)::text = 'P'::text THEN (-1.0)
 ELSE 1.0 END 
from c_doctype
where c_doctype_id = $1
$$ LANGUAGE sql;