Skip to content

Instantly share code, notes, and snippets.

View byaussy's full-sized avatar

Buddy Yaussy byaussy

  • HudsonMX
  • Atlanta, GA
View GitHub Profile
--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,
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
)
-- 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'),
--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
-- 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
),