source_db: art_relations
dim_table: booked_vendor_contract_snapshot
export_sql: >-
  select contract_snapshot_id, period_id, vendor_contract_id, insert_date,
  vendor_id,
  CASE
    WHEN cont_start = '0000-00-00' THEN '1900-01-01'
    WHEN cont_start = '2009-11-00' THEN '2009-10-31'
    WHEN cont_start = '2012-02-00' THEN '2012-01-31'
    WHEN cont_start = '2011-03-00' THEN '2011-02-28'
    WHEN cont_start = '2010-11-00' THEN '2010-10-31'
    WHEN cont_start = '2008-11-00' THEN '2008-10-31'
    ELSE cont_start
  END AS cont_start,
  CASE
    WHEN cont_end = '0000-00-00' THEN '1900-01-01'
    WHEN cont_end = '2009-11-00' THEN '2009-10-31'
    WHEN cont_end = '2012-02-00' THEN '2012-01-31'
    WHEN cont_end = '2011-03-00' THEN '2011-02-28'
    WHEN cont_end = '2010-11-00' THEN '2010-10-31'
    WHEN cont_end = '2008-11-00' THEN '2008-10-31'
    ELSE cont_end
  END AS cont_end,
  cont_version, exclusive, orchrep_name,
  carve_out, qualification_level, territory_carve_out,
  SUBSTRING(dms_carve_out, 1, 65535),
  dms_master_carve_out, encoding_fees_cap_back_ctlg,
  encoding_fees_cap_new_release, digital_split, physical_split,
  contract_type, release_term, advance_payment, advance_recoupable_percentage,
  dig_distribution_type, possible_track_restrictions, oms_type,
  oms_fee_percentage, negotiated_changes,
  substring(stripSpecialChars(negotiated_change_comments), 1, 65535),
  contract_complete,
  CASE
    WHEN signature_date = '0000-00-00' THEN '1900-01-01'
    ELSE signature_date
  END AS signature_date,
  marketing_restrictions,
  orchard_assignment_right_restriction, currency_id,
  third_party_responsibility, third_party_responsibility_detail,
  stripSpecialChars(possible_track_restriction_detail), is_amendment, extend_until_recouped,
  sync_admin_territory, sync_admin_commission, sync_admin_type_of_deal,
  royalty_collection_territory, royalty_collection_commission,
  publishing_admin_commission, publishing_admin_territory,
  publishing_admin_limit_grant_of_rights, publishing_admin_misc_provisions,
  parent_vendor_contract_id, vendor_type, vendor_proposed_term_id,
  sync_admin_response_time, ringtone_publishing_type, unlimited_roll,
  rollover_length_in_months, can_terminate, youtube_composition_clause,
  sx_royalty_collection_commission, topspin_rate, topspin_rate_territory,
  notice_required_in_days, term_continues_until_recouped, brand_split,
  other_rights_option, other_rights_text, special_product_split,
  special_product_carve_out, payment_interval,
  CASE
    WHEN pay_after = '' THEN NULL
    ELSE pay_after
  END AS pay_after,
  show_credit_card,
  opt_out, apply_fx_spread, orchard_compilation_agreement,
  orchard_compilation_split, orchard_compilation_authorization_required
  from booked_vendor_contract_snapshot
insert_sql:
  - >-
    INSERT INTO
        {db}.{schema}.{dim_table}
      SELECT
        b.contract_snapshot_id, b.period_id, b.vendor_contract_id, b.insert_date,
        b.vendor_id, b.cont_start, b.cont_end, b.cont_version, b.exclusive,
        b.orchrep_name, b.carve_out, b.qualification_level,
        b.territory_carve_out, b.dms_carve_out,
        b.dms_master_carve_out, b.encoding_fees_cap_back_ctlg,
        b.encoding_fees_cap_new_release, b.digital_split, b.physical_split,
        b.contract_type, b.release_term, b.advance_payment,
        b.advance_recoupable_percentage, b.dig_distribution_type,
        b.possible_track_restrictions, b.oms_type,
        b.oms_fee_percentage, b.negotiated_changes, b.negotiated_change_comments,
        b.contract_complete, b.signature_date, b.marketing_restrictions,
        b.orchard_assignment_right_restriction, b.currency_id,
        b.third_party_responsibility, b.third_party_responsibility_detail,
        b.possible_track_restriction_detail, b.is_amendment,
        b.extend_until_recouped, b.sync_admin_territory, b.sync_admin_commission,
        b.sync_admin_type_of_deal,
        b.royalty_collection_territory, b.royalty_collection_commission,
        b.publishing_admin_commission, b.publishing_admin_territory,
        b.publishing_admin_limit_grant_of_rights,
        b.publishing_admin_misc_provisions,
        b.parent_vendor_contract_id, b.vendor_type, b.vendor_proposed_term_id,
        b.sync_admin_response_time, b.ringtone_publishing_type, b.unlimited_roll,
        b.rollover_length_in_months, b.can_terminate,
        b.youtube_composition_clause, b.sx_royalty_collection_commission,
        b.topspin_rate, b.topspin_rate_territory,
        b.notice_required_in_days, b.term_continues_until_recouped,
        b.brand_split, b.other_rights_option, b.other_rights_text,
        b.special_product_split, b.special_product_carve_out, b.payment_interval,
        b.pay_after, b.show_credit_card, b.opt_out, b.apply_fx_spread,
        b.orchard_compilation_agreement, b.orchard_compilation_split,
        b.orchard_compilation_authorization_required,
        '{current_timestamp}',
        '{current_timestamp}'
      FROM
        {db}.{schema}.{staging_table} b
      LEFT JOIN
        {db}.{schema}.{dim_table} db
          ON db.contract_snapshot_id = b.contract_snapshot_id
      WHERE
        db.contract_snapshot_id IS NULL