Sony Music Publishing

SONY_PUBComposition

PUBLISHER · XLSX · USD

Configuration

Display name: Sony Music Publishing
Key: SONY_PUB
Rights layer: Composition
File format: XLSX
Default currency: USD
Source kind: PUBLISHER
Notes: CS04 royalty statement. The preamble states "Result: N record(s)" — cross-check that N against parsed_row_count after import, since stop_at_empty truncates at the first fully blank row inside the range.

Extraction query

Reads XLSXUpdated 07/12/2026

-- Sony Music Publishing CS04 statement -> normalized schema
SELECT
  row_number() OVER ()                                          AS line_number,
  trim("Song")                                                  AS song_title,
  upper(trim("Song"))                                           AS song_title_key,
  COALESCE(NULLIF(upper(trim("Src4 Nm")), ''),
           NULLIF(upper(trim("Src3 Nm")), ''),
           NULLIF(upper(trim("Src2 Nm")), ''),
           upper(trim("Src Nm")))                               AS source_name_key,
  upper(trim("Inc Typ"))                                        AS income_class_key,
  upper(trim("Inc Typ"))                                        AS right_type_key,
  COALESCE(NULLIF(upper(trim("Src4Ctry")), ''),
           NULLIF(upper(trim("Src3Ctry")), ''),
           NULLIF(upper(trim("Src2Ctry")), ''),
           upper(trim("Src Ctry")))                             AS territory_raw,
  trim("Writer")                                                AS writer_name,
  TRY_CAST("Units" AS DECIMAL(14,2))                            AS units,
  TRY_CAST("SngShr%" AS DECIMAL(9,6))                           AS share_percentage,
  TRY_CAST("RoyAmt" AS DECIMAL(18,4))                           AS amount_raw,
  FALSE                                                         AS adjustment,
  CAST(try_strptime(substr(COALESCE(NULLIF(trim("Sales Pd"), ''),
                                    trim("Rpt'g Pd")), 1, 4), '%m%y') AS DATE) AS earning_date,
  NULLIF(upper(trim("ISWC Cd")), '')                            AS iswc,
  NULLIF(upper(trim("ISRC")), '')                               AS isrc,
  'USD'                                                         AS currency,
  to_json(row)                                                  AS source_row
FROM read_xlsx('{{file}}',
          header = true,
          all_varchar = true,
          stop_at_empty = true,
          range = 'A10:AD1048576') AS row;

Raw row count query — overrides the generic reader for the integrity check

SELECT COUNT(*) AS raw_count FROM read_xlsx('{{file}}', header = true, all_varchar = true, stop_at_empty = true, range = 'A10:AD1048576');

Territory resolution

Edit overrides

territory_raw is resolved to an ISO 3166-1 alpha-2 code in code via a country lookup — not with rules. Values the lookup can't resolve (historical territories, regional groupings) fall back to this override table.

No overrides yet. Every territory value so far resolves through the country lookup.

Normalization rules

Edit rules

This source's own rules, grouped by the attribute each writes. First match wins, top to bottom; conditions within a rule are ANDed. Each group ends in a pinned catch-all.

Stage 1 — Raw match keys to standard enums

income_channel_std

26 rules

1STREAMING_AUDIOincome_class_key ~ STRMMECH*

2STREAMING_AUDIOincome_class_key ~ STRMPRF*

3STREAMING_AUDIOincome_class_key ~ MSTRMMCH*

4DOWNLOADincome_class_key ~ DPDMECH*

5DOWNLOADincome_class_key ~ MDPDMECH*

6PHYSICALincome_class_key ~ PHY?MECH*

7RINGTONESincome_class_key ~ MRNGTN*

8RINGTONESincome_class_key ~ MRNGBK*

9STREAMING_VIDEOincome_class_key ~ ISYNC*

10TVincome_class_key ~ STV*

11VIDEO_GAMESincome_class_key ~ SVIDGM*

12ADVERTISINGincome_class_key ~ SCOMM*

13ADVERTISINGincome_class_key ~ SPRO/TRA*

14HOME_VIDEOincome_class_key ~ SYNCVID*

15LIVEincome_class_key ~ STH/LIV*

16SYNC_GENERALincome_class_key ~ SYNC*

17LYRICSincome_class_key ~ ILYRCRPT*

18LYRICS_PRINTincome_class_key ~ LYRCRPT*

19SHEET_MUSIC_DIGITALincome_class_key ~ ISHEETMU*

20SHEET_MUSIC_DIGITALincome_class_key ~ ITAB*

21SHEET_MUSICincome_class_key ~ SHEETMUS*

22KARAOKE_DIGITALincome_class_key ~ IKAROKE*

23KARAOKEincome_class_key ~ KAROKE*

24KARAOKEincome_class_key ~ SKARMEC*

25BROADCAST_MECHANICALincome_class_key ~ MECHPERF*

26OTHERincome_class_key ~ OTHER*

Catch-allUNCLASSIFIEDMatches every line not caught above.

right_type_std

25 rules

1MECHANICALright_type_key ~ STRMMECH*

2MECHANICALright_type_key ~ MSTRMMCH*

3MECHANICALright_type_key ~ DPDMECH*

4MECHANICALright_type_key ~ MDPDMECH*

5MECHANICALright_type_key ~ PHY?MECH*

6MECHANICALright_type_key ~ MRNGTN*

7MECHANICALright_type_key ~ MRNGBK*

8MECHANICALright_type_key ~ KAROKE*

9MECHANICALright_type_key ~ IKAROKE*

10MECHANICALright_type_key ~ SKARMEC*

11MECHANICALright_type_key ~ MECHPERF*

12PERFORMANCEright_type_key ~ STRMPRF*

13SYNCright_type_key ~ ISYNC*

14SYNCright_type_key ~ STV*

15SYNCright_type_key ~ SVIDGM*

16SYNCright_type_key ~ SCOMM*

17SYNCright_type_key ~ SPRO/TRA*

18SYNCright_type_key ~ STH/LIV*

19SYNCright_type_key ~ SYNC*

20PRINTright_type_key ~ ILYRCRPT*

21PRINTright_type_key ~ LYRCRPT*

22PRINTright_type_key ~ ISHEETMU*

23PRINTright_type_key ~ ITAB*

24PRINTright_type_key ~ SHEETMUS*

25OTHERright_type_key ~ OTHER*

Catch-allUNCLASSIFIEDMatches every line not caught above.

Stage 2 — Standard enums to derived values

is_digital

6 rules

1truenormalized_income_channel ~ STREAMING_*

2truenormalized_income_channel ~ DOWNLOAD

3truenormalized_income_channel ~ RINGTONES

4truenormalized_income_channel ~ LYRICS

5truenormalized_income_channel ~ SHEET_MUSIC_DIGITAL

6truenormalized_income_channel ~ KARAOKE_DIGITAL

Catch-allfalseMatches every line not caught above.

is_performance

1 rule

1truenormalized_right_type ~ PERFORMANCE

Catch-allfalseMatches every line not caught above.