;with QRList as
(
 select distinct i.metadata->>'referenceNumber' as QR
 from individual i
 ,jsonb_array_elements(i.pep_details->'currentPepEntries') pep_details
 where
  pep_details.value->>'countryIsoCode' like 'SE'
 and
  i.metadata->>'isDeleted' <> 'true'
 
 union
 
 select distinct i.metadata->>'referenceNumber' as QR
 from individual i
 ,jsonb_array_elements(i.pep_details->'formerPepEntries') pep_details
 where
  pep_details.value->>'countryIsoCode' like 'SE'
 and
  i.metadata->>'isDeleted' <> 'true'
 
 union
 
 select distinct i.metadata->>'referenceNumber' as QR
 from individual i
 ,jsonb_array_elements(i.pep_details->'legacyPepPositions') pep_details
 where
  pep_details.value->>'countryId' like '133'
 and
  i.metadata->>'isDeleted' <> 'true'
 and
  pep_details.value->>'deleted' <> 'true'
)

select 
--QR
i.metadata->>'referenceNumber'

--NewFormatCurrent
,pep_details.value->>'id' as positionID
,'Current'
,pep_details.value->>'position' as positionValue
,pep_details.value->>'segment' as positionSegment
,concat_ws('-', pep_details.value->'dateFrom'->'year',pep_details.value->'dateFrom'->'month',pep_details.value->'dateFrom'->'day') as dateFrom
,concat_ws('-', pep_details.value->'dateTo'->'year',pep_details.value->'dateTo'->'month',pep_details.value->'dateTo'->'day') as dateTo
-- ,pep_details.value->'dateTo'->>'day' as dateToDay
-- ,pep_details.value->'dateTo'->>'month' as dateToMonth
-- ,pep_details.value->'dateTo'->>'year' as dateToYear
,pep_details.value->>'countryIsoCode' as positionCoutry
-- ,jsonb_array_elements(pep_details.value->'evidences')->>'evidenceId' as evidenceId
,pep_details->>'pepTier' as pepTier
,'false' as position_del_flg 

--Basic Details
,i.basic_details->>'firstName' as firstName
,i.basic_details->>'middleName' as middleName
,i.basic_details->>'lastName' as lastName
,i.basic_details->'datesOfBirth' as DOBs
,i.basic_details->'datesOfDeath' as DODs
,i.basic_details->>'nationalities' as nationalities
,i.basic_details->>'isDead' as isDead
,i.metadata->>'datasets' as categories
,i.individual_link_sorted_ids as hasRelatedInd
,i.business_link_sorted_ids as hasRelatedBiz

from individual i
,jsonb_array_elements(i.pep_details->'currentPepEntries') pep_details

where
 i.metadata->>'referenceNumber' in
 (
  select distinct QR
  from QRList
 )
 
UNION

select 
--QR
i.metadata->>'referenceNumber'

--NewFormatCurrent
,pep_details.value->>'id' as positionID
,'Former'
,pep_details.value->>'position' as positionValue
,pep_details.value->>'segment' as positionSegment
,concat_ws('-', pep_details.value->'dateFrom'->'year',pep_details.value->'dateFrom'->'month',pep_details.value->'dateFrom'->'day') as dateFrom
,concat_ws('-', pep_details.value->'dateTo'->'year',pep_details.value->'dateTo'->'month',pep_details.value->'dateTo'->'day') as dateTo
,pep_details.value->>'countryIsoCode' as positionCoutry
,pep_details->>'pepTier' as pepTier
,'false' as position_del_flg 
--Basic Details
,i.basic_details->>'firstName' as firstName
,i.basic_details->>'middleName' as middleName
,i.basic_details->>'lastName' as lastName
,i.basic_details->'datesOfBirth' as DOBs
,i.basic_details->'datesOfDeath' as DODs
,i.basic_details->>'nationalities' as nationalities
,i.basic_details->>'isDead' as isDead
,i.metadata->>'datasets' as categories
,i.individual_link_sorted_ids as hasRelatedInd
,i.business_link_sorted_ids as hasRelatedBiz

from individual i
,jsonb_array_elements(i.pep_details->'formerPepEntries') pep_details

where
 i.metadata->>'referenceNumber' in
 (
  select distinct QR
  from QRList
 )
 
UNION

select 
--QR
i.metadata->>'referenceNumber'

--LegacyFormat
,pep_details.value->>'positionId' as positionID
,'Legacy'
,pep_details.value->>'description' as positionValue
,'No Segment' as positionSegment
,pep_details.value->>'dateFrom' as dateFrom
,pep_details.value->>'dateTo' as dateTo
,pep_details.value->>'countryId' as countryId
,pep_details->>'pepTier'
,pep_details.value->>'deleted' as position_del_flg
--Basic Details
,i.basic_details->>'firstName' as firstName
,i.basic_details->>'middleName' as middleName
,i.basic_details->>'lastName' as lastName
,i.basic_details->'datesOfBirth' as DOBs
,i.basic_details->'datesOfDeath' as DODs
,i.basic_details->>'nationalities' as nationalities
,i.basic_details->>'isDead' as isDead
,i.metadata->>'datasets' as categories
,i.individual_link_sorted_ids as hasRelatedInd
,i.business_link_sorted_ids as hasRelatedBiz

from individual i
,jsonb_array_elements(i.pep_details->'legacyPepPositions') pep_details

where
 i.metadata->>'referenceNumber' in
 (
  select distinct QR
  from QRList
  )
and
 pep_details.value->>'deleted' <> 'true'