WITH vType AS (SELECT
        clt.UniqCdPolicyLineType
        ,clt.CdPolicyLineTypeID
        ,clr.ResourceText
FROM CdPolicyLineType clt
JOIN ConfigureLkLanguageResource clr ON clr.CultureCode LIKE 'en-US' AND clr.TableName = 'CdPolicyLineType' AND clr.ConfigureLkLanguageResourceID = clt.ConfigureLkLanguageResourceID
),
Main AS (SELECT
        CAST(int.UniqPolicy AS VARCHAR(255)) AS "CRM_Migration_External_Id__c"
        ,CAST(cli.UniqEntity AS VARCHAR(255)) AS "Account__c.CRM_Migration_External_Id__c"
        ,CAST(age.UniqAgency AS VARCHAR(255)) AS "Agency__c.CRM_Migration_External_Id__c"
        ,CAST(brc.UniqBranch AS VARCHAR(255)) AS "Branch__c.CRM_Migration_External_Id__c"
        ,CAST(dep.UniqDepartment AS VARCHAR(255)) AS "Department__c.CRM_Migration_External_Id__c"
,case
    when int.UniqLkPolicySource != -1 then concat('POL-', int.UniqLkPolicySource)
    else null end AS "Policy_Source__c.CRM_Migration_External_Id__c"
        ,SUBSTRING(int.DescriptionOf, 1, 80) AS "Name"
,case
    when int.Flags & 8 = 8 then 'Multi-Carrier Schedule'
    when int.Flags & 128 = 128 then 'Multi-Commission Schedule'
    else 'None' end AS "Multi__c"
        ,typ.ResourceText AS "Type__c"
        ,CONVERT(DATE, int.EffectiveDate, 101) AS "Effective_Date__c"
        ,CONVERT(DATE, int.ExpirationDate, 101) AS "Expiration_Date__c"
        ,int.PolicyNumber AS "Policy_Number__c"
,case
    when int.Flags & 32 = 32 then 1
    else 0 end AS "Download_Off__c"

	    ,CASE WHEN INT.FLAGS & 2 = 0 THEN 'Contracted' ELSE 'Prospective' END as "Prospective_Contracted__c"
-- Policy Premiums / Commissions --
		,CAST(int.BilledCommission AS DECIMAL(16, 2)) AS "Billed_Commission__c"
		,CAST(int.AnnualizedCommission AS DECIMAL(16, 2)) AS "Annualized_Commission__c"
		,CAST(int.EstimatedCommission AS DECIMAL(16, 2)) AS "Estimated_Commission__c"
		,CAST(int.BilledPremium AS DECIMAL(16, 2)) AS "Billed_Premium__c"

        ,CAST(int.AnnualizedPremium AS DECIMAL(16, 2)) AS "Annualized_Premium__c"
        ,CAST(int.EstimatedPremium AS DECIMAL(16, 2)) AS "Estimated_Premium__c"
        ,CAST(int.EstimatedMonthlyPremium AS DECIMAL(16, 2)) AS "Estimated_Monthly_Premium__c"
        ,CAST(int.LastDownloadedPremium AS DECIMAL(16, 2)) AS "Downloaded_Premium__c"
,case
    when len(bal.Balance__c) > 0 then CAST(bal.Balance__c AS DECIMAL(13, 2))
    else cast(0.00 as numeric(13,2)) end AS "Balance__c"
,case
    when int.Flags & 2 = 2 then 1
    else 0 end AS "DeadFlag"
-- Integration Fields
        ,'dbo_Policy_CT' AS "Table_Name"
        ,int.UniqPolicy AS "Epic_Id"
        ,int.__$start_lsn AS "LSN"
        ,int.__$seqval AS "LSN_Sequence"
        ,GETDATE() AS "Message_Timestamp"
        ,int.__$operation AS "DML_Operation"
        ,int.CRM_Int_Rank
FROM (select *,DENSE_RANK() OVER (PARTITION BY UniqPolicy ORDER BY __$start_lsn,__$seqval,newid()) AS "CRM_Int_Rank" from cdc.dbo_Policy_CT where __$operation != 3 union select null,null,null,null,null,*,null,null from dbo.Policy) int
JOIN dbo.Client cli ON cli.UniqEntity = int.UniqEntity
LEFT JOIN vType typ ON typ.UniqCdPolicyLineType = int.UniqCdPolicyLineType
LEFT JOIN dbo.Agency age on age.UniqAgency = int.UniqAgency
LEFT JOIN dbo.Branch brc on brc.UniqBranch = int.UniqBranch
LEFT JOIN dbo.Department dep on dep.UniqDepartment = int.UniqDepartment
LEFT JOIN crm.CRM_STD_Balance bal on bal.UniqPolicy = int.UniqPolicy and bal.AttachTo = 'P' and bal.EntityKey = 'CUST'
WHERE int.UniqPolicy <> -1
)
SELECT * FROM Main
WHERE NOT EXISTS (SELECT 1 FROM CRM.CRM_ErrorTracking WHERE TABLE_NAME = Main.Table_Name AND Epic_Id = Main.Epic_Id)
GO