aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
fn_get_room_charge_dept
Parameters
Name
Type
Mode
register_id
integer
IN (DEFAULT NULL)
charge_date
date
IN (DEFAULT NULL)
Definition
WITH rate AS ( SELECT room_charge_dept, sys_msg('X1', 'room_charge_dept') FROM registration_rates rr WHERE rr.register_id = $1 AND rr.charge_date = $2 ), plan AS ( SELECT room_charge_dept, sys_msg('X2', 'room_charge_dept') FROM registration rg LEFT JOIN rate_plan rp ON rp.id = rg.rateplan_id WHERE rg.id = $1 ) SELECT CASE WHEN charge_date IS NOT NULL THEN coalesce((SELECT room_charge_dept FROM rate), (SELECT room_charge_dept FROM plan), fn_default('ROOM_CHARGE_DEPT')::int) WHEN register_id IS NOT NULL THEN coalesce((SELECT room_charge_dept FROM plan), fn_default('ROOM_CHARGE_DEPT')::int) ELSE fn_default('ROOM_CHARGE_DEPT')::int END