temporaryshare/Admin - Documentation.md

# Technical Documentation — Admin Folder

## Table of Contents
1. [SR_Macro.xlsb — Main Macro](#1-sr_macroxlsb--main-macro)
   - [Sheets](#sheets--overview)
   - [VBA Modules](#vba-modules--code-architecture)
2. [Subupload with Bundled Indicator.xlsx — Upload File](#2-subupload-with-bundled-indicatorxlsx--upload-file)
3. [folder create.bat — Folder Creation Script](#3-folder-createbat--folder-creation-script)
4. [icon.ico — Application Icon](#4-iconico--application-icon)
5. [End-to-End Data Flow](#5-end-to-end-data-flow)
6. [Glossary](#6-glossary)

---

## 1. SR_Macro.xlsb — Main Macro

An Excel Binary Workbook (`.xlsb`) containing VBA macros and 7 data sheets. This is the heart of the entire system.

### Sheets — Overview

| Sheet | Role |
|---|---|
| MACRO | Control panel — run-time settings |
| AIS Import | Data from the Disney central system (AIS) |
| AIS Export | Comparison: operator data vs AIS |
| MailManager | Log of processed emails |
| MailManager_Mapping | Per-operator configuration |
| Mapping | Lookup tables (months, channels, packages) |
| Error_Log | Processing error log |

---

### Sheet 1: MACRO — Control Panel

Used to configure settings before running the macro. Contains three parameters:

| Field | Example value | Description |
|---|---|---|
| `Mailbox:` | `DWSS.Autocashsupport@disney.com` | The Outlook mailbox from which the macro retrieves operator emails |
| `Service Month:` | `April` | The month for which reports are being processed |
| `Latest AIS file:` | `P08_FY24_Subs_report 01.05.2024` | Name of the most recently loaded AIS system report |

**What the macro does on execution:**
1. Connects to the Outlook mailbox specified in the `Mailbox` field
2. Scans the inbox for new emails from operators with the subject `RAPORTY`
3. Downloads Excel attachments and saves them to `Import/POL/[operator_id]/`
4. For each operator, calls the appropriate sub-macro (`R_MROSA_EXCL.Run` or `R_MROSA_PDF.Run`)
5. Generates an output report (`.xls`) and saves it to `Admin/Export/PL/[Operator]/`
6. Archives the email (`.msg`) alongside the report

---

### Sheet 2: AIS Import — Central System Data

Data retrieved from the AIS (Affiliate Information System) — Disney's central database of historical subscriber figures. Used as the baseline for comparison against operator-submitted data.

**Structure (22 columns):**

| Column | Description | Example |
|---|---|---|
| `AFFILIATE NUMBER` | Affiliate ID (operator group) | `15259` |
| `COUNTRY-SERVED2` | Country code | `POL` |
| `STATE-SERVED` | Region / voivodeship | `ALL` |
| `CITY-SERVED` | City | `ALL` |
| `SYSTEM-NAME` | Full operator system name | `24IT MEDIA SP. Z O.O.` |
| `SYSTEM-ID` | Numeric system ID in AIS | `124266` |
| `NETWORK` | Numeric channel ID | `825` |
| `SERVICE-CODE` | Package code | `PL13` |
| `CABLE-HOUSEHOLDS` | Number of households with access | (usually empty for PL) |
| `SUBSCRIBERS` | Number of subscribers | `1` |
| `SMSO-IND` | SMSO indicator | (usually empty) |
| `PO-NUMBER` | Purchase order number | (usually empty) |
| `SERVICE-MONTH` | Service month (number) | `4` |
| `SERVICE-YEAR` | Service year | `2024` |
| `NETWORK-NAME` | Channel name | `DISNEY CHANNEL` |
| `SERVICE-NAME` | Package name | `ALL DISNEY/BABY/FX/NG` |
| `Frequency` | Reporting frequency | `MONTHLY` |
| `Last Reported Subs` | Previously reported subscriber count | `1` |
| `Last Date` | Date of previous report | `2024-03-31` |
| `Service Area` | Service region | `Poland` |
| `Legal Entity` | Disney legal entity | `TWDCPO` |
| `Bundle Indicator` | Bundle identification key | `124266PL13` |
| `Bundled` | Is it a bundled package? | `Yes` / (empty) |

**Record identification key:** `[SYSTEM-ID][SERVICE-CODE]` e.g. `124266PL13`

---

### Sheet 3: AIS Export — Data Comparison

A side-by-side comparison of operator-submitted data against historical AIS data. The macro uses this sheet to flag discrepancies.

**Additional columns relative to AIS Import:**

| Column | Description | Possible values |
|---|---|---|
| `Compare` | Comparison result | `Match` / `ok` / `Diff.` |
| `Column1` | Difference (new − previous) | number (negative = decline) |
| `Column2` | Row key | e.g. `14476121929DBABABY TV ` |

**`Compare` value meanings:**

| Value | Meaning |
|---|---|
| `Match` | Perfect match (difference = 0) |
| `ok` | Small, acceptable difference |
| `Diff.` | Large difference — requires manual review |

**Example discrepancy:**
```
SM Polnoc – BABY TV, DBA: new=5576, previous=3560 → Diff., change=−2016
```

---

### Sheet 4: MailManager — Email Log

A register of all emails processed by the macro.

**Structure:**

| Column | Description | Example |
|---|---|---|
| `sender` | Sender email address | `renata@eaw.com.pl` |
| `subject` | Email subject | `RAPORTY` |
| `time` | Date and time (Excel serial number) | `45419.577...` |
| `status` | Processing status | `New` |
| `filename` | Full path of the saved attachment | `C:\...\Import\POL\13245\CITY NET SP ZOO - 20240614151842_DisneyC.N..xlsx` |

**How email identification works:**
- The macro checks the subject line: if it contains `RAPORTY` → the email qualifies for processing
- The sender address is matched to an operator via the `Email` column in `MailManager_Mapping`
- Status `New` = just processed; on error, the entry is written to `Error_Log` instead

---

### Sheet 5: MailManager_Mapping — Operator Configuration

The key configuration sheet — defines every operator handled by the system. One row = one operator. The sheet has two halves: left side = direct operators, right side = Nasza Wizja sub-platform operators.

**Columns — direct operator (left side):**

| Column | Description | Example |
|---|---|---|
| `Name` | Full operator name | `CITY NET SP ZOO` |
| `Cntr` | Country code | `POL` |
| `Email` | Operator email address | `renata@eaw.com.pl` |
| `Bundle type` | Channel for bundle | `NAT GEO CORE ` |
| `type` | Bundle package code | `DEX` |
| `Bundle type2` | Second bundle channel (optional) | |
| `type2` | Second bundle package code (optional) | |
| `Special Bundle` | Special bundle indicator | `SpecBundle` |
| `SYSTEM-ID1` | System ID 1 in AIS | `119971` |
| `SYSTEM-ID 2` | System ID 2 (if operator has multiple) | |
| `AFFILIATE-ID` | Affiliate ID | `13245` |
| `AFFILIATE-ID 2` | Second affiliate ID | |
| `Folder Location` | Export folder path | `CITY NET ` |
| `Doc Type` | File format received from operator | `xlsx` / `xls` / `ods` / `pdf` |
| `Call Macro` | Sub-macro to invoke | `R_MROSA_EXCL.Run` |
| `comments` | Operational notes | `n` / `q` / `a` / `SKAN` / `x` |
| `Tested?` | Whether the configuration has been tested | `n` (no) / `q` (quarterly) |

**Columns — Nasza Wizja operator (right side, same logic):**

| Column | Description |
|---|---|
| `SYSTEM-NAME` | Nasza Wizja operator name |
| `SYSTEM-ID` | System ID in AIS |
| `AFFILIATE-ID` | Affiliate ID |
| `Folder Location` | e.g. `Nasza Wizja\AMBIT` |
| `BUNDLE` | Bundle channel |
| `Bundle Type` | Bundle package code |
| `Sum of Bundles?` | Whether to aggregate bundles across multiple sub-operators |
| `Test` | Test status |

**Document types (`Doc Type`):**

| Value | File format |
|---|---|
| `xlsx` | Excel (newer format) |
| `xls` | Excel (legacy format) |
| `ods` | OpenDocument Spreadsheet |
| `pdf` | PDF (scanned document) |

**Sub-macros (`Call Macro`):**

| Macro | Used for |
|---|---|
| `R_MROSA_EXCL.Run` | Excel and ODS files — fully automated |
| `R_MROSA_PDF.Run` | PDF files — requires OCR or manual data entry |

**Comment codes (`comments`):**

| Code | Meaning |
|---|---|
| `n` | Normal operator, no special handling |
| `q` | Quarterly reporting (not monthly) |
| `a` | Anomaly — requires attention |
| `SKAN` | Operator submits a paper scan |
| `x` | Not supported / operator inactive |

---

### Sheet 6: Mapping — Lookup Tables

A reference sheet containing mappings used by the macro when parsing operator data.

**Table 1 — Months (EN/PL ↔ number):**

| English | Polish | Number |
|---|---|---|
| January | sty | 1 |
| February | lut | 2 |
| March | mar | 3 |
| April | kwi | 4 |
| May | maj | 5 |
| June | cze | 6 |
| July | lip | 7 |
| August | sie | 8 |
| September | wrz | 9 |
| October | paz | 10 |
| November | lis | 11 |
| December | gru | 12 |

**Table 2 — Channels (numeric ID → name):**

| ID | Channel |
|---|---|
| 665 | BABY TV |
| 668 | FX |
| 679 | NAT GEO CORE |
| 680 | NAT GEO WILD |
| 681 | NAT GEO PEOPLE |
| 701 | FX COMEDY |
| 825 | DISNEY CHANNEL |
| 831 | DISNEY JUNIOR |
| 835 | DISNEY XD |
| 865 | NAT GEO + EMEA |
| 867 | NAT GEO PLAY EMEA |

**Table 3 — Package codes (SERVICE-CODE → SERVICE-NAME):**

| Code | Name |
|---|---|
| `BASIC` | Basic / Analogue Basic |
| `DBA` | Digital Basic |
| `EXT` | Extended |
| `DEX` | Digital Extended |
| `RES` | Residential |
| `ANG` | Analogue |
| `PL1` | FX/COM/NG/WILD/PEO |
| `PL13` | ALL DISNEY/BABY/FX/NG |
| `PL14` | ALL FX, NG & BABY |
| `THE` | Theme Package |
| `HD` | High Definition |
| `IPL` | IPLA |
| `OTT` | OTT |
| `FNG` | FX, NG CORE & NG WILD |
| `BU2` | NAT GEO & NAT GEO WILD |
| `COM` | Commercial |
| `PPR` | Pre-Paid Residential |
| `DCJ` | DC & Junior |
| `CAN` | CANAL+ Service |

**Table 4 — Bundle Indicator (key ↔ Bundled value):**
Key format: `[SYSTEM-ID][SERVICE-CODE]`, e.g. `124266PL13` → `Bundled = Yes`

---

### Sheet 7: Error_Log — Error Journal

A record of all emails and reports that could not be processed automatically.

**Structure:**

| Column | Description |
|---|---|
| `Email` | Sender address |
| `Topic` | Email subject |
| `Client` | Client name / contact |
| `Country` | Country |
| `Error` | Error code |
| `Reason` | Reason for rejection |
| `Date` | Date of occurrence |

**Example error entry:**
```
Email: malgosia@wektormedia.pl
Topic: RAPORT WEKTOR SP. Z O.O. ŻAGAŃ
Error: NOT PROCESSED
Reason: Not qualified for processing
```
> This means the operator is not configured in `MailManager_Mapping` — the macro found no match for the sender's email address.

---

### VBA Modules — Code Architecture

`SR_Macro.xlsb` contains 13 VBA modules. Each is described below.

#### Module Overview

| Module | Type | Role |
|---|---|---|
| `ThisWorkbook.cls` | Workbook class | Displays the loading screen when the file is opened |
| `A_MailManager.bas` | Standard module | Main orchestrator — email handling and operator queuing |
| `B_CompareChannels.bas` | Standard module | Channel-matching engine (fuzzy match + bundle logic) |
| `B_MAPPING.bas` | Standard module | Auxiliary data-mapping helpers |
| `R_MROSA_EXCL.bas` | Standard module | Excel / ODS report processor |
| `R_ERAP_SOP.bas` | Standard module | Nasza Wizja SOP report processor |
| `R_MROSA_PDF.bas` | Standard module | PDF report processor (OCR text) |
| `R_MROSA_DOC.bas` | Standard module | Word document processor (legacy / fallback path) |
| `A_AIS_file.bas` | Standard module | AIS file import and output file export |
| `X_Funcitons.bas` | Standard module | Utility functions (regex, text cleaning) |
| `X_RangeToHTML.bas` | Standard module | Excel range → HTML conversion (for email body) |
| `frmLoading.frm` | UserForm | Splash / loading screen |
| `frmProgressForm.frm` | UserForm | Progress bar during import |

---

#### `ThisWorkbook.cls` — startup

Single procedure: `Workbook_Open()` — triggered automatically when the file is opened. Calls `ShowLoadingScreen`, which displays `frmLoading` briefly before handing control back to the user.

---

#### `A_MailManager.bas` — main orchestrator

Contains the global variables used throughout the macro:

| Global variable | Type | Description |
|---|---|---|
| `EImportFile` | String | Full path to the downloaded attachment |
| `EDate` | String | Report date for the operator |
| `EType` | String | File type (xlsx / pdf / ods) |
| `EBundle` | String | Bundle channel name (e.g. `NAT GEO CORE`) |
| `SpecBundle` | Boolean | Whether a special bundle (NAT GEO CORE sum) applies |
| `MFileName` | String | Name of the .msg archive file to save |
| `ServMonth` | Integer | Service month number (1–12) |

**Main procedure: `Import_Mails_and_Call_Macros`**

Step by step:

1. **Connect to Outlook** — reads the mailbox name from cell `I3` of the MACRO sheet.
2. **Iterate the inbox** — loops over all items in reverse order (newest first).
3. **Identify the operator** — two mechanisms:
   - Match sender email address against column C in `MailManager_Mapping`
   - If no match: search for the operator name in the email body
4. **Nasza Wizja path** — if sender is `noreply-sop@naszawizja.org`, the macro uses column R of the mapping and calls `R_ERAP_SOP.Run`.
5. **Download attachment** — saved to `Import\[country]\[affiliate_id]\[name]-[timestamp]_[file]`; a `.msg` archive is saved alongside it.
6. **Invoke sub-macro** — `Application.Run Emacro` (value from the `Call Macro` column in the mapping, e.g. `R_MROSA_EXCL.Run`).
7. **On success** — email moved to the `Completed import` subfolder; Outlook category set.
8. **On error** — exception logged in the `Error_Log` sheet (fields: Email, Topic, Client, Country, Error, Reason, Date).

---

#### `B_CompareChannels.bas` — matching engine

Two key procedures:

**`FuzzyMatch(s1, s2) → Boolean`**

Channel name matching algorithm:
- Computes the Levenshtein distance between two strings
- Distance ≤ 2 → returns `True` (match)
- Additionally: hard-coded aliases for Polish variant names:

| Variant in operator report | Channel name in AIS |
|---|---|
| `NATIONAL GEOGRAPHIC` | `NAT GEO CORE` |
| `NATIONAL GEOGRAPHIC WILD` | `NAT GEO WILD` |
| `NATIONAL GEOGRAPHIC PEOPLE` | `NAT GEO PEOPLE` |
| `DISNEY` | `DISNEY CHANNEL` |
| `FOX` | `FX` |
| `FOX COMEDY` | `FX COMEDY` |

**`Compare()`**

Main comparison procedure:
1. Filters the `AIS Import` sheet by the current operator's affiliate ID
2. Copies matching records into the `Temp` sheet
3. For each channel in the operator's report: attempts `FuzzyMatch` against every channel name in AIS
4. On match: writes the subscriber count into the corresponding row in `AIS Export`
5. No match: writes `Not Found`
6. If `SpecBundle = True`: calls `Sum_NATGEOCORE` (sums NAT GEO CORE values across multiple sub-operators)

---

#### `B_MAPPING.bas` — mapping helpers

**`Sum_NATGEOCORE`** — sums NAT GEO CORE subscriber values from the `Temp` sheet (used when one operator reports multiple systems).

**`Mapping_Sysnbr`** — remaps the system number when the operator name in the file differs from the name registered in AIS (e.g. the operator has been renamed).

---

#### `R_MROSA_EXCL.bas` — Excel / ODS processor

Processes reports in `.xlsx`, `.xls`, or `.ods` format.

Algorithm:
1. Creates a working sheet named `Temp`
2. Opens the operator file with `Workbooks.Open`
3. Locates headers using `Range.Find`:
   - `"raport o stanie"` → row containing the month name
   - `"SUMA"` → totals row (end of data)
   - `"Pakiet"` → column containing package codes
4. **Month validation** — compares the month found in the file against `ServMonth`; mismatch → sets `varReason = "Incorrect month"` and exits
5. **Row loop** — for each data row:
   - `Val1` = channel name
   - `Val2` = package code
   - `Val3` = system number (SYSTEM-ID)
   - `Val4` = affiliate number (AFFILIATE-ID)
   - `Val5` = subscriber count (average: (month start + month end) / 2)
6. Calls `Compare()` → results written to `AIS Export`
7. Fills columns 16–17 (bundle indicator) after Compare completes

---

#### `R_ERAP_SOP.bas` — Nasza Wizja processor

Processes files from the Nasza Wizja platform (SOP format — fixed column layout).

Differences from `R_MROSA_EXCL`:
- Channels: column B; packages: column F; subscribers: column E — fixed positions
- Data starts at row 19
- Month read from cell C15
- Multiple rows may relate to different Nasza Wizja sub-operators (separate iteration over column R of the mapping)

---

#### `R_MROSA_PDF.bas` — PDF processor

Processes reports converted from PDF to plain text (by PdfGrabber).

Algorithm:
1. Opens the text file (`.txt`) — OCR output
2. Regex `"za okres:"` → extracts the reporting month
3. Regex `"arytmetyczna"` → extracts average subscriber values (Polish decimal format: comma as decimal separator)
4. Parses text lines, builds a data structure identical to the EXCL processor
5. Calls `Compare()` → results in `AIS Export`

---

#### `R_MROSA_DOC.bas` — Word processor (legacy)

Alternative path for operators who send reports as Word documents (`.doc` / `.docx`).

- Opens the document via COM (`CreateObject("Word.Application")`)
- Parsing logic analogous to R_MROSA_EXCL
- Rarely used in practice (most operators use Excel)

---

#### `A_AIS_file.bas` — AIS file handler

Two procedures:

**`AISimp()`** — import data from the AIS central system:
- Opens a file dialog (`Application.GetOpenFilename`) — user selects the AIS file
- Pastes range A2:U[lastRow] into the `AIS Import` sheet
- Updates cell `I30` of the MACRO sheet with the loaded file name

**`AISxpt()`** — export results to file:
- Copies the contents of the `AIS Export` sheet
- Removes the header row and column 22
- Saves as `System Reports\Export\Exported data_DD.MM.YY.xlsx`

---

#### `X_Funcitons.bas` — utility functions

| Function | Description |
|---|---|
| `RemoveCommaInValue(s)` | Removes commas from numeric values (Polish format → AIS format) |
| `RemoveSpecialChars(s)` | Strips special characters from operator names (regex) |
| `RegExpExtract(s, pattern)` | Extracts a substring using a regular expression |
| `CountLinesOfCode()` | Counts VBA lines of code in the file (developer tool) |

---

#### `X_RangeToHTML.bas` — range to HTML

Function `RangetoHTML(rng As Range) → String`:
- Saves the given range as a temporary HTML file
- Reads it back as a string
- Used when generating an email body containing a formatted data table (readable by the recipient)

---

#### `frmLoading.frm` — loading screen

A simple UserForm displayed when the file is opened. Contains a logo/graphic and a `CloseForm()` method. Closed automatically after initialisation (`Workbook_Open`) completes.

---

#### `frmProgressForm.frm` — progress bar

UserForm with a progress bar shown during email import. The `UserForm_Activate` procedure calls the `MailManager` procedure (the actual import logic) and displays the message `"Import in progress..."`. The progress bar is updated as each email is processed.

---

## 2. Subupload with Bundled Indicator.xlsx — Upload File

This file is used to **manually or semi-automatically upload data to the AIS system**. It contains one sheet, `Report 1`, with **5,029 rows** of global data (not limited to Poland).

### Data Scope
- **78 countries** — a global view covering all EMEA and further markets
- **22 Disney legal entities**
- **39 channels** (networks)
- **355 package names** / 360 package codes
- **5 reporting frequencies**

### Structure (22 columns — identical to AIS Import)

| Column | Description |
|---|---|
| AFFILIATE-ID | Affiliate ID |
| COUNTRY-SERVED | Country code |
| STATE-SERVED | Region |
| CITY-SERVED | City |
| SYSTEM-NAME | Operator system name |
| SYSTEM-ID | System ID |
| NETWORK | Channel ID (numeric) |
| SERVICE-CODE | Package code |
| CABLE-HOUSEHOLDS | Number of households with access |
| SUBSCRIBERS | **Subscriber count to be reported** |
| SMSO-IND | SMSO indicator |
| PO-NUMBER | Purchase order number |
| SERVICE-MONTH | Month (1–12) |
| SERVICE-YEAR | Year |
| NETWORK-NAME | Channel name |
| SERVICE-NAME | Package name |
| Frequency | Frequency (MONTHLY / QUARTERLY / etc.) |
| Last Reported Subs | Previously reported count |
| Last Date | Date of previous report |
| Service Area | Service area |
| Legal Entity | Disney legal entity |
| Bundled | `Yes` if the package is bundled |

### How It Is Used

1. After processing all operator reports, `SR_Macro.xlsb` updates the `SUBSCRIBERS` field for Polish (and other) operator records
2. The user verifies data in the `AIS Export` sheet (comparison view)
3. The file is then uploaded to the Disney AIS portal manually

### The Bundled Column — Explanation
`Bundled = Yes` means the operator has purchased a bundled package (e.g. `PL13 = ALL DISNEY/BABY/FX/NG`). A single subscriber figure then covers multiple channels simultaneously. The macro must account for this when mapping data channel-by-channel into AIS.

### Key Legal Entities

| Code | Country |
|---|---|
| `TWDCPO` | Poland (The Walt Disney Company Poland) |
| `TWDCDE` | Germany |
| `TWDCGB` | United Kingdom (global FNG EMEA entity) |
| `TWDCBG` | Balkans / Central Europe |
| `TWDCFR` | France |
| `TWDCSEA` | Southeast Asia |

---

## 3. folder create.bat — Folder Creation Script

A Windows command-line (`cmd`) batch script that creates the operator folder structure inside `Import/POL/`.

### Script content (excerpt)
```bat
md 12781
md 12784
md 12786
...
md 15258
```

### What it does
- Creates **~246 numeric sub-folders**
- Each number is an **affiliate ID** from the AIS system (the `AFFILIATE-ID` column in `MailManager_Mapping`)
- These folders are the drop target for `.xlsx` and `.msg` files received from operators

### How to run
1. Open a Command Prompt (`cmd`) in the `Import/POL/` directory
2. Execute: `folder create.bat`
3. Folders will be created; if a folder already exists, `md` will print an error but the script continues

### ID Range
- Lowest: `12781`
- Highest: `15258`
- Most IDs correspond to operators in `MailManager_Mapping`; a few may be reserved for future use

### Note
The script is intended for one-time execution during initial system setup or when new operators are onboarded. It is not required for day-to-day macro operation.

---

## 4. icon.ico — Application Icon

A Windows icon file (`.ico` format) used by the shortcut `SR_Macro - Shortcut.lnk`. It gives the shortcut a recognisable appearance on the desktop or in Windows Explorer. It has no effect on system behaviour.

---

## 5. End-to-End Data Flow

```
┌──────────────────────────────────────────────────────────────┐
│ CABLE / IPTV OPERATOR                                        │
│  Fills in the Excel template (SUB REPORT sheet):            │
│  - channels, packages, subscriber counts (start / end of    │
│    month)                                                    │
│  Sends to: intl.emea.poland.subs.reports@disney.com         │
│  Subject: RAPORTY                                            │
└──────────────────────┬───────────────────────────────────────┘
                       │ email with .xlsx / .ods / .pdf attachment
                       ▼
┌──────────────────────────────────────────────────────────────┐
│ OUTLOOK MAILBOX                                              │
│  DWSS.Autocashsupport@disney.com                            │
└──────────────────────┬───────────────────────────────────────┘
                       │ Macro scans the inbox
                       ▼
┌──────────────────────────────────────────────────────────────┐
│ SR_Macro.xlsb — Sheet: MailManager                          │
│  1. Identify sender → look up in MailManager_Mapping        │
│  2. Download attachment → Import/POL/[affiliate_id]/        │
│  3. Save .msg (email archive)                               │
│  4. Log entry in MailManager (sender, subject, time,        │
│     status=New)                                             │
└──────────────────────┬───────────────────────────────────────┘
                       │ Invoke sub-macro
                       ▼
┌──────────────────────────────────────────────────────────────┐
│ R_MROSA_EXCL.Run  or  R_MROSA_PDF.Run                       │
│  - Parse operator file (SUB REPORT sheet)                   │
│  - Map channels and packages via the Mapping sheet          │
│  - Retrieve historical data from AIS Import                 │
│  - Calculate average = (month start + month end) / 2        │
│  - Detect bundles (Bundled=Yes) → one record covers N chans │
└──────────────────────┬───────────────────────────────────────┘
                       │
          ┌────────────┴──────────────┐
          ▼                           ▼
┌─────────────────────┐    ┌──────────────────────────────────┐
│ AIS Export          │    │ Admin/Export/PL/[Operator]/      │
│  Comparison:        │    │  YYYYMMDD_HHMMSS_DISNEY10...xls  │
│  new vs historical  │    │  YYYYMMDD_HHMMSS_[email_subj].msg│
│  Match / ok / Diff. │    └──────────────────────────────────┘
└─────────────────────┘
                       │
                       ▼
┌──────────────────────────────────────────────────────────────┐
│ Subupload with Bundled Indicator.xlsx                        │
│  SUBSCRIBERS field updated for Polish (and other) operators  │
└──────────────────────┬───────────────────────────────────────┘
                       │ Manual upload via AIS portal
                       ▼
┌──────────────────────────────────────────────────────────────┐
│ AIS — Disney Affiliate Information System                    │
│  Central subscriber database                                 │
│  Generates → System Reports/Import/P[MM]_FY[YY]_Subs_report │
└──────────────────────────────────────────────────────────────┘
```

---

## 6. Glossary

| Term | Explanation |
|---|---|
| **AIS** | Affiliate Information System — Disney's central system for collecting subscriber data |
| **Affiliate** | An operator holding a Disney licence agreement; identified by AFFILIATE-ID |
| **System** | A specific cable / IPTV network belonging to an affiliate; one affiliate may have multiple systems (SYSTEM-ID) |
| **Bundle / Bundled** | A combined package — the operator has purchased a single tier covering multiple Disney channels simultaneously |
| **Service Code** | The internal package code used by the operator (e.g. `DBA`, `PL13`) — resolved via the Mapping sheet |
| **Legal Entity** | The Disney legal entity that issues invoices (e.g. `TWDCPO` = The Walt Disney Company Poland) |
| **Nasza Wizja** | A Polish distribution platform aggregating smaller cable operators |
| **R_MROSA_EXCL** | VBA sub-macro for processing Excel / ODS files |
| **R_MROSA_PDF** | VBA sub-macro for processing PDF files |
| **SMSO-IND** | Same Month Submission Only Indicator — an AIS system flag |
| **FNG EMEA** | Fox Networks Group EMEA — the Disney unit responsible for FX / NGC channels in EMEA |
| **Service Month / Year** | The month and year for which subscriber data is being reported |
| **Last Reported Subs** | Data from the previous report — used as the baseline for the `Compare` column in AIS Export |
| **Frequency** | Reporting cadence: `MONTHLY`, `QUARTERLY`, `SEMI ANNUALLY`, `ANNUALLY` |