UDEFv1

gistfile1.txt

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
)
SELECT 	cu.*,
		udf.value_type as client_udef_value_type,
	  	udf.label as client_udef_value_label,
	  	udfm.label as client_udef_meta_label,
	  	udf.meta_values -> 'values' ->0 ->> 'value' as client_udf_meta_value,
	  	udf.is_required as client_udef_is_required,
	  	udf.is_active as client_udef_is_active,
	  	udf.agency_id as mdm_agency_id,
	  	udf.holding_company_id as mdm_holding_company_id
from client_udefs cu
JOIN hmx_mdm_source.user_defined_field udf on udf.id = cu.client_udef_id
join hmx_mdm_source.user_defined_field_meta udfm on udfm.id = (udf.meta_values -> 'values' ->0->> 'id')::bigint;
with product_udefs as (
	select
		p.id as product_mdm_id,
		p.hmx_legacy_id as product_hmx_legacy_id,
  		p.code as product_code,
  		p."name" as product_name,
		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(p.user_defined_fields)::bigint as product_udef_id,
	  	p.user_defined_fields -> jsonb_object_keys(p.user_defined_fields) as product_udef_value
	from hmx_mdm_source.product p
	join hmx_mdm_source.client c on p.client_id = c.id
)
SELECT 
		pu.*,
		udf.value_type as udef_value_type,
	  	udf.label as udef_value_label,
	  	udfm.label as udef_meta_label,
	  	udf.meta_values -> 'values' ->0 ->> 'value' as product_udf_meta_value,
	  	udf.is_required as product_udef_is_required,
	  	udf.is_active as product_udef_is_active,
	  	udf.agency_id as mdm_agency_id,
	  	udf.holding_company_id as mdm_holding_company_id
from product_udefs pu
JOIN hmx_mdm_source.user_defined_field udf on udf.id = pu.product_udef_id
join hmx_mdm_source.user_defined_field_meta udfm on udfm.id = (udf.meta_values -> 'values' ->0->> 'id')::bigint;
with estimate_udefs as (
	select
		e.id as estimate_mdm_id,
  		e.code as estimate_code,
  		e.name as estimate_name,
		p.id as product_mdm_id,
		p.hmx_legacy_id as product_hmx_legacy_id,
  		p.code as product_code,
  		p."name" as product_name,
		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(e.user_defined_fields)::bigint as estimate_udef_id,
	  	e.user_defined_fields -> jsonb_object_keys(e.user_defined_fields) as estimate_udef_value
	from hmx_mdm_source.estimate e
	join hmx_mdm_source.product p on e.product_id = p.id
	join hmx_mdm_source.client c on p.client_id = c.id
)
SELECT 	eu.*,
		udf.value_type as estimate_udef_value_type,
	  	udf.label as estimate_udef_value_label,
	  	udfm.label as estimate_udef_meta_label,
	  	udf.meta_values -> 'values' ->0 ->> 'value' as estimate_udf_meta_value,
	  	udf.is_required as estimate_udef_is_required,
	  	udf.is_active as estimate_udef_is_active,
	  	udf.agency_id as estimate_mdm_agency_id,
	  	udf.holding_company_id as mdm_holding_company_id
from estimate_udefs eu
JOIN hmx_mdm_source.user_defined_field udf on udf.id = eu.estimate_udef_id
join hmx_mdm_source.user_defined_field_meta udfm on udfm.id = (udf.meta_values -> 'values' ->0->> 'id')::bigint;