CREATE TABLE IF NOT EXISTS `def_raci_signoff` (
`Guid` VARCHAR(64) NOT NULL,
`Package` VARCHAR(100) NOT NULL,
`Module` VARCHAR(255) DEFAULT NULL,
`SourceTable` VARCHAR(100) NOT NULL,
`SourceKey` VARCHAR(255) NOT NULL,
`SourceVersion` VARCHAR(50) DEFAULT NULL,
`Activity` VARCHAR(100) NOT NULL,
`Role` CHAR(1) NOT NULL,
`Email` VARCHAR(255) NOT NULL,
`Name` VARCHAR(255) DEFAULT NULL,
`Status` VARCHAR(20) NOT NULL DEFAULT 'PENDING',
`SignedAt` DATETIME DEFAULT NULL,
`Notes` TEXT DEFAULT NULL,
`RequestedAt` DATETIME DEFAULT CURRENT_TIMESTAMP,
`RequestedBy` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`Guid`),
UNIQUE KEY `uk_signoff` (
`Package`, `SourceTable`, `SourceKey`,
`Activity`, `Role`, `Email`
),
KEY `idx_signoff_status` (
`Package`, `SourceTable`, `SourceKey`,
`Status`
),
KEY `idx_signoff_email` (`Email`, `Status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation};
SELECT Activity, Role, Email, Name,
Status, SignedAt, Notes,
RequestedAt, RequestedBy
FROM def_raci_signoff
WHERE Package = '{package}'
AND SourceTable = '{source_table}'
AND SourceKey = '{source_key}'
ORDER BY Activity, Role, Email
SELECT Activity, Role, Email, Name,
RequestedAt, RequestedBy
FROM def_raci_signoff
WHERE Package = '{package}'
AND SourceTable = '{source_table}'
AND SourceKey = '{source_key}'
AND Status = 'PENDING'
ORDER BY Activity, Role
INSERT INTO def_raci_signoff (
Guid, Package, SourceTable, SourceKey,
SourceVersion, Activity, Role, Email,
Name, Status, RequestedBy
) VALUES (
'{guid}', '{package}', '{source_table}',
'{source_key}', '{source_version}',
'{activity}', '{role}', '{email}',
'{name}', 'PENDING', '{requested_by}'
) ON DUPLICATE KEY UPDATE
Status = 'PENDING',
SourceVersion = VALUES(SourceVersion),
RequestedAt = NOW(),
RequestedBy = VALUES(RequestedBy),
SignedAt = NULL,
Notes = NULL
UPDATE def_raci_signoff
SET Status = '{status}',
SignedAt = NOW(),
Notes = '{notes}'
WHERE Package = '{package}'
AND SourceTable = '{source_table}'
AND SourceKey = '{source_key}'
AND Activity = '{activity}'
AND Role = '{role}'
AND Email = '{email}'
SELECT
COUNT(*) AS total,
SUM(Status = 'APPROVED') AS approved,
SUM(Status = 'PENDING') AS pending,
SUM(Status = 'REJECTED') AS rejected
FROM def_raci_signoff
WHERE Package = '{package}'
AND SourceTable = '{source_table}'
AND SourceKey = '{source_key}'
AND Activity = '{activity}'