# Rule: System Macros Are Never Declared — User Params Always Are

DataSource SQL (in `<QueryCondition>` and `<Database><Load>` blocks) uses two kinds of substitutions:

-   **System macros** — injected automatically by the framework, no declaration needed
-   **User-declared parameters** — must appear in `<Parameters>` for declarative DS, or be read from `jsonQuery` in scripted DS

---

## The 10 System Macros

Available in any QueryCondition or Load block without a `<Parameter>` declaration.

| Macro                       | Purpose                         | Frequency              | Notes                                                        |
| --------------------------- | ------------------------------- | ---------------------- | ------------------------------------------------------------ |
| `#UserPKey#`                | Current user's primary key      | Very common            | Most joins on `VisitorId = '#UserPKey#'` use this            |
| `#UserSfId#`                | Current user's Salesforce ID    | Common                 | Used for permission queries                                  |
| `#TodayAsDate#`             | Current date as a date type     | Very common (307 uses) | For date comparisons                                         |
| `#Today#`                   | Current date (alternative)      | Common                 | Alternative to `#TodayAsDate#`                               |
| `#Language#` / `#LANGUAGE#` | User's language code            | Common                 | Use for localized column access: `Description_#Language#__c` |
| `#SalesOrg#` / `#SALESORG#` | Current sales organization      | Common                 | For org-scoped filtering                                     |
| `#MinDate#`                 | System minimum date constant    | Occasional             | "No date" sentinel value                                     |
| `#MaxDate#`                 | System maximum date constant    | Occasional             | "Infinite future" sentinel                                   |
| `#SECONDS_PER_DAY#`         | Constant 86400                  | Rare                   | For date arithmetic                                          |
| `#BUSINESSMODIFIED#`        | Business modification timestamp | Rare                   | Sync-related timestamp                                       |

**Key rule:** Do NOT put any of these in a `<Parameter>` tag. They are always available.

### Examples in QueryCondition

```xml
<QueryCondition><![CDATA[
  Visit.VisitorId = '#UserPKey#'
  AND Visit.IsDeleted = '0'
  AND #compareAsDate('Visit.PlannedVisitStartTime', 'DateTime','>=',#TodayAsDate#, 'Date')#
  AND Visit.SalesOrg__c = '#SalesOrg#'
]]></QueryCondition>
```

### Language-Specific Field Access

```xml
<Attribute name="description" table="Product" column="Description_#Language#__c" />
```

---

## User-Declared Parameters

Parameters passed from Process actions or BL code. MUST be declared in `<Parameters>` for declarative DS.

```xml
<Parameters>
  <Parameter name="pKey"         type="TEXT" />
  <Parameter name="customerPKey" type="TEXT" />
  <Parameter name="cardDate"     type="INTEGER" />
  <Parameter name="cond"         treatAs="sqlSnippet" />
</Parameters>
```

### Parameter Types

| Type       | Use for                    | Conversion in BL                                            |
| ---------- | -------------------------- | ----------------------------------------------------------- |
| `TEXT`     | IDs, names, status strings | Pass as-is or use `Utils.convertForDBParam(val, "DomPKey")` |
| `INTEGER`  | Numeric, date values       | `Utils.convertForDBParam(val, "DomDate")`                   |
| `DATE`     | Explicit date parameters   | —                                                           |
| `DATETIME` | Timestamps                 | —                                                           |
| `BOOLEAN`  | Feature flags              | —                                                           |

---

## Special: `treatAs="sqlSnippet"`

Injects the parameter value as a **raw SQL fragment** rather than a quoted literal. Use for dynamic WHERE conditions.

```xml
<Parameter name="cond" treatAs="sqlSnippet" />
```

In BL code that calls this DS:

```javascript
jsonQuery.cond = " AND Task.Status IN ('In Progress', 'Not Started') AND Task.ActivityDate <= #cardDate# ";
```

In QueryCondition:

```xml
<QueryCondition><![CDATA[
  Task.OwnerId = '#UserPKey#'
  #cond#
]]></QueryCondition>
```

Warning: because the snippet is injected verbatim, only BL code that owns this DS should write to it.

---

## Parameter Flow: How Values Reach the DS

### Via Process LOAD action (declarative DS)

```
Process LOAD action passes:
  <Input name="cardDate" value="ProcessContext::CardDate" />
  → Framework creates jsonQuery = {params: [{field: "cardDate", value: "..."}]}
  → Framework substitutes #cardDate# in QueryCondition
```

### Via BL method calling Facade.getListAsync (any DS)

```javascript
// Build the query object
var jsonQuery = {};
jsonQuery.params = [{ field: 'cardDate', value: Utils.convertForDBParam(cardDate, 'DomDate') }];
jsonQuery.cond = " AND Visit.Status = 'Planned' "; // only if cond param declared with treatAs="sqlSnippet"

// Kick off the load
return Facade.getListAsync('LoVisitsByDate', jsonQuery);
```

### Via scripted DS — reading from jsonQuery

```javascript
// In the <Database><Load> block
var customerPKey = Utils.convertForDBParam(jsonQuery.customerPKey, 'DomPKey');
var dateParam = Utils.convertForDBParam(jsonQuery.cardDate, 'DomDate');

var sqlParams = { customerPKey, dateParam };
var sql = 'SELECT ... WHERE Entity.CustomerId = #customerPKey# ';
// Always use replaceMacrosParam for safe injection
return Utils.replaceMacrosParam(sql, sqlParams);
```

---

## The 4 Helper Functions

Available inside QueryCondition and Load blocks.

| Function                                                  | Purpose                                               | Example                                                                                 |
| --------------------------------------------------------- | ----------------------------------------------------- | --------------------------------------------------------------------------------------- |
| `#compareAsDate(field, fieldType, op, value, valueType)#` | Date comparison with type conversion                  | `#compareAsDate('Visit.PlannedVisitStartTime', 'DateTime','<=',#TodayAsDate#, 'Date')#` |
| `#dateAsUnixepochLocaltime(field)#`                       | Convert a DateTime column to Unix epoch in local time | `#dateAsUnixepochLocaltime('Visit.PlannedVisitStartTime')# AS plannedStartDate`         |
| `#dateAsStringLocaltime(value)#`                          | Convert a date value to a localized string            | `#dateAsStringLocaltime('#MinDate#')#`                                                  |
| `#toggleMapping(table, field)#`                           | Field mapping toggle for managed packages             | `#toggleMapping('Visit', 'VisitPriority')# AS priority`                                 |

Real-file usage: `src/Visit/DS/DsLoVisit_sf.datasource.xml`
(uses `#dateAsUnixepochLocaltime#`, `#compareAsDate#`, and `#SALESORG#` in its Load block)