Dumped on 2018-02-22

Index of database - gnumed_v20

• 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