Doctor Extension Report Process

Overview of Doctor Extension Process:
- The Doctor (DR) extension report standardizes and tracks active DR records exceeding baseline duration thresholds.
- Timeline coverage extends past 1 year and 6 months, reaching up to nearly 2 years, specifically capturing durations up to 725 days.
- The primary workbook serves as the master dataset before final outputs are formatted and submitted into Connexium.
Excel Tab Configuration:
- Tab 1 ("master"): Holds the complete, unfiltered master report continuously referenced as the central source.
- Tab 2 ("with days"): A duplicated copy of the master tab used for dynamic filtering, data manipulation, and active day calculations.
- Layout Adjustments:
- Freeze the top row (header line) on the "with days" tab to maintain field visibility during scrolling.
- Enable column header filter drop-downs across the sheet.
- Delete Column Y, which contains "the breakdown" data, as it is unnecessary for extension tracking.
Calculating Active Days:
- Create a new header named Number of Days in place of the deleted breakdown column.
- Calculate active DR duration by finding the difference between Column R and Column S.
- Active days formula: $\text{Number of Days} = \text{Expiration Date} - \text{Start Date}$
- Apply this formula across all 4,000 records in the dataset.
- Re-engage header filters to isolate specific extension cohorts (e.g., 180-day baseline cohorts, confirming setup dates such as 09/25 versus March).

Cohort Identification:
- Identify DRs initially designated for 180 days that have been extended to 360 days in Zebra's records.
- Track outlier records reaching 720 to 725 days as requested by Donna.
- Filter the "with days" tab specifically for records displaying 360 days in Zebra system data.
Extracting and Deduplicating Data:
- Copy the entire list of filtered 360-day DRs.
- Paste the extracted data into a clean staging area and perform data cleanup by running Remove Duplicates.
- Validate the count: The deduplicated list typically yields approximately 140 to 141 DR records, matching standard Doctor report baselines.
SAP Export and Formatting:
- Export the deduplicated 141-record list into SAP to verify whether SAP records mirror the 360-day setup.
- Export file naming convention: Save export file under an identifier such as Zeebs extends.
- Open the exported SAP file, freeze the top header row, and apply filter drop-downs.

SAP Column Formatting and Formula Setup:
- Borrow an unused column in the status block area and rename the header to Number of Days.
- Cell Formatting Requirement: Explicitly verify and format the entire calculation column as General. Failing to set cell formatting to General results in erroneous numeric outputs.
- Apply the active days formula in the SAP export file: $\text{Number of Days} = \text{Expiration Date} - \text{Start Date}$
- Apply the formula down across all exported SAP rows.
Reconciliation Logic (Identifying Deficits):
- The list imported into SAP represents DRs configured as 360 days in Zebra.
- Filter the SAP Number of Days column to locate values less than 360 days (specifically identifying records still displaying 180 days).
- Any record showing 180 days in SAP represents an un-extended record requiring an update.
- Cross-reference un-extended DR numbers back to the master workbook to verify the newly updated expiration date (e.g., confirming extended dates like 03/26).
- Add verified DRs needing extension to the final Doctor Extension List.

Processing Extended Cohorts:
- Apply the identical extraction and cross-check methodology to identify DRs extended from 365 days up to 545 days (representing 1.5 years in Zebra records).
- Extract initial dataset batches, which typically yield around 529 to 544 total raw records before deduplication.
- Deduplicate the extracted dataset to isolate unique DR entries.
Analyzing the SAP Export (Zebra 545 EX EA):
- Export the 545-day candidate list from SAP (saved under file designations such as Zebra 545 EX or Zebra 545 EX EA).
- Configure the Number of Days column formatted as General using the formula: $\text{Number of Days} = \text{Expiration Date} - \text{Start Date}$
- Filter the SAP export for records displaying duration values of 365 days (records still sitting at 1 year in SAP that need pushing to 545 days).
- Filter outputs showing values like 546, 549, or 0 indicate duplicates or already extended records. Values of 0 strictly denote duplicate row entries.

Filtering Out Extended Records:
- Perform final deduplication on the 545-day SAP list to reduce clutter.
- Historical entries fall off over time; active attention focuses on records remaining at 365 days.
- Record Evaluation Case Example:
- DR 8931 appears twice in output lists.
- Detailed inspection of the green-coded row for DR 8931 shows a calculated validity of 546 days ( $546 \ge 545$ ).
- Because DR 8931 is already valid for over 545 days, remove 8931 from the pending extension list.
Assembling Final Extension Action List:
- Retain only un-extended candidate DRs on the final action sheet.
- Confirmed candidate DRs requiring immediate extension include: 7473, 6756, 77, along with 2 additional verified DR numbers (totaling approximately 5 to 6 actionable extensions).
- Spotlight Rule: DRs covered under Spotlight protocols are capped or extended precisely to 12/31 where applicable.

Core Reconciliation Logic Summary:
- The Doctor report defines the target validity period set by business rules.
- In SAP reconciliation, search for values less than the Zebra target duration:
- Target = 360 days $\rightarrow$ Filter SAP for $< 360$ days (e.g., 180 days) to identify missing extensions.
- Target = 545 days $\rightarrow$ Filter SAP for $< 545$ days (e.g., 365 days) to identify missing extensions.
- Avoid misinterpreting records already showing full duration (e.g., 545 or 546 days) as pending actions.
Operational Execution Schedule:
- Automated macros cannot penetrate SAP interface boundaries for this cross-system verification.
- Execution frequency: Conducted 3 times per week (typically a Monday, Wednesday, and Friday schedule).
- Timing alternatives: Executed during end-of-day processing or integrated into morning report routines based on Sales department requests.
- Final output distribution: Compile master/final report combining validated 360-day and 545-day extension lists (noting edge cases like 725-day requests) and deliver to Connexium and stakeholder teams.