0029040: API Change: Add unique constraint to C_AcctSchema_Element for Accounting Schema and Element Type

CREATE OR REPLACE FUNCTION public.fix_duplicated_acct_combinations()
  RETURNS void
  LANGUAGE plpgsql
AS
$body$
DECLARE 
  --TYPE RECORD IS REFCURSOR;
  cur_validcombination RECORD;
  cur_duplicates RECORD;
  cur_tables RECORD;
  
  v_mcostingid VARCHAR(32);
  v_first_validcombination VARCHAR(32);
  v_update VARCHAR(500);
  v_counter NUMERIC;
  v_countupdated NUMERIC;
  
BEGIN

  v_counter:=0;
  SET session_replication_role = replica;

  FOR cur_validcombination IN (
  
    select ad_client_id, ad_org_id, c_acctschema_id, account_id, 
    m_product_id, c_bpartner_id, c_salesregion_id, c_project_id, c_campaign_id, c_activity_id, user1_id, user2_id, 
    count(*) as number_of_duplicates
    from c_validcombination
    group by ad_client_id, ad_org_id, c_acctschema_id, account_id,
    m_product_id, c_bpartner_id, c_salesregion_id, c_project_id, c_campaign_id, c_activity_id, user1_id, user2_id
    having count(*) > 1
    
  ) LOOP
  
    RAISE NOTICE '%', 'Fixing valid combination for ClientId: ' || cur_validcombination.ad_client_id || ' OrganizationId: ' || cur_validcombination.ad_org_id 
    || ' SchemaId: ' || cur_validcombination.c_acctschema_id || ' AccountId: ' || cur_validcombination.account_id 
    || ' No. of duplicates: ' || cur_validcombination.number_of_duplicates;
    
    v_first_validcombination := null;
    
    FOR cur_duplicates IN ( 
    
       select c_validcombination_id
       from c_validcombination
       where ad_client_id = cur_validcombination.ad_client_id
       and ad_org_id = cur_validcombination.ad_org_id
       and c_acctschema_id = cur_validcombination.c_acctschema_id
       and account_id = cur_validcombination.account_id
       and m_product_id = cur_validcombination.m_product_id
       and c_bpartner_id = cur_validcombination.c_bpartner_id
       and c_salesregion_id = cur_validcombination.c_salesregion_id
       and c_project_id = cur_validcombination.c_project_id
       and c_campaign_id = cur_validcombination.c_campaign_id
       and c_activity_id = cur_validcombination.c_activiy_id
       and user1_id = cur_validcombination.user1_id
       and user2_id = cur_validcombination.user2_id
       order by c_validcombination_id asc
       
    ) LOOP
    
      IF (v_first_validcombination IS NULL) THEN
        v_first_validcombination := cur_duplicates.c_validcombination_id;
      ELSE
      
        FOR cur_tables IN (
        
          SELECT tc.table_name as table_name, kcu.column_name as column_name
          FROM information_schema.referential_constraints AS rc
          JOIN information_schema.table_constraints AS tc USING(constraint_catalog,constraint_schema,constraint_name)
          JOIN information_schema.key_column_usage AS kcu USING(constraint_catalog,constraint_schema,constraint_name)
          JOIN information_schema.key_column_usage AS ccu ON(ccu.constraint_catalog=rc.unique_constraint_catalog AND ccu.constraint_schema=rc.unique_constraint_schema AND ccu.constraint_name=rc.unique_constraint_name)
          WHERE ccu.table_catalog='acctcomb'
          AND ccu.table_schema='public'
          AND ccu.table_name='c_validcombination'
          AND ccu.column_name='c_validcombination_id'
          order by table_name asc
          
        ) LOOP
        
          v_countupdated := 0;
        
          v_update:= 'UPDATE ' || cur_tables.table_name || ' SET ' || cur_tables.column_name || ' = ''' ||
          v_first_validcombination || ''' WHERE ' || cur_tables.column_name || ' = ''' || cur_duplicates.c_validcombination_id || '''';
          EXECUTE v_update;
          GET DIAGNOSTICS v_countupdated = ROW_COUNT;
          IF (v_countupdated > 0) THEN
            RAISE NOTICE '%', '   CombinationId going to be used for this duplicates: ' || v_first_validcombination;
            RAISE NOTICE '%', '      Fixing Table: ' || cur_tables.table_name || ' Column: ' || cur_tables.column_name || ' Old CombinationId: ' || cur_duplicates.c_validcombination_id;
          END IF;

        END LOOP;
        
        DELETE FROM c_validcombination WHERE c_validcombination_id = cur_duplicates.c_validcombination_id;
        v_counter := v_counter + 1;

      END IF;
    
    END LOOP;

  END LOOP;
  
  SET session_replication_role = DEFAULT;
  RAISE NOTICE '%', '**************************************************************************';
  RAISE NOTICE '%', 'Number of Account Combinations affected: ' || v_counter;
  
END ;
$body$
 VOLATILE
 COST 100
/