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.

DOMINICAN REP.→DO

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.