Time off data with lookup expansion
CREATE VIEW vwtimeoffdata AS (
SELECT td.userid,
td.buid,
td.scheduleid,
td.businessunitdate,
td.length,
td.description,
td.activitycode,
td.paid,
td.timeoffrequestid,
ud.name AS agentname,
ud.managerid,
ud.managername,
td.isfulldayrequest,
ad.name AS activitycodedesc,
COALESCE(NULLIF((ad.name)::text, ''::text), (ad.category)::text) AS activitycodename
FROM ((timeoffdata td
LEFT JOIN vwuserdetail ud ON (((ud.id)::text = (td.userid)::text)))
LEFT JOIN activitycodedetails ad ON ((((ad.businessunitid)::text = (td.buid)::text) AND ((ad.id)::text = (td.activitycode)::text))))
)| Name | Type | Default | Nullable | Children | Parents | Comment |
|---|---|---|---|---|---|---|
| userid | varchar(50) | true | User GUID | |||
| buid | varchar(50) | true | Business Unit GUID | |||
| scheduleid | varchar(50) | true | Schedule GUID | |||
| businessunitdate | timestamp without time zone | true | Business Unit Date | |||
| length | integer | true | Length of Time Off | |||
| description | varchar(200) | true | Description of Time Off | |||
| activitycode | varchar(50) | true | Activity Code GUID | |||
| paid | bit(1) | true | Paid Time Off | |||
| timeoffrequestid | varchar(50) | true | Time Off Request GUID | |||
| agentname | varchar(200) | true | Agent Name | |||
| managerid | varchar(50) | true | Manager GUID | |||
| managername | varchar(200) | true | Manager Name | |||
| isfulldayrequest | bit(1) | true | Is Full Day Request | |||
| activitycodedesc | varchar(50) | true | Description of the activity code | |||
| activitycodename | text | true | Name of the activity code |
| Name | Columns | Comment | Type |
|---|---|---|---|
| public.timeoffdata | 12 | BASE TABLE | |
| public.vwuserdetail | 18 | User details with manager and division context | VIEW |
| public.activitycodedetails | 11 | Activity Code Lookup Data | BASE TABLE |