CREATE OR REPLACE PROCEDURE public.incremental_sync_customer_attr()
 LANGUAGE plpgsql
AS $$
DECLARE
        sql          VARCHAR(MAX) := '';
        staged_record_count BIGINT :=0;
BEGIN

     -- Truncate staging table

    EXECUTE 'TRUNCATE TABLE stg.CUSTOMER_ATTR;';

    EXECUTE 'COPY JOB RUN auto_copy_CUSTOMER_ATTR';
  
    -- Create staging table to store records from DM tables that match the current batch in the staging table

    EXECUTE 'drop table if exists temp_customer_master_match;';
    EXECUTE 'create temp table temp_customer_master_match(like dm.customer_master); ';




--Identify Customer Number records from DM table that exist in the latest extract
-- We will expire these records in a later step

EXECUTE 'insert into temp_customer_master_match (
Client
,Customer_Number
,Address
,Title
,Central_order_block_for_customer
,Express_train_station
,Train_station
,International_location_number_part_1
,International_location_number_Part_2
,Authorization_Group
,Industry_key
,Check_digit_for_the_international_location_number
,Data_communication_line_no
,Record_create_date
,object_creator_name
,Unloading_points_exist_Indicator
,Central_billing_block_for_customer
,Account_number_of_the_master_record_with_the_fiscal_address
,Working_Time_Calendar
,Account_Number_of_an_Alternative_Payer
,Group_key
,Customer_Account_Group
,Customer_Classification
,Country_or_Region_Key
,Supplier_Account_Number
,Central_delivery_block_for_the_customer
,City_Coordinates
,Central_Deletion_Flag_for_Master_Record
,Name_1
,Name_2
,Name_3
,Name_4
,Nielsen_ID
,City
,District
,PO_Box
,PO_Box_Postal_Code
,Postal_Code
,region_state_province_county
,County_Code
,City_Code
,Regional_market
,Sort_field
,Central_posting_block
,Language_Key
,Tax_Number_1
,Tax_Number_2
,Business_Partner_Subject_to_Equalization_Tax_Indicator
,Liable_for_VAT
,Street_and_House_Number
,Telebox_number
,First_telephone_number
,Second_telephone_number
,Fax_Number
,Teletex_number
,Telex_number
,Transportation_zone_to_or_from_which_the_goods_are_delivered
,One_time_account_Indicator
,Alternative_Payee_in_Document_Allowed_Indicator
,Company_ID_of_Trading_Partner
,VAT_Registration_Number
,Competitor_Indicator
,Sales_partner_Indicator
,Sales_prospect_Indicator
,Customer_type_4_Indicator
,Default_sold_to_party_id
,Consumer_Indicator
,Legal_status
,Industry_code_1
,Industry_code_2
,Industry_code_3
,Industry_code_4
,Industry_code_5
,Initial_contact
,Annual_sales
,Year_For_Which_Sales_are_Given
,Currency_of_sales_figure
,Yearly_number_of_employees
,Year_for_which_the_number_of_employees_is_given
,Attribute_1
,Attribute_2
,Attribute_3
,Attribute_4
,Attribute_5
,Attribute_6
,Attribute_7
,Attribute_8
,Attribute_9
,Attribute_10
,Natural_Person
,Annual_sales_1
,Tax_Jurisdiction
,Search_term_for_matchcode_search_1
,Search_Term_for_Matchcode_Search_2
,Search_term_for_matchcode_search_3
,Fiscal_Year_Variant
,Usage_Indicator
,Inspection_Carried_Out_By_Customer_No_Inspection_Lot
,Inspection_for_a_Delivery_Note_After_Outbound_Delivery
,Reference_Account_Group_for_One_Time_Account_Customer
,PO_Box_city
,own_or_external_plant
,Report_key_for_data_medium_exchange
,Instruction_Key_for_Data_Medium_Exchange
,Status_of_Data_Transfer_into_Subsequent_Release
,Assignment_to_Hierarchy
,Payment_Block
,IS_R_Labeling_customer_or_plant_group
,ID_for_mainly_non_military_use
,ID_for_mainly_military_use
,Customer_condition_group_1
,Customer_condition_group_2
,Customer_condition_group_3
,Customer_condition_group_4
,Customer_condition_group_5
,Alternative_payer_using_account_number_Indicator
,Tax_type
,Tax_Number_Type
,Tax_Number_3
,Tax_Number_4
,Customer_is_ICMS_exempt
,Customer_is_IPI_exempt
,Customer_group_for_Substituiçao_Tributária_calculation
,Customer_CFOP_category
,Tax_law_ICMS
,Tax_law_IPI
,biochemical_warfare_for_legal_control_Indicator
,nuclear_nonproliferation_for_legal_control_Indicator
,national_security_for_legal_control_Indicator
,missile_technology_for_legal_control_Indicator
,Central_sales_block_for_customer
,Uniform_resource_locator
,dm_record_start_date
,dm_record_end_date
,dm_is_current 
) 

select 
distinct
Client
,Customer_Number
,Address
,Title
,Central_order_block_for_customer
,Express_train_station
,Train_station
,International_location_number_part_1
,International_location_number_Part_2
,Authorization_Group
,Industry_key
,Check_digit_for_the_international_location_number
,Data_communication_line_no
,record_create_date
,object_creator_name
,Unloading_points_exist_Indicator
,Central_billing_block_for_customer
,Account_number_of_the_master_record_with_the_fiscal_address
,Working_Time_Calendar
,Account_Number_of_an_Alternative_Payer
,Group_key
,Customer_Account_Group
,Customer_Classification
,Country_or_Region_Key
,Supplier_Account_Number
,Central_delivery_block_for_the_customer
,City_Coordinates
,Central_Deletion_Flag_for_Master_Record
,Name_1
,Name_2
,Name_3
,Name_4
,Nielsen_ID
,City
,District
,PO_Box
,PO_Box_Postal_Code
,Postal_Code
,region_state_province_county
,County_Code
,City_Code
,Regional_market
,Sort_field
,Central_posting_block
,Language_Key
,Tax_Number_1
,Tax_Number_2
,Business_Partner_Subject_to_Equalization_Tax_Indicator
,Liable_for_VAT
,Street_and_House_Number
,Telebox_number
,First_telephone_number
,Second_telephone_number
,Fax_Number
,Teletex_number
,Telex_number
,Transportation_zone_to_or_from_which_the_goods_are_delivered
,One_time_account_Indicator
,Alternative_Payee_in_Document_Allowed_Indicator
,Company_ID_of_Trading_Partner
,VAT_Registration_Number
,Competitor_Indicator
,Sales_partner_Indicator
,Sales_prospect_Indicator
,Customer_type_4_Indicator
,Default_sold_to_party_id
,Consumer_Indicator
,Legal_status
,Industry_code_1
,Industry_code_2
,Industry_code_3
,Industry_code_4
,Industry_code_5
,Initial_contact
,Annual_sales
,Year_For_Which_Sales_are_Given
,Currency_of_sales_figure
,Yearly_number_of_employees
,Year_for_which_the_number_of_employees_is_given
,Attribute_1
,Attribute_2
,Attribute_3
,Attribute_4
,Attribute_5
,Attribute_6
,Attribute_7
,Attribute_8
,Attribute_9
,Attribute_10
,Natural_Person
,Annual_sales_1
,Tax_Jurisdiction
,Search_term_for_matchcode_search_1
,Search_Term_for_Matchcode_Search_2
,Search_term_for_matchcode_search_3
,Fiscal_Year_Variant
,Usage_Indicator
,Inspection_Carried_Out_By_Customer_No_Inspection_Lot
,Inspection_for_a_Delivery_Note_After_Outbound_Delivery
,Reference_Account_Group_for_One_Time_Account_Customer
,PO_Box_city
,own_or_external_plant
,Report_key_for_data_medium_exchange
,Instruction_Key_for_Data_Medium_Exchange
,Status_of_Data_Transfer_into_Subsequent_Release
,Assignment_to_Hierarchy
,Payment_Block
,IS_R_Labeling_customer_or_plant_group
,ID_for_mainly_non_military_use
,ID_for_mainly_military_use
,Customer_condition_group_1
,Customer_condition_group_2
,Customer_condition_group_3
,Customer_condition_group_4
,Customer_condition_group_5
,Alternative_payer_using_account_number_Indicator
,Tax_type
,Tax_Number_Type
,Tax_Number_3
,Tax_Number_4
,Customer_is_ICMS_exempt
,Customer_is_IPI_exempt
,Customer_group_for_Substituiçao_Tributária_calculation
,Customer_CFOP_category
,Tax_law_ICMS
,Tax_law_IPI
,biochemical_warfare_for_legal_control_Indicator
,nuclear_nonproliferation_for_legal_control_Indicator
,national_security_for_legal_control_Indicator
,missile_technology_for_legal_control_Indicator
,Central_sales_block_for_customer
,Uniform_resource_locator
,dm_record_start_date
,dm_record_end_date
,dm_is_current 
  from 
    dm.customer_master  customer_mas
  join
    stg.customer_attr  attr
    on customer_mas.customer_number = attr.kunnr   
  where 
    dm_is_current = ''1''
;';
 
    sql := 'SELECT COUNT(*) FROM temp_customer_master_match;';
    
    EXECUTE sql INTO staged_record_count;
    RAISE INFO 'Matched records into temp_customer_master_match: %', staged_record_count;
    

	-- Delete records from target table that also exist in staging table (updated records)
    
    EXECUTE 'DELETE FROM dm.customer_master   
using temp_customer_master_match tcm
 WHERE dm.customer_master.customer_number = tcm.customer_number
 and dm.customer_master.dm_is_current =''1'';';
    


	-- Insert all records from staging table into target table

	EXECUTE 'INSERT INTO DM.customer_master
(Client
,Customer_Number
,Address
,Title
,Central_order_block_for_customer
,Express_train_station
,Train_station
,International_location_number_part_1
,International_location_number_Part_2
,Authorization_Group
,Industry_key
,Check_digit_for_the_international_location_number
,Data_communication_line_no
,record_create_date
,object_creator_name
,Unloading_points_exist_Indicator
,Central_billing_block_for_customer
,Account_number_of_the_master_record_with_the_fiscal_address
,Working_Time_Calendar
,Account_Number_of_an_Alternative_Payer
,Group_key
,Customer_Account_Group
,Customer_Classification
,Country_or_Region_Key
,Supplier_Account_Number
,Central_delivery_block_for_the_customer
,City_Coordinates
,Central_Deletion_Flag_for_Master_Record
,Name_1
,Name_2
,Name_3
,Name_4
,Nielsen_ID
,City
,District
,PO_Box
,PO_Box_Postal_Code
,Postal_Code
,region_state_province_county
,County_Code
,City_Code
,Regional_market
,Sort_field
,Central_posting_block
,Language_Key
,Tax_Number_1
,Tax_Number_2
,Business_Partner_Subject_to_Equalization_Tax_Indicator
,Liable_for_VAT
,Street_and_House_Number
,Telebox_number
,First_telephone_number
,Second_telephone_number
,Fax_Number
,Teletex_number
,Telex_number
,Transportation_zone_to_or_from_which_the_goods_are_delivered
,One_time_account_Indicator
,Alternative_Payee_in_Document_Allowed_Indicator
,Company_ID_of_Trading_Partner
,VAT_Registration_Number
,Competitor_Indicator
,Sales_partner_Indicator
,Sales_prospect_Indicator
,Customer_type_4_Indicator
,Default_sold_to_party_id
,Consumer_Indicator
,Legal_status
,Industry_code_1
,Industry_code_2
,Industry_code_3
,Industry_code_4
,Industry_code_5
,Initial_contact
,Annual_sales
,Year_For_Which_Sales_are_Given
,Currency_of_sales_figure
,Yearly_number_of_employees
,Year_for_which_the_number_of_employees_is_given
,Attribute_1
,Attribute_2
,Attribute_3
,Attribute_4
,Attribute_5
,Attribute_6
,Attribute_7
,Attribute_8
,Attribute_9
,Attribute_10
,Natural_Person
,Annual_sales_1
,Tax_Jurisdiction
,Search_term_for_matchcode_search_1
,Search_Term_for_Matchcode_Search_2
,Search_term_for_matchcode_search_3
,Fiscal_Year_Variant
,Usage_Indicator
,Inspection_Carried_Out_By_Customer_No_Inspection_Lot
,Inspection_for_a_Delivery_Note_After_Outbound_Delivery
,Reference_Account_Group_for_One_Time_Account_Customer
,PO_Box_city
,own_or_external_plant
,Report_key_for_data_medium_exchange
,Instruction_Key_for_Data_Medium_Exchange
,Status_of_Data_Transfer_into_Subsequent_Release
,Assignment_to_Hierarchy
,Payment_Block
,IS_R_Labeling_customer_or_plant_group
,ID_for_mainly_non_military_use
,ID_for_mainly_military_use
,Customer_condition_group_1
,Customer_condition_group_2
,Customer_condition_group_3
,Customer_condition_group_4
,Customer_condition_group_5
,Alternative_payer_using_account_number_Indicator
,Tax_type
,Tax_Number_Type
,Tax_Number_3
,Tax_Number_4
,Customer_is_ICMS_exempt
,Customer_is_IPI_exempt
,Customer_group_for_Substituiçao_Tributária_calculation
,Customer_CFOP_category
,Tax_law_ICMS
,Tax_law_IPI
,biochemical_warfare_for_legal_control_Indicator
,nuclear_nonproliferation_for_legal_control_Indicator
,national_security_for_legal_control_Indicator
,missile_technology_for_legal_control_Indicator
,Central_sales_block_for_customer
,Uniform_resource_locator
,dm_record_start_date
,dm_record_end_date
,dm_is_current )


SELECT 
DISTINCT
MANDT,
kunnr,
ADRNR,
ANRED,
AUFSD,
BAHNE,
BAHNS,
BBBNR,
BBSNR,
BEGRU,
BRSCH,
BUBKZ,
DATLT,
erdat,
ernam,
EXABL,
FAKSD,
FISKN,
KNAZK,
KNRZA,
KONZS,
KTOKD,
KUKLA,
LAND1,
lifnr,
LIFSD,
LOCCO,
LOEVM,
NAME1,
NAME2,
NAME3,
NAME4,
NIELS,
ORT01,
ORT02,
PFACH,
PSTL2,
PSTLZ,
REGIO,
COUNC,
CITYC,
RPMKR,
SORTL,
SPERR,
SPRAS,
STCD1,
STCD2,
STKZA,
STKZU,
STRAS,
TELBX,
TELF1,
TELF2,
TELFX,
TELTX,
TELX1,
LZONE,
XCPDK,
XZEMP,
VBUND,
STCEG,
DEAR1,
DEAR2,
DEAR3,
DEAR4,
DEAR5,
DEAR6,
GFORM,
BRAN1,
BRAN2,
BRAN3,
BRAN4,
BRAN5,
EKONT,
UMSAT,
UMJAH,
UWAER,
JMZAH,
JMJAH,
KATR1,
KATR2,
KATR3,
KATR4,
KATR5,
KATR6,
KATR7,
KATR8,
KATR9,
KATR10,
STKZN,
UMSA1,
TXJCD,
MCOD1,
MCOD2,
MCOD3,
periv,
ABRVW,
INSPBYDEBI,
INSPATDEBI,
KTOCD,
PFORT,
werks,
DTAMS,
DTAWS,
DUEFL,
HZUOR,
SPERZ,
ETIKG,
CIVVE,
MILVE,
KDKG1,
KDKG2,
KDKG3,
KDKG4,
KDKG5,
XKNZA,
FITYP,
STCDT,
STCD3,
STCD4,
XICMS,
XXIPI,
XSUBT,
CFOPC,
TXLW1,
TXLW2,
CCC01,
CCC02,
CCC03,
CCC04,
CASSD,
KNURL,
current_timestamp,
''9999-12-31''::DATE,
''1''
FROM stg.customer_ATTR;';

  -- Insert Old records with expiry dates
    EXECUTE 'insert into dm.customer_master (
Client
,Customer_Number
,Address
,Title
,Central_order_block_for_customer
,Express_train_station
,Train_station
,International_location_number_part_1
,International_location_number_Part_2
,Authorization_Group
,Industry_key
,Check_digit_for_the_international_location_number
,Data_communication_line_no
,record_create_date
,object_creator_name
,Unloading_points_exist_Indicator
,Central_billing_block_for_customer
,Account_number_of_the_master_record_with_the_fiscal_address
,Working_Time_Calendar
,Account_Number_of_an_Alternative_Payer
,Group_key
,Customer_Account_Group
,Customer_Classification
,Country_or_Region_Key
,Supplier_Account_Number
,Central_delivery_block_for_the_customer
,City_Coordinates
,Central_Deletion_Flag_for_Master_Record
,Name_1
,Name_2
,Name_3
,Name_4
,Nielsen_ID
,City
,District
,PO_Box
,PO_Box_Postal_Code
,Postal_Code
,region_state_province_county
,County_Code
,City_Code
,Regional_market
,Sort_field
,Central_posting_block
,Language_Key
,Tax_Number_1
,Tax_Number_2
,Business_Partner_Subject_to_Equalization_Tax_Indicator
,Liable_for_VAT
,Street_and_House_Number
,Telebox_number
,First_telephone_number
,Second_telephone_number
,Fax_Number
,Teletex_number
,Telex_number
,Transportation_zone_to_or_from_which_the_goods_are_delivered
,One_time_account_Indicator
,Alternative_Payee_in_Document_Allowed_Indicator
,Company_ID_of_Trading_Partner
,VAT_Registration_Number
,Competitor_Indicator
,Sales_partner_Indicator
,Sales_prospect_Indicator
,Customer_type_4_Indicator
,Default_sold_to_party_id
,Consumer_Indicator
,Legal_status
,Industry_code_1
,Industry_code_2
,Industry_code_3
,Industry_code_4
,Industry_code_5
,Initial_contact
,Annual_sales
,Year_For_Which_Sales_are_Given
,Currency_of_sales_figure
,Yearly_number_of_employees
,Year_for_which_the_number_of_employees_is_given
,Attribute_1
,Attribute_2
,Attribute_3
,Attribute_4
,Attribute_5
,Attribute_6
,Attribute_7
,Attribute_8
,Attribute_9
,Attribute_10
,Natural_Person
,Annual_sales_1
,Tax_Jurisdiction
,Search_term_for_matchcode_search_1
,Search_Term_for_Matchcode_Search_2
,Search_term_for_matchcode_search_3
,Fiscal_Year_Variant
,Usage_Indicator
,Inspection_Carried_Out_By_Customer_No_Inspection_Lot
,Inspection_for_a_Delivery_Note_After_Outbound_Delivery
,Reference_Account_Group_for_One_Time_Account_Customer
,PO_Box_city
,own_or_external_plant
,Report_key_for_data_medium_exchange
,Instruction_Key_for_Data_Medium_Exchange
,Status_of_Data_Transfer_into_Subsequent_Release
,Assignment_to_Hierarchy
,Payment_Block
,IS_R_Labeling_customer_or_plant_group
,ID_for_mainly_non_military_use
,ID_for_mainly_military_use
,Customer_condition_group_1
,Customer_condition_group_2
,Customer_condition_group_3
,Customer_condition_group_4
,Customer_condition_group_5
,Alternative_payer_using_account_number_Indicator
,Tax_type
,Tax_Number_Type
,Tax_Number_3
,Tax_Number_4
,Customer_is_ICMS_exempt
,Customer_is_IPI_exempt
,Customer_group_for_Substituiçao_Tributária_calculation
,Customer_CFOP_category
,Tax_law_ICMS
,Tax_law_IPI
,biochemical_warfare_for_legal_control_Indicator
,nuclear_nonproliferation_for_legal_control_Indicator
,national_security_for_legal_control_Indicator
,missile_technology_for_legal_control_Indicator
,Central_sales_block_for_customer
,Uniform_resource_locator
,dm_record_start_date
,dm_record_end_date
,dm_is_current
) 

   
SELECT 
distinct 
Client
,Customer_Number
,Address
,Title
,Central_order_block_for_customer
,Express_train_station
,Train_station
,International_location_number_part_1
,International_location_number_Part_2
,Authorization_Group
,Industry_key
,Check_digit_for_the_international_location_number
,Data_communication_line_no
,record_create_date
,object_creator_name
,Unloading_points_exist_Indicator
,Central_billing_block_for_customer
,Account_number_of_the_master_record_with_the_fiscal_address
,Working_Time_Calendar
,Account_Number_of_an_Alternative_Payer
,Group_key
,Customer_Account_Group
,Customer_Classification
,Country_or_Region_Key
,Supplier_Account_Number
,Central_delivery_block_for_the_customer
,City_Coordinates
,Central_Deletion_Flag_for_Master_Record
,Name_1
,Name_2
,Name_3
,Name_4
,Nielsen_ID
,City
,District
,PO_Box
,PO_Box_Postal_Code
,Postal_Code
,region_state_province_county
,County_Code
,City_Code
,Regional_market
,Sort_field
,Central_posting_block
,Language_Key
,Tax_Number_1
,Tax_Number_2
,Business_Partner_Subject_to_Equalization_Tax_Indicator
,Liable_for_VAT
,Street_and_House_Number
,Telebox_number
,First_telephone_number
,Second_telephone_number
,Fax_Number
,Teletex_number
,Telex_number
,Transportation_zone_to_or_from_which_the_goods_are_delivered
,One_time_account_Indicator
,Alternative_Payee_in_Document_Allowed_Indicator
,Company_ID_of_Trading_Partner
,VAT_Registration_Number
,Competitor_Indicator
,Sales_partner_Indicator
,Sales_prospect_Indicator
,Customer_type_4_Indicator
,Default_sold_to_party_id
,Consumer_Indicator
,Legal_status
,Industry_code_1
,Industry_code_2
,Industry_code_3
,Industry_code_4
,Industry_code_5
,Initial_contact
,Annual_sales
,Year_For_Which_Sales_are_Given
,Currency_of_sales_figure
,Yearly_number_of_employees
,Year_for_which_the_number_of_employees_is_given
,Attribute_1
,Attribute_2
,Attribute_3
,Attribute_4
,Attribute_5
,Attribute_6
,Attribute_7
,Attribute_8
,Attribute_9
,Attribute_10
,Natural_Person
,Annual_sales_1
,Tax_Jurisdiction
,Search_term_for_matchcode_search_1
,Search_Term_for_Matchcode_Search_2
,Search_term_for_matchcode_search_3
,Fiscal_Year_Variant
,Usage_Indicator
,Inspection_Carried_Out_By_Customer_No_Inspection_Lot
,Inspection_for_a_Delivery_Note_After_Outbound_Delivery
,Reference_Account_Group_for_One_Time_Account_Customer
,PO_Box_city
,own_or_external_plant
,Report_key_for_data_medium_exchange
,Instruction_Key_for_Data_Medium_Exchange
,Status_of_Data_Transfer_into_Subsequent_Release
,Assignment_to_Hierarchy
,Payment_Block
,IS_R_Labeling_customer_or_plant_group
,ID_for_mainly_non_military_use
,ID_for_mainly_military_use
,Customer_condition_group_1
,Customer_condition_group_2
,Customer_condition_group_3
,Customer_condition_group_4
,Customer_condition_group_5
,Alternative_payer_using_account_number_Indicator
,Tax_type
,Tax_Number_Type
,Tax_Number_3
,Tax_Number_4
,Customer_is_ICMS_exempt
,Customer_is_IPI_exempt
,Customer_group_for_Substituiçao_Tributária_calculation
,Customer_CFOP_category
,Tax_law_ICMS
,Tax_law_IPI
,biochemical_warfare_for_legal_control_Indicator
,nuclear_nonproliferation_for_legal_control_Indicator
,national_security_for_legal_control_Indicator
,missile_technology_for_legal_control_Indicator
,Central_sales_block_for_customer
,Uniform_resource_locator
,dm_record_start_date
,current_timestamp 
,0 
FROM
       temp_customer_master_match  tmm ;';

-- Refresh dm.sales_document_item_latest MV. This MV will be used to list the latest version of the sales_document_items

EXECUTE 'refresh materialized view archdm.customer_master_latest;';
END
$$