User Manual & Job Aid | Beta Version 1.0 | April 2026
The DCP Report Formatter reads a raw Oracle DCP report, applies the filters and business
rules you define on worksheets, highlights the rows you want to look at, and saves the
result to a new .xlsx file in a folder of your choice. The original source
file is never modified.
| Input | Output |
|---|---|
Oracle DCP report (.xlsx, .xlsm, .xlsb, .xls, or .csv)
with a single header row and up to thirty-seven columns of item, org, demand, supply and cost data. |
A clean .xlsx file in the output folder with: a styled header band, a SUM / AVG / COUNT band
on row 2 for every numeric column you enabled, filtered data rows from row 3 downward, a Rule Hits
column at the end, and colour-highlighted rows for every rule that fired. |
The tool workbook contains the tabs listed below. Most of them are reference or configuration sheets you edit once and leave alone; only the Run tab is touched on every run.
| Sheet | Purpose | Do you edit it? |
|---|---|---|
| Instructions | Quick-start reference seeded by the tool. Read-only for users. | No |
| Run | The control panel. Source file path, output folder, filename pattern, open-after-save flag, and all buttons. | Yes — every run |
| DCP_Columns | Lists every column the macro looks for. The Aliases column lets you teach the tool the exact
header text your source file uses. The Format column controls the Excel number format of each
column in the output. |
Yes — once, when your source header text changes |
| DCP_Filters | INCLUDE / EXCLUDE filters. Each row trims the data by Inventory Org, Buyer, Status, Product Family, etc. | Yes — whenever you want to change scope |
| DCP_Rules | Business rules that FLAG, DROP or KEEP_ONLY rows. This is where you add the logic for shortage detection, excess detection, MOQ exceptions, supplier-source sanity checks, etc. | Yes |
| DCP_SumColumns | Tells the tool which numeric columns should get a SUM, AVG, or COUNT
formula on row 2 of the output file. |
Yes — rarely |
| DCP_Last_Run | Status dashboard. After every run it shows the saved file path (as a clickable hyperlink), row counts, filter and rule counts, and a green OK / red ERROR indicator. | No — read-only |
| Log | Append-only history of every run, file pick, and error. Useful for debugging. | No — read-only |
Run these steps once on a fresh workbook to create the tab structure. After the first setup you only use Amend Sheet Setup when you upgrade the macro code.
These are the cells you fill in before each run, along with their default values and hints.
| Cell | Field | Example / default |
|---|---|---|
C6 | Source file path | Full path to the DCP report — typically filled by Pick Source File.
Supports .xlsx / .xlsm / .xlsb / .xls / .csv. |
C7 | Source sheet name | Default Sheet1. Ignored for CSV sources — the tool auto-resolves. |
C8 | Header row number | Default 1. Change only if your source has banners above the header. |
C9 | Output folder | Folder where the new .xlsx is saved. Fill via Pick Output Folder. |
C10 | Output filename | Default DCP_Output_{DATE}_{TIME}.xlsx. See Section 13 for token list. |
C11 | Open after save (Y/N) | Default Y. Y opens the file in Excel after saving; N leaves it on disk only. |
The routine weekly run after your first setup is three clicks. Anything you have already configured on the DCP_Filters, DCP_Rules, and DCP_SumColumns tabs is reused automatically.
| Step | Action |
|---|---|
| 1 | Click Pick Source File and select today's DCP export. |
| 2 | Confirm the output folder in C9 is correct. If not, click
Pick Output Folder. |
| 3 | Click Run Extract. A progress window shows rows processed. |
| 4 | When the summary pop-up appears, the tool jumps to the DCP_Last_Run tab.
If C11 is Y the saved file also opens automatically. |
{DATE} and {TIME} guarantee a unique
file per run, so you never overwrite yesterday's output by accident.
Every button visible on the Run tab is listed below. The macro the button runs is shown in column C on the sheet itself.
| Button | What it does | When to use |
|---|---|---|
| Setup Workbook | Full factory reset. Wipes and re-seeds every setup sheet with defaults. | First time only, or when you want a clean reset. |
| Amend Sheet Setup | Safe rerun. Creates missing sheets; never touches existing ones. | After importing a new version of the macro. |
| Pick Source File | File-picker dialog. Writes the chosen path into C6. |
Every run — select today's DCP file. |
| Pick Output Folder | Folder-picker dialog. Writes the chosen path into C9. |
First time, and whenever you change output location. |
| Run (Dialog) | Opens the frmDCPRun preflight form — one-stop panel showing
source file, output folder, filename pattern, and open-after-save toggle in one place. |
Use this instead of editing cells one-by-one. |
| Run Extract | Reads the source, applies filters and rules, saves the output .xlsx. |
The main action. |
| Open Output Folder | Opens the folder from C9 in Windows Explorer. |
To browse past runs. |
| Open Last Output | Opens the most recently saved .xlsx (reads the hyperlink on
DCP_Last_Run). |
When you closed the file and want it back. |
| Open Instructions | Navigates to the Instructions tab. | Quick reference. |
The macro refers to columns by internal names called tokens. The DCP_Columns tab maps each token to the actual header text your source file uses. If Oracle renames a header, you fix it here — not in the code.
| Col | Field | What to put in it |
|---|---|---|
A | Token | Internal name. Do not change. |
B | Aliases | Comma-separated list of header texts the tool should accept as matching this token. Add any new wording Oracle uses. |
C | Required | Y or N. A Required column missing from the source will stop the run. |
D | Type | TEXT, NUM, or KEY. Reference only. |
E | Notes | Free text. |
F | Format | Excel number format string (e.g. #,##0.00). Applied to that column in the
output file. Leave blank to inherit the source column's own format. |
G | Extract | Y (or blank) to write this column to the output .xlsx;
N to skip it. Filters and rules still see every column regardless — the
flag only controls what lands in the final file. |
|).
Example: Inventory Org, Inv Org, Org.
The source report from Oracle can have 37+ columns. If you only need a handful — say 6 —
set the Extract column (G) to N for everything you
want to drop, and leave it blank (or Y) for the columns you want to keep. The
output file will contain only those columns, plus the automatic Rule Hits column
at the end.
Average Daily Usage even
if you've hidden that column from the final file. This lets you filter by columns you don't
want to see, and see columns you don't want to filter by.
Y pre-filled on every row — so the behaviour matches the previous version
until you change it.
Each row on the DCP_Filters tab is one independent filter. All enabled filters must pass for a row to be kept (AND logic across filters). If no filters are enabled, every row is kept.
| Col | Field | What to put in it |
|---|---|---|
A | Enabled | Y to apply this filter, N to ignore it. |
B | Token | The column to test — must exactly match a token in DCP_Columns. |
C | Mode | INCLUDE keeps only matching values.
EXCLUDE drops matching values. |
D | Values | Comma-separated list of values to match. Blank means the filter is effectively a no-op (everything passes). |
E | Notes | Free text. |
| Intent | Token | Mode | Values |
|---|---|---|---|
| Keep only active items | Inventory Item Status | INCLUDE |
Active |
| Only three orgs of interest | Inventory Org | INCLUDE |
320, 327, 613 |
| Drop a problem buyer | Buyer Default Name | EXCLUDE |
ToBeReassigned |
Rules are the heart of the tool. A rule fires when a row matches its Scope (or has no Scope) and the Trigger cell satisfies the Operator test. When a rule fires, one of three actions is taken.
| Action | Effect |
|---|---|
| FLAG | Keep the row. Append Tag Text to the Rule Hits column.
Paint the row in the rule's highlight colour (see Section 9). |
| DROP | Drop the row from the output. Takes priority over FLAG. |
| KEEP_ONLY | Mark the row as a keeper. If any KEEP_ONLY rule is enabled, rows that never fire a KEEP_ONLY rule are dropped. |
| Col | Field | What to put in it |
|---|---|---|
A | Enabled | Y or N. |
B | Rule ID | Short label like R1, R2, ... |
C | Rule Name | Description for humans. |
D | Scope Token | Optional — restricts the rule to rows whose Scope Token column matches one of the Scope Values. Blank means applies to every row. |
E | Scope Values | Comma-separated values paired with D. |
F | Trigger Token | The column the rule inspects. |
G | Operator | LT, LE, GT, GE, EQ,
NE, IN, NOTIN, BLANK, NOTBLANK,
CONTAINS. |
H | Trigger Value | The value compared against the trigger cell. For IN / NOTIN
use a comma list. |
I | Action | FLAG, DROP, or KEEP_ONLY. |
J | Tag Text | Short label added to the output
Rule Hits column when the rule fires. |
K | Notes | Free text. |
L | Highlight | Colour for the row when the rule fires. See Section 9. |
| Rule ID | Purpose | Default |
|---|---|---|
R1 | Flag rows where Potential Shortage/Excess is less than zero. | Enabled |
R2 | Flag orgs 4M4/4M5 when an MOQ is set. |
Disabled |
R3 | Flag orgs 320/327 planned as Not Planned. |
Disabled |
R4 | Flag items whose Source Type is Supplier but whose Item Inventory Org is not blank. | Enabled |
R5 | Flag rows where Potential Shortage/Excess is greater than zero (excess focus). | Disabled |
Enabled = Y,
give it a new Rule ID (e.g. R6), fill in Scope / Trigger / Operator / Value, set
the Action, and write a short Tag Text. No code changes needed.
Column L on DCP_Rules controls the row colour in
the output file when a rule fires. You can use any of six formats — whichever is easiest.
| Format | What to type in col L | Example |
|---|---|---|
| Keyword | SHORTAGE — preset light red.EXCESS — preset light lavender. |
SHORTAGE EXCESS |
| Color name | One of: WHITE, BLACK, RED, GREEN,
BLUE, YELLOW, ORANGE, PURPLE,
PINK, HOTPINK, BOLDPINK, LIGHTPINK,
LIGHTBLUE, LIGHTGREEN, LIGHTYELLOW,
LAVENDER, CYAN, MAGENTA, GRAY,
LIGHTGRAY, AMBER, SALMON, GOLD. |
HOTPINK |
| Hex | #RRGGBB in web hex notation. |
#FF69B4 |
| R,G,B | Three integers 0-255 separated by commas. | 255,105,180 |
| Long | A single Excel colour Long value (the number you see in the Immediate window). | 11862015 |
| Cell fill | Leave column L blank and paint the cell itself using Excel's
paint-bucket tool. The tool reads Interior.Color. |
Yellow-painted cell → yellow row |
When column L has text, the tool tries, in order: keyword → hex → R,G,B
→ Long → color name. If none parse and the cell itself is painted, it reads the fill
colour. If nothing resolves, it falls back to amber.
Enabled=Y,
Rule ID=R6, your scope/trigger/operator/value, Action=FLAG,
Tag Text=R6 HIT.L type HOTPINK (or #FF69B4, or
255,105,180, or paint the cell bright pink and leave the text blank).Row 2 of every output file is reserved for formulas over the data range. The DCP_SumColumns tab controls which columns get a formula and which formula they get.
| Col | Field | What to put in it |
|---|---|---|
A | Enabled | Y or N. |
B | Token | The column to total — must match a token in DCP_Columns. |
C | Formula | SUM, AVG, or COUNT.
Blank defaults to SUM. |
D | Notes | Free text. |
By default the tool seeds 16 numeric quantity and cost tokens with SUM enabled.
Disable any row you do not want totalled, or change the formula to AVG / COUNT.
Every run writes a brand-new .xlsx workbook in the output folder. The layout is
the same every time.
| Row | What is there |
|---|---|
1 |
Header band — dark navy fill, white text. Columns copied from the source header,
plus one extra column called Rule Hits at the very end. |
2 |
Formula band — light-red fill. Contains SUM/AVG/COUNT
formulas for every token enabled on DCP_SumColumns. |
3+ |
Data rows. Order matches the original source. Flagged rows are filled with the colour
from the rule that hit (Section 9). The Rule Hits column lists the tag text of
every rule that fired on that row, separated by ;. |
#,##0.00, which
keeps near-zero Oracle floats like -4.5E-13 from showing up in scientific notation.
After every run the tool writes a short status block on the DCP_Last_Run tab. Use it to find the last output file without leaving the workbook.
| Field | Meaning |
|---|---|
| Run timestamp | Exact date and time of the last run. |
| Source file | The .xlsx or .csv that fed the run. |
| Source sheet | Sheet name inside the source (auto for CSV). |
| Output file | Full path of the saved .xlsx, stored as a clickable hyperlink.
Click it to open the file. Open Last Output does the same thing. |
| Rows read / kept / dropped / flagged | The row counts. |
| Filters applied | Number of enabled filters on this run. |
| Rules applied | Number of enabled rules on this run. |
| Status | Green OK on success, or red ERROR — <message>. |
Cell C10 on the Run tab is a filename pattern. Tokens
in curly braces are replaced at run time.
| Token | Replaced with | Example |
|---|---|---|
{DATE} | Today's date in yyyy-mm-dd format. | 2026-04-17 |
{TIME} | Current time in hhmm 24-hour format. | 0935 |
{USER} | Windows username (USERNAME environment variable), sanitised. | jsmith |
{SRC} | Source filename without extension, sanitised. | DCP_Weekly_2026-04-17 |
Characters that Windows rejects (\ / : * ? " < > |) are stripped from
{USER} and {SRC} automatically. If your pattern has no extension, the
tool appends .xlsx.
| Pattern in C10 | Result |
|---|---|
DCP_Output_{DATE}_{TIME}.xlsx (default) |
DCP_Output_2026-04-17_0935.xlsx |
{USER}_DCP_{DATE}.xlsx |
jsmith_DCP_2026-04-17.xlsx |
{SRC}_filtered.xlsx |
DCP_Weekly_2026-04-17_filtered.xlsx |
DisplayAlerts is off under AppLock). If you want to keep
history, always include {DATE} and {TIME}.
The tool shows a pop-up message for every predictable failure. Match the message text below to find the fix.
| Message | Cause and fix |
|---|---|
| Run sheet is missing. Click 'Setup Workbook' first. | You clicked a button before initialising the workbook. Click Setup Workbook on a fresh instance, or Amend Sheet Setup if you already have data. |
| Source file path is empty. Fill in cell C6 ... | Cell C6 is blank. Click Pick Source File
or type the full path manually. |
| Output folder is empty. Fill in cell C9 ... | Cell C9 is blank. Click Pick Output Folder. |
| Could not open source workbook: ... | The source file does not exist, is locked by another user, or is on a network
path that timed out. Confirm the path in C6 and that you can open the
file in Excel manually. |
| Source sheet not found: 'Sheet1' | Your source file uses a different sheet name. Update C7 on the
Run tab to match, or leave blank for CSV files. |
| No data rows found in source below the SUM band. | The tool looked for data in row hdrRow + 2 (after a one-row SUM band)
but found nothing. Either the source has no data, or C8 (header row) is wrong. |
| Output folder/filename could not be resolved. | Cell C9 or C10 is blank. Set both and try again. |
| The last saved output file no longer exists: ... | You clicked Open Last Output but the file was moved or deleted since the last run. Run the extract again, or navigate manually. |
| Cancelled by user at row N | You clicked Cancel on the progress form. Nothing was saved. Click Run Extract again to retry. |
L has a
value in one of the six formats listed in Section 9.Enabled=N on
DCP_Rules and re-run. No code changes are needed.| Term | Definition |
|---|---|
| DCP | Demand / Capacity / Planning report generated by Oracle — the source input for this tool. |
| Token | Internal column name the macro uses. Mapped to an actual source header via the Aliases column on DCP_Columns. |
| Alias | One of the header texts your source file may use for a given token. Multiple aliases per token, comma-separated. |
| Scope | Optional sub-condition on a rule. The rule only evaluates rows whose Scope Token column value is in the Scope Values list. |
| Trigger | The column and comparison that determines whether the rule fires. |
| FLAG | Rule action that keeps the row and paints it with the rule's highlight colour. |
| DROP | Rule action that removes the row from the output. |
| KEEP_ONLY | Rule action that marks a row as a keeper. If any KEEP_ONLY rule is enabled, rows that never fire a KEEP_ONLY rule are dropped. |
| MOQ | Minimum Order Quantity — the MIN QTY column in the DCP report. |
| Inventory Org | Oracle Inventory Organization code (e.g. 320, 327, 613, 4M4, 4M5). |
| Source Type | Oracle Item Source Type: Supplier, Inventory, etc. |
| AppLock | Internal safeguard that switches off screen updating, events, alerts and auto-calculation while the macro runs. Lifted automatically when the run finishes (or fails). |
| Rule Hits | The extra column added at the end of the output file. Contains the Tag Text of every rule that fired on that row. |