from processing_accounting.flows.accounting_statement_export import setting from processing_accounting.flows.accounting_statement_export \ import sql_generator def test_get_sql_quarter_label_snowflake(): period_ids = '202,203,204' user_type = 'label' payment_interval = 'quarter' generator = sql_generator.SqlGenerator( period_ids, user_type, payment_interval) actual_sql = generator.get_sql().replace('\n', '').replace(' ', '') expected_sql = QUARTER_LABEL.replace('\n', '').replace(' ', '') assert actual_sql == expected_sql actual_sql = generator.get_sql_for_bulk_status_update().replace( '\n', '').replace(' ', '') expected_sql = QUARTER_LABEL_STATUS.replace('\n', '').replace(' ', '') assert actual_sql == expected_sql def test_get_sql_month_label(monkeypatch): period_ids = '204' user_type = 'label' payment_interval = 'month' generator = sql_generator.SqlGenerator( period_ids, user_type, payment_interval) actual_sql = generator.get_sql().replace('\n', '').replace(' ', '') expected_sql = MONTH_LABEL.replace('\n', '').replace(' ', '') assert actual_sql == expected_sql actual_sql = generator.get_sql_for_bulk_status_update().replace( '\n', '').replace(' ', '').replace(' ', '') expected_sql = MONTH_LABEL_STATUS.replace( '\n', '').replace(' ', '').replace(' ', '') assert actual_sql == expected_sql def test_get_sql_month_subaccount(monkeypatch): period_ids = '204' user_type = 'subaccount' payment_interval = 'month' generator = sql_generator.SqlGenerator( period_ids, user_type, payment_interval) actual_sql = generator.get_sql().replace('\n', '').replace(' ', '') expected_sql = MONTH_SUBACCOUNT.replace('\n', '').replace(' ', '') assert actual_sql == expected_sql actual_sql = generator.get_sql_for_bulk_status_update().replace( '\n', '').replace(' ', '') expected_sql = MONTH_SUBACCOUNT_STATUS.replace('\n', '').replace(' ', '') assert actual_sql == expected_sql def test_get_sql_quarter_subaccount(monkeypatch): period_ids = '202,203,204' user_type = 'subaccount' payment_interval = 'quarter' generator = sql_generator.SqlGenerator( period_ids, user_type, payment_interval) actual_sql = generator.get_sql().replace('\n', '').replace(' ', '') expected_sql = QUARTER_SUBACCOUNT.replace('\n', '').replace(' ', '') assert actual_sql == expected_sql actual_sql = generator.get_sql_for_bulk_status_update().replace( '\n', '').replace(' ', '') expected_sql = QUARTER_SUBACCOUNT_STATUS.replace('\n', '').replace( ' ', '') assert actual_sql == expected_sql def test_get_quarter_year_period_ids(monkeypatch): actual = sql_generator.SqlGenerator.get_quarter_year_period_ids(204) assert actual == ( "select x.period_ids, dp.periodid from (" "select year, quarter, min(periodid) as min_periodid, " "max(periodid) as max_periodid, " "min(periodid)||','" "|| (min(periodid)+1)||','" "||max(periodid) as period_ids " "from dim_period " "group by year, quarter) x " "inner join dim_period dp on dp.periodid " "between x.min_periodid and x.max_periodid " "where dp.periodid = 204;") def test_get_digital_split_sql(monkeypatch): period_ids = '202,203,204' user_type = 'subaccount' payment_interval = 'quarter' generator = sql_generator.SqlGenerator( period_ids, user_type, payment_interval) actual = generator._get_digital_split_sql() assert actual == ''' (case ringtone_publishing_type when 'both' then digital_split when 'orchard' then 0 else 1 end) ''' QUARTER_SUBACCOUNT_STATUS = ( "select (fs.subaccountid) || 'S' as user_id_type, " "bvcs.payment_interval," "'202,203,204__all__AVRO__en_US' as user_params, " "'AVRO' as file_type, " " 's3://{}/schematized_files/" "202,203,204_subaccount_all_all_quarter/user_id_type=' || " "(fs.subaccountid) || 'S' as s3_path, " "'en_US' as number_format, 'all' as transaction_types, '' as file_size, " "'' as row_count, '' as workflow_id, 'GENERATING' as status, " "current_timestamp() as generation_start, '' as generation_end " "from booked_vendor_contract_snapshot bvcs " "inner join fact_sales fs on fs.labelid = bvcs.vendor_id" " and fs.accountingperiodid = bvcs.period_id " "inner join dim_period dp on dp.periodid = bvcs.period_id " "where payment_interval = 'quarter' and bvcs.period_id in(202,203,204) " "group by fs.subaccountid, bvcs.payment_interval, dp.quarter, dp.year;" ).format(setting.report_bucket) QUARTER_LABEL_STATUS = ( "select (fs.labelid) || 'L' as user_id_type, " "bvcs.payment_interval, " "'202,203,204__all__AVRO__en_US' as user_params, " "'AVRO' as file_type, " "'s3://{}/schematized_files/" "202,203,204_label_all_all_quarter/user_id_type=' || " "(fs.labelid) || 'L' as s3_path, " "'en_US' as number_format, 'all' as transaction_types, '' as file_size, " "'' as row_count, '' as workflow_id, 'GENERATING' as status, " "current_timestamp() as generation_start, '' as generation_end " "from booked_vendor_contract_snapshot bvcs " "inner join fact_sales fs on fs.labelid = bvcs.vendor_id" " and fs.accountingperiodid = bvcs.period_id " "inner join dim_period dp on dp.periodid = bvcs.period_id " "where payment_interval = 'quarter' and bvcs.period_id in(202,203,204) " "group by fs.labelid, bvcs.payment_interval, dp.quarter, dp.year;" ).format(setting.report_bucket) MONTH_SUBACCOUNT_STATUS = ( "select (fs.subaccountid) || 'S' as user_id_type, " "bvcs.payment_interval, " "'204__all__AVRO__en_US' as user_params, " "'AVRO' as file_type, " "'s3://{}/schematized_files/" "204_subaccount_all_all_month/user_id_type=' || " "(fs.subaccountid) || 'S' as s3_path, " "'en_US' as number_format, 'all' as transaction_types, '' as file_size, " "'' as row_count, '' as workflow_id, 'GENERATING' as status, " "current_timestamp() as generation_start, '' as generation_end " "from booked_vendor_contract_snapshot bvcs " "inner join fact_sales fs on fs.labelid = bvcs.vendor_id " "and fs.accountingperiodid = bvcs.period_id " "inner join dim_period dp on dp.periodid = bvcs.period_id " "where payment_interval = 'month' and bvcs.period_id in(204) " "group by fs.subaccountid, bvcs.payment_interval, bvcs.period_id;" ).format(setting.report_bucket) MONTH_LABEL_STATUS = ( "select (fs.labelid) || 'L' as user_id_type, bvcs.payment_interval," "'204__all__AVRO__en_US' as user_params, " "'AVRO' as file_type, " "'s3://{}/schematized_files" "/204_label_all_all_month/user_id_type=' || (fs.labelid) || " "'L' as s3_path, 'en_US' as number_format, " "'all' as transaction_types, '' as file_size, '' as row_count, " "'' as workflow_id, 'GENERATING' as status, " "current_timestamp() as generation_start" ", " "'' as generation_end " "from booked_vendor_contract_snapshot bvcs " "inner join fact_sales fs on fs.labelid = bvcs.vendor_id " "and fs.accountingperiodid = bvcs.period_id " "inner join dim_period dp on dp.periodid = bvcs.period_id " "where payment_interval = 'month' and bvcs.period_id in(204) " "group by fs.labelid, bvcs.payment_interval, bvcs.period_id;" ).format(setting.report_bucket) QUARTER_SUBACCOUNT = ''' SELECT COALESCE(dp2.year,'') || CAST('M' as varchar) || COALESCE(dp2.month,'') AS period, COALESCE(dp1.year,'') || cast('M' as varchar) || COALESCE(dp1.month,'') AS activity_period, ds.storeName AS dms, dc.countryName AS territory, COALESCE(dr.display_upc,'') AS orchard_upc, COALESCE(dr.manufacturer_upc,'') AS manufacturer_upc, COALESCE(dr.vendor_catalog_number,'') AS label_catalog, COALESCE(dr.product_code, '') AS product_code, COALESCE(dss.subaccountname,'') as subaccount, COALESCE(dr.imprint,'') AS imprint_label, COALESCE(da.artistName,'') AS artist_name, COALESCE(dr.releaseName,'') AS release_name, COALESCE((CASE WHEN dt.cd=0 AND dt.track_id=0 THEN 'Full Album' ELSE dt.trackName END),'') AS track_name, COALESCE( (CASE WHEN dt.cd=0 AND dt.track_id=0 THEN cast(dr.releaseId as varchar) ELSE dt.isrc END),'') AS isrc, COALESCE(dt.cd,0) AS volume, COALESCE(dt.track_id,0) AS track_number, (CASE WHEN x.sales IS NULL THEN 0 ELSE x.sales END) AS quantity, ROUND(x.unitPrice::DECIMAL(38, 19), 6) AS unit_price, (COALESCE(x.gross,0) ) AS gross, dtt.transactionTypeAbbr as trans_type, COALESCE(dtt.transactiontypedesc, '') AS transactiontypedesc, COALESCE(x.adjusted_gross,0) AS adjusted_gross, ROUND(x.splitRate::DECIMAL(38, 19), 6) as split_rate, COALESCE(x.net_receipt,0) AS label_share_net_receipts, (COALESCE(x.ringtone_publishing,0) * (case ringtone_publishing_type when 'both' then digital_split when 'orchard' then 0 else 1 end) ) AS ringtone_publishing, (COALESCE(x.cloud_publishing,0) * (case ringtone_publishing_type when 'both' then digital_split when 'orchard' then 0 else 1 end) ) AS cloud_publishing, (COALESCE(x.dpd_publishing,0) * (case ringtone_publishing_type when 'both' then digital_split when 'orchard' then 0 else 1 end) ) AS publishing, (COALESCE(x.oms_fees,0) ) AS mech_administrative_fee, CASE WHEN dss.commissionOverride IS NULL THEN '' ELSE (COALESCE( x.net_receipt,0) * dss.commissionOverride)::VARCHAR END AS subaccount_label_share_net_receipts, arc.iso_4217_code AS preferred_currency, x.statement_detail_id, dl.isdistributor, COALESCE(dr.physical_product_type, '') AS physical_product_type, COALESCE(dr.physical_product_format, '') AS physical_product_format, COALESCE(dr.display_configuration, '') AS display_configuration, COALESCE(dtaa.track_artists, '') AS track_artists, x.original_price AS original_price, x.discount AS discount, ((x.subaccountid) || 'S') as user_id_type FROM ( SELECT fs.labelid, fs.trackId, fs.releaseId, fs.artistId, fs.transactionTypeId, fs.activityPeriodId, fs.accountingPeriodId, fs.genreId, fs.countryId, fs.storeId, fs.statement_detail_id, fs.subaccountId, fs.fx_adjusted_gross AS adjusted_gross, fs.fx_gross AS gross , fs.fx_net_receipt AS net_receipt, fs.fx_ringtone_publishing AS ringtone_publishing, fs.fx_cloud_publishing as cloud_publishing, fs.fx_dpd_publishing AS dpd_publishing, fs.fx_oms_fees AS oms_fees, fs.sales AS sales, CASE WHEN fs.sales<>0 THEN (fs.fx_gross / fs.sales) ELSE 0 END AS unitPrice, CASE WHEN fs.fx_adjusted_gross <> 0 THEN (fs.fx_net_receipt::FLOAT / fs.fx_adjusted_gross::FLOAT)::DECIMAL(38, 19) ELSE 0 END AS splitRate, fs.original_price, fs.discount FROM fact_sales fs WHERE 1 AND accountingPeriodId in(202,203,204) ) X INNER JOIN booked_vendor_contract_snapshot bs ON bs.vendor_id = x.labelid and bs.period_id = X.accountingPeriodId INNER JOIN accounting_report_currencies arc ON arc.id = bs.currency_id INNER JOIN dim_label dl on dl.labelid = x.labelid INNER JOIN dim_track dt ON dt.trackId=x.trackId LEFT JOIN dim_track_artists_aggregated_temp dtaa ON dt.track_unique_id=dtaa.track_id INNER JOIN dim_release dr ON dr.releaseId=x.releaseId INNER JOIN dim_subaccount dss ON dss.subaccountId = dr.subaccountId INNER JOIN dim_artist da ON da.artistId=x.artistId INNER JOIN dim_store ds ON ds.storeId = x.storeId INNER JOIN dim_country dc ON dc.countryId = x.countryId INNER JOIN dim_transactiontype dtt ON dtt.transactionTypeId=x.transactionTypeId INNER JOIN dim_period dp1 ON dp1.periodId=x.activityPeriodId INNER JOIN dim_period dp2 ON dp2.periodId=x.accountingPeriodId WHERE 1 and bs.payment_interval = 'quarter' ''' MONTH_SUBACCOUNT = ''' SELECT COALESCE(dp2.year,'') || CAST('M' as varchar) || COALESCE(dp2.month,'') AS period, COALESCE(dp1.year,'') || cast('M' as varchar) || COALESCE(dp1.month,'') AS activity_period, ds.storeName AS dms, dc.countryName AS territory, COALESCE(dr.display_upc,'') AS orchard_upc, COALESCE(dr.manufacturer_upc,'') AS manufacturer_upc, COALESCE(dr.vendor_catalog_number,'') AS label_catalog, COALESCE(dr.product_code, '') AS product_code, COALESCE(dss.subaccountname,'') as subaccount, COALESCE(dr.imprint,'') AS imprint_label, COALESCE(da.artistName,'') AS artist_name, COALESCE(dr.releaseName,'') AS release_name, COALESCE((CASE WHEN dt.cd=0 AND dt.track_id=0 THEN 'Full Album' ELSE dt.trackName END),'') AS track_name, COALESCE( (CASE WHEN dt.cd=0 AND dt.track_id=0 THEN cast(dr.releaseId as varchar) ELSE dt.isrc END),'') AS isrc, COALESCE(dt.cd,0) AS volume, COALESCE(dt.track_id,0) AS track_number, (CASE WHEN x.sales IS NULL THEN 0 ELSE x.sales END) AS quantity, ROUND(x.unitPrice::DECIMAL(38, 19), 6) AS unit_price, (COALESCE(x.gross,0) ) AS gross, dtt.transactionTypeAbbr as trans_type, COALESCE(dtt.transactiontypedesc, '') AS transactiontypedesc, COALESCE(x.adjusted_gross,0) AS adjusted_gross, ROUND(x.splitRate::DECIMAL(38, 19), 6) as split_rate, COALESCE(x.net_receipt,0) AS label_share_net_receipts, (COALESCE(x.ringtone_publishing,0) * (case ringtone_publishing_type when 'both' then digital_split when 'orchard' then 0 else 1 end) ) AS ringtone_publishing, (COALESCE(x.cloud_publishing,0) * (case ringtone_publishing_type when 'both' then digital_split when 'orchard' then 0 else 1 end) ) AS cloud_publishing, (COALESCE(x.dpd_publishing,0) * (case ringtone_publishing_type when 'both' then digital_split when 'orchard' then 0 else 1 end) ) AS publishing, (COALESCE(x.oms_fees,0) ) AS mech_administrative_fee, CASE WHEN dss.commissionOverride IS NULL THEN '' ELSE (COALESCE( x.net_receipt,0) * dss.commissionOverride)::VARCHAR END AS subaccount_label_share_net_receipts, arc.iso_4217_code AS preferred_currency, x.statement_detail_id, dl.isdistributor, COALESCE(dr.physical_product_type, '') AS physical_product_type, COALESCE(dr.physical_product_format, '') AS physical_product_format, COALESCE(dr.display_configuration, '') AS display_configuration, COALESCE(dtaa.track_artists, '') AS track_artists, x.original_price AS original_price, x.discount AS discount, ((x.subaccountid) || 'S') as user_id_type FROM ( SELECT fs.labelid, fs.trackId, fs.releaseId, fs.artistId, fs.transactionTypeId, fs.activityPeriodId, fs.accountingPeriodId, fs.genreId, fs.countryId, fs.storeId, fs.statement_detail_id, fs.subaccountId, fs.fx_adjusted_gross AS adjusted_gross, fs.fx_gross AS gross , fs.fx_net_receipt AS net_receipt, fs.fx_ringtone_publishing AS ringtone_publishing, fs.fx_cloud_publishing as cloud_publishing, fs.fx_dpd_publishing AS dpd_publishing, fs.fx_oms_fees AS oms_fees, fs.sales AS sales, CASE WHEN fs.sales<>0 THEN (fs.fx_gross / fs.sales) ELSE 0 END AS unitPrice, CASE WHEN fs.fx_adjusted_gross <> 0 THEN (fs.fx_net_receipt::FLOAT / fs.fx_adjusted_gross::FLOAT)::DECIMAL(38, 19) ELSE 0 END AS splitRate, fs.original_price, fs.discount FROM fact_sales fs WHERE 1 AND accountingPeriodId in(204) ) X INNER JOIN booked_vendor_contract_snapshot bs ON bs.vendor_id = x.labelid and bs.period_id = X.accountingPeriodId INNER JOIN accounting_report_currencies arc ON arc.id = bs.currency_id INNER JOIN dim_label dl on dl.labelid = x.labelid INNER JOIN dim_track dt ON dt.trackId=x.trackId LEFT JOIN dim_track_artists_aggregated_temp dtaa ON dt.track_unique_id=dtaa.track_id INNER JOIN dim_release dr ON dr.releaseId=x.releaseId INNER JOIN dim_subaccount dss ON dss.subaccountId = dr.subaccountId INNER JOIN dim_artist da ON da.artistId=x.artistId INNER JOIN dim_store ds ON ds.storeId = x.storeId INNER JOIN dim_country dc ON dc.countryId = x.countryId INNER JOIN dim_transactiontype dtt ON dtt.transactionTypeId=x.transactionTypeId INNER JOIN dim_period dp1 ON dp1.periodId=x.activityPeriodId INNER JOIN dim_period dp2 ON dp2.periodId=x.accountingPeriodId WHERE 1 and bs.payment_interval = 'month' ''' QUARTER_LABEL = ''' SELECT COALESCE(dp2.year,'') || CAST('M' as varchar) || COALESCE(dp2.month,'') AS period, COALESCE(dp1.year,'') || cast('M' as varchar) || COALESCE(dp1.month,'') AS activity_period, ds.storeName AS dms, dc.countryName AS territory, COALESCE(dr.display_upc,'') AS orchard_upc, COALESCE(dr.manufacturer_upc,'') AS manufacturer_upc, COALESCE(dr.vendor_catalog_number,'') AS label_catalog, COALESCE(dr.product_code, '') AS product_code, COALESCE(dss.subaccountname,'') as subaccount, COALESCE(dr.imprint,'') AS imprint_label, COALESCE(da.artistName,'') AS artist_name, COALESCE(dr.releaseName,'') AS release_name, COALESCE((CASE WHEN dt.cd=0 AND dt.track_id=0 THEN 'Full Album' ELSE dt.trackName END),'') AS track_name, COALESCE( (CASE WHEN dt.cd=0 AND dt.track_id=0 THEN cast(dr.releaseId as varchar) ELSE dt.isrc END),'') AS isrc, COALESCE(dt.cd,0) AS volume, COALESCE(dt.track_id,0) AS track_number, (CASE WHEN x.sales IS NULL THEN 0 ELSE x.sales END) AS quantity, ROUND(x.unitPrice::DECIMAL(38, 19), 6) AS unit_price, (COALESCE(x.gross,0) ) AS gross, dtt.transactionTypeAbbr as trans_type, COALESCE(dtt.transactiontypedesc, '') AS transactiontypedesc, COALESCE(x.adjusted_gross,0) AS adjusted_gross, ROUND(x.splitRate::DECIMAL(38, 19), 6) as split_rate, COALESCE(x.net_receipt,0) AS label_share_net_receipts, (COALESCE(x.ringtone_publishing,0) * (case ringtone_publishing_type when 'both' then digital_split when 'orchard' then 0 else 1 end) ) AS ringtone_publishing, (COALESCE(x.cloud_publishing,0) * (case ringtone_publishing_type when 'both' then digital_split when 'orchard' then 0 else 1 end) ) AS cloud_publishing, (COALESCE(x.dpd_publishing,0) * (case ringtone_publishing_type when 'both' then digital_split when 'orchard' then 0 else 1 end) ) AS publishing, (COALESCE(x.oms_fees,0) ) AS mech_administrative_fee, CASE WHEN dss.commissionOverride IS NULL THEN '' ELSE (COALESCE(x.net_receipt,0) * dss.commissionOverride)::VARCHAR END AS subaccount_label_share_net_receipts, arc.iso_4217_code AS preferred_currency, x.statement_detail_id, dl.isdistributor, COALESCE(dr.physical_product_type, '') AS physical_product_type, COALESCE(dr.physical_product_format, '') AS physical_product_format, COALESCE(dr.display_configuration, '') AS display_configuration, COALESCE(dtaa.track_artists, '') AS track_artists, x.original_price AS original_price, x.discount AS discount, ((x.labelid) || 'L') as user_id_type FROM ( SELECT fs.labelid, fs.trackId, fs.releaseId, fs.artistId, fs.transactionTypeId, fs.activityPeriodId, fs.accountingPeriodId, fs.genreId, fs.countryId, fs.storeId, fs.statement_detail_id, fs.subaccountId, fs.fx_adjusted_gross AS adjusted_gross, fs.fx_gross AS gross , fs.fx_net_receipt AS net_receipt, fs.fx_ringtone_publishing AS ringtone_publishing, fs.fx_cloud_publishing as cloud_publishing, fs.fx_dpd_publishing AS dpd_publishing, fs.fx_oms_fees AS oms_fees, fs.sales AS sales, CASE WHEN fs.sales<>0 THEN (fs.fx_gross / fs.sales) ELSE 0 END AS unitPrice, CASE WHEN fs.fx_adjusted_gross <> 0 THEN (fs.fx_net_receipt::FLOAT / fs.fx_adjusted_gross::FLOAT)::DECIMAL(38, 19) ELSE 0 END AS splitRate, fs.original_price, fs.discount FROM fact_sales fs WHERE 1 AND accountingPeriodId in(202,203,204) ) X INNER JOIN booked_vendor_contract_snapshot bs ON bs.vendor_id = x.labelid and bs.period_id = X.accountingPeriodId INNER JOIN accounting_report_currencies arc ON arc.id = bs.currency_id INNER JOIN dim_label dl on dl.labelid = x.labelid INNER JOIN dim_track dt ON dt.trackId=x.trackId LEFT JOIN dim_track_artists_aggregated_temp dtaa ON dt.track_unique_id=dtaa.track_id INNER JOIN dim_release dr ON dr.releaseId=x.releaseId LEFT JOIN dim_subaccount dss ON dss.subaccountId = dr.subaccountId INNER JOIN dim_artist da ON da.artistId=x.artistId INNER JOIN dim_store ds ON ds.storeId = x.storeId INNER JOIN dim_country dc ON dc.countryId = x.countryId INNER JOIN dim_transactiontype dtt ON dtt.transactionTypeId=x.transactionTypeId INNER JOIN dim_period dp1 ON dp1.periodId=x.activityPeriodId INNER JOIN dim_period dp2 ON dp2.periodId=x.accountingPeriodId WHERE 1 and bs.payment_interval = 'quarter' ''' MONTH_LABEL = ''' SELECT COALESCE(dp2.year,'') || CAST('M' as varchar) || COALESCE(dp2.month,'') AS period, COALESCE(dp1.year,'') || cast('M' as varchar) || COALESCE(dp1.month,'') AS activity_period, ds.storeName AS dms, dc.countryName AS territory, COALESCE(dr.display_upc,'') AS orchard_upc, COALESCE(dr.manufacturer_upc,'') AS manufacturer_upc, COALESCE(dr.vendor_catalog_number,'') AS label_catalog, COALESCE(dr.product_code, '') AS product_code, COALESCE(dss.subaccountname,'') as subaccount, COALESCE(dr.imprint,'') AS imprint_label, COALESCE(da.artistName,'') AS artist_name, COALESCE(dr.releaseName,'') AS release_name, COALESCE((CASE WHEN dt.cd=0 AND dt.track_id=0 THEN 'Full Album' ELSE dt.trackName END),'') AS track_name, COALESCE( (CASE WHEN dt.cd=0 AND dt.track_id=0 THEN cast(dr.releaseId as varchar) ELSE dt.isrc END),'') AS isrc, COALESCE(dt.cd,0) AS volume, COALESCE(dt.track_id,0) AS track_number, (CASE WHEN x.sales IS NULL THEN 0 ELSE x.sales END) AS quantity, ROUND(x.unitPrice::DECIMAL(38, 19), 6) AS unit_price, (COALESCE(x.gross,0) ) AS gross, dtt.transactionTypeAbbr as trans_type, COALESCE(dtt.transactiontypedesc, '') AS transactiontypedesc, COALESCE(x.adjusted_gross,0) AS adjusted_gross, ROUND(x.splitRate::DECIMAL(38, 19), 6) as split_rate, COALESCE(x.net_receipt,0) AS label_share_net_receipts, (COALESCE(x.ringtone_publishing,0) * (case ringtone_publishing_type when 'both' then digital_split when 'orchard' then 0 else 1 end) ) AS ringtone_publishing, (COALESCE(x.cloud_publishing,0) * (case ringtone_publishing_type when 'both' then digital_split when 'orchard' then 0 else 1 end) ) AS cloud_publishing, (COALESCE(x.dpd_publishing,0) * (case ringtone_publishing_type when 'both' then digital_split when 'orchard' then 0 else 1 end) ) AS publishing, (COALESCE(x.oms_fees,0) ) AS mech_administrative_fee, CASE WHEN dss.commissionOverride IS NULL THEN '' ELSE (COALESCE( x.net_receipt,0) * dss.commissionOverride)::VARCHAR END AS subaccount_label_share_net_receipts, arc.iso_4217_code AS preferred_currency, x.statement_detail_id, dl.isdistributor, COALESCE(dr.physical_product_type, '') AS physical_product_type, COALESCE(dr.physical_product_format, '') AS physical_product_format, COALESCE(dr.display_configuration, '') AS display_configuration, COALESCE(dtaa.track_artists, '') AS track_artists, x.original_price AS original_price, x.discount AS discount, ((x.labelid) || 'L') as user_id_type FROM ( SELECT fs.labelid, fs.trackId, fs.releaseId, fs.artistId, fs.transactionTypeId, fs.activityPeriodId, fs.accountingPeriodId, fs.genreId, fs.countryId, fs.storeId, fs.statement_detail_id, fs.subaccountId, fs.fx_adjusted_gross AS adjusted_gross, fs.fx_gross AS gross , fs.fx_net_receipt AS net_receipt, fs.fx_ringtone_publishing AS ringtone_publishing, fs.fx_cloud_publishing as cloud_publishing, fs.fx_dpd_publishing AS dpd_publishing, fs.fx_oms_fees AS oms_fees, fs.sales AS sales, CASE WHEN fs.sales<>0 THEN (fs.fx_gross / fs.sales) ELSE 0 END AS unitPrice, CASE WHEN fs.fx_adjusted_gross <> 0 THEN (fs.fx_net_receipt::FLOAT / fs.fx_adjusted_gross::FLOAT)::DECIMAL(38, 19) ELSE 0 END AS splitRate, fs.original_price, fs.discount FROM fact_sales fs WHERE 1 AND accountingPeriodId in(204) ) X INNER JOIN booked_vendor_contract_snapshot bs ON bs.vendor_id = x.labelid and bs.period_id = X.accountingPeriodId INNER JOIN accounting_report_currencies arc ON arc.id = bs.currency_id INNER JOIN dim_label dl on dl.labelid = x.labelid INNER JOIN dim_track dt ON dt.trackId=x.trackId LEFT JOIN dim_track_artists_aggregated_temp dtaa ON dt.track_unique_id=dtaa.track_id INNER JOIN dim_release dr ON dr.releaseId=x.releaseId LEFT JOIN dim_subaccount dss ON dss.subaccountId = dr.subaccountId INNER JOIN dim_artist da ON da.artistId=x.artistId INNER JOIN dim_store ds ON ds.storeId = x.storeId INNER JOIN dim_country dc ON dc.countryId = x.countryId INNER JOIN dim_transactiontype dtt ON dtt.transactionTypeId=x.transactionTypeId INNER JOIN dim_period dp1 ON dp1.periodId=x.activityPeriodId INNER JOIN dim_period dp2 ON dp2.periodId=x.accountingPeriodId WHERE 1 and bs.payment_interval = 'month' '''