VTS Sublease Upload Process
A monthly, exception-based process that identifies net-new subleases in JD Edwards and routes them to Oxford's Service Desk for manual entry into VTS — because VTS's own data model can't correctly represent Oxford's sublease structure.
Overview
- Project Name: VTS Sublease Upload Process
- Project Type: Monthly exception-based data process (SSIS + Service Desk workflow)
- Source: JD Edwards (net-new subleases only)
- Target System: VTS (via manual Service Desk entry)
- Tech Stack: SQL Server Agent, SSIS, ServiceNow, Email
- Status: Delivered
VTS couldn't accurately represent Oxford's sublease structure through the standard JDE-to-VTS integration — a single suite can have multiple subtenants, each with their own commencement and expiry dates, and VTS used those subtenant dates instead of the prime lease's during testing. That broke occupancy calculations and stacking-plan visibility, and split what leasing teams needed to see as one leasable unit into artificial sub-spaces. Subleases were deliberately excluded from the standard portfolio integration as a result, and this process exists to fill that gap without repeating the same problem.
Sublease records stay in JDE as the source of truth. Once a month, this process identifies only the subleases that are new since the last run, builds a CSV, and emails it straight into the Service Desk's ticketing workflow — a Service Desk representative reviews it and enters the new sublease occupancy into VTS by hand. It's a deliberate trade: manual entry for a small, focused set of net-new records, instead of building and maintaining a VTS integration that VTS's own data model can't actually support correctly.
Architecture
End-to-End Flow
From JDE to a completed VTS record, the process only ever automates the parts that are safe to automate — detection and delivery — and leaves the actual VTS entry to a person.
Net-New Detection
The core logic is a single stored procedure, [JDE_Load].[Get_Net_New_Subleases], running one EXCEPT query — Subleases_Current EXCEPT Subleases_Previous. Only records that are genuinely new since the last cycle land in [JDE_Load].[Subleases]; nothing already reported, unchanged, or previously handled gets sent again. That's what keeps this a small, monthly review instead of a full re-check of Oxford's entire sublease portfolio every time.
Monthly Job
A SQL Server Agent job, 0615_VTS_Monthly_Send_Subleases_Prod, runs on the first day of every month at 6:15 AM in production, notifying the DBA Team operator group. It executes Email_Subleases.dtsx, an SSIS package living in SSISDB\Prod_DBA\07_VTS, which loads the current month's sublease data, diffs it against the prior month, and triggers the email if anything net-new turns up.
Generated CSV
When new subleases exist, the job builds a CSV — subleases_yyyymmddhhmmss.csv — with one row per new sublease: PropertySourceID, BusinessUnit, BuildingName, SubtenantName, Unit, Area, CommencementDate, and ExpirySubleaseDate. The same stored procedure that finds the net-new records also builds the header row and appends them, so the file is ready to attach without a separate formatting step.
Key Features
- Exception-Based, Not Full Sync: only net-new subleases go out each month — an EXCEPT query against the prior run means unchanged, previously-reported, and already-entered records never resurface.
- Service Desk as the Integration Layer: instead of a direct VTS API integration, the process hands off to Oxford's existing ServiceNow workflow, where a person enters the handful of new records by hand — deliberately, because VTS's own data model can't represent Oxford's sublease structure correctly.
- Self-Documenting CSV: the same stored procedure that finds the net-new records builds the file's header row and rows, so there's no separate formatting step between detection and delivery.
- No-Op Confirmation: when there's nothing new, the process still sends an email confirming that — “There are no net-new subleases this month” — so a quiet month reads as success, not a silent failure.
- Missing-File Alerting: a separate procedure, Email_Subleases_Missing_CSV, notifies Leasing Technology, SRE, and Service Desk directly if the expected monthly file never shows up, rather than letting a broken run go unnoticed.
Technologies Used
Runs the SQL Agent job, hosts the JDE_Load schema's sublease tables, and the EXCEPT-based net-new detection logic.
Email_Subleases.dtsx loads, diffs, and triggers the monthly CSV and email.
Where the Service Desk ticket lands and gets tracked through to completion.
The delivery channel — both the monthly sublease CSV to Service Desk and the exception notifications when something's missing.
Support Model
Oxford's Service Desk is the first point of contact for operational and data-related VTS support issues; from there, ownership splits by what actually broke:
- SQL Job Failure: DBA Team.
- Missing CSV File: SRE + Leasing Technology.
- ServiceNow Ticket Processing: Service Desk.
- VTS Data Entry: Service Desk Representative.
- Business Questions: Leasing Technology Team.
- VTS Product Issues: VTS Support Team.
Outcome
VTS's occupancy and stacking-plan data stays accurate because subleases never get forced through an integration path that can't represent them correctly. Leasing teams keep visibility into subtenant occupancy without VTS overriding prime lease dates, Service Desk only ever sees the handful of records that actually changed, and a quiet month or a missing file both get flagged automatically instead of failing silently.