Customer Service Log (Notes)

Source library: svclog (10 windows, 26 DataWindows, 2 user objects, 2 structures, 2 menus)

Purpose

The service log is implemented as a generic Notes subsystem: free-text, keyword-categorized, timestamped notes attached to any of eight business entity types — member, provider, group, vendor, health-care network (HCN), utilization-management (UM) entity, medical director, and UM case (svclog/n_notes of_build_sys_key; svclog/dddw_notes_entities reading NOTE_ENTITIES). Customer-service staff use it to log and retrieve contacts/observations about these entities; other modules embed it so that a caller's history surfaces automatically wherever the entity is being worked (status-bar notes indicator, auto-popup of VIP notes) (svclog/n_notes of_check_notes).

Notes carry a VIP flag (urgent/priority notes sort first, display bold, and auto-open a window when the entity is loaded) and one or more keywords (note keys) that serve as the categorization/reason coding of the log (svclog/d_notes_body, svclog/d_notes_display, svclog/n_notes, svclog/w_notes_key_select).

The official user guide confirms this reading: "Notes provide users with the ability to record any and all information that cannot otherwise be saved in the system. Notes can be used, among other things, to produce a customer service log with which to track customer service requests." The module is a general-purpose notes facility, not a structured call-ticket system; the customer-service log is one use of it, achieved by convention through keywords (User Guide ch. 9). The guide describes VIP note text as always displayed with a yellow background; the extracted display DataWindows render VIP rows bold (svclog/d_notes_display) — a cosmetic difference only.

Screen catalog

Object	Type	Title / role	Evidence
w_notes_view	Sheet window	"Notes" — principal window: entity selector, per-entity search panel, keyword filter, date range, notes body (view/add/edit/delete/print)	svclog/w_notes_view
w_notes_display	Response dialog	"Notes" — simplified read-only display of member notes with an Edit button that opens w_notes_view	svclog/w_notes_display
w_notes_key_select	Response dialog	"Select Keywords" — assign one or more keywords to a note	svclog/w_notes_key_select
w_notes_key_maintenance	Sheet window	"Notes Key Maintenance" — add/modify/delete keyword codes	svclog/w_notes_key_maintenance
w_notes_entity_select	Response dialog	"Select Entity" — when several entities on a screen have notes, pick which one to view	svclog/w_notes_entity_select
w_notes_member_select	Response dialog	"Select Member" — disambiguation list when a member search matches several	svclog/w_notes_member_select
w_notes_group_select	Response dialog	Group disambiguation list	svclog/w_notes_group_select
w_notes_provider_select	Response dialog	Provider disambiguation list	svclog/w_notes_provider_select
w_notes_vendor_select	Response dialog	Vendor disambiguation list	svclog/w_notes_vendor_select
w_notes_hcn_select	Response dialog	HCN disambiguation list	svclog/w_notes_hcn_select
m_notes_view	Menu	Menu for w_notes_view (Save, Print, Add, Delete on toolbar)	svclog/m_notes_view
m_notes_key_maintenance	Menu	Menu for keyword maintenance (Save, Print, Add, Delete)	svclog/m_notes_key_maintenance
u_notes_search	User object (DataWindow)	Hosts the per-entity search DataWindow; builds and runs entity searches	svclog/u_notes_search
n_notes	User object (non-visual)	Embedded notes service: count notes per entity, set status-bar icon, auto-open VIP notes, open the viewer	svclog/n_notes
d_notes_body	DataWindow	The notes list/editor in w_notes_view (date, user, VIP checkbox, text, Keys button)	svclog/d_notes_body
d_notes_display	DataWindow	Read-only member notes with author display name; bold when VIP	svclog/d_notes_display
d_notes_search_member / _provider / _group / _vendor / _hcn / _um / _director / _case	External DataWindows	Per-entity search criteria panels (each with Search/Clear buttons)	svclog/d_notes_search_*
d_notes_member_select / _provider_select / _group_select / _vendor_select / _hcn_select / _director_select / _cases_select	DataWindows	Result lists behind the disambiguation dialogs	svclog/d_notes_*_select
d_notes_key	DataWindow	Keyword list (NOTE_KEY_CODES), used both as the search filter list and in keyword assignment	svclog/d_notes_key
d_notes_key_maintenance	DataWindow	Updateable keyword list (key text + key type drop-down)	svclog/d_notes_key_maintenance
d_notes_system_keys	DataWindow	Key codes where NOTE_KEY_TYPE_CD = 'SYSTEM' (protected keys)	svclog/d_notes_system_keys
d_notes_count	DataWindow	Shell query used for dynamically built count(*) searches	svclog/d_notes_count
d_notes_select_entity / dddw_notes_entities	DataWindows	Entity-type drop-down (from NOTE_ENTITIES)	svclog/d_notes_select_entity, svclog/dddw_notes_entities
dddw_dep_select	Drop-down DataWindow	Family members (dependents) of a subscriber, for the member search "Name" drop-down	svclog/dddw_dep_select
dddw_notes_key_type	Drop-down DataWindow	Key types: (none) plus CODES rows with CODE_KEY = 'NOTEKEYTYP'	svclog/dddw_notes_key_type
str_note_keys / str_notes_parms	Structures	Keyword set per note; entity list passed to the entity chooser	svclog/str_note_keys, svclog/str_notes_parms

Workflow

Intake and categorization

• The notes window opens either standalone from the frame menu (Administrative > Client Services > Notes Maint, passing entity mode MENU) or in context from an entity screen with the entity pre-selected and its search panel pre-filled (homersys/m_sys_frame.srm m_notesmaint.clicked; svclog/w_notes_view open; svclog/u_notes_search of_parse_and_set).
• The user picks an entity type, searches for the specific entity, optionally narrows by keywords and a start/end date range, and retrieves the notes (svclog/w_notes_view, svclog/u_notes_search, svclog/d_notes_body).
• Add creates a note stamped with the current user and timestamp; the Keys button on each note opens the keyword picker (categorization); Save persists notes and their keyword links together (svclog/w_notes_view dw_notes_body.pfc_addrow, buttonclicked, pfc_save).

Surfacing history (follow-up)

• Roughly twenty windows across provider, enrollment, UM/CM/UR, COBRA, client, moon and other libraries instantiate n_notes to check for notes on the entity being displayed; VIP notes open the notes window immediately, otherwise a status-bar icon signals that history exists (svclog/n_notes of_check_notes, of_set_notes/of_set_vip_notes; callers include provider/w_provider_maintenance, enroll/w_enrollment_maintenance, um_cm/w_um_case_management, cobra/w_cobra_administration, client/w_client_maintenance).
• of_display_notes shows a quick read-only member-notes popup with an Edit hand-off into the full viewer (svclog/n_notes, svclog/w_notes_display).

No explicit follow-up/resolution status field (open/closed, owner, due date) exists on notes. The user guide confirms this is by design: follow-up is tracked by convention (keywords and note text), and a call-statistics report ("number of calls during a specified period, by type, category, and user") plus a phone log / telephone log were listed only as Future Enhancements (User Guide ch. 9) — see Open questions.

Operational policy (User Guide ch. 9)

• No claim-level notes — by design. Claims are deliberately not a notes entity. Information pertaining to a single claim must be recorded with the Claims Message function, not in a note attached to one of the claim's entities (User Guide ch. 9).
• Notes are permanent once saved. "Once saved, notes may not be deleted from the system. The keywords and the VIP indicator may be changed for any note at any time." The guide also documents an editing quirk: after saving a new note the user can appear to type over or cut its text, but those modifications are not saved (clicking another note clears the quirk) (User Guide ch. 9). See the discrepancy note under Open questions — the extracted code exposes a Delete action on the notes list.
• Keyword governance. Users are encouraged to assign keywords as precisely as possible so notes can be reported on across entities. The keyword catalog is centrally maintained: if a desired keyword does not exist, users contact the MIS department to have it added, and are explicitly told not to "fudge" keywords by using an inappropriate one (User Guide ch. 9).
• Multiple VIP entities. If several entities on one window each have VIP notes, the system displays notes for each of those entities (User Guide ch. 9).

Requirements

• SVC-1 (Entity-attached notes). A note must be attached to exactly one entity occurrence, identified by entity type (NOTE_ENTITIES) plus a tilde-terminated entity key string (e.g. member sys key, group id, vendor sys key, case sys key). (svclog/d_notes_body, svclog/n_notes of_build_sys_key)
• SVC-2 (Supported entity types). The system must support notes for MEMBER, PROVIDER, GROUP, VENDOR, HCN, UM, DIRECTOR, and CASE entity types, with the available list driven by the NOTE_ENTITIES table. (svclog/n_notes, svclog/w_notes_view open, svclog/dddw_notes_entities)
• SVC-3 (Note content and audit). A note consists of free text (up to 32,000 characters in the editor), a VIP indicator, creation user and timestamp, and update user/timestamp columns. New notes are stamped with the logged-in user and current date/time and receive a system-generated key from a sequence. (svclog/d_notes_body note_text edit limit; svclog/d_notes_display; svclog/w_notes_view dw_notes_body.pfc_addrow)
• SVC-4 (Keyword categorization, mandatory). Every note must carry at least one keyword. New notes default to the (none) keyword; a save that would leave any note without keywords must roll back with an error. In the keyword picker, at least one selection is required. (svclog/w_notes_view pfc_addrow, pfc_save key-count check; svclog/w_notes_key_select cb_ok.clicked)
• SVC-5 (Keyword rewrite on save). Saving rewrites a note's keyword links: existing NOTE_KEYS rows for each displayed note are deleted before update and re-inserted from the edited keyword sets, in the same transaction as the note update, with rollback on any failure. (svclog/w_notes_view pfc_preupdate, pfc_save)
• SVC-6 (Keyword-filtered retrieval). Retrieval must filter notes by the selected keywords; selecting none, or selecting the (all) keyword, means all keywords. Retrieval must also honor an optional creation-date range (blank dates default to an open range; end date is inclusive through end of day). (svclog/w_notes_view dw_notes_body.pfc_retrieve; svclog/d_notes_body retrieval arguments and WHERE clause)
• SVC-7 (Ordering). Notes lists must sort VIP notes first, then newest first. (svclog/d_notes_body sort vip_ind D create_dt D create_user_id A; svclog/d_notes_display sort)
• SVC-8 (VIP surfacing). When an entity with VIP notes is loaded by a hosting screen, the notes window must open automatically (unless the caller suppresses it via parameter); entities with any notes set a "notes found" indicator, VIP notes a distinct "VIP notes" indicator, on the application status bar. (svclog/n_notes of_check_notes, of_set_notes, of_set_vip_notes)
• SVC-9 (Multi-entity choice). When the hosting screen has tracked notes for more than one entity, opening notes must first present the list of entities (type plus resolved display name) for the user to choose from. (svclog/n_notes of_show_notes; svclog/w_notes_entity_select)
• SVC-10 (Per-entity search). The search panel must adapt to the entity type: member (SSN, group id, last name, member id, group name, dependent-name drop-down), provider (TIN with EIN/SSN type, company/look-up name), group (id, name), vendor (TIN with type, name), HCN (name), UM (entity drop-down), medical director (last name), case (case sys key). At least one criterion is required, otherwise the user is told to enter a search parameter; a search with no match reports "not found"; multiple matches open the corresponding selection dialog. (svclog/u_notes_search of_set_dw_entity, ue_enter, of_none_found; svclog/d_notes_search_*; svclog/w_notes_*_select)
• SVC-11 (Search security). Member, group, and case searches must be restricted to groups the user is authorized for (existence check against security_user_group / security_user_class_user); provider, vendor, and HCN searches are restricted to the user's client segment preference. (svclog/u_notes_search ue_enter SQL for MEMBER/GROUP/CASE/PROVIDER/VENDOR/HCN)
• SVC-12 (CASE/UM restriction). Users who are not in the UR/CM or develop security groups must not see the CASE and UM entity types, CASE/UM notes in the read-only display, or the UR/CM keyword filters. (svclog/w_notes_view open entity filter and dw_notes_body.pfc_retrieve key filter; svclog/w_notes_display ue_filter; svclog/w_notes_entity_select open)
• SVC-13 (System/protected keywords). Keyword codes typed SYSTEM are protected: they cannot be manually assigned in the keyword picker, and a note carrying a system keyword is display-only and cannot be deleted. (svclog/w_notes_key_select dw_keys.clicked; svclog/w_notes_view wf_issystem, dw_notes_body.rowfocuschanged; svclog/d_notes_system_keys)
• SVC-14 (Editing safeguards). Switching entity type, clearing, or launching a new search while unsaved changes exist must prompt to save (Yes/No/Cancel). Deleting a note removes it from the list and re-sequences its keyword bookkeeping. (svclog/w_notes_view ue_check_save, dw_note_entity.itemchanged, dw_notes_body.pfc_deleterow; svclog/u_notes_search buttonclicked)
• SVC-15 (Printing). The retrieved notes list can be printed. (svclog/w_notes_view pfc_print; svclog/m_notes_view)
• SVC-16 (Keyword maintenance). Authorized users maintain the keyword catalog — add (key from sequence), modify text, assign a key type from the NOTEKEYTYP code set (or (none)), delete — from a dedicated screen reached via Administrative > Client Services > Note Keys. (svclog/w_notes_key_maintenance, svclog/d_notes_key_maintenance, svclog/dddw_notes_key_type, homersys/m_sys_frame.srm m_notekeys)
• SVC-17 (Read-only quick view). A read-only member-notes popup shows each note's timestamp and author display name (resolved via SECURITY_USERS, falling back to the raw user id), renders VIP notes bold, applies the CASE/UM security filter, closes itself when there is nothing viewable, and offers Edit to open the full notes window. (svclog/w_notes_display, svclog/d_notes_display, svclog/n_notes of_display_notes)
• SVC-18 (No claim-level notes). Notes must not be attachable to claims; information pertaining to a single claim is recorded via the Claims Message function instead. Claims screens therefore do not host the notes service for the claim itself, only for its entities. (User Guide ch. 9; corroborated by the absence of a claim entity in NOTE_ENTITIES handling, svclog/n_notes)
• SVC-19 (Note permanence). Once a note has been saved it must not be deletable, and its text must not be modifiable; only its keywords and VIP indicator may be changed afterwards, at any time. Deletion is permitted only for notes not yet saved. (User Guide ch. 9) — see the discrepancy recorded in Open questions before treating this as settled behavior.
• SVC-20 (Central keyword catalog governance). The keyword catalog must be centrally administered (in HOMER practice, by the MIS department via the Notes Key Maintenance screen); end users request new keywords rather than creating them, and are instructed to categorize precisely rather than reuse an inappropriate keyword. (User Guide ch. 9; maintenance screen: svclog/w_notes_key_maintenance)

Categorization code tables

Call/notes categorization is data-driven; the code values themselves live in the database, not in source:

Table	Role	Evidence
NOTE_KEY_CODES	Keyword catalog: note_key_code_sys_key, key_text (20 chars), note_key_type_cd	svclog/d_notes_key, svclog/d_notes_key_maintenance
NOTE_KEYS	Many-to-many link note ↔ keyword	svclog/w_notes_view pfc_save
NOTE_ENTITIES	Entity-type catalog: entity_type_sys_key, entity_type (20 chars)	svclog/dddw_notes_entities
CODES (CODE_KEY = 'NOTEKEYTYP')	Keyword types; SYSTEM type marks protected keys	svclog/dddw_notes_key_type, svclog/d_notes_system_keys

Keyword values referenced by name in code (therefore expected to exist as data): (all), (none), UR, CM, and the legacy system key WANG (svclog/w_notes_view dw_notes_body.pfc_retrieve and commented code; svclog/u_notes_search).

The user guide enumerates the keyword suite in use at the time it was written (c. 2000): Enrollment, UR, CM, Stoploss, Claims, Payments, COBRA, COB, Subrogation, Coverage, Deposits, Checks, Accounts, Billing, Commissions, Miscellaneous, WANG, Panel (Provider), Contracts (Provider), Plan Of Benefits, Case Summary, Case Detail, Plan of Treatment (User Guide ch. 9). The live NOTE_KEY_CODES table should still be extracted to capture keywords added since, but this list confirms the intended taxonomy of the service log.

Retrieval semantics per the guide: selecting no keywords retrieves all notes for the entity; selecting keywords narrows to notes carrying them; selecting (none) retrieves only uncategorized notes (i.e., notes carrying only the default (none) key) (User Guide ch. 9) — consistent with SVC-4/SVC-6, where (none) is itself a real keyword assigned by default.

Links to members, groups, claims

• Members — notes keyed by member sys key; member search joins MEMBERS, FAMILY (subscriber/dependent), GROUPS; a dependent drop-down lists the family (svclog/u_notes_search MEMBER case, svclog/dddw_dep_select, svclog/d_notes_member_select).
• Groups — notes keyed by group id; group search honors group security (svclog/u_notes_search GROUP case, svclog/d_notes_search_group).
• Providers / vendors / HCNs / medical directors / UM entities / UM cases — keyed by their respective sys keys (svclog/u_notes_search, svclog/d_notes_search_*).
• Claims — no direct claim-level notes exist in this library; the nearest link is the UM CASE entity (UM_CASES, secured through the case's subscriber's group) (svclog/u_notes_search CASE handling). Claims screens are not among the notes entity types. The user guide confirms this is deliberate: claim-specific information belongs in the Claims Message function, and users are instructed not to put it in notes attached to the claim's entities (User Guide ch. 9).
• Enrollment — the enroll library also reads/writes NOTES/NOTE_KEYS (_re/analysis/schema.json used_by), so the notes tables are a shared contract, not private to this module.

Data

Written: NOTES (note_sys_key, vip_ind, note_text, entity_type_sys_key, entity_sys_key, create_dt, create_user_id, updt_dt, updt_user_id), NOTE_KEYS, NOTE_KEY_CODES (svclog/d_notes_body, svclog/w_notes_view, svclog/d_notes_key_maintenance). Note keys come from the shared Oracle sequence all_purpose_seq (svclog/w_notes_view pfc_addrow, svclog/w_notes_key_maintenance dw_keys.pfc_addrow).

Read: NOTE_ENTITIES, CODES, SECURITY_USERS, SECURITY_GROUPINGS, SECURITY_USER_GROUP, SECURITY_USER_CLASS_USER, MEMBERS, FAMILY, GROUPS, PROVIDERS, PROVIDER_TINS, PROVIDER_ADDRESSES, PM_ADDRESSES, VENDORS, HEALTH_CARE_NETWORKS, MEDICAL_DIRECTORS, UM_CASES, UM_ENTITIES (svclog/u_notes_search, svclog/w_notes_view, svclog/w_notes_entity_select, svclog/d_notes_*).

Migration notes

• Tilde-delimited entity keys. notes.entity_sys_key is a char(255) holding the entity's key followed by ~ (and matched with LIKE key || '~' for members) (svclog/n_notes of_build_sys_key, svclog/d_notes_display). Replace with a typed polymorphic association or per-entity link tables.
• Dynamically concatenated SQL. Entity searches build SQL strings from user input without parameter binding (svclog/u_notes_search ue_enter) — a SQL-injection and correctness risk to eliminate in migration.
• Security by string membership. Access rules hinge on hard-coded security group names UR/CM and develop and keyword texts UR/CM, duplicated in at least four objects (svclog/w_notes_view, svclog/w_notes_display, svclog/w_notes_entity_select, svclog/d_notes_body callers). Centralize as role/permission checks.
• Hard-coded schema and duplication. Many DataWindows qualify tables with the SFPIDBA schema owner (svclog/d_notes_body and others); the CASE/UM filter block is copy-pasted per entity case in w_notes_view.open. Normalize.
• Delete-and-reinsert keyword saves (svclog/w_notes_view pfc_preupdate / pfc_save) — replace with differential updates, preserving the "at least one key" invariant (SVC-4).
• Client-segment filter. Provider/vendor/HCN searches filter by the user's client segment preference (default SFPI, system/u_app_preference.sru) — carry this multi-client partitioning into the new design.
• Embedded service reuse. n_notes is consumed by 20+ windows across the application; the migration equivalent (notes badge/auto-popup on entity screens) is a cross-cutting component, not just a screen.
• No history versioning. Notes are edited in place; updt_dt/updt_user_id columns exist on NOTES but no code was found populating them in this library (svclog/d_notes_body update spec omits them) — decide whether edit history is a requirement.

Open questions

1. Follow-up / resolution model — RESOLVED. Follow-up is tracked purely by convention (keywords and free text). The user guide confirms notes carry no status/owner/callback model, and lists both a call-statistics report (calls by period, type, category, and user) and a phone/telephone log as unbuilt Future Enhancements. The telephone log was one branch of a wider voice-response (IVR) ambition — Verification of Eligibility, Plan of Benefits, Disclaimer, Provider Information, Refer to PPO and the log itself — none of which was built. UM users likewise asked for a way to pull all notes for a case into a standard format, edit that copy for outside readers (stop-loss carriers) and print it under consistent headings; svclog prints only the retrieved list (SVC-15) (User Guide ch. 9). Structured follow-up exists only in the UM module (um_main/w_um_follow_up). The migration should treat "call ticket workflow" as a new capability, not a re-implementation.
2. Actual keyword catalog — RESOLVED (as of the guide's writing). The guide enumerates the 23-keyword suite (see Categorization code tables above) (User Guide ch. 9). Still extract the live NOTE_KEY_CODES table to pick up keywords added after the guide was written.
3. Key types. What values does CODES hold for NOTEKEYTYP besides SYSTEM, and what behavior (if any) attaches to the others? (The guide does not mention key types at all.)
4. WANG system notes — RESOLVED. WANG is a keyword marking notes migrated from the pre-HOMER (Wang) system: the guide documents a transfer application at homerrun/checkout/axc/transfer_notes.pbl that converts notes "in the old Homer format" into the new format (User Guide ch. 9, "Transferring Notes From Old Homer Format To New Homer Format"). Combined with the SYSTEM-key rules (SVC-13), WANG-keyed notes are a read-only historical archive and should be carried forward as such.
5. Note deletion semantics — PARTIALLY RESOLVED, discrepancy for SME confirmation. The user guide (ch. 9) states "Once saved, notes may not be deleted from the system" (only keywords and the VIP flag remain changeable), with Delete intended for not-yet-saved notes — a compliance-friendly policy. The extracted code, however, enables Delete on the notes list and pfc_deleterow removes the row (svclog/w_notes_view, svclog/m_notes_view), blocking deletion only for system-keyed notes (SVC-13) — discrepancy for SME confirmation: verify whether saved-note deletion is actually blocked at runtime (trigger, later code path) or the guide states policy rather than enforcement. Code wins for observed behavior; the migration should enforce the stated policy explicitly (see also the HIPAA member notes report entry in homersys/m_sys_frame.srm, m_hipaamembernotesreport).
6. Case search dead ends. The CASE entity path contains a developer placeholder ("Whoops - see Dave") for the impossible multi-row case and a commented-out selection window (svclog/u_notes_search CASE handling; w_notes_cases_select does not exist as a window) — confirm CASE notes are still in use.
7. Update audit columns. updt_dt / updt_user_id are selected but not maintained by the extracted code — verify against database triggers before concluding they are dead.
8. d_notes_member_select base SQL joins MEMBERS to FAMILY without join predicates in the stored SELECT; the real predicates are injected at runtime by u_notes_search (svclog/d_notes_member_select, svclog/u_notes_search MEMBER case). Any reuse of the .srd SQL alone would be incorrect — flag for the migration team.
9. Medical director search criteria. The user guide (ch. 9) states the Director search offers Last Name, full Name, and Specialty criteria; the extracted search panel exposes last name only (svclog/d_notes_search_director, svclog/u_notes_search DIRECTOR case) — discrepancy for SME confirmation (likely a guide-era screen that was later simplified). Code wins for behavior.