Redbook Data Dictionary

Complete field reference for the REDBOOK.1287 UniData table, which stores RFC (Request for Change) quality tracking records.

Overview

Property	Value
Table Name	REDBOOK.1287
Database	UniData (AFTEC ERP)
Key Format	`{CO}!{RFCNO}` (e.g., “1!12345”)
Record Count	~50,000+
Primary Use	Quality issue tracking

Key Structure

Key: {CompanyCode}!{RFCNumber}
Example: 1!12345

Components:
- CompanyCode: Always "1" for PSI
- RFCNumber: Sequential integer, auto-generated

Field Reference

Core Identification Fields

Field #	Name	Type	Length	Description
Key	ID	String	-	Primary key: `{CO}!{RFCNO}`
1	PROJECT	String	MV	Associated project/job number
3	EMP.ID	String	3	Creator’s employee initials (samAccountName)
4	CREATION.DATE	Date	D2/	Date RFC was created

Problem Description Fields

Field #	Name	Type	Length	Description
5	DWG.REFERENCE	String	-	Drawing number reference
6	PROBLEM.DESC	String	200	Problem description text
23	PROBLEM.TYPE	String	1	RFC type code (see lookup below)
36	PRIORITY	String	1	Priority level

Status & Resolution Fields

Field #	Name	Type	Length	Description
7	COMPLETED	String	1	”Y” = closed, "" = open
8	COMPLETED.BY	String	3	Employee who closed the RFC
9	COMPLETED.DATE	Date	D2/	Date RFC was closed
10	SOLUTION	String	200	Resolution/solution text
15	DELETED	String	1	”Y” = soft deleted

Assignment Fields

Field #	Name	Type	Length	Description
13	ASSIGN.TO	String	3	Currently assigned employee
14	ASSIGN.DATE	Date	D2/	Date of assignment
34	DUE.DATE	Date	D2/	Target completion date

Department Status Fields (Multi-Value)

These four fields work together to track department-level status. Each field contains up to 16 values corresponding to the 16 departments.

Field #	Name	Type	Description
17	DEPT.COMPLETED	String MV	”Y” if department completed work
18	DEPT.ASSIGNED	String MV	”X” if department is assigned
19	DEPT.COMPLETEDBY	String MV	Employee who completed (per dept)
20	DEPT.COMPLETEDATE	Date MV	Completion date (per dept)

Corrective Action Fields

Used when PROBLEM.TYPE = “4” (Corrective Action Required):

Field #	Name	Type	Length	Description
21	AUDIT.TYPE	String	-	Type of audit
22	AUDIT.CAUSE	String	250	Root cause analysis
31	CONTAINMENT	String	200	Containment action taken
32	PREVENTATIVE	String	200	Preventative action
33	VERIFICATION	String	200	Verification method

Linked Records (Multi-Value)

Field #	Name	Type	Description
11	CHANGE.NO	String MV	Linked ECN numbers
37	NCN.NO	String MV	Linked NCN numbers

Activity & Notes

Field #	Name	Type	Length	Description
35	ACTIVITY	String	200	Activity log/notes

Department Position Mapping

The multi-value fields (17, 18, 19, 20) use positions 0-15 to represent 16 departments:

Index	Position	Dept Code	Department Name
0	1	150	Purchasing
1	2	160	Receiving/Stock (RCV/STK)
2	3	170	Quality
3	4	115	Proposal
4	5	140	Sales
5	6	110E	Electrical Engineering
6	7	110M	Mechanical Engineering
7	8	102	Machine/Weld
8	9	130	Process Services
9	10	-	Process Engineering
10	11	106	Mechanical Assembly
11	12	108	Electrical Assembly
12	13	120	Customer Service
13	14	180	Human Resources
14	15	180	Accounting
15	16	160Z	Production Planning

Department Status Logic

For each department position (0-15):

Status = NOT_ASSIGNED  if DEPT.ASSIGNED<pos> = ""
Status = ASSIGNED      if DEPT.ASSIGNED<pos> = "X" AND DEPT.COMPLETED<pos> = ""
Status = COMPLETED     if DEPT.COMPLETED<pos> = "Y"

When COMPLETED:
  - DEPT.COMPLETEDBY<pos> = Employee initials
  - DEPT.COMPLETEDATE<pos> = Completion date

Lookup Values

Problem Type (Field 23)

Code	Description
1	Design Issue
2	Manufacturing Issue
3	Supplier Issue
4	Corrective Action Required
5	Customer Request
6	Other

Priority (Field 36)

Code	Description
1	Low
2	Medium
3	High
4	Critical

Date Format

UniData stores dates as integer days since December 31, 1967.

Conversion Formula:
  UniData Date â†’ .NET DateTime:
    new DateTime(1967, 12, 31).AddDays(uniDataValue)

  .NET DateTime â†’ UniData Date:
    (dateTime - new DateTime(1967, 12, 31)).Days

Examples

UniData Value	Actual Date
0	1967-12-31
10000	1995-05-18
20000	2022-09-03
20850	2025-02-04

UniData Subroutines

VB_REDBOOK.REV1 - Save/Delete Operations

Location: S:\LinuxShare\pro3prog\VBBASE\VB_REDBOOK.REV1

SUBROUTINE VB_REDBOOK(CO, REQTYPE, RFCNO, RREC, ADDFLAG, MESSAGE)

Arguments:
  1. CO        (String, IN)     - Company code (always "1")
  2. REQTYPE   (String, IN)     - "DELETE" for delete, "" for save
  3. RFCNO     (String, IN)     - RFC number
  4. RREC      (UniDynArray, IN) - Record data (50 fields, AM-delimited)
  5. ADDFLAG   (Integer, IN)    - 1 = Add new, 0 = Update existing
  6. MESSAGE   (String, OUT)    - Result: "SAVED" or "DELETED"

IMPORTANT: Does NOT support GET/READ operations.
For reading, use direct READ from REDBOOK.1287 table.

VB_REDBOOKLIST - Search Operations

Location: S:\LinuxShare\pro3prog\VBBASE\VB_REDBOOKLIST

SUBROUTINE VB_REDBOOKLIST(SQL, DATA, MESSAGE)

Arguments:
  1. SQL     (String, IN)      - SSELECT statement to execute
  2. DATA    (UniDynArray, OUT) - Results array
  3. MESSAGE (String, OUT)     - Error message (empty on success)

Output DATA structure:
  DATA<1,n> = RFC Number (extracted from ID after "!")
  DATA<2,n> = Project Number (field 1)
  DATA<3,n> = Problem Description (field 6)
  DATA<4,n> = Complete Status (field 8)

Access Patterns

Read Single RFC

// Direct READ - VB_REDBOOK.REV1 doesn't support GET
var key = $"1!{rfcNumber}";
var record = session.Read("REDBOOK.1287", key);

Search RFCs

// Use VB_REDBOOKLIST subroutine
var sql = "SSELECT REDBOOK.1287 WITH PROJECT = '95188'";
session.CallSubroutine("VB_REDBOOKLIST", sql, out data, out message);

Create RFC

// VB_REDBOOK.REV1 with ADDFLAG = 1
session.CallSubroutine("VB_REDBOOK", "1", "", newRfcNo, recordData, 1, out message);
// message = "SAVED"

Update RFC

// VB_REDBOOK.REV1 with ADDFLAG = 0
session.CallSubroutine("VB_REDBOOK", "1", "", rfcNo, recordData, 0, out message);
// message = "SAVED"

Delete RFC (Soft Delete)

// VB_REDBOOK.REV1 with REQTYPE = "DELETE"
session.CallSubroutine("VB_REDBOOK", "1", "DELETE", rfcNo, null, 0, out message);
// message = "DELETED"
// Sets field 15 (DELETED) = "Y"

C# Model Mapping

public class RedbookEntry
{
    // Key
    public string RfcNumber { get; set; }           // From key after "!"
 
    // Core fields
    public string ProjectNumber { get; set; }        // Field 1
    public string CreatedBy { get; set; }            // Field 3
    public DateTime? CreatedDate { get; set; }       // Field 4
    public string DrawingReference { get; set; }     // Field 5
    public string ProblemDescription { get; set; }   // Field 6
 
    // Status
    public bool IsCompleted { get; set; }            // Field 7 = "Y"
    public string CompletedBy { get; set; }          // Field 8
    public DateTime? CompletedDate { get; set; }     // Field 9
    public string Solution { get; set; }             // Field 10
    public bool IsDeleted { get; set; }              // Field 15 = "Y"
 
    // Assignment
    public string AssignedTo { get; set; }           // Field 13
    public DateTime? AssignedDate { get; set; }      // Field 14
    public DateTime? DueDate { get; set; }           // Field 34
 
    // Classification
    public string ProblemType { get; set; }          // Field 23
    public string Priority { get; set; }             // Field 36
 
    // Corrective Action (when ProblemType = "4")
    public string AuditType { get; set; }            // Field 21
    public string RootCause { get; set; }            // Field 22
    public string Containment { get; set; }          // Field 31
    public string PreventativeAction { get; set; }   // Field 32
    public string Verification { get; set; }         // Field 33
 
    // Activity
    public string ActivityLog { get; set; }          // Field 35
 
    // Linked records (multi-value)
    public List<string> EcnNumbers { get; set; }     // Field 11
    public List<string> NcnNumbers { get; set; }     // Field 37
 
    // Department statuses (16 departments)
    public Dictionary<string, DepartmentStatus> DepartmentStatuses { get; set; }
}
 
public class DepartmentStatus
{
    public string DepartmentCode { get; set; }
    public string DepartmentName { get; set; }
    public bool IsAssigned { get; set; }
    public bool IsCompleted { get; set; }
    public string CompletedBy { get; set; }
    public DateTime? CompletedDate { get; set; }
}

TypeScript Interface

interface RedbookEntry {
  rfcNumber: string;
  projectNumber: string;
  createdBy: string;
  createdDate: string | null;  // ISO date string
  drawingReference: string;
  problemDescription: string;
 
  isCompleted: boolean;
  completedBy: string | null;
  completedDate: string | null;
  solution: string;
  isDeleted: boolean;
 
  assignedTo: string | null;
  assignedDate: string | null;
  dueDate: string | null;
 
  problemType: ProblemType;
  priority: Priority;
 
  // Corrective Action fields
  auditType: string | null;
  rootCause: string | null;
  containment: string | null;
  preventativeAction: string | null;
  verification: string | null;
 
  activityLog: string;
 
  ecnNumbers: string[];
  ncnNumbers: string[];
 
  departmentStatuses: Record<DepartmentCode, DepartmentStatus>;
}
 
type ProblemType = '1' | '2' | '3' | '4' | '5' | '6';
type Priority = '1' | '2' | '3' | '4';
 
type DepartmentCode =
  | '150'   // Purchasing
  | '160'   // RCV/STK
  | '170'   // Quality
  | '115'   // Proposal
  | '140'   // Sales
  | '110E'  // Electrical Engineering
  | '110M'  // Mechanical Engineering
  | '102'   // Machine/Weld
  | '130'   // Process Services
  | 'PE'    // Process Engineering
  | '106'   // Mechanical Assembly
  | '108'   // Electrical Assembly
  | '120'   // Customer Service
  | '180HR' // Human Resources
  | '180AC' // Accounting
  | '160Z'; // Production Planning
 
interface DepartmentStatus {
  departmentCode: DepartmentCode;
  departmentName: string;
  isAssigned: boolean;
  isCompleted: boolean;
  completedBy: string | null;
  completedDate: string | null;
}

Table	Relationship	Description
PROJECT.1287	PROJECT field	Job/project master data
EMPLOYEE.PUBLIC.1287	EMP.ID, ASSIGN.TO, etc.	Employee lookup by initials
ECN.1287	CHANGE.NO field	Engineering Change Notices
NCN.1287	NCN.NO field	Non-Conformance Notices

Business Rules

RFC Creation

PROJECT is required
PROBLEM.DESC is required (min 3 characters)
At least one department must be assigned
CREATION.DATE auto-set to current date
EMP.ID auto-set to current user’s initials

RFC Completion

All assigned departments must be completed first
COMPLETED = “Y”, COMPLETED.BY, COMPLETED.DATE set together
SOLUTION should be provided

RFC Deletion

Requires “Computer Group” AD membership
Soft delete only (DELETED = “Y”)
Record remains in table for audit

Department Assignment

Multiple departments can be assigned
Department completion is independent
DEPT.COMPLETEDBY and DEPT.COMPLETEDATE set when marking complete

Quality Process - Redbook workflow documentation
PSI.UniData.API - API for accessing Redbook data
Agentic Implementation - Redbook Web conversion project
Redbook Analysis - Analytics dashboard project

Last updated: February 2026*

PSI Knowledge Base

Explorer

redbook-schema