byaussy

--UDEF DEFINITIONS	
select udf.id udf_id,	
       udf.default_value udf_default_value,	
       udf.entity_type udf_entity_type,	
       udf.is_active udf_is_active,	
       udf.is_required udf_is_required,	
       udf.label udf_label,	
       udf.value_type udf_value_type,	
       udf.values udf_values,	
       udf.entity_ids udf_entity_ids,	

byaussy

with client_udefs as (
	select
		c.id as client_mdm_id,
		c.hmx_legacy_id as client_hmx_legacy_id,
  		c.code as client_code,
  		c."name" as client_name,
	  	jsonb_object_keys(c.user_defined_fields)::bigint as client_udef_id,
	  	c.user_defined_fields -> jsonb_object_keys(c.user_defined_fields) as client_udef_value
	from hmx_mdm_source.client c
)

byaussy

-- billing rules
DELETE FROM billing_rules WHERE created_username = 'dm-batch-import';
DELETE FROM billing_rule_groups WHERE created_username = 'dm-batch-import';
SELECT setval(pg_get_serial_sequence('billing_rule_groups', 'id'),
              (SELECT GREATEST(max(billing_rule_groups.id) + 1, 1000) FROM billing_rule_groups) ,
              false);
SELECT setval(pg_get_serial_sequence('billing_rules', 'id'),
              (SELECT GREATEST(max(billing_rules.id) + 1, 1000) FROM billing_rules) ,
              false);
SELECT setval(pg_get_serial_sequence('custom_fee', 'id'),

byaussy

--client audits
with client_audits as
         (
             select id as audit_log_id
                  , log_datetime_created
                  , user_name as modified_by_user_name
                  , response_body::jsonb -> 'id' as client_mdm_id
                  , response_body::jsonb -> 'code' as client_code
                  , response_body::jsonb -> 'name' as name
                  , response_body::jsonb -> 'isActive' as is_active

byaussy

-- CLIENTS FULL MAP
with client_all as
    (
        select distinct client.office, client.externalcode, client.client
        from dentsu_customer_client_unique client
                 join base_customer_unique cust
                      on client.office = cust.office and
                         client.externalcode = cust.externalcode
        where cust.mdm_id is not null
    ),