dbo.DeltekStoredProc_BB_ConcurEmployeeFieldPopulation builds a single, normalized “employee snapshot” row (in a temp table) that’s ready to drive Concur User create/update payloads. It:

  1. Pulls the employee’s base data from Deltek (EMMain/EMCompany).

  2. Derives org segments, status, locale, country/sub-code, reimbursement currency, approver relationships, and entitlement flags.

  3. Applies per-employee overrides when they exist in Custom Tab fields.

  4. Uses a LocaleMapper table (country/state → locale + default currency) when values are not provided.

  5. Marks the record QualifiedForSync if any tracked fields changed (or if not yet qualified).

  6. Writes the computed values back into EmployeeCustomTabFields so downstream jobs (Add/Update/Entitlement batches) can act on consistent data.

Parameters & “DefaultQueryValue” behavior

When a parameter is passed as the literal string DefaultQueryValue, the procedure does not force that value. Instead, it derives the field from Deltek data, per-employee overrides, or mapping tables. If you pass a concrete value (not DefaultQueryValue), the procedure uses your value.

ParameterWhat it controls (target meaning)If set to DefaultQueryValueIf set to a specific value
@Employee (NVARCHAR(20))The Deltek Employee Number used to select/build the snapshot; also used as fallback for Concur EmployeeID and various lookups.N/A (must be a real employee).Uses this employee as the subject of the snapshot.
@Status (NVARCHAR(255))Concur User “active” flag (Y/N) equivalent.Derives from EMMain/EMCompany.Status: A → Y, else N.Uses your value as-is.
@LocalCode (NVARCHAR(255))Concur locale (e.g., en_US, en_GB).Uses per-employee override if CustBBConcurLocaleOverride = 'Y', otherwise later fills from #LocaleMapper (country/state → locale).Uses your value (bypassing #LocaleMapper unless the per-employee override flag is set to use the custom field value).
@ReimbCurrency (NVARCHAR(255))Concur reimbursement currency (ISO 4217, e.g., USD, GBP).Uses per-employee override if CustBBConcurReimbCurrencyOverride = 'Y', else fills from #LocaleMapper.ReimbursementCurrency.Uses your value as the currency.
@ExpenseGroup (NVARCHAR(255))Concur Expense Group (policy/grouping).If employee override flag is not set and the computed field still equals DefaultQueryValue, set to your parameter’s value (often a rule-built expression).Uses your value directly.
@InvoiceGroup (NVARCHAR(255))Concur Invoice Group.Same behavior as Expense Group—falls back to your parameter when no per-employee override is claimed.Uses your value directly.
@IsExpApprover (NVARCHAR(255))Whether user is an Expense Approver (Y/N).If not overridden, marks Y when the employee is a supervisor of anyone in EMCompany; otherwise defaults to N.Uses your value (Y/N).
@IsInvApprover (NVARCHAR(255))Whether user is an Invoice Approver (Y/N).If not overridden, defaults to N.Uses your value (Y/N).
@ExpSupervisorEmpID (NVARCHAR(20))Expense Approver Employee ID for this user.If not overridden, defaults to the employee’s Supervisor from EMCompany.Uses your value.
@InvSupervisorEmpID (NVARCHAR(20))Invoice Approver Employee ID.If not overridden, defaults to the employee’s Supervisor from EMCompany.Uses your value.
@LedgerCode (NVARCHAR(255))Ledger code (often same for all users).N/A—parameter is always used (the proc doesn’t derive a default here).Uses your value.
@ConcurLogin (NVARCHAR(255))Concur login ID (unique across the whole Concur realm; typically email).Uses EMMain.Email.Uses your value as login ID.
@InvoiceUser (NVARCHAR(255))Whether the user has Invoice entitlement (Y/N).If a single char isn’t provided, falls back to CustBBConcurInvoiceUser (custom field).Uses your Y/N.
@ExpenseUser (NVARCHAR(255))Whether the user has Expense entitlement (Y/N).If a single char isn’t provided, derives from EMCompany.Status: A → Y, else N.Uses your Y/N.
@TravelUser (NVARCHAR(255))Whether the user has Travel entitlement (Y/N).If a single char isn’t provided, falls back to CustBBConcurTripUser (custom field).Uses your Y/N.

Key idea: DefaultQueryValue says “let the procedure compute it.” A concrete parameter says “force it.”

How the procedure computes values (processing flow)

  1. Seed tables

    • Builds #LocaleMapper from UDIC_FSPProductCfg_EMtoConcurCountrySubCodeMap (country/province → CountrySubCode, Locale, ReimbursementCurrency).

    • Creates #EmployeeTableUpdates with all fields needed to provision Concur users (org, status, locale, currency, approvers, groups, entitlements, IDs/logins).

  2. Load base employee snapshot

    • Joins EMMain + EMCompany and splits Org using CFGFormat into OrgLevel1..5.

    • Sets Status: if @Status = DefaultQueryValue, uses A→Y else N; otherwise uses your parameter.

    • Sets Locale: if employee override flag CustBBConcurLocaleOverride='Y', uses CustBBConcurLocale; else tentatively stores @LocalCode (which may be DefaultQueryValue and get filled later).

    • Sets Country/State from EMMain; maps CountrySubcode via #LocaleMapper.

    • Sets Reimbursement Currency using per-employee override flag; else holds @ReimbCurrency (which may be DefaultQueryValue and get filled later).

    • Expense/Invoice Groups: per-employee override or parameter.

    • Approver flags and IDs: per-employee overrides if flagged; otherwise derive (IsExpenseApprover based on being a supervisor; IDs default to supervisor).

    • EmployeeID / LoginID: fallbacks to @Employee and EMMain.Email respectively if DefaultQueryValue.

    • Entitlements (ConcurUser, ConcurInvoiceUser, TravelUser): derived from parameters or fallback custom fields/status.

    • CurrentConcurStatus and QualifiedForSync captured for later logic.

  3. LoginID normalization

    • If Custom Tab CustBBConcurLoginID differs, it sets NewLoginID and preserves current LoginID so downstream processes can push a user rename.

  4. Qualification for sync

    • If not previously flagged as qualified, the row is marked QualifiedForSync = 'Y' when any of a long list of tracked fields differs between Custom Tab fields and the freshly computed snapshot (org levels, locale, country subcode, currency, groups, approver flags/IDs, IDs/logins, active, entitlements).

  5. Fallback fills (only when still DefaultQueryValue)

    • IsExpenseApprover: set to Y if the user supervises anyone; else N.

    • IsInvoiceApprover: set to N by default (unless overridden).

    • Expense/Invoice Approver IDs: default to employee’s supervisor.

    • Expense/Invoice Groups: default to the respective input parameters.

    • Reimbursement Currency: default via #LocaleMapper.ReimbursementCurrency.

    • Locale: default via #LocaleMapper.Locale.

    • EmployeeID / LoginID: fill with safe fallbacks if null (Employee, Email).

  6. Entitlement interactions

    • If Invoice entitlement is effectively off (ConcurInvoiceUser='N'), clears invoice approver fields.

  7. Persist the snapshot back to Custom Tab

    • Writes the computed values into EmployeeCustomTabFields (locale, country subcode, reimbursement currency, groups, approver flags and IDs, ledger, IDs/logins, active flag, entitlements, qualified-for-sync flag, etc.). This makes the data visible and consistent for audit and for the batch builders that call Concur.

What “DefaultQueryValue” really means for you

  • It’s declarative: “Use the best available source.”

    • If a per-employee override flag is set, use the employee’s override value.

    • Else, use a parameter (for groups) or mapping (for locale/currency), or derived value (status, approver flags, supervisor IDs).

  • It keeps rules centralized and predictable so you don’t have to push hardcoded values unless you truly need to.