RPTDEF - Retriever Report Definitions

Jul 30, 2026

-- RPTDEF — Retriever (Report Builder) definitions.
-- One row per saved report. DEFINITION holds the JSON query AST (tables,
-- joins, columns, filters, grouping, chart, output); shape is owned by the
-- web app and versioned inside the JSON itself, e.g. {"v":1,...}.
-- SHARED = 'Y' makes the report visible/runnable by other users (required
-- for any report referenced by a schedule); 'N' = owner only.
-- SOURCE = 'K' for K3S-shipped library reports (copy-on-edit), 'U' = user.
-- ACTIVE = soft delete: UI delete sets 'N' (schedules and audit history
-- survive; hard delete is an admin/purge action).
-- CATEGORY groups the library screen ('Inventory', 'Service Level', ...).
-- Runs in each install's XXX_5WEB library (K3S_5WEB shown, per docs convention).

SET SCHEMA XXX_5WEB;

CREATE TABLE RPTDEF (
	REPORT_ID INTEGER GENERATED ALWAYS AS IDENTITY
		( START WITH 1 INCREMENT BY 1 ) NOT NULL ,
	USER_ID INTEGER NOT NULL ,
	RPTNAME VARCHAR(100) CCSID 37 NOT NULL ,
	DESCRIPTION FOR COLUMN DESCR VARCHAR(500) CCSID 37 NOT NULL DEFAULT '' ,
	SHARED CHAR(1) CCSID 37 NOT NULL DEFAULT 'N' ,
	SOURCE CHAR(1) CCSID 37 NOT NULL DEFAULT 'U' ,
	ACTIVE CHAR(1) CCSID 37 NOT NULL DEFAULT 'Y' ,
	CATEGORY VARCHAR(50) CCSID 37 NOT NULL DEFAULT '' ,
	DEFINITION CLOB(1M) CCSID 37 NOT NULL ,
	BIRTH TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
	LASTUPDATE FOR COLUMN LASTU00001 TIMESTAMP GENERATED ALWAYS
		FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP NOT NULL ,
	PRIMARY KEY ( REPORT_ID ) )

	RCDFMT RPTDEF ;

LABEL ON TABLE RPTDEF
	IS 'Web - Retriever Report Definitions' ;

---