# Publishing Sales Accounting Run Process

> Source: [Publishing Sales Accounting Run Process (Notion)](https://www.notion.so/a504bc32c8ab4513bd5136c6ddd16c20)  
> Last updated in Notion: May 2026

Publishing sales are processed **quarterly**. They are handled as **manual adjustments** (with statement attachments) as part of the legacy accounting run.

## Contacts

| Role | Current Contact | Responsibility |
|------|----------------|----------------|
| Publishing team lead | Aidan Miller | Rate approval, split instructions, final sign-off |
| Data matching | James Kass | Transaction matching (adds vendor_id, shares, etc.) |
| Finance confirmation | Kam | Confirms SMP payment receipt |
| Payment approval | Matt | Go-ahead for processing |
| Statement generation | Aleix | Generates statement files for S3 upload |
| Customer Accounting | Ciara's team | Receives approved manual adjustments |
| Scripting / tooling | Wilson Cheong | Maintains [publishing-sales-accounting-run](https://github.com/deltaex1/publishing-sales-accounting-run) scripts |

> **Maintenance note:** When team members change, update this table. Role references below will remain accurate.

## Pre-Requisites

- Create a Google Drive folder in the [Revenue directory](https://drive.google.com/drive/folders/1WvCx5vo6R1ERLBaB1F7E9Z449A1kXlxl)
- Set up a meeting with the publishing team lead (see [Contacts](#contacts)) to discuss timing and any special considerations

## Step 1: Retrieve the Sales File

The sales file from Sony Music Publishing (SMP) is delivered via a Sharepoint link (too large for SCORE).

## Step 2: Run Prep and Messaging

### Get the Numbers
The [publishing Python scripts](https://github.com/deltaex1/publishing-sales-accounting-run) (maintained by the scripting contact; see [Contacts](#contacts)) compile the key figures:

1. **Total royalty amount** — column W (`RoyAmt`)
2. **Non-standard royalty rates** — column V (`Roy %`); anything not 90.0%
3. **Negative transactions** — column W negatives

### Meet with Publishing Team
Discuss how to handle non-standard rows and negatives.

### Send Numbers to Finance
Email the totals to the finance distribution list (see [Contacts](#contacts)). Finance will confirm payment receipt from SMP. Wait for payment approval before proceeding.

**Exclusions:** If rows are excluded, move them to a separate sheet for potential later processing.

### Missing Song IDs
The publishing script identifies songs with blank/incorrect `ExtSong` values. Cross-reference against [master mapping list](https://docs.google.com/spreadsheets/d/1V2dSWIA0HvQ8-0nPtY2lK7YRa3CSKQGBF5MCAQeucsU). Send missing list to the publishing team lead for completion.

Verify all `ExtSong` IDs exist in:
```sql
SELECT DISTINCT pub_song_id FROM facts.prod.publishing_composition WHERE DRAFT = FALSE;
```

### Missing Songwriter Agreements
Check for agreements missing in Snowflake but present in Neo4j. Fix via [DB PR sync](https://github.com/theorchard/database/pull/25190/files).

### Send File to James Kass
Post a checksum notice in `#pub-sales-processing` (total rows + sum of column W). The data matching contact runs a matching process that adds: `transaction_id`, `vendor_id`, `has_multiple_owners`, `vendor_controlled_share`, `adjusted_share`.

## Step 3: Calculation

### Compile Contract Rates
The publishing script compiles rates. Requires the data matching output + the [contract admin file](https://docs.google.com/spreadsheets/d/1zSGK7pNypsQMfo-1qxHx8Gbs1_yJ6CCl). Share with the publishing team lead for approval.

**Currency note:** Use Abacus currency codes, not the contract admin sheet:
```sql
SELECT account_id, currency_code
FROM ORCHARD_APP_REPORTING_V2.PROD_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.ACCOUNT_PAYMENT_TERM
WHERE account_id IN (...) ORDER BY account_id;
```

### Snowflake Configuration
- **Role:** `ROYALTYACCOUNTING_DB_<PROD|QA>_SCHEMA_READWRITE`
- **Warehouse:** `<PROD|QA>_ABACUS_WH`
- **Queries:** [Publishing Run folder in Snowflake](https://app.snowflake.com/sme/orchard/#/publishing-run-f6kiylZdt)

### Load and Calculate

1. **Load sales file** — download, rename to `Sony_Publishing_P<period_id>.txt`, gzip, upload to S3, run Snowflake load query
2. **Load contract rates** — download as TSV, upload to S3, run Snowflake load query
3. **Match transactions** — run "02 - CTS Match Transactions"
4. **Manual fiddling** — update `base_fee` for copub deals, specific exclusions (Lovely Bastards, sombr)
5. **Calculate sales** — run "03 - Accounting Run Results"
6. **Review** — run "04 Report Summary"

Results viewable in: `royalty_accounting.prod.vw_abacus_fact_sales_publishing`

## Step 4: Payment Approval

1. Copy [Publishing Run Results template](https://docs.google.com/spreadsheets/d/1eQd2c1br1KWplgsn8fR-3nu4HmCQn_EBgx7txLQKrgM)
2. Fill in vendor data from Snowflake query results
3. **QA checks:**
   - Calculated Fee Diff should be 0% per row
   - Multi-owner totals + exclusions should match original sales total
   - Fees + Net Revenue should balance to zero delta
4. Send to the publishing team lead for approval

## Step 5: Final Steps

### Create Manual Adjustments
1. Copy template from [Abacus Adjustments](https://abacus.theorchard.com/adjustments)
2. Populate from run results (follow Aidan's special instructions for splits)
3. Upload to QA for validation, then send to the publishing team lead for final sign-off
4. Send approved adjustments to Customer Accounting (see [Contacts](#contacts))

### Statement Generation and Upload
1. The statement generation contact generates statement files → uploaded to S3 (`qa-publish-client-report`)
2. Download via AWS CLI: `aws s3 sync s3://qa-publish-client-report .`
3. Upload to `prod-orcdbucket/prod-statement-attachments` (auto-processing moves them)
4. **Files over 20MB:** Upload a placeholder first, then overwrite directly in `prod-per-label-statement-attachments`
5. Create a DB PR to add contract IDs to statement attachment records
6. Verify uploads, spot-check accounts after period goes live