Easytrieve Retail Reporting — Real Project Guide

Business Context

Store operations close registers; extract jobs package transactions with store number, register, timestamp, SKU or UPC, quantity, extended amount, tender type, transaction type (sale, return, void), and promotional flags. Easytrieve batch jobs start after extract success—often a JCL dependency on POSDAILY(+1) GDG. Downstream consumers include district managers (PDF or printed reports via AFP), finance general ledger bridges, and inventory adjustment workflows. Missing a batch window means morning merchandising decisions run on stale data; overtolerance of bad records distorts comp-store metrics. Programs must balance throughput, accuracy, and auditable exception handling.

Typical Data Flow

Representative Input Record Layout

Layouts vary by POS vendor; beginners should obtain the enterprise data dictionary. A simplified teaching layout might include STORE-NUM (5 numeric), TRAN-DATE (8), REGISTER (3), SKU (12), QTY-SOLD (signed numeric), EXT-AMT (signed packed), TRAN-TYPE (1 char: S/R/V), PROMO-CODE (4). Easytrieve FILE definition maps offsets; production uses macros from the retail application library so every program agrees on EXT-AMT scale and sign.

1
2
3
4
5
6
7
8
9
FILE POS-DAILY
  STORE-NUM    5  N
  TRAN-DATE    8  N
  REGISTER-ID  3  N
  SKU         12  A
  QTY-SOLD     5  N  SIGN
  EXT-AMT      7  P  2
  TRAN-TYPE    1  A
  PROMO-CODE   4  A

Validation and Exception Handling

Retail feeds include closed stores still transmitting, test SKUs, and duplicate sequence rows after extract retries. Validation PERFORM checks: STORE-NUM exists on store master, SKU not spaces, TRAN-TYPE in allowed set, EXT-AMT sign matches TRAN-TYPE rules. Failures WRITE EXCEP-FILE with reason code and original record image. CNT-BAD-REC increments; header SYSPRINT documents count for operations. Finance prefers controlled exceptions over silent exclusion—reconcile teams reprocess EXCEP after master fixes. See validation framework and error handling pattern pages for reusable skeletons.

Enrichment and Lookup Tables

POS files carry SKU; reports show department and merchandise class. Load SKU master into memory table at job start (INIT procedure) or read keyed VSAM during JOB INPUT depending on size and site standards. PERFORM ENRICH-SKU sets DEPT, CLASS, and COST for margin reports. Promotional calendar lookup maps PROMO-CODE to campaign id for marketing analytics. When lookup misses, route to exception or default to UNKNOWN class per standards—document which choice finance approved.

Control-Break Sales Reports

District managers expect subtotals when store changes and department subtotals within store. REPORT activity sorts input by STORE-NUM, DEPT (presorted upstream or SORT activity). BREAK-LEVEL 1 on STORE-NUM triggers AFTER-BREAK store totals; BREAK-LEVEL 2 on DEPT for department lines. BEFORE-BREAK resets accumulators; LINE prints detail optional when report is summary-only. TITLE macros insert chain logo text, run date, and extract generation timestamp. Footing prints page numbers and grand totals reconciled to SYSPRINT control counts.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
REPORT STORE-SALES
  BREAK-LEVEL STORE-NUM
  BREAK-LEVEL DEPT-CODE
 
BEFORE-BREAK. PROC
  PERFORM RESET-DEPT-ACCUM
END-PROC
 
AFTER-BREAK. PROC
  WHEN DEPT-CODE
    PRINT DEPT-SUBTOTAL-LINE
  WHEN STORE-NUM
    PRINT STORE-SUBTOTAL-LINE
END-PROC

Exact WHEN grammar follows your release manual; adapt to documented AFTER-BREAK break-level handling. Grand totals must tie to sum of store totals—reconciliation PROC compares to controller file.

Returns, Voids, and Markdown

TRAN-TYPE R multiplies or signs amounts negative per business convention—never assume without spec. Voids may zero out paired sale rows; some chains net at extract time, others require Easytrieve pairing logic (advanced; often handled upstream). Markdown reports filter PROMO-CODE or price-change reason codes from reference file. Separate PERFORM keeps regular sales report clean while markdown listing job shares same FILE macros.

Inventory and Sales Reconciliation

Merchandising compares sales movement to inventory shrink reports. Easytrieve jobs join aggregated SKU quantities to inventory snapshot files—multi-file JOB with MATCH logic or work file from prior step. Mismatch report lists SKU, sold qty, expected on-hand delta, variance percent. Schedule after both POS rollup and inventory extract complete. These jobs are audit-sensitive; retain control totals and job logs per retention policy.

Scheduling and Operations

• Dependency: POS extract job must complete RC=0 before SALERPT01 runs.
• Record count sanity: compare to seven-day average; halt if below threshold percent.
• Restart: document whether job can rerun with same GDG or needs extract re-send.
• Peak events (Black Friday): pre-scale SORT WORK and VFM; review BUFNO on largest file.
• Coordinate time zone cutoffs for chains spanning regions—extract calendar in header comment.

Testing Retail Reports

1. Single-store file with known SKUs—verify dept subtotals manually.
2. Inject invalid store and SKU—confirm exception file reason codes.
3. Return and sale pair—verify net amount matches policy.
4. Empty file—graceful message, RC acceptable to operations.
5. Compare grand total to POS controller control report for same day.

Explain It Like I'm Five

Imagine every lemonade stand in town sends a list of cups sold at end of day. Retail reporting is adding up those lists: first check each line makes sense (real stand number, not typo), then group cups by stand and by flavor, print a summary for the boss, and set aside messy lines in a pink envelope to fix tomorrow. Easytrieve is the helper that reads all the lists, sorts them, adds them, and prints the summary pages automatically.

Exercises

1. Design FILE definitions for POS and store master with macro names you would publish.
2. Write validation rules for three retail exception reason codes.
3. Sketch BREAK-LEVEL order for region, store, and department hierarchy.
4. List JCL dependencies for a nightly store sales report job.
5. Map which design patterns apply to each pipeline stage in the table above.