SHARE
TWEET

Untitled

a guest Dec 3rd, 2019 65 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. WITH vType AS (SELECT
  2.         clt.UniqCdPolicyLineType
  3.         ,clt.CdPolicyLineTypeID
  4.         ,clr.ResourceText
  5. FROM CdPolicyLineType clt
  6. JOIN ConfigureLkLanguageResource clr ON clr.CultureCode LIKE 'en-US' AND clr.TableName = 'CdPolicyLineType' AND clr.ConfigureLkLanguageResourceID = clt.ConfigureLkLanguageResourceID
  7. ),
  8. Main AS (SELECT
  9.         CAST(int.UniqPolicy AS VARCHAR(255)) AS "CRM_Migration_External_Id__c"
  10.         ,CAST(cli.UniqEntity AS VARCHAR(255)) AS "Account__c.CRM_Migration_External_Id__c"
  11.         ,CAST(age.UniqAgency AS VARCHAR(255)) AS "Agency__c.CRM_Migration_External_Id__c"
  12.         ,CAST(brc.UniqBranch AS VARCHAR(255)) AS "Branch__c.CRM_Migration_External_Id__c"
  13.         ,CAST(dep.UniqDepartment AS VARCHAR(255)) AS "Department__c.CRM_Migration_External_Id__c"
  14. ,case
  15.     when int.UniqLkPolicySource != -1 then concat('POL-', int.UniqLkPolicySource)
  16.     else null end AS "Policy_Source__c.CRM_Migration_External_Id__c"
  17.         ,SUBSTRING(int.DescriptionOf, 1, 80) AS "Name"
  18. ,case
  19.     when int.Flags & 8 = 8 then 'Multi-Carrier Schedule'
  20.     when int.Flags & 128 = 128 then 'Multi-Commission Schedule'
  21.     else 'None' end AS "Multi__c"
  22.         ,typ.ResourceText AS "Type__c"
  23.         ,CONVERT(DATE, int.EffectiveDate, 101) AS "Effective_Date__c"
  24.         ,CONVERT(DATE, int.ExpirationDate, 101) AS "Expiration_Date__c"
  25.         ,int.PolicyNumber AS "Policy_Number__c"
  26. ,case
  27.     when int.Flags & 32 = 32 then 1
  28.     else 0 end AS "Download_Off__c"
  29.  
  30.         ,CASE WHEN INT.FLAGS & 2 = 0 THEN 'Contracted' ELSE 'Prospective' END as "Prospective_Contracted__c"
  31. -- Policy Premiums / Commissions --
  32.         ,CAST(int.BilledCommission AS DECIMAL(16, 2)) AS "Billed_Commission__c"    
  33.         ,CAST(int.AnnualizedCommission AS DECIMAL(16, 2)) AS "Annualized_Commission__c"    
  34.         ,CAST(int.EstimatedCommission AS DECIMAL(16, 2)) AS "Estimated_Commission__c"      
  35.         ,CAST(int.BilledPremium AS DECIMAL(16, 2)) AS "Billed_Premium__c"
  36.  
  37.         ,CAST(int.AnnualizedPremium AS DECIMAL(16, 2)) AS "Annualized_Premium__c"
  38.         ,CAST(int.EstimatedPremium AS DECIMAL(16, 2)) AS "Estimated_Premium__c"
  39.         ,CAST(int.EstimatedMonthlyPremium AS DECIMAL(16, 2)) AS "Estimated_Monthly_Premium__c"
  40.         ,CAST(int.LastDownloadedPremium AS DECIMAL(16, 2)) AS "Downloaded_Premium__c"
  41. ,case
  42.     when len(bal.Balance__c) > 0 then CAST(bal.Balance__c AS DECIMAL(13, 2))
  43.     else cast(0.00 as numeric(13,2)) end AS "Balance__c"
  44. ,case
  45.     when int.Flags & 2 = 2 then 1
  46.     else 0 end AS "DeadFlag"
  47. -- Integration Fields
  48.         ,'dbo_Policy_CT' AS "Table_Name"
  49.         ,int.UniqPolicy AS "Epic_Id"
  50.         ,int.__$start_lsn AS "LSN"
  51.         ,int.__$seqval AS "LSN_Sequence"
  52.         ,GETDATE() AS "Message_Timestamp"
  53.         ,int.__$operation AS "DML_Operation"
  54.         ,int.CRM_Int_Rank
  55. 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
  56. JOIN dbo.Client cli ON cli.UniqEntity = int.UniqEntity
  57. LEFT JOIN vType typ ON typ.UniqCdPolicyLineType = int.UniqCdPolicyLineType
  58. LEFT JOIN dbo.Agency age on age.UniqAgency = int.UniqAgency
  59. LEFT JOIN dbo.Branch brc on brc.UniqBranch = int.UniqBranch
  60. LEFT JOIN dbo.Department dep on dep.UniqDepartment = int.UniqDepartment
  61. LEFT JOIN crm.CRM_STD_Balance bal on bal.UniqPolicy = int.UniqPolicy and bal.AttachTo = 'P' and bal.EntityKey = 'CUST'
  62. WHERE int.UniqPolicy <> -1
  63. )
  64. SELECT * FROM Main
  65. WHERE NOT EXISTS (SELECT 1 FROM CRM.CRM_ErrorTracking WHERE TABLE_NAME = Main.Table_Name AND Epic_Id = Main.Epic_Id)
  66. GO
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top