Query is given below:
select
ed.type AS "Type",
ed.imrUniqueId AS "IMR Unique Id",
ed.legalEntityName AS "Legal Entity Name",
ed.country AS "Country",
ed.region AS "Region",
ed.relationshipwithftis AS "Relationship with FTIS",
ed.distributionContactName AS "Distribution Contact Name",
ed.taOversightTeam AS "TA Oversight Team",
ec.contactName AS "Contact Name",
ec.agreementContactJobTitle AS "Agreement Contact Job Title",
ec.contactEmailId AS "Contact Email Id",
ec.contactPhoneNumber AS "Contact Phone Number",
ec.contactPostalAddress AS "Contact Postal Address",
ed.gfiNonGfi AS "GFI non GFI",
ed.subDelegation AS "Sub Delegation",
ed.agreementExecutionDate AS "Agreement Execution Date",
ed.ftLegalEntity AS "FT Legal Entity",
ed.fundUmbrella AS "Fund Umbrella",
ed.amlCategory AS "AML Category",
ed.kycStatus AS "KYC Status",
ed.proofOfRegulationStatus AS "Proof Of Regulation Status",
ed.plannedKycRefreshDate AS "Planned KYC Refresh Date",
ed.commissionSubscriptionStop AS "Commission Subscription Stop",
ed.inUme AS "In UME",
ed.ddqstatus AS "DDQ status",
ed.riskRating AS "Risk Rating",
ed.fisBrokerCode AS "FIS Broker Code",
ed.bnyBrokerCode AS "BNY Broker Code",
ed.fisAum AS "FIS AUM",
ed.bnyAum AS "BNY AUM",
ed.totalAumAsOfLastQuarter AS "Total AUM As Of LastQuarter",
ed.aumRange AS "AUM Range",
ed.inactiveRelationship AS "Inactive Relationship",
ed.relationshipCaseType AS "Relationship Case Type",
ed.nboComments AS "NBO Comments",
ed.approvalToTerminate AS "Approval To Terminate",
ed.relationshipReviewDate AS "Relationship Review Date",
ed.legalPabloComments AS "Legal Pablo Comments",
ed.salesComments AS "Sales Comments",
ed.iioComments AS "IIO Comments",
ed.dartComments AS "Dart Comments",
ed.id AS "Entity ID",
ed.currentTeamAssigned AS "Current Team Assigned",
ed.typeofrelationship AS "Type Of Relationship",
ed.subregion AS "Subregion"
from entitydata ed
left join entitydata_contactdetails ec on ec.entityId = ed.Id
where ed.chipintermediaryoffboarded is null and
ed.inactive is null and
ed.type = 'Company'
Root cause: The issue was causing inactive property wasn’t there in the schema
Solution:
When you import policies from source to a target tenant, the policy should always be republished on the target tenant.
This is required to sync the service between policy (data requirements) and advance reporting (schema). This is on the product backlog to resolve. So, republish of global policy will not be required in future after the import is complete. For now, i have republished the policy on uat and that resolved the problem.