DCP_Rules — Deep-Dive Guide

Companion to Section 8 of the main user manual  |  Beta Version 1.0  |  April 2026

1. What Is a Rule?

A rule is one row on the DCP_Rules tab. It tells the tool “whenever you see a row that looks like this, do that.”

Think of it the same way you'd write an instruction for a colleague reviewing the same report by hand:

That sentence is a rule. On the DCP_Rules sheet, that same instruction is split across 12 columns — one column per element of the sentence. The macro reads every rule on every row, decides which rows match, and applies the labels and colours to your output file.

You can have as many rules as you like. Rules never conflict dangerously — the worst that can happen is two rules label the same row, in which case both labels appear in the Rule Hits column and the row takes the colour of whichever rule fires first in sheet order.

2. What Is a Token?

A token is a short internal name for a column in the source file. Rules talk to columns through tokens.

The Oracle DCP report has dozens of columns, and the exact header text sometimes changes (Inventory Org one month, Inv Org the next). The tool solves this with a two-layer lookup:

1. The DCP_Columns sheet lists every token the tool understands — things like Inventory Org, Potential Shortage /Excess, Buyer Default Name.
2. Next to each token, a list of aliases is kept — all the header texts Oracle has ever used for that column. The tool picks whichever alias matches your source file today.

Because of this, rules only need to know the token. If Oracle renames a header next year, you edit DCP_Columns once and every rule that references that token keeps working.

3. Anatomy of a Rule (Diagram)

Every rule has three logical parts: Scope (who does this apply to?), Trigger (what condition fires it?), and Action (what do we do when it fires?). The 12 columns on the sheet break down into these three parts.

The shorthand:

4. Column-by-Column Guide

Each column on DCP_Rules in the order they appear on the sheet.

4.1   Column A — Enabled

Accepts Y or N.

• Y — the rule is active. The tool will evaluate it on every row of the source file during the next Run Extract.
• N — the rule is parked. The tool reads the row but does nothing with it. All its other settings are preserved for when you turn it back on.

4.2   Column B — Rule ID

A short label like R1, R2, R3. Purely for human reference — the tool doesn't use it internally.

4.3   Column C — Rule Name

A one-line, human-readable description of what the rule does. For example: Flag all rows where potential shortage is below zero.

4.4   Column D — Scope Token

The name of the column (a token, see Section 2) whose value decides whether this rule applies to a given row. Leave blank to apply the rule to every row.

4.5   Column E — Scope Values

A comma-separated list of values to match against the Scope Token column. If the row's value in that column is in the list, the rule proceeds to its trigger. If not, the rule does nothing on that row.

When your values already contain a comma

Buyer names in Oracle are often formatted Smith, John — the value itself contains a comma. A plain comma-separated list would split that one name into two. To handle this, the Scope Values cell supports a semicolon auto-detect:

4.6   Column F — Trigger Token

The name of the column that the rule actually inspects — the column whose value decides whether the rule fires. This is required for every rule.

4.7   Column G — Operator

The comparison to perform between the Trigger Token's cell value and the Trigger Value. You pick one from the following list:

Blank sentinels — why - counts as blank

Oracle and several other source systems export a literal dash (-) when a field has no value, instead of leaving the cell empty. To keep BLANK and NOTBLANK rules working on that data, the tool treats a configurable list of sentinel values as blank.

The list lives on the Run tab in cell C12 (Blank indicators). The default is:

4.8   Column H — Trigger Value

The value you compare against. This is entirely user-determined — the tool does not suggest, derive, or auto-fill this cell. You type whatever value makes sense for the operator you picked.

When your IN / NOTIN list contains values with commas

Same rule as Scope Values in §4.5: if any value you want to list already contains a comma, switch the whole list to semicolons. As soon as the parser sees a ; in the cell it treats semicolons as the separator and leaves embedded commas intact.

4.9   Column I — Action

What the tool does when the rule fires on a row. Pick one of:

4.10   Column J — Tag Text

A short label — usually 1 to 10 characters — that gets added to the Rule Hits column on the output file whenever this rule fires. If multiple rules fire on the same row, their tags are joined with a semicolon: SHORTAGE; R4 HIT.

4.11   Column K — Notes

Free-text notes. The tool doesn't read this column at all. Use it to record why you created the rule, who asked for it, or when it was last reviewed.

4.12   Column L — Highlight

The colour the tool paints the row when this rule fires. There are six ways to specify a colour — pick whichever feels most natural.

5. How a Rule Is Evaluated (Workflow Diagram)

For each row in the source file, the tool walks every enabled rule through the decision tree below. This explains exactly what happens and in what order.

Order of evaluation within one row

1. All filters on DCP_Filters are checked first. If any filter rejects the row, it's dropped before rules even run.
2. For each enabled rule, in sheet order, the tool runs the flow above.
3. If any DROP rule fires, the row is removed and no further rules are evaluated for that row.
4. All other firing rules contribute their Tag Text to the Rule Hits column.
5. The row's highlight colour comes from the first firing rule's Highlight setting.
6. After every row has been processed, if any KEEP_ONLY rule is enabled anywhere, rows that didn't fire at least one KEEP_ONLY rule are dropped.

6. How to Add a New Rule

Follow these six steps to create any rule, from the simplest flag to a scoped KEEP_ONLY combination.

7. Worked Examples

Each example starts with the plain-English sentence, then shows exactly what goes in each column.

Example AA — Combine two columns with AND (the most-asked pattern)

“Flag a row only when Inventory Org = 898 AND Item Source Type = Make.”

Every rule has two column slots: Scope (columns D+E) and Trigger (columns F+G+H). A rule only fires when both slots match on the same row, so you already have AND built in. Put one condition in Scope and the other in Trigger:

Example A — Flag all shortage rows in red

“Any row whose Potential Shortage /Excess is less than zero → highlight red and tag SHORTAGE.”

Example B — Flag shortages, but only for the 3 orgs my team owns

“For rows in orgs 320, 327, or 613, if Potential Shortage /Excess is less than zero → tag 'MY ORG SHORT' in pink.”

Example C — Drop every row where the buyer is “ToBeReassigned”

“Any row whose Buyer Default Name equals ToBeReassigned → remove from output.”

Example D — Only show rows that are either a shortage or an excess

“Show me rows where Potential Shortage /Excess is below 0 (R5) OR above 0 (R6). Hide every other row.”

Two rules together, both with Action = KEEP_ONLY:

Example E — Flag rows where a supplier item has a blank source org

“For rows whose Item Source Type is Supplier, if Item Inventory Org (Source) is blank, flag as 'MISSING SRC ORG' in amber.”

8. Highlight Colour Reference

Every way you can tell the tool what colour to paint a flagged row.

Colour name cheat sheet

Priority order when column L contains text

1. Keyword (SHORTAGE or EXCESS)
2. Hex (#RRGGBB)
3. R,G,B (255,105,180)
4. Long integer
5. Colour name lookup
6. Cell fill colour (only if column L is blank)
7. Fallback to AMBER if nothing resolved

Easiest for visual users

1. Leave column L blank.
2. Click the L cell.
3. Home ribbon → Fill Colour drop-down → pick a shade.
4. Save.

That's it. The rule's flagged rows will match the colour you painted.

9. Troubleshooting

10. Glossary