Dumped on 2024-12-23

Index of database - gnumed_v21


Table: bill

actual bills

bill Structure
F-Key Name Type Description
pk_audit integer NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass)
row_version integer NOT NULL
modified_when timestamp with time zone NOT NULL DEFAULT now()
modified_by name NOT NULL DEFAULT "current_user"()
pk serial PRIMARY KEY
invoice_id text UNIQUE

the ID of the bill
close_date timestamp with time zone

cannot add further bill_items after this date if not NULL
apply_vat boolean

whether or not to apply VAT on the invoice
fk_receiver_identity integer

link to the receiver as a GNUmed identity, if known
fk_receiver_address integer

links the address of the receiver of the invoice
fk_doc integer

links to the document which contains the invoice PDF
comment text

arbitrary comments on bills

 

bill Constraints
Name Constraint
audit_audit_fields_sane_modified_when CHECK (((modified_when <= clock_timestamp()) IS TRUE))
bill_bill_sane_apply_vat CHECK (((apply_vat IS NOT NULL) OR ((apply_vat IS NULL) AND (fk_doc IS NULL))))
bill_bill_sane_comment CHECK ((gm.is_null_or_non_empty_string(comment) IS TRUE))
bill_bill_sane_invoice_id CHECK ((gm.is_null_or_blank_string(invoice_id) IS FALSE))
bill_bill_sane_recv_adr CHECK (((fk_receiver_address IS NOT NULL) OR (close_date IS NULL)))

Tables referencing this one via Foreign Key Constraints:

Index - Schema bill


Table: bill_item

items patients currently *are* billed for

bill_item Structure
F-Key Name Type Description
pk_audit integer NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass)
row_version integer NOT NULL
modified_when timestamp with time zone NOT NULL DEFAULT now()
modified_by name NOT NULL DEFAULT "current_user"()
pk serial PRIMARY KEY
fk_provider integer NOT NULL

by whom or on whose behalf did the billable activity happen
fk_encounter integer NOT NULL

the encounter during which the billable action for this item took place
date_to_bill date

The date the bill item was caused. If NULL, use .fk_encounter -> .started
description text

Can be used to further explain the bill item over and above .fk_billable.description.
net_amount_per_unit numeric NOT NULL

How much to charge for one unit of this bill item.
currency text NOT NULL

Which currency to charge in. Must not be NULL if .net_amount_per_unit is not NULL.
status text DEFAULT 'new'::text

the status of this item
fk_billable integer NOT NULL

Links to the billable item this bill item stands for.
bill.pk fk_bill integer

Links to the bill this bill item is on if any.
unit_count integer NOT NULL DEFAULT 1

The number of times this item is to be billed. 0 can be used for informative items.
amount_multiplier numeric NOT NULL DEFAULT 1

A multiplier to apply to .net_amount_per_unit. Can be used for discounts, rebates, or increases. Must be > 0.

 

bill_item Constraints
Name Constraint
audit_audit_fields_sane_modified_when CHECK (((modified_when <= clock_timestamp()) IS TRUE))
bill_bill_item_sane_count CHECK ((unit_count > '-1'::integer))
bill_bill_item_sane_currency CHECK ((gm.is_null_or_blank_string(currency) IS FALSE))
bill_bill_item_sane_desc CHECK ((gm.is_null_or_non_empty_string(description) IS TRUE))
bill_bill_item_sane_multiplier CHECK ((amount_multiplier > (0)::numeric))
valid_stati CHECK ((status = ANY (ARRAY['new'::text, 'transferred'::text])))

Index - Schema bill


Table: lnk_enc_type2billable

actual bills

lnk_enc_type2billable Structure
F-Key Name Type Description
pk serial PRIMARY KEY
fk_encounter_type integer NOT NULL

Links to encounter type this billable is associated with.
fk_billable integer NOT NULL

Links to the billable item the encounter type is associated with.

Index - Schema bill


View: v_bill_items

v_bill_items Structure
F-Key Name Type Description
pk_bill_item integer
billable_code text
billable_description text
item_detail text
date_to_bill timestamp with time zone
net_amount_per_unit numeric
unit_count integer
amount_multiplier numeric
total_amount numeric
vat numeric
currency text
raw_date_to_bill date
billable_amount numeric
vat_multiplier numeric
billable_currency text
billable_comment text
billable_active boolean
billable_discountable boolean
catalog_long text
catalog_short text
catalog_version text
catalog_language text
pk_patient integer
pk_encounter_type integer
pk_provider integer
pk_encounter_to_bill integer
pk_bill integer
pk_billable integer
pk_data_source integer
xmin_bill_item xid
SELECT b_bi.pk AS pk_bill_item
,
    r_b.code AS billable_code
,
    r_b.term AS billable_description
,
    b_bi.description AS item_detail
,
    COALESCE
(
     (b_bi.date_to_bill)::timestamp with time zone
     , c_enc.started
) AS date_to_bill
,
    b_bi.net_amount_per_unit
,
    b_bi.unit_count
,
    b_bi.amount_multiplier
,
    
(
     (
           (b_bi.unit_count)::numeric * b_bi.net_amount_per_unit
     ) * b_bi.amount_multiplier
) AS total_amount
,
    
(
     (
           (
                 (b_bi.unit_count)::numeric * b_bi.net_amount_per_unit
           ) * b_bi.amount_multiplier
     ) * r_b.vat_multiplier
) AS vat
,
    b_bi.currency
,
    b_bi.date_to_bill AS raw_date_to_bill
,
    r_b.amount AS billable_amount
,
    r_b.vat_multiplier
,
    r_b.currency AS billable_currency
,
    r_b.comment AS billable_comment
,
    r_b.active AS billable_active
,
    r_b.discountable AS billable_discountable
,
    r_ds.name_long AS catalog_long
,
    r_ds.name_short AS catalog_short
,
    r_ds.version AS catalog_version
,
    r_ds.lang AS catalog_language
,
    c_enc.fk_patient AS pk_patient
,
    c_enc.fk_type AS pk_encounter_type
,
    b_bi.fk_provider AS pk_provider
,
    b_bi.fk_encounter AS pk_encounter_to_bill
,
    b_bi.fk_bill AS pk_bill
,
    r_b.pk AS pk_billable
,
    r_b.fk_data_source AS pk_data_source
,
    b_bi.xmin AS xmin_bill_item
   
FROM (
     (
           (bill.bill_item b_bi
     
              JOIN ref.billable r_b 
                ON (
                       (b_bi.fk_billable = r_b.pk)
                 )
           )
     
   LEFT JOIN ref.data_source r_ds 
          ON (
                 (r_b.fk_data_source = r_ds.pk)
           )
     )
     
LEFT JOIN clin.encounter c_enc 
    ON (
           (b_bi.fk_encounter = c_enc.pk)
     )
);

Index - Schema bill


View: v_bills

v_bills Structure
F-Key Name Type Description
pk_bill integer
invoice_id text
pk_receiver_identity integer
total_amount numeric
total_vat numeric
total_amount_with_vat numeric
percent_vat numeric
currency text
close_date timestamp with time zone
apply_vat boolean
comment text
pk_receiver_address integer
pk_doc integer
pk_patient integer
pk_bill_items integer[]
xmin_bill xid
SELECT b_b.pk AS pk_bill
,
    b_b.invoice_id
,
    b_b.fk_receiver_identity AS pk_receiver_identity
,
    
(
SELECT round
     (sum
           (v_bill_items.total_amount)
           , 2
     ) AS round
           
  FROM bill.v_bill_items
          
 WHERE (v_bill_items.pk_bill = b_b.pk)
) AS total_amount
,
    
(
SELECT round
     (sum
           (v_bill_items.vat)
           , 2
     ) AS round
           
  FROM bill.v_bill_items
          
 WHERE (v_bill_items.pk_bill = b_b.pk)
) AS total_vat
,
    
(
SELECT round
     (sum
           (
                 (v_bill_items.total_amount + v_bill_items.vat)
           )
           , 2
     ) AS round
           
  FROM bill.v_bill_items
          
 WHERE (v_bill_items.pk_bill = b_b.pk)
) AS total_amount_with_vat
,
    
(
SELECT (v_bill_items.vat_multiplier * 
           (100)::numeric
     )
           
  FROM bill.v_bill_items
          
 WHERE (v_bill_items.pk_bill = b_b.pk)
         LIMIT 1
) AS percent_vat
,
    
(
SELECT v_bill_items.currency
           
  FROM bill.v_bill_items
          
 WHERE (v_bill_items.pk_bill = b_b.pk)
         LIMIT 1
) AS currency
,
    b_b.close_date
,
    b_b.apply_vat
,
    b_b.comment
,
    b_b.fk_receiver_address AS pk_receiver_address
,
    b_b.fk_doc AS pk_doc
,
    
(
SELECT encounter.fk_patient
           
  FROM clin.encounter
          
 WHERE (encounter.pk = 
           (
            SELECT bill_item.fk_encounter
                   
              FROM bill.bill_item
                  
             WHERE (bill_item.fk_bill = b_b.pk)
                 LIMIT 1
           )
     )
) AS pk_patient
,
    
(
SELECT array_agg
     (sorted_values.pk_bill_item) AS array_agg
           
  FROM (
      SELECT b_vbi.pk_bill_item
                   
        FROM bill.v_bill_items b_vbi
                  
       WHERE (b_vbi.pk_bill = b_b.pk)
                  
    ORDER BY b_vbi.date_to_bill
           , b_vbi.billable_code
     ) sorted_values
) AS pk_bill_items
,
    b_b.xmin AS xmin_bill
   
FROM bill.bill b_b;

Index - Schema bill


Function: get_bill_receiver_identity(integer)

Returns: integer

Language: SQL

select
	value
from (
	select
		id.pk_id,
		id.value::integer
	from
		dem.v_external_ids4identity id
			join dem.identity d_i on (id.value = d_i.pk::text)
	where
		id.pk_type = (select pk from dem.enum_ext_id_types where name = 'bill receiver' and issuer = 'GNUmed')
			and
		id.pk_identity = $1
	union all
	select
		0,
		$1
) me
limit 1;

Function: trf_prevent_empty_bills()

Returns: trigger

Language: PLPGSQL

Prevent bills to become void of items due to deletions/updates of bill items.

DECLARE
	_item_count integer;
	_msg text;
BEGIN
	if TG_OP = 'UPDATE' then
		if OLD.fk_bill IS NULL then
			return NULL;
		end if;
		if OLD.fk_bill IS NOT DISTINCT FROM NEW.fk_bill then
			return NULL;
		end if;
	else
		if OLD.fk_bill is NULL then
			return NULL;
		end if;
	end if;
	-- we now either:
	--	DELETE with .fk_bill NOT NULL
	-- or:
	--	UPDATE with an .fk_bill change (including towards fk_bill = NULL)
	-- let us check whether the (previous) bill still exists
	-- at all or whether we are deleting the bill (and thereby
	-- setting our .fk_bill to NULL)
	-- only works at or below REPEATABLE READ after deletion of bill
	perform 1 from bill.bill where pk = OLD.fk_bill;
	if FOUND is FALSE then
		return NULL;
	end if;
	select count(1) into _item_count
	from bill.bill_item
	where
		fk_bill = OLD.fk_bill
			and
		pk != OLD.pk;
	if _item_count > 0 then
		return NULL;
	end if;
	_msg := '[bill.trf_prevent_empty_bills]: cannot remove (by '
			|| '<' || TG_OP || '>'
			||') the only item (bill.bill_item.pk='
			|| coalesce(OLD.pk::text, '<NULL>'::text)
			|| ') from bill (bill.bill_item.fk_bill=bill.bill.pk='
			|| coalesce(OLD.fk_bill::text, '<NULL>'::text)
			|| ') ';
	raise exception unique_violation using message = _msg;
	return NULL;
END;

Function: trf_prevent_mislinked_bills()

Returns: trigger

Language: PLPGSQL

Prevent bills to link to invoices of another patient.

DECLARE
	_doc_patient integer;
	_bill_patient integer;
	_msg text;
BEGIN
	if NEW.fk_doc IS NULL then
		return NEW;
	end if;
	if TG_OP = 'UPDATE' then
		if OLD.fk_doc IS NOT DISTINCT FROM NEW.fk_doc then
			return NEW;
		end if;
	end if;
	-- we now either:
	--	INSERT with .fk_doc NOT NULL
	-- or:
	--	UPDATE with an .fk_bill change to a NON-NULL value
	select pk_patient into _doc_patient
	from blobs.v_doc_med
	where
		pk_doc = NEW.fk_doc;
	select pk_patient into _bill_patient
	from bill.v_bills
	where
		pk_bill = NEW.pk;
	if _doc_patient = _bill_patient then
		return NEW;
	end if;
	_msg := '[bill.trf_prevent_mislinked_bills]: patient mismatch between '
		|| 'bill (pk=' || NEW.pk || ', patient=' || _bill_patient || ') '
		|| 'and invoice (pk=' || NEW.fk_doc || ', patient=' || _doc_patient || ')';
	raise exception integrity_constraint_violation using message = _msg;
	return NULL;
END;

Generated by PostgreSQL Autodoc

W3C HTML 4.01 Strict