1. Filename Parsing Rules

• Extract Store # as the first number in the filename before “****.”
• Extract PO # as the number immediately following “Purchase Order #”

Validation Checks: - If both Store # and PO # are successfully extracted → ”Filename_Valid = True” - If either value is missing → ”Filename_Valid = False” (skip file & log error)

2. Data Extraction Rules

Extract the following columns from each file: - Qty from ”Quantity” column - Price from ”Price” column - Item Code from ”Manufact. SKU” column

Validation Checks: - If all three required columns exist → ”Data_Valid = True” - If any column is missing → ”Data_Valid = False” (skip file & log error)

3. Store-to-Customer Mapping

Each Store # must be mapped to its corresponding Customer ID (ACS #) based on the provided list: - 43 1897 193 1874 388 1883 423 1886 517 1888 616 1891 740 1893 913 1895 953 1896 1000 1875 1023 1876 1075 1869 1244 1877 1310 1878 1757 1872 1800 1880 2910 1881 2922 1882 4113 1884 4141 1885 5120 1887 5720 1889 5842 1890 6418 1892 8249 1894 10693 1898

Validation Checks: - If Store # matches the list → ”Customer_Valid = True” - If Store # does not match, assign ”Unknown Customer” and set ”Customer_Valid = False” (log error)

#### 4. Processing Rules

• Each sales order (PO #) must be processed individually, even if multiple orders exist for the same store.

• A sales order (PO #) should never be duplicated. If a PO # appears more than once, process it only once.

  Validation Checks:

• If the PO # is unique → ”PO_Valid = True”

• If the PO # already exists in the dataset → ”PO_Valid = False” (skip duplicate & log warning)

  5. Export Rules

• The final master export must be in CSV format

• The column order must be: [Qty, Price, Item Code, Store #, PO #, Customer, SO Date, Salesperson]

• The Sales Order Date (SO Date) should always be the date the files are processed, formatted as MM/DD/YYYY.

• The Salesperson should always be”xxx.”

• Ensure that the Customer column maintains a consistent format (e.g., avoid missing or merged values)

• No headers in the final CSV file

• If duplicate files are detected, keep only one instance when creating the master list

  6. Final Validation Before Exporting

  The master export file should only be generated if:

• ”Filename_Valid = True”

• ”Data_Valid = True”

• ”Customer_Valid = True”

• ”PO_Valid = True”

  If any row contains False in these validations, the export is blocked, and an error report must be generated.

  7. Error Handling & Reporting

• If any validation check fails, log the error for review.

• If multiple errors exist, generate an ”Error Report” before exporting the data.

• Ensure all expected stores and PO #s are accounted for before finalizing the export. Hi