1. ;WITH QRList AS
2. (
3.  SELECT DISTINCT i.metadata->>'referenceNumber' AS QR
4.  FROM individual i
5.  ,jsonb_array_elements(i.pep_details->'currentPepEntries') pep_details
6.  WHERE
7.   pep_details.value->>'countryIsoCode' LIKE 'SE'
8.  AND
9.   i.metadata->>'isDeleted' <> 'true'
10.  
11.  UNION
12.  
13.  SELECT DISTINCT i.metadata->>'referenceNumber' AS QR
14.  FROM individual i
15.  ,jsonb_array_elements(i.pep_details->'formerPepEntries') pep_details
16.  WHERE
17.   pep_details.value->>'countryIsoCode' LIKE 'SE'
18.  AND
19.   i.metadata->>'isDeleted' <> 'true'
20.  
21.  UNION
22.  
23.  SELECT DISTINCT i.metadata->>'referenceNumber' AS QR
24.  FROM individual i
25.  ,jsonb_array_elements(i.pep_details->'legacyPepPositions') pep_details
26.  WHERE
27.   pep_details.value->>'countryId' LIKE '133'
28.  AND
29.   i.metadata->>'isDeleted' <> 'true'
30.  AND
31.   pep_details.value->>'deleted' <> 'true'
32. )
33.  
34. SELECT
35. --QR
36. i.metadata->>'referenceNumber'
37.  
38. --NewFormatCurrent
39. ,pep_details.value->>'id' AS positionID
40. ,'Current'
41. ,pep_details.value->>'position' AS positionValue
42. ,pep_details.value->>'segment' AS positionSegment
43. ,concat_ws('-', pep_details.value->'dateFrom'->'year',pep_details.value->'dateFrom'->'month',pep_details.value->'dateFrom'->'day') AS dateFrom
44. ,concat_ws('-', pep_details.value->'dateTo'->'year',pep_details.value->'dateTo'->'month',pep_details.value->'dateTo'->'day') AS dateTo
45. -- ,pep_details.value->'dateTo'->>'day' as dateToDay
46. -- ,pep_details.value->'dateTo'->>'month' as dateToMonth
47. -- ,pep_details.value->'dateTo'->>'year' as dateToYear
48. ,pep_details.value->>'countryIsoCode' AS positionCoutry
49. -- ,jsonb_array_elements(pep_details.value->'evidences')->>'evidenceId' as evidenceId
50. ,pep_details->>'pepTier' AS pepTier
51. ,'false' AS position_del_flg
52.  
53. --Basic Details
54. ,i.basic_details->>'firstName' AS firstName
55. ,i.basic_details->>'middleName' AS middleName
56. ,i.basic_details->>'lastName' AS lastName
57. ,i.basic_details->'datesOfBirth' AS DOBs
58. ,i.basic_details->'datesOfDeath' AS DODs
59. ,i.basic_details->>'nationalities' AS nationalities
60. ,i.basic_details->>'isDead' AS isDead
61. ,i.metadata->>'datasets' AS categories
62. ,i.individual_link_sorted_ids AS hasRelatedInd
63. ,i.business_link_sorted_ids AS hasRelatedBiz
64.  
65. FROM individual i
66. ,jsonb_array_elements(i.pep_details->'currentPepEntries') pep_details
67.  
68. WHERE
69.  i.metadata->>'referenceNumber' IN
70.  (
71.   SELECT DISTINCT QR
72.   FROM QRList
73.  )
74.  
75. UNION
76.  
77. SELECT
78. --QR
79. i.metadata->>'referenceNumber'
80.  
81. --NewFormatCurrent
82. ,pep_details.value->>'id' AS positionID
83. ,'Former'
84. ,pep_details.value->>'position' AS positionValue
85. ,pep_details.value->>'segment' AS positionSegment
86. ,concat_ws('-', pep_details.value->'dateFrom'->'year',pep_details.value->'dateFrom'->'month',pep_details.value->'dateFrom'->'day') AS dateFrom
87. ,concat_ws('-', pep_details.value->'dateTo'->'year',pep_details.value->'dateTo'->'month',pep_details.value->'dateTo'->'day') AS dateTo
88. ,pep_details.value->>'countryIsoCode' AS positionCoutry
89. ,pep_details->>'pepTier' AS pepTier
90. ,'false' AS position_del_flg
91. --Basic Details
92. ,i.basic_details->>'firstName' AS firstName
93. ,i.basic_details->>'middleName' AS middleName
94. ,i.basic_details->>'lastName' AS lastName
95. ,i.basic_details->'datesOfBirth' AS DOBs
96. ,i.basic_details->'datesOfDeath' AS DODs
97. ,i.basic_details->>'nationalities' AS nationalities
98. ,i.basic_details->>'isDead' AS isDead
99. ,i.metadata->>'datasets' AS categories
100. ,i.individual_link_sorted_ids AS hasRelatedInd
101. ,i.business_link_sorted_ids AS hasRelatedBiz
102.  
103. FROM individual i
104. ,jsonb_array_elements(i.pep_details->'formerPepEntries') pep_details
105.  
106. WHERE
107.  i.metadata->>'referenceNumber' IN
108.  (
109.   SELECT DISTINCT QR
110.   FROM QRList
111.  )
112.  
113. UNION
114.  
115. SELECT
116. --QR
117. i.metadata->>'referenceNumber'
118.  
119. --LegacyFormat
120. ,pep_details.value->>'positionId' AS positionID
121. ,'Legacy'
122. ,pep_details.value->>'description' AS positionValue
123. ,'No Segment' AS positionSegment
124. ,pep_details.value->>'dateFrom' AS dateFrom
125. ,pep_details.value->>'dateTo' AS dateTo
126. ,pep_details.value->>'countryId' AS countryId
127. ,pep_details->>'pepTier'
128. ,pep_details.value->>'deleted' AS position_del_flg
129. --Basic Details
130. ,i.basic_details->>'firstName' AS firstName
131. ,i.basic_details->>'middleName' AS middleName
132. ,i.basic_details->>'lastName' AS lastName
133. ,i.basic_details->'datesOfBirth' AS DOBs
134. ,i.basic_details->'datesOfDeath' AS DODs
135. ,i.basic_details->>'nationalities' AS nationalities
136. ,i.basic_details->>'isDead' AS isDead
137. ,i.metadata->>'datasets' AS categories
138. ,i.individual_link_sorted_ids AS hasRelatedInd
139. ,i.business_link_sorted_ids AS hasRelatedBiz
140.  
141. FROM individual i
142. ,jsonb_array_elements(i.pep_details->'legacyPepPositions') pep_details
143.  
144. WHERE
145.  i.metadata->>'referenceNumber' IN
146.  (
147.   SELECT DISTINCT QR
148.   FROM QRList
149.   )
150. AND
151.  pep_details.value->>'deleted' <> 'true'