UDEFs v2

gistfile1.txt

--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,	
       udf.entity_names udf_entity_names,	
       udf.sequence_number udf_sequence_number,	
       udf.max_length udf_max_length,	
       udf.agency_id udf_agency_id,	
       udf.holding_company_id udf_holding_company_id,	
       client_ids, product_ids, estimate_ids,	
       udfm.id user_defined_field_meta_id,	
       udfm.label user_defined_field_meta_label,	
       udfm."type" user_defined_field_meta_type,	
       udfm.value_type user_defined_field_meta_value_type,	
       udfm.default_value user_defined_field_meta_default_value,	
       udfm."values" user_defined_field_meta_values,	
       udfm.is_required user_defined_field_meta_is_required,	
       udfm.is_active user_defined_field_meta_is_active,	
       udfudfm.value user_defined_field_meta_value	
from user_defined_field udf,	
     user_defined_field_user_defined_field_meta udfudfm,	
     user_defined_field_meta udfm	
where     udf.id = udfudfm.user_defined_field_id	
and     udfudfm.user_defined_field_meta_id = udfm.id;	


--UDEFS (CLIENT)
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
     , c.user_defined_fields -> 'valueList' as udef_values
from client c
    where jsonb_array_length(c.user_defined_fields -> 'valueList') > 1
), client_expanded_udefs as (
    select client_mdm_id
         , client_hmx_legacy_id
         , client_code
         , client_name
         , jsonb_array_elements(udef_values) as udef_value
    from client_udefs
), client_udefs_and_meta as (
    select cu.client_mdm_id
         , cu.client_hmx_legacy_id
         , cu.client_code
         , cu.client_name
         , udf.agency_id                as agency_mdm_id
         , udf.holding_company_id       as holding_company_id
         , udf.id                       as udef_id
         , udf.label                    as udef_label
         , cu.udef_value ->> 'value'    as udef_value
         , udf.value_type               as udef_value_type
         , meta.ele ->> 'label'         as meta_label
         , meta.ele ->> 'value'         as meta_value
         , meta.ele ->> 'valueType'     as meta_value_type
         , udf.is_active                as udef_is_active
         , udf.max_length               as udef_max_length
    from client_expanded_udefs cu
    join user_defined_field udf
      on udf.id = (cu.udef_value -> 'id')::bigint and
             (  ( udf.client_ids = '{}' and
                  udf.product_ids is null and
                  udf.estimate_ids is NULL) or
                ( udf.client_ids @> ('{'||cu.client_mdm_id||'}')::bigint[] and
                  udf.product_ids is null and
                  udf.estimate_ids is NULL))
    join user_defined_field_user_defined_field_meta udfudfm
      on udf.id = udfudfm.user_defined_field_id
    join user_defined_field_meta udfm
      on udfm.id = udfudfm.user_defined_field_meta_id
    cross join lateral jsonb_array_elements(cu.udef_value -> 'metadata') meta(ele)
    where cu.udef_value -> 'value' <> 'null'
)
select cu.client_mdm_id
     , cu.client_hmx_legacy_id
     , cu.client_code
     , cu.client_name
     , cu.udef_id
     , cu.udef_label
     , cu.udef_value
     , cu.udef_value_type
     , max(case when cu.meta_label = 'subMediaTypeId'
         then cu.meta_value::numeric end) as sub_media_type_id
     , max(case when cu.meta_label = 'subMediaTypeId'
         then smt.name end)               as sub_media_type_name
     , max(case when cu.meta_label = 'udf.sequenceNumber'
         then cu.meta_value::numeric end) as sequence_number
     , cu.udef_is_active
     , cu.agency_mdm_id
     , cu.holding_company_id
     , cu.udef_max_length
from client_udefs_and_meta cu
left join sub_media_type smt
  on smt.id = (case when cu.meta_label = 'subMediaTypeId' then cu.meta_value::numeric end)
group by cu.client_mdm_id, cu.client_hmx_legacy_id, cu.client_code, cu.client_name, cu.udef_id,
         cu.udef_label, cu.udef_value, cu.udef_value_type, cu.udef_is_active, cu.agency_mdm_id,
         cu.holding_company_id, cu.udef_max_length
    order by client_mdm_id, sequence_number
;

--UDEFS (PRODUCT)
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
         , p.user_defined_fields -> 'valueList' as udef_values
    from product p
    join client c on c.id = p.client_id
    where jsonb_array_length(p.user_defined_fields -> 'valueList') > 1
), product_expanded_udefs as (
    select product_mdm_id
         , product_hmx_legacy_id
         , product_code
         , product_name
         , client_mdm_id
         , client_hmx_legacy_id
         , client_code
         , client_name
         , jsonb_array_elements(udef_values) as udef_value
    from product_udefs
), product_udefs_and_meta as (
    select pu.product_mdm_id
         , pu.product_hmx_legacy_id
         , pu.product_code
         , pu.product_name
         , pu.client_mdm_id
         , pu.client_hmx_legacy_id
         , pu.client_code
         , pu.client_name
         , udf.agency_id                as agency_mdm_id
         , udf.holding_company_id       as holding_company_id
         , udf.id                       as udef_id
         , udf.label                    as udef_label
         , pu.udef_value ->> 'value'    as udef_value
         , udf.value_type               as udef_value_type
         , meta.ele ->> 'label'         as meta_label
         , meta.ele ->> 'value'         as meta_value
         , meta.ele ->> 'valueType'     as meta_value_type
         , udf.is_active                as udef_is_active
         , udf.max_length               as udef_max_length
    from product_expanded_udefs pu
    join user_defined_field udf
      on udf.id = (pu.udef_value -> 'id')::bigint and
             (  ( client_ids = '{}' and
                  product_ids is null and
                  estimate_ids is NULL) or
                ( udf.client_ids @> ('{'||pu.client_mdm_id||'}')::bigint[] and
                  product_ids = '{}' and
                  estimate_ids is NULL) or
                ( product_ids is null and
                  udf.product_ids @> ('{'||pu.product_mdm_id||'}')::bigint[] and
                  estimate_ids is NULL))
    join user_defined_field_user_defined_field_meta udfudfm
      on udf.id = udfudfm.user_defined_field_id
    join user_defined_field_meta udfm
      on udfm.id = udfudfm.user_defined_field_meta_id
    cross join lateral jsonb_array_elements(pu.udef_value -> 'metadata') meta(ele)
    where pu.udef_value -> 'value' <> 'null'
)
select pu.product_mdm_id
     , pu.product_hmx_legacy_id
     , pu.product_code
     , pu.product_name
     , pu.client_mdm_id
     , pu.client_hmx_legacy_id
     , pu.client_code
     , pu.client_name
     , pu.udef_id
     , pu.udef_label
     , pu.udef_value
     , pu.udef_value_type
     , max(case when pu.meta_label = 'subMediaTypeId'
         then pu.meta_value::numeric end) as sub_media_type_id
     , max(case when pu.meta_label = 'subMediaTypeId'
         then smt.name end)               as sub_media_type_name
     , max(case when pu.meta_label = 'udf.sequenceNumber'
         then pu.meta_value::numeric end) as sequence_number
     , pu.udef_is_active
     , pu.agency_mdm_id
     , pu.holding_company_id
     , pu.udef_max_length
from product_udefs_and_meta pu
left join sub_media_type smt
  on smt.id = (case when pu.meta_label = 'subMediaTypeId' then pu.meta_value::numeric end)
group by pu.product_mdm_id, pu.product_hmx_legacy_id, pu.product_code, pu.product_name,
         pu.client_mdm_id, pu.client_hmx_legacy_id, pu.client_code, pu.client_name, pu.udef_id,
         pu.udef_label, pu.udef_value, pu.udef_value_type, pu.udef_is_active, pu.agency_mdm_id,
         pu.holding_company_id, pu.udef_max_length
order by client_mdm_id, product_mdm_id, sequence_number
;

--UDEFS (ESTIMATE)
with estimate_udefs as (
    select e.id as estimate_mdm_id
         , e.code as estimate_code
         , e.name as estimate_name
         , e.active_dates as estimate_active_dates
         , 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
         , e.user_defined_fields -> 'valueList' as udef_values
    from estimate e
    join product p on p.id = e.product_id
    join client c on c.id = p.client_id
    where jsonb_array_length(e.user_defined_fields -> 'valueList') > 1
), estimate_expanded_udefs as (
    select estimate_mdm_id
         , estimate_code
         , estimate_name
         , estimate_active_dates
         , product_mdm_id
         , product_hmx_legacy_id
         , product_code
         , product_name
         , client_mdm_id
         , client_hmx_legacy_id
         , client_code
         , client_name
         , jsonb_array_elements(udef_values) as udef_value
    from estimate_udefs
), estimate_udefs_and_meta as (
    select eu.estimate_mdm_id
         , eu.estimate_code
         , eu.estimate_name
         , eu.estimate_active_dates
         , eu.product_mdm_id
         , eu.product_hmx_legacy_id
         , eu.product_code
         , eu.product_name
         , eu.client_mdm_id
         , eu.client_hmx_legacy_id
         , eu.client_code
         , eu.client_name
         , udf.agency_id                as agency_mdm_id
         , udf.holding_company_id       as holding_company_id
         , udf.id                       as udef_id
         , udf.label                    as udef_label
         , eu.udef_value ->> 'value'    as udef_value
         , udf.value_type               as udef_value_type
         , meta.ele ->> 'label'         as meta_label
         , meta.ele ->> 'value'         as meta_value
         , meta.ele ->> 'valueType'     as meta_value_type
         , udf.is_active                as udef_is_active
         , udf.max_length               as udef_max_length
    from estimate_expanded_udefs eu
    join user_defined_field udf
      on udf.id = (eu.udef_value -> 'id')::bigint and
             (  ( client_ids = '{}' and
                  product_ids is null and
                  estimate_ids is NULL) or
                ( udf.client_ids @> ('{'||eu.client_mdm_id||'}')::bigint[] and
                  product_ids = '{}' and
                  estimate_ids is NULL)  or
                ( client_ids is null and
                  udf.product_ids @> ('{'||eu.product_mdm_id||'}')::bigint[] and
                  estimate_ids  = '{}') or
                ( client_ids is null and
                  product_ids is null and
                  udf.estimate_ids @> ('{'||eu.estimate_mdm_id||'}')::bigint[] ) )
    join user_defined_field_user_defined_field_meta udfudfm
      on udf.id = udfudfm.user_defined_field_id
    join user_defined_field_meta udfm
      on udfm.id = udfudfm.user_defined_field_meta_id
    cross join lateral jsonb_array_elements(eu.udef_value -> 'metadata') meta(ele)
    where eu.udef_value -> 'value' <> 'null'
)
select eu.estimate_mdm_id
     , eu.estimate_code
     , eu.estimate_name
     , eu.estimate_active_dates
     , eu.product_mdm_id
     , eu.product_hmx_legacy_id
     , eu.product_code
     , eu.product_name
     , eu.client_mdm_id
     , eu.client_hmx_legacy_id
     , eu.client_code
     , eu.client_name
     , eu.udef_id
     , eu.udef_label
     , eu.udef_value
     , eu.udef_value_type
     , max(case when eu.meta_label = 'subMediaTypeId'
         then eu.meta_value::numeric end) as sub_media_type_id
     , max(case when eu.meta_label = 'subMediaTypeId'
         then smt.name end)               as sub_media_type_name
     , max(case when eu.meta_label = 'udf.sequenceNumber'
         then eu.meta_value::numeric end) as sequence_number
     , eu.udef_is_active
     , eu.agency_mdm_id
     , eu.holding_company_id
     , eu.udef_max_length
from estimate_udefs_and_meta eu
left join sub_media_type smt
  on smt.id = (case when eu.meta_label = 'subMediaTypeId' then eu.meta_value::numeric end)
group by eu.estimate_mdm_id, eu.estimate_code, eu.estimate_name, eu.estimate_active_dates,
         eu.product_mdm_id, eu.product_hmx_legacy_id, eu.product_code, eu.product_name,
         eu.client_mdm_id, eu.client_hmx_legacy_id, eu.client_code, eu.client_name, eu.udef_id,
         eu.udef_label, eu.udef_value, eu.udef_value_type, eu.udef_is_active, eu.agency_mdm_id,
         eu.holding_company_id, eu.udef_max_length
order by eu.client_mdm_id, eu.product_mdm_id, eu.estimate_mdm_id, sequence_number
;