Skip to main content

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).

Screen catalog​

ObjectTypeTitle / roleEvidence
w_notes_viewSheet 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_displayResponse dialog"Notes" — simplified read-only display of member notes with an Edit button that opens w_notes_viewsvclog/w_notes_display
w_notes_key_selectResponse dialog"Select Keywords" — assign one or more keywords to a notesvclog/w_notes_key_select
w_notes_key_maintenanceSheet window"Notes Key Maintenance" — add/modify/delete keyword codessvclog/w_notes_key_maintenance
w_notes_entity_selectResponse dialog"Select Entity" — when several entities on a screen have notes, pick which one to viewsvclog/w_notes_entity_select
w_notes_member_selectResponse dialog"Select Member" — disambiguation list when a member search matches severalsvclog/w_notes_member_select
w_notes_group_selectResponse dialogGroup disambiguation listsvclog/w_notes_group_select
w_notes_provider_selectResponse dialogProvider disambiguation listsvclog/w_notes_provider_select
w_notes_vendor_selectResponse dialogVendor disambiguation listsvclog/w_notes_vendor_select
w_notes_hcn_selectResponse dialogHCN disambiguation listsvclog/w_notes_hcn_select
m_notes_viewMenuMenu for w_notes_view (Save, Print, Add, Delete on toolbar)svclog/m_notes_view
m_notes_key_maintenanceMenuMenu for keyword maintenance (Save, Print, Add, Delete)svclog/m_notes_key_maintenance
u_notes_searchUser object (DataWindow)Hosts the per-entity search DataWindow; builds and runs entity searchessvclog/u_notes_search
n_notesUser object (non-visual)Embedded notes service: count notes per entity, set status-bar icon, auto-open VIP notes, open the viewersvclog/n_notes
d_notes_bodyDataWindowThe notes list/editor in w_notes_view (date, user, VIP checkbox, text, Keys button)svclog/d_notes_body
d_notes_displayDataWindowRead-only member notes with author display name; bold when VIPsvclog/d_notes_display
d_notes_search_member / _provider / _group / _vendor / _hcn / _um / _director / _caseExternal DataWindowsPer-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_selectDataWindowsResult lists behind the disambiguation dialogssvclog/d_notes_*_select
d_notes_keyDataWindowKeyword list (NOTE_KEY_CODES), used both as the search filter list and in keyword assignmentsvclog/d_notes_key
d_notes_key_maintenanceDataWindowUpdateable keyword list (key text + key type drop-down)svclog/d_notes_key_maintenance
d_notes_system_keysDataWindowKey codes where NOTE_KEY_TYPE_CD = 'SYSTEM' (protected keys)svclog/d_notes_system_keys
d_notes_countDataWindowShell query used for dynamically built count(*) searchessvclog/d_notes_count
d_notes_select_entity / dddw_notes_entitiesDataWindowsEntity-type drop-down (from NOTE_ENTITIES)svclog/d_notes_select_entity, svclog/dddw_notes_entities
dddw_dep_selectDrop-down DataWindowFamily members (dependents) of a subscriber, for the member search "Name" drop-downsvclog/dddw_dep_select
dddw_notes_key_typeDrop-down DataWindowKey types: (none) plus CODES rows with CODE_KEY = 'NOTEKEYTYP'svclog/dddw_notes_key_type
str_note_keys / str_notes_parmsStructuresKeyword set per note; entity list passed to the entity choosersvclog/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 — see Open questions.

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)

Categorization code tables​

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

TableRoleEvidence
NOTE_KEY_CODESKeyword catalog: note_key_code_sys_key, key_text (20 chars), note_key_type_cdsvclog/d_notes_key, svclog/d_notes_key_maintenance
NOTE_KEYSMany-to-many link note ↔ keywordsvclog/w_notes_view pfc_save
NOTE_ENTITIESEntity-type catalog: entity_type_sys_key, entity_type (20 chars)svclog/dddw_notes_entities
CODES (CODE_KEY = 'NOTEKEYTYP')Keyword types; SYSTEM type marks protected keyssvclog/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).

  • 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.
  • 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. The domain brief expects call intake → follow-up → resolution, but notes carry no status, owner, callback date, or resolution fields — categorization is only via keywords. Is follow-up tracked purely by convention (keywords/text), or in another module (e.g. UM follow-up, um_main/w_um_follow_up)?
  2. Actual keyword catalog. Keyword values are data (NOTE_KEY_CODES); only (all), (none), UR, CM, WANG are referenced in code. Extract the live table to enumerate the real service-log categories before migration.
  3. Key types. What values does CODES hold for NOTEKEYTYP besides SYSTEM, and what behavior (if any) attaches to the others?
  4. WANG system notes. Commented code and the SYSTEM-key rules suggest notes imported from a legacy WANG system are locked read-only (svclog/w_notes_view, svclog/w_notes_key_select commented blocks). Confirm whether these archives must be carried forward.
  5. Note deletion semantics. pfc_deleterow removes the row and menu Delete is toolbar-enabled (svclog/m_notes_view), yet compliance-wise notes deletion may need restriction/auditing — confirm intended policy (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.