-- ============================================================================= -- Recruitment — JobRequisition submit-for-approval: real MWORKFLOWCONFIG row -- Migration: 20260903 — Recruitment / JobRequisition workflow wiring (SQL Server) -- Database: tenant business DB — companion _Postgres.sql defines the identical -- logical row for Npgsql hosts. -- -- CONTEXT (read GB5Shared/EntityHandler/EventHandler.cs and -- GB5Shared/WorkFlow/WorkFlowEngine/WorkFlowEngine.cs before touching this file): -- Every JobRequisitionBLL save (SaveJobRequisition, and the SubmitJobRequisitionForApproval / -- ApproveJobRequisition / RejectJobRequisition transitions via PersistTransition) already -- routes through BaseEntityAppService.ExecuteSaveAsync, which — for EVERY -- entity, with no BLL-side workflow-engine call needed — runs -- IWorkFlowEngine.CheckWorkFlowApplicability(...) against MWORKFLOWCONFIG before persisting. -- That gate has been a no-op for JobRequisition purely because no MWORKFLOWCONFIG row existed -- for EntityConstant.OBJECTRECRUITMENTJOBREQUISITION (-1392100010) — this migration adds one. -- -- TRIGGER-POINT DESIGN DECISION: EVALCONDITION = 'DTO.RequisitionStatus == 2' so the engine -- only engages on the exact save that flips REQUISITIONSTATUS to PendingApproval(2) — i.e. -- specifically JobRequisitionBLL.SubmitJobRequisitionForApproval. Draft creates/updates -- (RequisitionStatus IN (1)) and the Approve/Reject transitions (which set 3/4) do NOT match, -- so this condition is evaluated on every JobRequisition save but only ever matches the one -- transition that should actually kick off approval routing. This mirrors the exact -- "DTO.TaskDetailType == 0" pattern documented in WorkFlowEngine.cs/EventHandler.cs — same -- evaluator, same syntax, not a fabricated shape. -- -- WHY THIS ROW IS SEEDED INERT (WORKFLOWID = -1) — READ BEFORE FLIPPING IT LIVE: -- MWORKFLOWCONFIG itself has NO foreign keys (live-verified DDL — see -- 20260716_DXP_Phase1_WorkflowConfig_Schema_SqlServer.sql) so this INSERT is 100% safe on any -- tenant DB. WORKFLOWID = -1 is the engine's own documented sentinel for "no workflow assigned to -- this config row" (WorkFlowEngine.CheckWorkFlowApplicability: "if (config.WorkflowId == -1) -- continue;") — so today this row changes NOTHING at runtime: every JobRequisition save behaves -- exactly as before. What is NOT seeded here, and why: -- * MWORKFLOW.ENTITYID has a real FK to MENTITY(ENTITYID). No MENTITY row exists for -- OBJECTRECRUITMENTJOBREQUISITION (-1392100010) — confirmed absent from every migration in -- this repo. MENTITY also carries DBOBJECTID (FK to DBOBJECT), PROJECTID, MODULEID, -- ASSEMBLYID and several other framework FKs. Inserting MENTITY safely means CLONING a real, -- working row column-by-column via dynamic SQL and overriding only ENTITYID/ENTITYCODE/ -- ENTITYNAME/DBOBJECTID (exact proven pattern: see -- 20260725_GOP_Qualifier_MEntity_Seed_SqlServer.sql) — but DBOBJECTID specifically MUST -- point at a real DBOBJECT row for TJOBREQUISITION, which needs a live -- "SELECT * FROM DBOBJECT WHERE DBOBJECTNAME = 'TJOBREQUISITION'" to confirm exists (or to -- create correctly) before it can be cloned. Getting this wrong is not cosmetic: when a -- REGULAR-mode workflow instance completes, WorkFlowEngine.SetEntityStatusAsync resolves -- TWORKFLOWINSTANCE.ENTITYID -> MENTITY.DBOBJECTID -> DBOBJECT.DBOBJECTNAME and runs -- "UPDATE {tableName} SET STATUS = {status} WHERE {pk} = OBJECTID" against WHATEVER table -- that resolves to — a wrong DBOBJECTID silently corrupts an unrelated table's STATUS column. -- * MWORKFLOWDETAIL + MWORKFLOWASSIGNMENT (the actual approval step + approver) need a real -- approver identity (a verified UserId for StrategyType=0, a verified UserGroupId for -- StrategyType=1, or a tested Dynamic-SQL/EnrichQualifier rule for StrategyType=2/3). That is -- a product/org decision (who approves a job requisition — the requester's manager? a fixed -- HR role?) which cannot be safely guessed, and WorkFlowEngine.ValidateWorkflowDefinition -- throws at submit time for ANY step whose MWORKFLOWASSIGNMENT is missing/unresolvable — so -- seeding these without a verified approver would actively break -- SubmitJobRequisitionForApproval rather than leave it inert. -- -- ACTIVATION CHECKLIST (do this against a live tenant DB, via gb5-schema MCP, not blind): -- 1. SELECT * FROM DBOBJECT WHERE DBOBJECTNAME = 'TJOBREQUISITION'; create the row if missing, -- following an existing DBOBJECT row's shape (see feedback_dbobject_constraint_and_autonumber_seed -- project memory — DBOBJECTNAME has a real unique constraint). -- 2. Clone a real, working MENTITY row (dynamic-SQL column clone, per the GOP Qualifier seed -- pattern referenced above) into ENTITYID = -1392100010, overriding ENTITYCODE, ENTITYNAME, -- and DBOBJECTID (from step 1). -- 3. Insert MWORKFLOW (ENTITYID = -1392100010), MWORKFLOWDETAIL (one APPROVALLEVEL, EVALCONDITION -- = '' so the step always applies), and MWORKFLOWASSIGNMENT with a verified real approver. -- 4. UPDATE MWORKFLOWCONFIG SET WORKFLOWID = -- WHERE WORKFLOWCONFIGID = -1392100011. -- -- Until that checklist runs, JobRequisition's approval stays exactly what it was before this -- migration: JobRequisitionBLL's own explicit Draft/PendingApproval/Approved/Rejected state -- machine (REQUISITIONSTATUS), with the real workflow engine now genuinely consulted on every -- save (not bypassed) but declining to engage because WORKFLOWID = -1. -- ============================================================================= IF NOT EXISTS (SELECT 1 FROM MWORKFLOWCONFIG WHERE WORKFLOWCONFIGID = -1392100011) BEGIN INSERT INTO MWORKFLOWCONFIG ( WORKFLOWCONFIGID, CLIENTID, ENTITYID, OUID, BIZTRANSACTIONCLASSID, ISBIZTRANSACTIONWISE, BIZTRANSACTIONID, ISFORMBASEDAPPROVAL, WORKFLOWID, EVALCONDITION, REMARKS, CALLBACKENDPOINT, SORTORDER, VERSION, STATUS, SOURCETYPE, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, TENANTID ) VALUES ( -1392100011, -- WORKFLOWCONFIGID (manually chosen literal — re-check uniqueness live before deploy) -1, -- CLIENTID: -1 = all tenants (wildcard) -1392100010, -- ENTITYID = EntityConstant.OBJECTRECRUITMENTJOBREQUISITION -1, -- OUID: -1 = all OUs -1, -- BIZTRANSACTIONCLASSID: -1 = wildcard 0, -- ISBIZTRANSACTIONWISE: 0 = No -1, -- BIZTRANSACTIONID: -1 = wildcard 0, -- ISFORMBASEDAPPROVAL: 0 = REGULAR mode, not WIP. -- JobRequisitionBLL already persists the row itself (via its own -- DAL call inside ExecuteSaveAsync's persistFunc) before the engine -- is asked to start a workflow — WIP mode would instead defer that -- persist to a replay/dispatch callback that has never been exercised -- in this repo (see 20260719_Entitlement_Phase3_ChangeRequest_Schema_ -- SqlServer.sql's header) and whose generic approval-finalisation -- (BaseEntityAppService.SetDtoStatus -> DTO status property = 1) would -- incorrectly force REQUISITIONSTATUS back to Draft(1) on approval — -- REGULAR mode has neither problem. -1, -- WORKFLOWID: -1 sentinel = inert until the activation checklist runs (see header) 'DTO.RequisitionStatus == 2', -- fires only on the submit-for-approval save 'Recruitment JobRequisition approval routing. Seeded INERT (WORKFLOWID=-1) pending MENTITY/DBOBJECT registration and a verified approver — see migration header for the activation checklist.', NULL, -- CALLBACKENDPOINT: not used outside WIP mode 9999, 0, 1, 5, -1, GETUTCDATE(), -1, GETUTCDATE(), -1 -- TENANTID: -1 = framework-level row, applies across tenants ); END GO