Docstoc

Data dictionary

Document Sample
Data dictionary Powered By Docstoc
					                                 Existing Licensing System


Revision History
Date of Change   Version   Editor      Change
9/13/2007        1.0       Jed Meade




                                                             1
Terms

     Active licenses – Licenses that are requested and not marked as DNU or voided.
     Blue content/tracks – One of three licensing classifications (Green, blue and yellow). For blue
      content, the label manages/pays all licensing fees to Publisher but in effect, they prefer a separate
      check.
     BBA or “Billy Bad Ass” - A series of queries for the primary Access db files of the licensing
      system (master, HFA and research queue).
     Direct publisher – Direct publishers use eMusic’s standard/boilerplate mechanical license
      agreement and are paid directly (as opposed to through HFA).
     DNU – Do not use.
     End-dated – Start-dating and end-dating are used in the existing licensing system to track license
      change history. Used to determine when to stop paying one publisher and start paying another.
     Green content/tracks – One of three licensing classifications (Green, blue and yellow). For green
      content, the label manages/pays all licensing fees to Publisher.
     HFA – Harry Fox Agency.
     PD – Public Domain. There are no mechanical royalties for public domain tracks.
     QRR – Quarterly Royalty Reports.
     6595 – Legacy license IDs left over from the old (San Diego) system. HFA still confirms license
      requests sent when these IDs were still in use. So, we need to retain these IDs to match the license
      confirmation in the HFA output file with the appropriate record in the new system.
     Start-dated – Start-dating and end-dating are used in the existing licensing system to track license
      change history. Used to determine when to stop paying one publisher and start paying another.
     Sub-accounts – Some publishers have many sub-accounts. Bug Music, for example, has about
      580 sub accounts.
     3rd party publisher – A publisher that prefers to use their own mechanical license agreement as
      opposed to eMusic’s.
     Yellow content/tracks – One of three licensing classifications (Green, blue and yellow). For
      yellow content, eMusic manages/pays all licensing fees to each publisher. eMusic pays label
      separately.



Roles

     Licensing Admins – Rebecca Hughes and Shilpa Das
     Researchers – Miguel Dizon and Michael Fredricks
     Royalties Admin – Formerly James Kass
     Royalties Manager – Evan Parness




                                                                                                          2
Ongoing Process Prior to Quarterly Process

Label Color Determination and Content Import

1) Label Relations and Legal establish a contract with a particular label.
   a) Color coding is determined: green, blue or yellow label
2) Content is delivered by label.
3) Content is ingested. Color is added by the content team:
   a) During manual ingestion, color added manually for each album.
   b) For remote ingestion, color added when the partner is initially created in the content system
      (partner has one payee which has many labels).
4) Content export file generated weekly and posted here: http://abv-mdb-
   01.ny.emusic.com/content/ingestion/
5) Export file is reviewed by Licensing Admin. They will use the Contracts database to look up any
   questionable label colors.
   a) If any label colors are incorrect, they are fixed in the file prior to import. Licensing Admin will e-
      mail any such color updates to Content team. Content team will then update the color in the
      content system.
6) Export file is imported into licensing system.



License Research and License Runs

Green Content

1) Green content is stored directly in the master table.
2) Date licensed is set upon import.


Blue Content

3) Blue content is flagged with the dummy researcher of “BLU”. This must be done prior to assigning to
   researchers since the assignment macro will assign any track without a researcher.
4) Blue content is also assigned dummy publisher info of “[Label Name] – Publishing”.
5) Blue content is saved into the master table with a date licensed field at the end of the license run.


Yellow Content

6) Licensing Admin auto-imports songwriter/publisher data from Finetunes through Access system. The
   macro supposedly handles an Uploadr feed but there are no feeds for this according to Alex.
      i) Songwriter/publisher info from these two aggregators is generated and posted to their FTP site.
          Once a week, content will pull this down and post it to the network.
7) Yellow label tracks are assigned to researchers.
   a) The Licensing Admin enters the # of researchers.
   b) The Licensing Admin enters the initials for each researcher.
                                                                                                               3
    c) The unassigned tracks are divided up equally by the # of researchers.
         i) The tracks in the queue are ordered by street date at time of import.
8) Researchers look on HFA site (approx 70% of the data is found here) or elsewhere on the Internet to
    find the following data:
    a) Songwriters – One or many songwriters.
    b) Publishers – One or many publishers.
         i) In instances where publisher info can not be found, publisher is set to “Unknown”
         ii) If a publisher for a given track is not in the system, the researcher will flag the track for
             follow-up. The Licensing Admin will then confirm that the publisher is missing (as opposed to
             just named differently in the system). If the publisher does not in fact exist in the system, the
             Licensing Admin will add the new publisher. See the Publishers section.
    c) % of ownership – percentage of the track that each publisher owns.
    d) Medley – whether or not the song is a medley.
9) Tracks are reviewed and either approved or rejected by the Licensing Admin.
    a) Tracks can be rejected for whatever reason (songwriter info appears incorrect, etc.). The Licensing
         Admin will typically provide a reason for rejection in the notes area. To reject a track, the
         Licensing Admin will uncheck the “Done” box. Once rejected, the track(s) will disappear from the
         approval queue and re-appear in the researcher’s queue.
10) If the researched info looks OK to the Licensing Admin, they will check the “QA’ed” box to approve.
11) When enough license requests are ready to be sent, the Licensing Admin will do a “license run”.
    There are typically several license runs a month.
12) The Licensing Admin will run a macro in the system to generate license request lists. Examples of
    such files are here: \\tb2\public\Product\jed\licensing\2007-10-16 License Run\
    a) HFA license request list (xls)
    b) Direct publisher license request list to be sent via e-mail (xls)
    c) Direct publisher license request list to be sent via fax (xls)
    d) Direct publisher license request list to be sent via postal mail (xls)
    e) 3rd party license request
         i) “3rd party” is a publisher that uses their own license request agreement as opposed to eMusic’s
             licensing agreement template.
13) Licensing Admin will run the following queries in the “LICENSE RUN BETA” file:
    a) Step 8a - Display publisher mismatches – Generates a report where a publisher is entered in the
         research queue but does not exist in the publisher table.
    b) Step 8b - Add tracks to master table – Insert all tracks in the research queue that have been
         approved by the Licensing Admin to the master table. This includes researched yellow tracks as
         well as blue tracks (where no research is necessary).
    c) Step 9 - Remove tracks from research queue – Delete the tracks from the research queue that have
         been inserted into the master (in step 8b).


HFA License Requests

14) Licensing Admin uses client program to generate an HFA license request file (xls):
    \\tb2\public\Product\jed\licensing\EM2HFA.exe
    a) See HFA license request file specs in Data Dictionary section.
15) Licensing Admin will upload the license request file to a directory on HFA’s FTP server
    (ftp://ftp.harryfox.com – ask licensing for username/password) and e-mail HFA to notify them the file
    has been uploaded.
16) HFA will upload an output/confirmation file (tab-delimited text file) of all executed licenses to the
    same FTP location and reply to the e-mail we sent notifying us that this file is there.
                                                                                                          4
    a) Note: not all requested licenses will be executed immediately by HFA. In some cases, HFA will
         upload executed licenses that were requested years before.
17) Licensing Admin will need to cleanup the HFA file prior to import. They will pull the file into excel
    and check for anomalies such as odd license numbers, numbers where letters should be, etc.
18) Licensing Admin will import the cleansed file into a temporary “holding table”.
19) Licensing Admin will check the holding table for anomalies such as odd characters, shifted lines (due
    to field values being dropped), quotes (which will need to be removed).
    a) Also check for “6595” IDs. These numbers are legacy track ID values from the older system (San
         Diego). As mentioned previously, HFA will sometimes send executed licenses referring to these
         old codes. If there are 6595 numbers, Licensing Admin will need to run two updates after the next
         step.
20) Licensing Admin will transfer HFA executed license records from the holding table to the master
    table. This query is in the “Licensing Database Workshop.mdb” file and it’s titled “HFA Move
    Holding to HFA Master Table”.
    a) For any “6595” IDs found in the previous step, run the following two queries in the Licensing
         Database Workshop.mdb file:
         i) Update Master HFA Oldies – copies all “6595” IDs into the old code field. i.e. “Archives” the
             “6595” ID.
         ii) Update Master HFA List with New Codes – this looks up the new catalog ID corresponding to
             the “6595” ID and copies it into the catalog number field of the HFA Master table. i.e. inputs
             the new ID in lieu of the “6595” ID.
21) Licensing Admin will archive HFA holding table as mdb file in \\prk-files-01\licensing\Licensing
    Docs\Backups\.


Direct Publisher License Requests

31) Licensing Admin uses a client program to generate partially executed license agreements (using
    eMusic’s license agreement template) based on the e-mail, fax and postal mail xls lists:
    \\tb2\public\Product\jed\licensing\EMLicense - direct tool.exe
32) Licensing Admin e-mails, faxes or mails partially executed licenses to publisher.
33) Publisher will sign (execute) the license agreement and send this back to Licensing Admin.
34) Upon receipt, Licensing Admin will physically file the executed license.
    a) As the executed licenses are returned by the publisher, the license date is manually entered into the
        system.


3rd Party Publisher License Requests

As mentioned, a “3rd party” publisher is a publisher that uses their own license request agreement as
opposed to eMusic’s license request agreement.

41) Licensing Admin will send one license request (xls sheet) to each 3rd party publisher.
42) The 3rd party publisher will send a partially executed license agreement back to Licensing Admin.
43) Licensing Admin will sign the license agreement, file a copy for eMusic records and send the fully
    executed license back to the 3rd party publisher.
    a) As the licenses are executed, the license date is manually entered into the system.



                                                                                                          5
Remaps

Remaps are macros written to bulk update certain fields in the licensing system. In particular, remaps are
most commonly used each week to change incorrect colors and labels after new content has been ingested
into the licensing system.

   Label or Album Color Remap – This is run along with the label remap.
       o Remapping to yellow
                Finds all green or blue active license records associated to a particular label (via
                  dropdown in the tool) or album (eMusic album ID) from the master table. Note: this
                  does not look for blue tracks in research queue.
                Inserts new record into the research queue marking the color as yellow (Y1) and the
                  start date as the date specified in the tool. Start dates are preset to the first day of each
                  future quarter.
                Updates the old record with an end date (the day before the selected start date) and any
                  notes that were entered.
       o Remapping to blue
                Finds all yellow or green active license records associated to a particular label (via
                  dropdown in the tool) or album (eMusic album ID) from the master table.
                Inserts new record into the research queue marking the color as blue (B1) and the start
                  date as the date specified in the tool. Start dates are preset to the first day of each future
                  quarter.
                Updates the old record with an end date (the day before the selected start date) and any
                  notes that were entered. Any yellow license records that were in the research queue
                  will be flagged as “Research Done” and re-assigned to the BLU researcher. This is
                  required to “license” a blue track.
       o Remapping to green
                Finds all yellow or blue active license records associated to a particular label (via
                  dropdown in the tool) or album (eMusic album ID) from the master table.
                Inserts new record into the master table marking the color as green (G1) and the start
                  date as the date specified in the tool. Start dates are preset to the first day of each future
                  quarter.
                Updates the old record with an end date (the day before the selected start date) and any
                  notes that were entered. Any license records that were in the research queue will be
                  deleted.

   Full or Album Label Remap – This is run along with the color remap. Could potentially combine with
    color remap
        o Finds all active license records associated to a particular label (via search in the tool) or album
            (eMusic album ID) from the master table and research queue.
        o If the record was in the master table:
                The new record is inserted into the master table with the updated start date as the date
                   specified in the tool. Start dates are preset to the first day of each future quarter.
                The old record is updated with an end date (the day before the selected start date) and
                   any notes that were entered.
        o If the record was in the research queue, the old record is simply updated with the new label
            name.

   Full or Song Publisher Remap This is rarely (if ever) used
                                                                                                               6
       o Finds all active license records associated to a particular publisher (via dropdown in the tool)
         or song (eMusic song ID).
       o Inserts new record into the master table with the new publisher (selected via dropdown in the
         tool) and the start date as the date specified in the tool.
              Tool includes the option to insert the new tracks into the research queue instead of the
                 master table.
       o Updates the old record with an end date (the day before the selected start date) and any notes
         that were entered.


Adding and Editing Publishers

Editing Publishers

If publishers information is not correct on the license, they will send corrected info. We make these
corrections in the tool.

   1. Edit publishers through the “Edit Publishers” form in the Research Admin module.
   2. On a quarterly basis, Royalty Manager provides a dump of existing publisher data from
      Counterpoint.
   3. That publisher’s record is copied from Counterpoint into an xls log. This xls log contains all
      updated Publishers in a given quarter. The Licensing Admin highlights changes to publisher info
      in the log.
          a. Sample log is here (publisher updates are on the first sheet):
              \\tb2\public\Product\jed\licensing\UPDATED 3Q07 NEW AND EXISTING
              PUBLISHERS for Evan.xls
   4. This log is sent to Royalties at the end of the quarter to update the publisher list in Counterpoint.
      The Royalties Manager will make these updates manually.


Adding New Publishers

If publishers are not found during the research phase, they’ll need to be added.

   1. Add the new publisher through the “Add New Publisher” form in the Research Admin module.
      New publishers are researched at various sites on the Web.
   2. As license confirmations are received from newly added publishers in a given quarter, the
      Licensing Admin copies publisher information from the licensing system into this log.
          a. Sample log is here (new publishers are on the second sheet):
              \\tb2\public\Product\jed\licensing\UPDATED 3Q07 NEW AND EXISTING
              PUBLISHERS for Evan.xls
   3. At the end of the quarter, before sending the log to Royalties, the columns that Counterpoint does
      not need are removed (Publisher Code, Payee Code, direct/HFA checkbox, Notes, 3rd
      party_direct, and the Update_Tracking field).
   4. The log is sent to Royalties. The Royalties Manager will import the new publishers into
      Counterpoint.




                                                                                                              7
Post-Quarter Process
This is the process that occurs within the first month following the close of each quarter.


QRR

1) A series of queries are run to export data from the licensing system. These exports are referred to as
   QRR reports. Examples of each of these reports can be found in:
   \\tb2\public\Product\jed\licensing\QRR Reports Q207\
2) The QRR reports consist of the following:
   a) QRR Blue content – An export of blue tracks that are not public domain and were inserted into the
       master table after the last day of the preceding quarter.
   b) QRR Direct – An export of yellow tracks that are not public domain, were flagged as direct
       (Direct/HFA flag) and were inserted into the master table within the preceding quarter.
   c) QRR EndDated and StartDated – An export of any tracks that are not public domain where the
       publisher, color or label has been end-dated in the previous quarter or start-dated for the following
       quarter. This export is only used by the licensing team to QA the label remaps. The licensing team
       will look for any label remaps that look incorrect.
       i) Royalties Manager does not import label remap data since those are handled separately in
           Counterpoint. Royalties Manager and Licensing Admin both maintain a log for label remaps
           (xls). Royalties and Licensing will compare sheets to verify that all label remaps are accounted
           for.
   d) QRR EndDated and StartDated (No label remaps) – Same as endated/startdated export above
       except excluding label remaps.
   e) QRR Green content – An export of green tracks that are not public domain and were inserted into
       the master table after the last day of the preceding quarter. This is export is only used by the
       licensing team to QA the green content.
       i) The Royalties Manager will not import this data into Counterpoint.
   f) QRR HFA – An export of tracks that were licensed by HFA (via HFA confirmation file) after the
       last day of the preceding quarter where the HFA Rep Share and HFA license number are greater
       than zero (i.e., they exist).
   g) QRR HFA Voids – An export of HFA tracks that were voided after the last day of the preceding
       quarter.
   h) QRR Medley – An export of tracks that are not public domain but were flagged as a medley and
       were licensed after the last day of the preceding quarter.
   i) QRR Public Domain/Unpublished – An export of our public domain or unpublished tracks that
       were not flagged as a medley and were licensed after the last day of the preceding quarter.
   j) QRR Publisher Unknown – An export of tracks from unknown publishers that were not flagged as
       a medley and were inserted into the master table after the last day of the preceding quarter.
3) Licensing Admin will review these files and correct any issues with the data in the licensing system.
4) Royalties Admin will generate the export the data again.
5) Licensing Admin will review the files one final time, then deliver the files to Royalties Manager.
6) Royalties Manager will import data from the following reports into Counterpoint:
   a) QRR Blue content
   b) QRR Direct
   c) QRR EndDated and StartDated (No label remaps)
   d) QRR HFA
   e) QRR HFA Voids
                                                                                                           8
   f) QRR Medley
   g) QRR Public Domain/Unpublished
   h) QRR Publisher Unknown


Songwriter/Public Domain (PD) Research

Buma Stemra requires that we submit songwriter data when remitting mechanical royalties for EU/UK
downloads. PD and spoken word tracks are not subject to mechanical royalties. So, it’s important to find
as many PD tracks as possible before remitting royalties for the quarter. Songwriter and Public Domain
research happens in parallel to the QRR imports into Counterpoint. Typically, one researcher looks for
songwriter and the other finds out if a track is PD or not.

1) After the final weekly ingestion file is imported into the Licensing system, the Licensing Admin will
   run a process to update the songwriter research queue. This process will:
   a) The Songwriter queue is synched with the master table and research queue.
       i) Remove tracks with songwriters (in the master table or research queue) from the songwriter
            queue.
       ii) Update the master table and research queue with songwriter data found in the songwriter
            queue.
       iii) Import any new tracks with missing songwriters from the master table and research queue.
   b) Import the latest EU/UK sales file. These files are generated on a quarterly basis.
   c) The total downloads for each track are calculated based on the sales file. Download counts for
       each song will be used to prioritize what gets researched first.
   d) Add any new tracks pulling metadata from the master table (album ID, label, artist, album, track
       number and song title).
2) Before any manual research, Engineering (Bob) runs a Songwriter-to-AMG match to find more
   songwriters.
   a) Licensing will provide Engineering with an MDB file containing an export of all tracks from the
       master table that meet the following criteria:
       i) No songwriter information exists.
       ii) No enddate has been set.
       iii) Has “24” or a null value for a code. “24” denotes tracks where we need to start paying. (The
            date we need to start paying is set in the notes field.)
   b) Engineering will attempt to match these tracks against AMG metadata resulting in two tab-
       delimited files that they will send to the Licensing Admin:
       i) Exact match – artist name and song title in provided file exactly match an artist and song title
            in AMG.
       ii) “Fuzzy” match - artist name and song title in provided file exactly match song title but loosely
            match an artist name in AMG. The loose match is determined by an Oracle function that can
            compare similar sounding artist names.
   c) Licensing will QA the “fuzzy” matches to verify that they are, in fact, matches.
   d) Licensing will update the master table and songwriter queue with the matching songwriters (by
       Album ID). This update query is written by hand each quarter.
3) For each track in the queue, researchers will look online to find songwriter info and whether or not
   track is PD. Researchers will typically have 2 to 3 weeks to get as much research done before it must
   be delivered to royalties for calculation.
4) An export is sent to Royalties for:
   a) Each track where songwriter has been found. This update query is written by hand each quarter.

                                                                                                           9
      i) This songwriter file consists of eMusic catalog ID and songwriters.
      ii) Each of these tracks will be flagged with the date that it is delivered to Royalties (in the DTR-
          SW field).
   b) Each track where PD has been determined. PD file consists of eMusic catalog ID and Public
      Domain flag (Yes/No).
      i) Each of these tracks will be flagged with the date that it is delivered to Royalties (in the DTR-
          PD field).
      ii) Royalties Manager changes yes to no and vice versa in export file prior to import into
          Counterpoint. We should just store values according to how Counterpoint expects them to
          eliminate the need to flip.
5) Royalties Admin will import each of these files into Counterpoint.
   a) For each track where the songwriter and PD status have been delivered to royalties, they will
      moved into the “SWQ-Delivered” (archived) table and deleted from SWQ (active) table. This data
      is migrated for performance reasons – to cut down on the response time for queries on this table.




                                                                                                         10
Data Dictionary
EMusic_Mechanical_Licensing_Master_Table (in New Licensing Database - Master Table.mdb)

Field                    Description                                                    Data Type         Possible Values
Album ID                 Album ID in eMusic catalog. Fed in from the content feed.      Number (Long
                                                                                        Integer)
Label                    Label name.                                                    Text (128)
Artist                   Artist name.                                                   Text (255)
Album                    Album name.                                                    Text (255)
Track #                  Number of the track on the album.                              Number (Long
                                                                                        Integer)
Song Title               Song title.                                                    Text
Song Length              Song length.                                                   Date/Time
                                                                                        (hh:mm:ss)
eMusic Catalog #         eMusic catalog ID for the track.                               Text (32)
Songwriter(s)            List of songwriters for the track.                             Text (255)
Publisher                Publisher for the track.                                       Text (255)
Ownership %              Percentage of that song that the specified publisher in that   Number (Double)   0-100
                         record owns.
Direct/Harry Fox         Yes = Licenses directly from publisher; No = Licenses          Yes/No            Yes or No
                         through HFA.
Date Sent                Date license request was sent.                                 Date/Time
                                                                                        (MM/dd/yyyy)
Date Licensed            Date license was confirmed. For green content, this is         Date/Time
                         updated upon import of the content feed into the licensing     (MM/dd/yyyy)
                         system. For blue content, this is updated for each license
                         run (when license requests are generated). For yellow
                         content, this is updated upon receipt/confirmation of the
                         license from the publisher or HFA.
Delivered to Royalties   This is used effectively to backdate certain records so they   Date/Time
                         are not delivered again to royalties.                          (MM/dd/yyyy)
Notes                    These notes are entered during the remap process. In some      Text (255)
                         cases, this field is written into directly.
Key_Code                 These codes were used prior to 2006 to determine the           Text (2)          Should be one of
                                                                                                                             11
status of tracks. Codes correspond to the following:           the values listed
 00    DO NOT USE                                              in the description
 01    BAD ADDRESS
 02    SENT TO WRONG PARTY
 03    GOES THRU HFA
 04    GOES DIRECT
 05    LICENSE DENIED
 06    WRONG OWNERSHIP %
 07    OWNED BY E-MUSIC
 08    LOST PACKAGE/RE-SEND
 09    SHOULD BE PUBLIC DOMAIN
 10    SHOULD NOT BE PUBLIC DOMAIN
 11    ALREADY LICENSED PER LABEL AGREEMENT
 12    COVERED UNDER
       ASSIGNMENT/BLANKET/WAIVER AGREEMENT
 13    DO NOT USE AFTER ______ (date)
 14    RE-SENT TO PUBLISHER
 15    PUBLISHER UNKNOWN AFTER INDEPENDENT
       RESEARCH & PER RECORD LABEL - 25% of S
 16    TRACK TAKE DOWN PER LABEL (This code still in
       use) Should integrate this with content tool takedown
       if possible.
 17    ESCROW ACCOUNT 100%
 18    LICENSE SENT VIA EMAIL
 19    SENT TO SAME PUBLISHER TWICE
 20    EXCEPTION TO THE
       BLANKET/WAIVER/ASSIGNMENT LICENSE
 21    LICENSE REVISIONS MADE/RESENT
 22    PREVIOUSLY LICENSED
 23    SENT TO WRONG PARTY, NOW IS "PUBLISHER
       UNKNOWN"
 24    PAY STARTING ______ (date) - not retroactively
       (This code still in use)
 25    THIRD PARTY LICENSE REQUIRED
 26    HFA LICENSE FOR NEW AGRMT IN HFA TABLE
       DATED 9/21/2004
 27    TRACK TAKE DOWN PRIOR TO 11/08/03

                                                                                    12
                       N      JSP PUBLISHING DATA TAKEN FROM NAPSTER
                              REPORT, 2002

                       28     Tracks Already Paid By Royalties - RE: Mechanical
                              Licenses Sent By Third Party Publishers (on their
                              form)
Label Code            Color of label for that song (green, blue, yellow). Needed       Text (2)          G1, B1, (B2
                      to keep track of label color at the time each license was                          exists but is no
                      granted.                                                                           longer used), Y1
Live                  16 denotes that track has been taken down. 16 is a code          Text (50)
                      that *should* be entered into the live field but is not (for
                      some unknown reason).
ISRC_code             ISRC code for the track. Fed in from the content feed.           Text (16)
HFA_song_code         This is entered during research. This value is input to          Text (8)
                      facilitate the license confirmation from HFA. If we provide
                      HFA with this code, it guarantees confirmation of the
                      license for that particular track.
Medley                Denotes whether or not the song is a medley. If a song is        Yes/No
                      medley, there will be multiple sets of publishers.
Researcher Tracking   The ID or name of the researcher for that song.                  Text (5)
Pending               Legacy – no longer used.                                         Text (10)         “Pending”
date_inserted         Date record is inserted into master table. Green label tracks    Date/Time
                      are inserted immediately. Blue and yellow label tracks are       (MM/dd/yyyy
                      inserted when the license run is complete.                       hh:mm:ss AM/PM)
Date_modified         Date record is modified.                                         Date/Time
                                                                                       (MM/dd/yyyy
                                                                                       hh:mm:ss AM/PM)
Delete                This field is used to flag tracks for deletion, similar to the   Yes/No
                      function of a recycle bin. Shilpa suggests this is legacy.
                      Should confirm with Rebecca.
Library_Id            Library ID from content system. Legacy – no longer used.         Text (50)         CD xxxx
Research Category     Legacy – no longer used.                                         Text (32)
Research Notes        Notes used in the research process for yellow content.           Text (255)
Old_Code              Old license IDs (“6595’s” from San Diego). Used to match         Text (32)
                      HFA license confirmations for license requests sent by the
                      old system.
                                                                                                                            13
Denial_Date           Legacy – no longer used.                                    Date/Time
                                                                                  (MM/dd/yyyy)
Voided_Date           Date license was voided. Old licenses are voided any time   Date/Time
                      the associated label color changes.                         (MM/dd/yyyy)
Remap_Publisher_Dat   A timestamp signifying the end of a particular track’s      Date/Time
e_End                 association to a publisher.                                 (MM/dd/yyyy)
Remap_Publisher_Dat   A timestamp signifying the beginning of a particular        Date/Time
e_Start               track’s association to a publisher.                         (MM/dd/yyyy)
Remap_Color_Date_E    A timestamp signifying the end of a particular track’s      Date/Time
nd                    association to a color.                                     (MM/dd/yyyy)
Rempa_Color_Date_S    A timestamp signifying the beginning of a particular        Date/Time
tart                  track’s association to a color.                             (MM/dd/yyyy)
Remap_Label_Date_E    A timestamp signifying the end of a particular track’s      Date/Time
nd                    association to a label.                                     (MM/dd/yyyy)
Remap_Label_Date_S    A timestamp signifying the beginning of a particular        Date/Time
tart                  track’s association to a label.                             (MM/dd/yyyy)



tblPublishers (in New Licensing Database - Master Table.mdb)

Field                 Description                                                 Data Type      Possible Values
Publisher             Publisher name.                                             Text (255)
Publisher Code        Assigned by Counterpoint. However, these are no longer      Text (255)
                      needed in the licensing system.
Payee Code            Assigned by Counterpoint. However, these are no longer      Text (255)
                      needed in the licensing system.
First Name            First name of licensing manager.                            Text (255)
Last Name             Last name of licensing manager.                             Text (255)
Company               Company name.                                               Text (255)
Street Address        Street address of the company.                              Text (255)
City                  City the company is in.                                     Text (255)
State                 State the company is in.                                    Text (255)
Zip Code              Zip code the company is in.                                 Text (255)
Country Code          Country the company is in.                                  Text (255)
Direct / Harry Fox    Yes = Licenses directly from publisher; No = Licenses       Yes/No         Yes or No
                                                                                                                   14
                     through HFA.
Email                E-mail address of the licensing manager.                         Text (255)
Fax                  Fax number of licensing manager.                                 Text (255)
Phone                Fax number of licensing manager.                                 Text (255)
Tax ID               Tax ID of the company.                                           Text (255)
Payee / Check Name   Name on check if different from company name.                    Text (255)
Notes                Used for any notes, particularly to note changes to              Memo
                     publisher info.
Bad Address          Flagged if address provided is not valid. During the license     Yes/No
                     run, if the address is not valid, these are sent to HFA to see
                     if they have a correct address (even though they are always
                     direct publishers).
3rd Party Direct     Flagged if a publisher is 3rd party direct. This means they      Text (255)        Yes or No
                     use their own licensing agreement as opposed to eMusic’s
                     boilerplate agreement.
Update_tracking      Legacy – no longer used.                                         Memo



Research_Import (in New Licensing Database - Research Queue.mdb)

Field                Description                                                      Data Type         Possible Values
Album ID             Same as master table                                             Number (Double)
                     (EMusic_Mechanical_Licensing_Master_Table).
Label                Same as master table                                             Text (128)
                     (EMusic_Mechanical_Licensing_Master_Table).
Artist               Same as master table                                             Text (255)
                     (EMusic_Mechanical_Licensing_Master_Table).
Album                Same as master table                                             Text (255)
                     (EMusic_Mechanical_Licensing_Master_Table).
Track #              Same as master table                                             Number (Long
                     (EMusic_Mechanical_Licensing_Master_Table).                      Integer)
Song Title           Same as master table                                             Text (255)
                     (EMusic_Mechanical_Licensing_Master_Table).
Song Length          Same as master table                                             Time (hh:mm:ss)
                     (EMusic_Mechanical_Licensing_Master_Table).
                                                                                                                          15
Emusic Catalog #         Same as master table                                        Text (50)
                         (EMusic_Mechanical_Licensing_Master_Table).
Songwriter(s)            Same as master table                                        Text (255)
                         (EMusic_Mechanical_Licensing_Master_Table).
                         Songwriters are entered here by the researchers.
Publisher                Same as master table                                        Text (255)
                         (EMusic_Mechanical_Licensing_Master_Table).
                         Publishers are entered here by the researchers.
Ownership %              Same as master table                                        Number (Double)
                         (EMusic_Mechanical_Licensing_Master_Table). This is
                         researched and entered by the researcher.
Direct / Harry Fox       Same as master table                                        Yes/No            Yes or No
                         (EMusic_Mechanical_Licensing_Master_Table).
Date Sent                Same as master table                                        Date/Time
                         (EMusic_Mechanical_Licensing_Master_Table).                 (MM/dd/yyyy)
Date Licensed            Same as master table                                        Date/Time
                         (EMusic_Mechanical_Licensing_Master_Table).                 (MM/dd/yyyy)
Delivered to Royalties   Same as master table                                        Date/Time
                         (EMusic_Mechanical_Licensing_Master_Table).                 (MM/dd/yyyy)
Key_Code                 Same as master table                                        Text (10)
                         (EMusic_Mechanical_Licensing_Master_Table).
Label_Code               Same as master table                                        Text (10)
                         (EMusic_Mechanical_Licensing_Master_Table).
Isrc_code                Same as master table                                        Text (16)
                         (EMusic_Mechanical_Licensing_Master_Table).
HFA_song_code            Same as master table                                        Text (8)
                         (EMusic_Mechanical_Licensing_Master_Table).
                         Researchers look for this on HFA’s site and enter here if
                         found.
Medley                   Same as master table                                        Yes/No            Yes or No
                         (EMusic_Mechanical_Licensing_Master_Table).
                         Researcher look for this on the Web and enter here if
                         found.
Pending                  Same as master table                                        Text (10)
                         (EMusic_Mechanical_Licensing_Master_Table).
Date_inserted            Same as master table                                        Date/Time
                                                                                                                   16
                          (EMusic_Mechanical_Licensing_Master_Table).                     (MM/dd/yyyy)
LIBRARY_ID                Same as master table                                            Text (50)
                          (EMusic_Mechanical_Licensing_Master_Table).
Research Notes            Any notes left by the researcher or Licensing Admin             Text (255)
                          during the research process.
Research_Done             A flag set by researchers when a track is done. This flag       Yes/No             Yes or No
                          puts the track in the approval queue for the Licensing
                          Admin.
Research_Follow-Up        A flag used by either the researcher or the Licensing           Yes/No             Yes or No
                          Admin to denote a track that requires further follow-up.
Research_QA_Done          A flag set by the Licensing Admin if the researched track       Yes/No             Yes or No
                          is approved.
Research_Initials         The initials of the researcher for the track. These intitials   Text (3)
                          are set when the tracks are assigned to each researcher.
Import_File               The name of the import file this track came from. The           Text (50)
                          import name is named according to the date it was
                          generated. The week this track was ingested can therefore
                          be determined from this.
Streetdate                Street date for that particular track. Tracks in the research   Date/Time
                          queue are ordered by street date at the time of import.         (MM/dd/yyyy)




HFA License Request File and Output File

For each license run, we generate a license request file for HFA using the following naming convention: Emusic_ManufacturerNumber
_EMU_eLicensing_DateGenerated.txt

This file is uploaded to a directory on HFA’s FTP server. HFA will ingest and process the license request file after it is uploaded onto HFA’s
FTP site. HFA will then post an output file back to the FTP site under the following naming convention: ManufacturerNumber_xxxx_xxxxxx

The data included in both the request file and the output file is as follows:




                                                                                                                                             17
18
19
20
21
HFA_Master_table (in New Licensing Database - HFA Table.mdb)

Our HFA master table consists of data fed in from the HFA license request output file as well as data for eMusic’s internal tracking purposes.

Field                    Description                                                   Data Type              Possible Values
Manufacturer_Num         See above.                                                    Text (6)
Transaction_Date         See above.                                                    Number (Double)
                                                                                       Not date?
Manufacturer_Req_Nu      See above.                                                    Number (Decimal –
m                                                                                      8 chars) Decimal?
Label_Name               See above.                                                    Text (15)
ISRC_Code                See above.                                                    Text (15)
Playing_Time_Min         See above. Time relates to different royalty rates per HFA:   Number (Decimal –
                         http://www.harryfox.com/public/licenseeRateCurrent.jsp        3 chars)

                                                                                                                                             22
Playing_Time_Sec      See above.                                                Number (Decimal –
                                                                                2 chars)
Artist                See above.                                                Text (200)
Song_Title            See above.                                                Text (200)
Aka_Song_Title        See above. We don’t provide this information.             Text (200)
ISWC_Code             See above. We don’t provide this information.             Text (11)
HFA_Song_Code         See above.                                                Text (6)
Song_Writer           See above.                                                Text (200)
Publisher_Name        See above.                                                Text (60)
HFA_Publisher_Num     See above. HFA provides this number to us upon            Text (6)
                      confirmation of the license.
Exact_Pub_Share       See above.                                                Number (Decimal –
                                                                                7 chars)
Catalog_Num           See above.                                                Text (15)
Album_Title           See above.                                                Text (200)
UPC_Code              See above.                                                Text (16)
Configuration_Code    See above.                                                Text (2)
License_Type          See above.                                                Text (1)
Server_Fixation_Dt    See above.                                                Number (Double)
                                                                                Not date?
Rate_Code             See above.                                                Text (1)
Rate_Cents            See above.                                                Number (Decimal –
                                                                                7 chars)
Rate_Percent_Statutor See above.                                                Number (Decimal –
y                                                                               5 chars)
Rate_Percent_Min_Sta See above.                                                 Number (Decimal –
tutory                                                                          5 chars)
HFA_License_Num       See above.                                                Number (Decimal –
                                                                                10 chars)
HFA_License_Status_   See status code definitions above.Value will be “RM” if   Text (2)
CD_1                  track is voided.
HFA_Flg_Amend_Pu      See above. Just for HFA’s use.                            Text (1)
b_Data
Publisher_Status      Just for HFA’s use.                                       Text (1)
Total_HFA_Rep_Shar    Total % of ownership among HFA publishers.                Number (Decimal –
e                                                                               7 chars)
                                                                                                    23
HFA_License_Status_      Not used.                                                 Text (2)
CD_2
HFA_License_Status_      Not used.                                                 Text (2)
CD_3
User_Defined             Used to flag voids (“VOID 10/15/2004”).                   Text (30)
Delivered_to_Royaltie    Don’t use. There are two of these columns.                Text (50)
s
Licensed                 Date license is confirmed.                                Date/Time
                                                                                   (MM/dd/yyyy)
Voided                   Voided date. Used to flag voids along with User Defined   Date/Time
                         and “RM” in status code field.                            (MM/dd/yyyy)
Delivered to Royalties   Don’t use. There are two of these columns.                Date/Time
                                                                                   (MM/dd/yyyy)
Date Inserted            Date confirmation record was written into this table.     Date/Time
                                                                                   (MM/dd/yyyy)
Delete?                  Not used.                                                 Yes/No         Yes or No
QRR Publisher            Publisher for export to Counterpoint.                     Text (50)      Always “Harry
                                                                                                  Fox”
QRR Publisher Code       Publisher ID for export to Counterpoint.                  Number (Long   Always “11135”
                                                                                   Integer)
Old_Code                 Reference to old track code.                              Text (50)




                                                                                                                   24

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:10
posted:10/14/2011
language:English
pages:24