--Refresh salesorders;

CREATE MATERIALIZED VIEW archdm.salesorders
distkey
    (sls_dstrbn_document_nbr) AS
SELECT
    sdh.cncl_data_recd_ind,
    sdh.sls_dstrbn_document_nbr,
    sdh.quotation_or_inquiry_is_valid_from,
    sdh.sales_document_type,
    sdh.order_reason,
    sdh.bid_quotation_valid_date,
    sdh.company_code,
    sdh.record_create_date,
    sdh.billing_block_in_sd_document,
    sdh.local_currency,
    sdh.sold_to_party,
    sdh.exchange_rate_type,
    sdh.customer_group_1,
    sdh.customer_group_2,
    sdh.customer_group_3,
    sdh.customer_group_4,
    sdh.customer_group_5,
    sdh.delivery_block_document_header,
    sdh.billing_type,
    sdh.statistics_currency,
    sdh.sd_document_category,
    sdh.statistics_update_date,
    sdh.sales_office,
    sdh.sales_group,
    sdh.sales_organization,
    sdh.distribution_channel,
    sdh.sd_document_currency,
    sdh.order_header_division,
    sdh.order_header_sales_doc_reference_category,
    sdh.num_of_orders,
    sdh.fiscal_year_variant,
    sdh.odq_change_mode,
    sdh.odq_counter,
    sdi.item_rejection_status,
    sdi.sd_doc_item_nbr,
    sdi.item_incompletion_status,
    sdi.item_billing_incompletion_status,
    sdi.item_pricing_incompletion_status,
    sdi.item_delivery_incompletion_status,
    sdi.quotation_or_inquiry_is_valid_from itemquotation_or_inquiry_is_valid_from,
    sdi.bid_quotation_valid_date item_bid_quotation_valid_date,
    sdi.reason_for_rejection_of_sales_documents,
    sdi.last_changed_on,
    sdi.item_order_probability,
    sdi.item_gross_weight,
    sdi.application_component,
    sdi.sap_bw_transaction_key,
    sdi.batch_number,
    sdi.credit_data_exchange_rate_for_requested_delivery_date,
    sdi.international_article_number_ean_upc,
    sdi.record_create_date item_record_create_date,
    sdi.object_creator_name,
    sdi.entry_time,
    sdi.billing_block_for_item,
    sdi.weight_unit,
    sdi.cumulative_confirmed_quantity_in_sales_unit,
    sdi.cumulative_confirmed_quantity_in_base_unit,
    sdi.condition_unit,
    sdi.sales_deal,
    sdi.condition_pricing_unit,
    sdi.cumulative_order_quantity_in_sales_units,
    sdi.subtotal_1_from_pricing_procedure_for_price_element,
    sdi.subtotal_2_from_pricing_procedure_for_price_element,
    sdi.subtotal_3_from_pricing_procedure_for_price_element,
    sdi.subtotal_4_from_pricing_procedure_for_price_element,
    sdi.subtotal_5_from_pricing_procedure_for_price_element,
    sdi.subtotal_6_from_pricing_procedure_for_price_element,
    sdi.minimum_delivery_quantity_in_delivery_note_processing,
    sdi.storage_location,
    sdi.cumulative_required_delivery_qty_all_dlv_relev_sched_lines,
    sdi.material_group,
    sdi.material_number,
    sdi.material_entered,
    sdi.base_unit_of_measure,
    sdi.material_group_1,
    sdi.material_group_2,
    sdi.material_group_3,
    sdi.material_group_4,
    sdi.material_group_5,
    sdi.tax_amount_in_document_currency,
    sdi.net_price,
    sdi.net_value_of_the_order_item_in_document_currency,
    sdi.net_weight_of_the_item,
    sdi.ship_to_party_unload_point,
    sdi.bill_to_party,
    sdi.payer,
    sdi.ship_to_party,
    sdi.product_hierarchy,
    sdi.forwarding_agent,
    sdi.sales_document_item_category,
    sdi.sales_employee,
    sdi.route,
    sdi.special_stock_indicator,
    sdi.division,
    sdi.statistics_date,
    sdi.exchange_rate_for_statistics,
    sdi.reason_for_material_substitution,
    sdi.unlimited_overdelivery_allowed,
    sdi.overdelivery_tolerance,
    sdi.sales_qty_sku_denominator,
    sdi.sales_qty_sku_numerator,
    sdi.sales_base_sales_units_conversion_factor,
    sdi.sales_base_sales_units_conversion_factor2,
    sdi.underdelivery_tolerance,
    sdi.statistics_update_date item_statistics_update_date,
    sdi.reference_document_nbr,
    sdi.reference_item_nbr,
    sdi.preceding_sd_doc_category,
    sdi.volume_unit,
    sdi.item_volume,
    sdi.sales_unit,
    sdi.shipping_or_receiving_point,
    sdi.cost_in_document_currency,
    sdi.own_or_external_plant,
    sdi.target_quantity_uom,
    sdi.target_quantity_in_sales_units,
    sdi.outline_agreement_target_value_in_document_currency,
    sdi.sales_district,
    sdi.services_rendered_date,
    sdi.billing_date,
    sdi.incoterms_part_1,
    sdi.incoterms_part_2,
    sdi.customer_group,
    sdi.customer_account_assignment_group,
    sdi.price_determination_exchange_rate,
    sdi.translation_date,
    sdi.pricing_and_exchange_rate_date,
    sdi.promotion,
    sdi.product_catalog_number,
    sdi.sd_document_currency_vbak,
    sdi.wbs_element,
    sdi.number_of_order_items,
    sdi.campaign_of_referred_sales_order_item_for_bw_extraction,
    sdi.planning_in_apo,
    sdi.item_sales_qty_net_price
FROM
    archdm.sales_document_header_latest sdh
    join archdm.sales_document_item_latest sdi on sdh.sls_dstrbn_document_nbr = sdi.sls_dstrbn_document_nbr --RequestID=83e2f13b-2122-49f0-b532-2e816ea39b1b; TraceID=1-646d4505-5ee66afa036bdb6e204a464f;


CREATE MATERIALIZED VIEW archdm.billingdocs
distkey
    (sls_dstrbn_document_nbr) AS
Select
    bdh.sls_dstrbn_document_nbr,
    bdh.company_code,
    bdh.sales_district,
    bdh.record_create_date,
    bdh.billing_type,
    bdh.billing_date,
    bdh.billing_category,
    bdh.local_currency,
    bdh.customer_group,
    bdh.sold_to_party,
    bdh.payer,
    bdh.financial_accounting_exchangerate,
    bdh.exchange_rate_type,
    bdh.sales_employee,
    bdh.statistics_currency,
    bdh.sd_document_category,
    bdh.sales_organization,
    bdh.distribution_channel,
    bdh.sd_document_currency,
    bdh.num_of_billing_docs,
    bdh.fiscal_year_variant,
    bdh.odq_change_mode,
    bdh.odq_counter cncl_data_recd_ind,
    bdi.sd_doc_item_nbr,
    bdi.last_changed_on,
    bdi.promotion,
    bdi.sales_document,
    bdi.sales_document_item,
    bdi.rebate_basis_1,
    bdi.volume_rebate_group,
    bdi.item_gross_weight,
    bdi.billing_item_grossvalue,
    bdi.application_component,
    bdi.sap_bw_transaction_key,
    bdi.batch_number,
    bdi.international_article_number_ean_upc,
    bdi.record_create_date item_record_create_date,
    bdi.billing_or_invoice_plan_rule,
    bdi.services_rendered_date,
    bdi.actual_billed_quantity,
    bdi.billing_quantity_in_stock_keeping_unit,
    bdi.weight_unit,
    bdi.sales_deal,
    bdi.controlling_area,
    bdi.costcenter,
    bdi.price_determination_exchange_rate,
    bdi.translation_date,
    bdi.customer_group_1,
    bdi.customer_group_2,
    bdi.customer_group_3,
    bdi.customer_group_4,
    bdi.customer_group_5,
    bdi.subtotal_1_from_pricing_procedure_for_price_element,
    bdi.subtotal_2_from_pricing_procedure_for_price_element,
    bdi.subtotal_3_from_pricing_procedure_for_price_element,
    bdi.subtotal_4_from_pricing_procedure_for_price_element,
    bdi.subtotal_5_from_pricing_procedure_for_price_element,
    bdi.subtotal_6_from_pricing_procedure_for_price_element,
    bdi.storage_location,
    bdi.mat_management_requiredquantity,
    bdi.material_group,
    bdi.material_number,
    bdi.material_entered,
    bdi.base_unit_of_measure,
    bdi.material_group_1,
    bdi.material_group_2,
    bdi.material_group_3,
    bdi.material_group_4,
    bdi.material_group_5,
    bdi.tax_amount_in_document_currency,
    bdi.net_value_of_the_order_item_in_document_currency,
    bdi.net_weight_of_the_item,
    bdi.bill_to_party,
    bdi.ship_to_party,
    bdi.item_type,
    bdi.product_hierarchy,
    bdi.commissiongroup,
    bdi.pricing_and_exchange_rate_date,
    bdi.sales_document_item_category,
    bdi.amounteligibleforcashdiscount,
    bdi.scale_quantity,
    bdi.order_header_division,
    bdi.division,
    bdi.statistics_date,
    bdi.exchange_rate_for_statistics,
    bdi.sales_qty_sku_denominator,
    bdi.sales_qty_sku_numerator,
    bdi.statistics_update_date,
    bdi.reference_document_nbr,
    bdi.reference_item_nbr,
    bdi.sales_office,
    bdi.sales_group,
    bdi.volume_unit,
    bdi.item_volume,
    bdi.sales_unit,
    bdi.shipping_or_receiving_point,
    bdi.cost_in_document_currency,
    bdi.own_or_external_plant,
    bdi.wbs_element,
    bdi.num_of_billing_items,
    bdi.campaign_of_referred_sales_order_item_for_bw_extraction
from
    archdm.billing_document_header_latest bdh
    join archdm.billing_document_item_latest bdi on bdh.sls_dstrbn_document_nbr = bdi.sls_dstrbn_document_nbr --RequestID=4d87c12e-0b6a-41b3-9016-3b7523094d26; TraceID=1-646d489c-46b84d6a6f0e9b3c1e70e9c7;


CREATE MATERIALIZED VIEW archdm.invoicedocs distkey(sls_dstrbn_document_nbr)  AS 
select
sdh.sls_dstrbn_document_nbr
,sdh.unloading_point
,sdh.total_weight
,sdh.company_code
,sdh.sales_district
,sdh.record_create_date
,sdh.billing_block
,sdh.weight_unit
,sdh.incoterms_part_1
,sdh.incoterms_part_2
,sdh.customer_group
,sdh.sold_to_party
,sdh.ship_to_party
,sdh.delivery_type
,sdh.delivery_date
,sdh.vendor_account_number
,sdh.delivery_block__document_header
,sdh.loading_point
,sdh.net_weight
,sdh.bill_to_party
,sdh.payer
,sdh.forwarding_agent
,sdh.sales_employee
,sdh.route
,sdh.sd_document_category
,sdh.sales_organization
,sdh.volume_unit
,sdh.volume
,sdh.shipping_point_receiving_point
,sdh.planned_goods_movement_date
,sdh.actual_goods_movement_date
,sdh.num_of_deliveries
,sdh.num_of_packages_per_delivery
,sdh.fiscal_year_variant
,sdh.actual_gi_delay_gi_date_for_delivery
,di.picking_or_putaway_confirmation_status
,di.item_picking_or_putaway_status
,di.sd_doc_item_nbr
,di.item_goods_movement_status
,di.unloading_point item_unloading_point
,di.billing_block_in_sd_document
,di.ship_to_party item_ship_to_party
,di.delivery_type item_delivery_type
,di.delivery_date item_delivery_date
,di.delivery_block_document_header
,di.loading_point item_loading_point
,di.route item_route
,di.shipping_or_receiving_point
,di.planned_goods_movement_date item_planned_goods_movement_date
,di.actual_goods_movement_date item_actual_goods_movement_date
,di.last_changed_on
,di.promotion
,di.item_gross_weight
,di.application_component
,di.sap_bw_transaction_key
,di.batch_number
,di.international_article_number_ean_upc
,di.record_create_date item_record_create_date
,di.object_creator_name
,di.entry_time
,di.billing_block_for_item
,di.weight_unit item_weight_unit
,di.business_area
,di.picking_control_indicator
,di.customer_group_1
,di.customer_group_2
,di.customer_group_3
,di.customer_group_4
,di.customer_group_5
,di.consumption_posting
,di.actual_quantity_delivered_in_sales_units
,di.actualquantitydeliveredinstockkeepingunits
,di.warehouse_number_or_complex
,di.storage_location
,di.storage_bin
,di.storagetype
,di.material_group
,di.material_number
,di.material_entered
,di.base_unit_of_measure
,di.material_group_1
,di.material_group_2
,di.material_group_3
,di.material_group_4
,di.material_group_5
,di.net_weight_of_the_item
,di.bill_to_party iem_bill_to_party
,di.item_type
,di.product_hierarchy
,di.sales_document_item_category
,di.statistics_date
,di.sales_qty_sku_denominator
,di.sales_qty_sku_numerator
,di.fixed_shipping_processing_time_in_days
,di.variable_shipping_processing_time_in_days
,di.statistics_update_date
,di.reference_document_nbr
,di.reference_item_nbr
,di.preceding_sd_doc_category
,di.sales_office
,di.sales_group
,di.volume_unit item_volume_unit
,di.item_volume
,di.sales_unit
,di.distribution_channel
,di.own_or_external_plant
,di.num_of_delivery_items
,di.order_header_division
,di.wbs_element
,di.bw_extraction_le_actual_gi_delay
,di.campaign_of_referred_sales_order_item_for_bw_extraction
from
        archdm.sales_delivery_header_latest sdh
        join archdm.delivery_item_latest di on sdh.sls_dstrbn_document_nbr = di.sls_dstrbn_document_nbr