Dumped on 2015-04-16

Index of database - gnumed_v19

• bill
• bill
• bill_item
• lnk_enc_type2billable
• v_bill_items
• v_bills
• get_bill_receiver_identity(integer)
• trf_prevent_empty_bills()
• trf_prevent_mislinked_bills()

Table: bill

actual bills

Tables referencing this one via Foreign Key Constraints:

• bill_item

Index - Schema bill

Table: bill_item

items patients currently *are* billed for

Index - Schema bill

Table: lnk_enc_type2billable

actual bills

Index - Schema bill

View: v_bill_items

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

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