""" config.py — Publishing Sales Accounting Run Configuration ========================================================== Update the QUARTERLY section below at the start of each new run. Everything else should remain stable across quarters unless org details change. """ from pubsalesacc.utils.dates import get_reporting_month_object # ============================================================================= # QUARTERLY — update these values at the start of each run # ============================================================================= # Snowflake period ID for this run PERIOD: int = 327 # Source sales file downloaded from Sony Music Publishing SharePoint # Supported formats: .csv or .xlsx FILENAME: str = "Orchard_Dec_2025.csv" # Local directory containing the sales file — leave as "" if in project root FILEPATH: str = "" # Prefix used for all generated output files this run (format: {YYYY}{MON}_{YYYYMMDD}) FILESERIES: str = "2025DEC_20260313" # Standard royalty percentage(s). Anything not in this list is flagged as non-standard. # Updated 2025-01-13: changed from [87.5] to [90.0] STANDARD_ROY_PCT: list[float] = [90.0] # ============================================================================= # DERIVED — auto-computed from system date, no manual update needed # ============================================================================= _d = get_reporting_month_object(-1) YEAR: str = _d["YYYY"] MONTH: str = _d["Month"] # File received back from James Kass after his matching process JAMES_FILE_PATH: str = f"Sony_Publishing_P{PERIOD}.txt" # Generated by step 2 (reconciliation) — sent to Aidan Miller to fill in missing IDs PUBSONG_LOOKUP_FULL: str = f"PubSongIDsNotInListMaster-{PERIOD}.xlsx" PUBSONG_LOOKUP_LITE: str = f"{MONTH} {YEAR} - Missing Songs & Agreements.xlsx" # Generated by step 5 (contract rate calculation) — reviewed by Publishing team CONTRACT_RATES_FILE: str = ( f"Publishing Contract Rates - {MONTH} {YEAR} - Period {PERIOD}.xlsx" ) # Downloaded from Abacus > Reports > Payout Rates before running step 6 # See README for instructions on obtaining this file CURRENCY_RATES_FILE: str = f"Payout Rates {MONTH} {YEAR} ({PERIOD}).xls" # ============================================================================= # FILE PATHS — stable, rarely change quarter to quarter # ============================================================================= # Master song ID correction lookup — accumulates corrections across all quarters # Source: https://docs.google.com/spreadsheets/d/1V2dSWIA0HvQ8-0nPtY2lK7YRa3CSKQGBF5MCAQeucsU PUBSONG_LOOKUP_MASTER: str = "Missing Pub Song Correction Master Lookup.xlsx" # Publishing deal tracker — download a fresh copy from Google Sheets before step 5 # Source: https://docs.google.com/spreadsheets/d/1zSGK7pNypsQMfo-1qxHx8Gbs1_yJ6CCl LABEL_MAP_FILE: str = "Publishing Admin Deal Tracker_Official.xlsx" # ============================================================================= # CLEANUP — controls which files get archived in step 9 # ============================================================================= CLEANUP_EXTENSIONS: list[str] = ["xlsx", "txt", "bak", "zip", "csv", "xlsb"] CLEANUP_EXCEPTIONS: list[str] = [ "n4j-dbpr-template.txt", "requirements.txt", PUBSONG_LOOKUP_MASTER, LABEL_MAP_FILE, ] # ============================================================================= # ORG CONSTANTS — update only if personnel or org structure changes # ============================================================================= # Jira: Aidan Miller's account ID — assigned to DBPR Neo4j sync tickets JIRA_ASSIGNEE_ID: str = "712020:ddb3bf46-f93a-44e9-bdfd-a1791f794516" # Neo4j DBPR Liquibase changeset authorship DBPR_AUTHOR: str = "wcheong" DBPR_AUTHOR_UUID: str = "edc2a5e0-d499-4ec3-bc93-052998728bd7" # Neo4j cluster hostnames per environment NEO4J_HOSTS: dict[str, str] = { "dev": "dev-neo4j-cluster.dev.theorchard.io:7687", "qa": "qa-neo4j-cluster.theorchard.io:7687", "prod": "prod-neo4j-cluster.theorchard.io:7687", } # Finance and Publishing team email recipients for the royalty output report (step 1) # Ready for future email automation FINANCE_EMAIL_RECIPIENTS: list[str] = [ "Mey Tseng ", "Peter Cairis ", "Publishing ", "Kam Sue Chang ", "Tina Kim ", "Kari Lystad ", "James Kass ", "wilson.cheong@sonymusic-pde.com", "pkang@theorchard.com", "aidanm@theorchard.com", "statements@theorchard.com", "Sam Thomas ", "jessica.farquharson@theorchard.com", "ciara.mcsorley@theorchard.com", "george.theka@sonymusic-pde.com", "liz.mcaskil.ext@theorchard.com", "nlombard@theorchard.com", "tdenkinger@sonymusic-pde.com", ] # Slack channel for the checksum notice posted before sending the file to James Kass # Ready for future Slack automation SLACK_CHECKSUM_CHANNEL: str = "#pub-sales-processing" # Google Drive Revenue folder — parent directory for quarterly audit folders # Ready for future Drive automation GDRIVE_REVENUE_FOLDER_ID: str = "1WvCx5vo6R1ERLBaB1F7E9Z449A1kXlxl" # Google Sheets source URLs — for reference and future automation MASTER_LOOKUP_GDRIVE_URL: str = "https://docs.google.com/spreadsheets/d/1V2dSWIA0HvQ8-0nPtY2lK7YRa3CSKQGBF5MCAQeucsU" DEAL_TRACKER_GDRIVE_URL: str = ( "https://docs.google.com/spreadsheets/d/1zSGK7pNypsQMfo-1qxHx8Gbs1_yJ6CCl" ) # ============================================================================= # SPECIAL ACCOUNT HANDLING # ============================================================================= # Elysium Melodies Limited (793219) — tiered base fee based on gross sales in SMP file # Determine gross total for this account then apply the appropriate tier before # loading contract rates into Snowflake ELYSIUM_ACCOUNT_ID: int = 793219 ELYSIUM_RATE_TIERS: list[tuple] = [ (0, 150_000, 0.800), # < $150k → 80% (150_000, 500_000, 0.825), # $150k–$500k → 82.5% (500_000, 1_000_000, 0.850), # $500k–$1M → 85% (1_000_000, 2_000_000, 0.875), # $1M–$2M → 87.5% (2_000_000, None, 0.900), # > $2M → 90% ] # Lovely Bastards (account 25223) — 0% base fee override for these specific song IDs LOVELY_BASTARDS_ACCOUNT_ID: int = 25223 LOVELY_BASTARDS_SONG_IDS: list[int] = [61290, 61291, 61305, 61569, 61686, 61295] # Abacus query template: payout currency code per account # Used in step 6 (payment summary); {account_ids} replaced at runtime with # a comma-separated list of vendor IDs from the contract rates sheet ABACUS_CURRENCY_QUERY: str = """ SELECT account_id, currency_code FROM ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.ACCOUNT_PAYMENT_TERM WHERE account_id IN ({account_ids}) ORDER BY account_id """