API tools faq
paste
Advertisement
Guest User

Untitled

a guest
May 25th, 2015
218
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 108.08 KB | None | 0 0
raw download clone embed print report
  1. DECLARE @listingid int
  2. SET @listingid = 1843955
  3. DECLARE @userid int
  4. select @userid=userid from circleone..listings where id=@listingid
  5. SET nocount on;
  6. SET transaction isolation level read uncommitted
  7. DECLARE @Aggregate TABLE (
  8. [ID] [int] NOT NULL,[UserID] [int] NOT NULL,[PendingStartDate] [datetime] NULL,[MissingData] [int] NOT NULL,[eID] [int] NULL,[eUserID] [int] NULL,[numAgeOfAuthenticationMonths] [int] NULL,[numAgeOfAuthenticationYears] [int] NULL,[datAuthenticationCreationDate] [datetime] NULL,[numConsumerTotal] [int] NULL,[numCustomerOnlyTotal] [int] NULL,[catSSNMatch] [varchar](9) NULL,[catYOBMatch] [varchar](9) NULL,[catDLMatch] [varchar](9) NULL,[catDLSMatch] [varchar](9) NULL,[numAuthenticationScore] [int] NULL,[catAuthenticationScore] [varchar](8) NULL,[ASScoreFactor1] [int] NULL,[ASScoreFactor2] [int] NULL,[ASScoreFactor3] [int] NULL,[ASScoreFactor4] [int] NULL,[numScoreFactorCount] [int] NULL,[flgBlockedFile] [int] NULL,[flgVictimStatement] [int] NULL,[flgConsumerStatement] [int] NULL,[flgLargeFile] [int] NULL,[catAlertMessage] [varchar](8) NULL,[flgFraudAlert] [int] NULL,[flgSecurityAlert] [int] NULL,[catIPCountryMatch] [varchar](10) NULL,[catIPStateMatch] [varchar](12) NULL,[txtIPState] [varchar](50) NULL,[catIPCityMatch] [varchar](12) NULL,[txtIPCity] [varchar](50) NULL,[catIPZipMatch] [varchar](12) NULL,[txtIPZip] [varchar](5) NULL,[catAcceptReferCode] [varchar](6) NULL,[catOutputTypeCode] [varchar](10) NULL,[numInWalletScore] [int] NULL,[catInWalletScore] [varchar](8) NULL,[flgFS01] [int] NULL,[flgFS02] [int] NULL,[flgFS03] [int] NULL,[flgFS04] [int] NULL,[flgFS05] [int] NULL,[flgFS06] [int] NULL,[flgFS07] [int] NULL,[flgFS08] [int] NULL,[flgFS09] [int] NULL,[flgFS10] [int] NULL,[flgFS11] [int] NULL,[flgFS12] [int] NULL,[flgFS13] [int] NULL,[flgFS14] [int] NULL,[flgFS15] [int] NULL,[flgFS16] [int] NULL,[flgFS17] [int] NULL,[flgFS18] [int] NULL,[flgFS19] [int] NULL,[flgFS20] [int] NULL,[flgFS21] [int] NULL,[flgFS22] [int] NULL,[flgFS25] [int] NULL,[flgFS26] [int] NULL,[flgFS27] [int] NULL,[numCheckpointScore] [int] NULL,[catCheckpointScore] [varchar](8) NULL,[numModelScore] [int] NULL,[catModelScore] [varchar](8) NULL,[catLevelOneDecisionCode] [varchar](6) NULL,[numPrimaryResult] [varchar](10) NULL,[txtAddressVerificationResult] [varchar](10) NULL,[catAddressUnitMismatchResult] [varchar](11) NULL,[txtPhoneVerificationResult] [varchar](10) NULL,[txtPhoneUnitMismatchResult] [varchar](10) NULL,[txtAddressTypeResult] [varchar](10) NULL,[catAddressHighRiskResult] [varchar](7) NULL,[catPhoneHighRiskResult] [varchar](7) NULL,[txtSocialSecurityNumberResult] [varchar](10) NULL,[numResidentialAddressDetailRecords] [int] NULL,[numBusinessAddressDetailRecords] [int] NULL,[numAddressHighRiskDetailRecords] [int] NULL,[numAddressHighRiskDescriptionRecords] [int] NULL,[numResidentialPhoneDetailRecords] [int] NULL,[numBusinessPhoneDetailRecords] [int] NULL,[numPhoneHighRiskDetailRecords] [int] NULL,[numPhoneHighRiskDescriptionRecords] [int] NULL,[numSSNDetailRecords] [int] NULL,[numCOARecords] [int] NULL,[numCOADescriptionRecords] [int] NULL,[txtDLResultCode] [varchar](10) NULL,[catDateOfBirthMatch] [varchar](9) NULL,[txtSSNIssueState] [varchar](5) NULL,[flgSSNDeceased] [int] NULL,[txtSSNFormat] [varchar](10) NULL,[numSSNIssueStartRange] [int] NULL,[catSSNIssueStartRange] [varchar](7) NULL,[numSSNIssueEndRange] [int] NULL,[txtSSNIssueResultCode] [varchar](10) NULL,[catOFACValidationResult] [varchar](7) NULL,[sID] [int] NULL,[sUserID] [int] NULL,[numALL001] [int] NULL,[numALL021] [int] NULL,[numALL026] [int] NULL,[numALL051] [int] NULL,[numALL052] [int] NULL,[numALL062] [int] NULL,[numALL064] [int] NULL,[numALL081] [int] NULL,[numALL084] [int] NULL,[numALL141] [int] NULL,[numALL142] [int] NULL,[numALL201] [int] NULL,[numALL207] [int] NULL,[numALL208] [int] NULL,[numALL701] [int] NULL,[numALL806] [int] NULL,[numALL901] [int] NULL,[numBAC026] [int] NULL,[numBAC401] [int] NULL,[numBAC403] [int] NULL,[numILN201] [int] NULL,[numREF001] [int] NULL,[numREP002] [int] NULL,[numREV201] [int] NULL,[numREV023] [int] NULL,[numILN005] [int] NULL,[numILN108] [int] NULL, numILN002 [int] NULL, numALL803[int] NULL, numALE007 [int] NULL, numILN007 [int] NULL, numALL002 [int] NULL, numRTR751 [int] NULL, [dID] [int] NULL,[dUserID] [int] NULL,[numDistinctDups] [int] NULL,[flgDupExistence] [int] NULL,[flgDupTypeCookie] [int] NULL,[flgDupTypePhone] [int] NULL,[flgDupTypeAddress] [int] NULL,[flgDupTypeBank] [int] NULL,[numMaxDupDateOfBirthDiff] [int] NULL,[numAvgDupDateOfBirthDiff] [int] NULL,[numMinDupCreditScore] [int] NULL,[numAvgDupCreditScore] [int] NULL,[flgMaxDupUserStatusNotActive] [int] NULL,[flgMaxDupUserStatusSuspendedTerminated] [int] NULL,[flgMaxDupCreditScoreLow] [int] NULL,[flgMaxDupCreditScoreMissing] [int] NULL,[flgMaxDupLoanChargeoffDefault] [int] NULL,[flgMaxDupLoanDPD0Greater] [int] NULL,[flgMaxDupLoanDPD30Greater] [int] NULL,[flgMaxDupLoanDPD60Greater] [int] NULL,[flgMaxDupLoanDPD90Greater] [int] NULL,[flgMaxDupLoanDPD120Greater] [int] NULL,[flgMaxDupLoanActive] [int] NULL,[flgMaxDupLoanPaid] [int] NULL,[flgMaxDupNoLoan] [int] NULL,[flgMaxDupRecentLoan] [int] NULL,[flgMaxDupRecentListing] [int] NULL,[flgMaxDupManualPayment] [int] NULL,[flgMaxDupHighPayment] [int] NULL,[flgMaxDupLatePayment] [int] NULL,[flgMaxDupFailedPayment] [int] NULL,[flgMaxDupBusinessAddress] [int] NULL,[flgMaxDupSameName] [int] NULL,[flgMaxDupSameFirstName] [int] NULL,[flgMaxDupSameLastName] [int] NULL,[flgMaxDupHighAgeDiff] [int] NULL,[uID] [int] NULL,[uUserID] [int] NULL,[numUserListingAmount] [money] NULL,[flgUserMultiBankAccounts] [int] NULL,[flgUserMultiBankHolders] [int] NULL,[flgUserPrePaidBankAccount] [int] NULL,[flgUserSuspicion] [int] NULL,[ListingTitle] [varchar](50) NULL,[flgUserTitleConsolidation] [int] NULL,[flgUserTitleDebt] [int] NULL,[numUser30DBankChange] [int] NULL,[numUser30DBankCount] [int] NULL,[numUserAgeYears] [int] NULL,[numUserBankCount] [int] NULL,[numUserEmploymentLengthMonths] [int] NULL,[numUserFailLogins] [int] NULL,[numUserLogins] [int] NULL,[numUserPercentFailLogins] [numeric](33, 17) NULL,[numUserRegistrationAgeMonths] [int] NULL,[numUserSuccessLogins] [int] NULL,[numUserTitleLength] [int] NULL,[numPrevCancellations] [int] NULL,[numPrevOriginations] [int] NULL,[numPrevWithdrawals] [int] NULL,[flgFraudScreen] [int] NOT NULL,[FSSUM] [int] NULL,[flgChannelDM] [int] NOT NULL, [channel] [varchar] (5), [AggregateBalanceCreditRatioOnAllTrades6Months] [decimal](15, 5) NULL,[OpenPaidClosedInactiveInstallTrades] [int] NULL
  9. )
  10.  
  11. DECLARE @AggregateFraud TABLE (
  12. [ID] [int] NOT NULL,[UserID] [int] NOT NULL,[PendingStartDate] [datetime] NULL,[MissingData] [int] NOT NULL,[eID] [int] NULL,[eUserID] [int] NULL,[numAgeOfAuthenticationMonths] [int] NULL,[numAgeOfAuthenticationYears] [int] NULL,[datAuthenticationCreationDate] [datetime] NULL,[numConsumerTotal] [int] NULL,[numCustomerOnlyTotal] [int] NULL,[catSSNMatch] [varchar](9) NULL,[catYOBMatch] [varchar](9) NULL,[catDLMatch] [varchar](9) NULL,[catDLSMatch] [varchar](9) NULL,[numAuthenticationScore] [int] NULL,[catAuthenticationScore] [varchar](8) NULL,[ASScoreFactor1] [int] NULL,[ASScoreFactor2] [int] NULL,[ASScoreFactor3] [int] NULL,[ASScoreFactor4] [int] NULL,[numScoreFactorCount] [int] NULL,[flgBlockedFile] [int] NULL,[flgVictimStatement] [int] NULL,[flgConsumerStatement] [int] NULL,[flgLargeFile] [int] NULL,[catAlertMessage] [varchar](8) NULL,[flgFraudAlert] [int] NULL,[flgSecurityAlert] [int] NULL,[catIPCountryMatch] [varchar](10) NULL,[catIPStateMatch] [varchar](12) NULL,[txtIPState] [varchar](50) NULL,[catIPCityMatch] [varchar](12) NULL,[txtIPCity] [varchar](50) NULL,[catIPZipMatch] [varchar](12) NULL,[txtIPZip] [varchar](5) NULL,[catAcceptReferCode] [varchar](6) NULL,[catOutputTypeCode] [varchar](10) NULL,[numInWalletScore] [int] NULL,[catInWalletScore] [varchar](8) NULL,[flgFS01] [int] NULL,[flgFS02] [int] NULL,[flgFS03] [int] NULL,[flgFS04] [int] NULL,[flgFS05] [int] NULL,[flgFS06] [int] NULL,[flgFS07] [int] NULL,[flgFS08] [int] NULL,[flgFS09] [int] NULL,[flgFS10] [int] NULL,[flgFS11] [int] NULL,[flgFS12] [int] NULL,[flgFS13] [int] NULL,[flgFS14] [int] NULL,[flgFS15] [int] NULL,[flgFS16] [int] NULL,[flgFS17] [int] NULL,[flgFS18] [int] NULL,[flgFS19] [int] NULL,[flgFS20] [int] NULL,[flgFS21] [int] NULL,[flgFS22] [int] NULL,[flgFS25] [int] NULL,[flgFS26] [int] NULL,[flgFS27] [int] NULL,[numCheckpointScore] [int] NULL,[catCheckpointScore] [varchar](8) NULL,[numModelScore] [int] NULL,[catModelScore] [varchar](8) NULL,[catLevelOneDecisionCode] [varchar](6) NULL,[numPrimaryResult] [varchar](10) NULL,[txtAddressVerificationResult] [varchar](10) NULL,[catAddressUnitMismatchResult] [varchar](11) NULL,[txtPhoneVerificationResult] [varchar](10) NULL,[txtPhoneUnitMismatchResult] [varchar](10) NULL,[txtAddressTypeResult] [varchar](10) NULL,[catAddressHighRiskResult] [varchar](7) NULL,[catPhoneHighRiskResult] [varchar](7) NULL,[txtSocialSecurityNumberResult] [varchar](10) NULL,[numResidentialAddressDetailRecords] [int] NULL,[numBusinessAddressDetailRecords] [int] NULL,[numAddressHighRiskDetailRecords] [int] NULL,[numAddressHighRiskDescriptionRecords] [int] NULL,[numResidentialPhoneDetailRecords] [int] NULL,[numBusinessPhoneDetailRecords] [int] NULL,[numPhoneHighRiskDetailRecords] [int] NULL,[numPhoneHighRiskDescriptionRecords] [int] NULL,[numSSNDetailRecords] [int] NULL,[numCOARecords] [int] NULL,[numCOADescriptionRecords] [int] NULL,[txtDLResultCode] [varchar](10) NULL,[catDateOfBirthMatch] [varchar](9) NULL,[txtSSNIssueState] [varchar](5) NULL,[flgSSNDeceased] [int] NULL,[txtSSNFormat] [varchar](10) NULL,[numSSNIssueStartRange] [int] NULL,[catSSNIssueStartRange] [varchar](7) NULL,[numSSNIssueEndRange] [int] NULL,[txtSSNIssueResultCode] [varchar](10) NULL,[catOFACValidationResult] [varchar](7) NULL,[sID] [int] NULL,[sUserID] [int] NULL,[numALL001] [int] NULL,[numALL021] [int] NULL,[numALL026] [int] NULL,[numALL051] [int] NULL,[numALL052] [int] NULL,[numALL062] [int] NULL,[numALL064] [int] NULL,[numALL081] [int] NULL,[numALL084] [int] NULL,[numALL141] [int] NULL,[numALL142] [int] NULL,[numALL201] [int] NULL,[numALL207] [int] NULL,[numALL208] [int] NULL,[numALL701] [int] NULL,[numALL806] [int] NULL,[numALL901] [int] NULL,[numBAC026] [int] NULL,[numBAC401] [int] NULL,[numBAC403] [int] NULL,[numILN201] [int] NULL,[numREF001] [int] NULL,[numREP002] [int] NULL,[numREV201] [int] NULL,[numREV023] [int] NULL,[numILN005] [int] NULL,[numILN108] [int] NULL, numILN002 [int] NULL, numALL803[int] NULL, numALE007 [int] NULL, numILN007 [int] NULL, numALL002 [int] NULL, numRTR751 [int] NULL, [dID] [int] NULL,[dUserID] [int] NULL,[numDistinctDups] [int] NULL,[flgDupExistence] [int] NULL,[flgDupTypeCookie] [int] NULL,[flgDupTypePhone] [int] NULL,[flgDupTypeAddress] [int] NULL,[flgDupTypeBank] [int] NULL,[numMaxDupDateOfBirthDiff] [int] NULL,[numAvgDupDateOfBirthDiff] [int] NULL,[numMinDupCreditScore] [int] NULL,[numAvgDupCreditScore] [int] NULL,[flgMaxDupUserStatusNotActive] [int] NULL,[flgMaxDupUserStatusSuspendedTerminated] [int] NULL,[flgMaxDupCreditScoreLow] [int] NULL,[flgMaxDupCreditScoreMissing] [int] NULL,[flgMaxDupLoanChargeoffDefault] [int] NULL,[flgMaxDupLoanDPD0Greater] [int] NULL,[flgMaxDupLoanDPD30Greater] [int] NULL,[flgMaxDupLoanDPD60Greater] [int] NULL,[flgMaxDupLoanDPD90Greater] [int] NULL,[flgMaxDupLoanDPD120Greater] [int] NULL,[flgMaxDupLoanActive] [int] NULL,[flgMaxDupLoanPaid] [int] NULL,[flgMaxDupNoLoan] [int] NULL,[flgMaxDupRecentLoan] [int] NULL,[flgMaxDupRecentListing] [int] NULL,[flgMaxDupManualPayment] [int] NULL,[flgMaxDupHighPayment] [int] NULL,[flgMaxDupLatePayment] [int] NULL,[flgMaxDupFailedPayment] [int] NULL,[flgMaxDupBusinessAddress] [int] NULL,[flgMaxDupSameName] [int] NULL,[flgMaxDupSameFirstName] [int] NULL,[flgMaxDupSameLastName] [int] NULL,[flgMaxDupHighAgeDiff] [int] NULL,[uID] [int] NULL,[uUserID] [int] NULL,[numUserListingAmount] [money] NULL,[flgUserMultiBankAccounts] [int] NULL,[flgUserMultiBankHolders] [int] NULL,[flgUserPrePaidBankAccount] [int] NULL,[flgUserSuspicion] [int] NULL,[ListingTitle] [varchar](50) NULL,[flgUserTitleConsolidation] [int] NULL,[flgUserTitleDebt] [int] NULL,[numUser30DBankChange] [int] NULL,[numUser30DBankCount] [int] NULL,[numUserAgeYears] [int] NULL,[numUserBankCount] [int] NULL,[numUserEmploymentLengthMonths] [int] NULL,[numUserFailLogins] [int] NULL,[numUserLogins] [int] NULL,[numUserPercentFailLogins] [numeric](33, 17) NULL,[numUserRegistrationAgeMonths] [int] NULL,[numUserSuccessLogins] [int] NULL,[numUserTitleLength] [int] NULL,[numPrevCancellations] [int] NULL,[numPrevOriginations] [int] NULL,[numPrevWithdrawals] [int] NULL,[flgFraudScreen] [int] NOT NULL,[FSSUM] [int] NULL,[flgChannelDM] [int] NOT NULL, [channel] [varchar] (5), [AggregateBalanceCreditRatioOnAllTrades6Months] [decimal](15, 5) NULL,[OpenPaidClosedInactiveInstallTrades] [int] NULL, [flgSuspicionCriterion] [int] NOT NULL, [flgFraudScreenEmail] [int] NULL
  13. )
  14.  
  15. DECLARE @AllPendingListings TABLE (
  16. [ID] [int] NOT NULL,[UserID] [int] NOT NULL,[PendingStartDate] [datetime] NULL
  17. )
  18.  
  19. DECLARE @ChannelAttribution TABLE (
  20. [ListingID] [int] NOT NULL,[CreditPullDate] [datetime] NULL,[ListingScoreID] [int] NULL,[TermsApprovalDate] [datetime] NULL,[PartnershipBin] [varchar](4000) NULL, [Channel] int null
  21. )
  22.  
  23. DECLARE @Duplicates TABLE (
  24. [dID] [int] NOT NULL,[dUserID] [int] NOT NULL,[numDistinctDups] [int] NULL,[flgDupExistence] [int] NOT NULL,[flgDupTypeCookie] [int] NULL,[flgDupTypePhone] [int] NULL,[flgDupTypeAddress] [int] NULL,[flgDupTypeBank] [int] NULL,[numMaxDupDateOfBirthDiff] [int] NOT NULL,[numAvgDupDateOfBirthDiff] [int] NOT NULL,[numMinDupCreditScore] [int] NOT NULL,[numAvgDupCreditScore] [int] NOT NULL,[flgMaxDupUserStatusNotActive] [int] NULL,[flgMaxDupUserStatusSuspendedTerminated] [int] NULL,[flgMaxDupCreditScoreLow] [int] NULL,[flgMaxDupCreditScoreMissing] [int] NULL,[flgMaxDupLoanChargeoffDefault] [int] NULL,[flgMaxDupLoanDPD0Greater] [int] NULL,[flgMaxDupLoanDPD30Greater] [int] NULL,[flgMaxDupLoanDPD60Greater] [int] NULL,[flgMaxDupLoanDPD90Greater] [int] NULL,[flgMaxDupLoanDPD120Greater] [int] NULL,[flgMaxDupLoanActive] [int] NULL,[flgMaxDupLoanPaid] [int] NULL,[flgMaxDupNoLoan] [int] NULL,[flgMaxDupRecentLoan] [int] NULL,[flgMaxDupRecentListing] [int] NULL,[flgMaxDupManualPayment] [int] NULL,[flgMaxDupHighPayment] [int] NULL,[flgMaxDupLatePayment] [int] NULL,[flgMaxDupFailedPayment] [int] NULL,[flgMaxDupBusinessAddress] [int] NULL,[flgMaxDupSameName] [int] NULL,[flgMaxDupSameFirstName] [int] NULL,[flgMaxDupSameLastName] [int] NULL,[flgMaxDupHighAgeDiff] [int] NOT NULL
  25. )
  26.  
  27. DECLARE @DuplicatesStg TABLE (
  28. [ID] [int] NOT NULL,[UserID] [int] NOT NULL,[aaaDupUserID] [int] NULL,[catDupType] [varchar](7) NULL,[flgDupSuspicious] [int] NOT NULL,[datDupDateOfBirth] [datetime] NULL,[datUserDateOfBirth] [datetime] NULL,[numDateOfBirthDiff] [int] NULL,[catDupUserStatus] [varchar](32) NULL,[flgDupUserStatusNotActive] [int] NOT NULL,[flgDupUserStatusSuspendedTerminated] [int] NOT NULL,[numDupCreditScore] [int] NULL,[flgDupCreditScoreLow] [int] NOT NULL,[flgDupCreditScoreMissing] [int] NOT NULL,[flgDupLoanChargeoffDefault] [int] NOT NULL,[flgDupLoanDPD0Greater] [int] NOT NULL,[flgDupLoanDPD30Greater] [int] NOT NULL,[flgDupLoanDPD60Greater] [int] NOT NULL,[flgDupLoanDPD90Greater] [int] NOT NULL,[flgDupLoanDPD120Greater] [int] NOT NULL,[flgDupLoanActive] [int] NOT NULL,[flgDupLoanPaid] [int] NOT NULL,[flgDupNoLoan] [int] NOT NULL,[flgDupRecentLoan] [int] NOT NULL,[flgDupRecentListing] [int] NOT NULL,[flgDupManualPayment] [int] NOT NULL,[flgDupHighPayment] [int] NOT NULL,[flgDupLatePayment] [int] NOT NULL,[flgDupFailedPayment] [int] NOT NULL,[flgDupBusinessAddress] [int] NOT NULL,[flgDupSameName] [int] NOT NULL,[flgDupSameFirstName] [int] NOT NULL,[flgDupSameLastName] [int] NOT NULL
  29. )
  30.  
  31. DECLARE @DuplicateUsers TABLE (
  32. [ID] [int] NOT NULL,[UserID] [int] NOT NULL,[DupUserID] [int] NULL,[DupType] [varchar](7) NOT NULL
  33. )
  34.  
  35. DECLARE @DuplicateUsersADRStg TABLE (
  36. [ID] [int] NOT NULL,[UserID] [int] NOT NULL,[DupUserID] [int] NULL,[DupType] [varchar](7) NULL,[flgDupBusinessAddress] [int] NOT NULL,[flgDupSameLastName] [int] NOT NULL,[flgDupSameFirstName] [int] NOT NULL,[flgDupSameName] [int] NOT NULL
  37. )
  38.  
  39. DECLARE @DuplicateUsersCODStg TABLE (
  40. [ID] [int] NOT NULL,[UserID] [int] NOT NULL,[DupUserID] [int] NULL,[DupType] [varchar](7) NULL,[numDupLoanChargeoffDefaulted] [int] NULL,[numDupLoanDPD0Greater] [int] NULL,[numDupLoanDPD30Greater] [int] NULL,[numDupLoanDPD60Greater] [int] NULL,[numDupLoanDPD90Greater] [int] NULL,[numDupLoanDPD120Greater] [int] NULL,[numDupLoanActive] [int] NULL,[numDupLoanPaid] [int] NULL,[flgDupNoLoan] [int] NULL,[numDupRecentLoan] [int] NULL
  41. )
  42.  
  43. DECLARE @DuplicateUsersDOBStg TABLE (
  44. [ID] [int] NOT NULL,[UserID] [int] NOT NULL,[DupUserID] [int] NULL,[DupType] [varchar](7) NOT NULL,[datDupDateOfBirth] [datetime] NULL,[datUserDateOfBirth] [datetime] NULL,[numDateOfBirthDiff] [int] NULL
  45. )
  46.  
  47. DECLARE @DuplicateUsersEMPStg TABLE (
  48. [ID] [int] NOT NULL,[UserID] [int] NOT NULL,[DupUserID] [int] NULL,[DupType] [varchar](7) NULL
  49. )
  50.  
  51. DECLARE @DuplicateUsersPMTStg TABLE (
  52. [ID] [int] NOT NULL,[UserID] [int] NOT NULL,[DupUserID] [int] NULL,[DupType] [varchar](7) NULL,[numDupManualPayment] [int] NULL,[numDupHighPayment] [int] NULL,[numDupLatePayment] [int] NULL,[numDupFailedPayment] [int] NULL,[numDupLoanPayments] [int] NULL
  53. )
  54.  
  55. DECLARE @Experian TABLE (
  56. [eID] [int] NOT NULL,[eUserID] [int] NOT NULL,[numAgeOfAuthenticationMonths] [int] NULL,[numAgeOfAuthenticationYears] [int] NULL,[datAuthenticationCreationDate] [datetime] NULL,[numConsumerTotal] [int] NULL,[numCustomerOnlyTotal] [int] NULL,[catSSNMatch] [varchar](9) NULL,[catYOBMatch] [varchar](9) NULL,[catDLMatch] [varchar](9) NOT NULL,[catDLSMatch] [varchar](9) NOT NULL,[numAuthenticationScore] [int] NULL,[catAuthenticationScore] [varchar](8) NOT NULL,[ASScoreFactor1] [int] NOT NULL,[ASScoreFactor2] [int] NOT NULL,[ASScoreFactor3] [int] NOT NULL,[ASScoreFactor4] [int] NOT NULL,[numScoreFactorCount] [int] NULL,[flgBlockedFile] [int] NOT NULL,[flgVictimStatement] [int] NOT NULL,[flgConsumerStatement] [int] NOT NULL,[flgLargeFile] [int] NOT NULL,[catAlertMessage] [varchar](8) NOT NULL,[flgFraudAlert] [int] NOT NULL,[flgSecurityAlert] [int] NOT NULL,[catIPCountryMatch] [varchar](10) NOT NULL,[catIPStateMatch] [varchar](12) NOT NULL,[txtIPState] [varchar](50) NOT NULL,[catIPCityMatch] [varchar](12) NOT NULL,[txtIPCity] [varchar](50) NOT NULL,[catIPZipMatch] [varchar](12) NOT NULL,[txtIPZip] [varchar](5) NOT NULL,[catAcceptReferCode] [varchar](6) NULL,[catOutputTypeCode] [varchar](10) NULL,[numInWalletScore] [int] NULL,[catInWalletScore] [varchar](8) NOT NULL,[flgFS01] [int] NOT NULL,[flgFS02] [int] NOT NULL,[flgFS03] [int] NOT NULL,[flgFS04] [int] NOT NULL,[flgFS05] [int] NOT NULL,[flgFS06] [int] NOT NULL,[flgFS07] [int] NOT NULL,[flgFS08] [int] NOT NULL,[flgFS09] [int] NOT NULL,[flgFS10] [int] NOT NULL,[flgFS11] [int] NOT NULL,[flgFS12] [int] NOT NULL,[flgFS13] [int] NOT NULL,[flgFS14] [int] NOT NULL,[flgFS15] [int] NOT NULL,[flgFS16] [int] NOT NULL,[flgFS17] [int] NOT NULL,[flgFS18] [int] NOT NULL,[flgFS19] [int] NOT NULL,[flgFS20] [int] NOT NULL,[flgFS21] [int] NOT NULL,[flgFS22] [int] NOT NULL,[flgFS25] [int] NOT NULL,[flgFS26] [int] NOT NULL,[flgFS27] [int] NOT NULL,[numCheckpointScore] [int] NULL,[catCheckpointScore] [varchar](8) NOT NULL,[numModelScore] [int] NULL,[catModelScore] [varchar](8) NOT NULL,[catLevelOneDecisionCode] [varchar](6) NULL,[numPrimaryResult] [varchar](10) NULL,[txtAddressVerificationResult] [varchar](10) NULL,[catAddressUnitMismatchResult] [varchar](11) NULL,[txtPhoneVerificationResult] [varchar](10) NULL,[txtPhoneUnitMismatchResult] [varchar](10) NOT NULL,[txtAddressTypeResult] [varchar](10) NULL,[catAddressHighRiskResult] [varchar](7) NOT NULL,[catPhoneHighRiskResult] [varchar](7) NOT NULL,[txtSocialSecurityNumberResult] [varchar](10) NULL,[numResidentialAddressDetailRecords] [int] NULL,[numBusinessAddressDetailRecords] [int] NULL,[numAddressHighRiskDetailRecords] [int] NULL,[numAddressHighRiskDescriptionRecords] [int] NULL,[numResidentialPhoneDetailRecords] [int] NULL,[numBusinessPhoneDetailRecords] [int] NULL,[numPhoneHighRiskDetailRecords] [int] NULL,[numPhoneHighRiskDescriptionRecords] [int] NULL,[numSSNDetailRecords] [int] NULL,[numCOARecords] [int] NULL,[numCOADescriptionRecords] [int] NULL,[txtDLResultCode] [varchar](10) NULL,[catDateOfBirthMatch] [varchar](9) NULL,[txtSSNIssueState] [varchar](5) NULL,[flgSSNDeceased] [int] NOT NULL,[txtSSNFormat] [varchar](10) NULL,[numSSNIssueStartRange] [int] NULL,[catSSNIssueStartRange] [varchar](7) NOT NULL,[numSSNIssueEndRange] [int] NULL,[txtSSNIssueResultCode] [varchar](10) NULL,[catOFACValidationResult] [varchar](7) NULL
  57. )
  58.  
  59. DECLARE @ExperianStaging TABLE (
  60. [ListingID] [int] NOT NULL,[id] [int] NULL,[userid] [int] NULL,[creationdate] [datetime] NULL,[ErrorCode] [int] NULL,[ErrorMessage] [varchar](1000) NULL,[ConsumerTotal] [int] NULL,[CustomerOnlyTotal] [int] NULL,[SSNMatch] [int] NULL,[YOBMatch] [int] NULL,[DLMatch] [int] NULL,[DLSMatch] [int] NULL,[AuthenticationScore] [int] NULL,[ASScoreFactor1] [int] NULL,[ASScoreFactor2] [int] NULL,[ASScoreFactor3] [int] NULL,[ASScoreFactor4] [int] NULL,[CombinedCode] [varchar](4) NULL,[BlockedFileFlag] [varchar](4) NULL,[VictimStmtFlag] [varchar](4) NULL,[ConsumerStatementFlag] [varchar](4) NULL,[LargeFileFlag] [varchar](4) NULL,[CaliforniaFrozenFileFlag] [varchar](4) NULL,[VictimStatementMessage] [varchar](8000) NULL,[IPCountryMatch] [int] NULL,[IPCountry] [varchar](50) NULL,[IPStateMatch] [int] NULL,[IPState] [varchar](50) NULL,[IPCityMatch] [int] NULL,[IPCity] [varchar](50) NULL,[IPZipMatch] [int] NULL,[IPZip] [varchar](50) NULL,[AcceptReferCode] [varchar](10) NULL,[OutputTypeCode] [varchar](10) NULL,[InWalletScore] [int] NULL,[Fraud Shield Codes] [varchar](500) NULL,[FS01] [varchar](10) NULL,[FS02] [varchar](10) NULL,[FS03] [varchar](10) NULL,[FS04] [varchar](10) NULL,[FS05] [varchar](10) NULL,[FS06] [varchar](10) NULL,[FS07] [varchar](10) NULL,[FS08] [varchar](10) NULL,[FS09] [varchar](10) NULL,[FS10] [varchar](10) NULL,[FS11] [varchar](10) NULL,[FS12] [varchar](10) NULL,[FS13] [varchar](10) NULL,[FS14] [varchar](10) NULL,[FS15] [varchar](10) NULL,[FS16] [varchar](10) NULL,[FS17] [varchar](10) NULL,[FS18] [varchar](10) NULL,[FS19] [varchar](10) NULL,[FS20] [varchar](10) NULL,[FS21] [varchar](10) NULL,[FS22] [varchar](10) NULL,[FS25] [varchar](10) NULL,[FS26] [varchar](10) NULL,[FS27] [varchar](10) NULL,[CheckpointScore] [int] NULL,[ModelScore] [int] NULL,[LevelOneDecisionCode] [varchar](10) NULL,[PrimaryResult] [varchar](10) NULL,[AddressVerificationResult] [varchar](10) NULL,[AddressUnitMismatchResult] [varchar](10) NULL,[PhoneVerificationResult] [varchar](10) NULL,[PhoneUnitMismatchResult] [varchar](10) NULL,[AddressTypeResult] [varchar](10) NULL,[AddressHighRiskResult] [varchar](10) NULL,[PhoneHighRiskResult] [varchar](10) NULL,[SocialSecurityNumberResult] [varchar](10) NULL,[NumResidentialAddressDetailRecords] [int] NULL,[NumBusinessAddressDetailRecords] [int] NULL,[NumAddressHighRiskDetailRecords] [int] NULL,[NumAddressHighRiskDescriptionRecords] [int] NULL,[NumResidentialPhoneDetailRecords] [int] NULL,[NumBusinessPhoneDetailRecords] [int] NULL,[NumPhoneHighRiskDetailRecords] [int] NULL,[NumPhoneHighRiskDescriptionRecords] [int] NULL,[NumSSNDetailRecords] [int] NULL,[NumCOARecords] [int] NULL,[NumCOADescriptionRecords] [int] NULL,[DLResultCode] [varchar](10) NULL,[DateOfBirthMatch] [int] NULL,[SSNIssueState] [varchar](5) NULL,[SSNDeceased] [varchar](10) NULL,[SSNFormat] [varchar](10) NULL,[SSNIssueStartRange] [int] NULL,[SSNIssueEndRange] [int] NULL,[SSNIssueResultCode] [varchar](10) NULL,[OFACValidationResult] [int] NULL,[ModifiedDate] [datetime] NULL
  61. )
  62.  
  63. DECLARE @FilterUnprocessedStaging TABLE (
  64. [ID] [int] NOT NULL,[UserID] [int] NOT NULL,[PendingStartDate] [datetime] NULL,[MissingData] [int] NOT NULL,[eID] [int] NULL,[eUserID] [int] NULL,[numAgeOfAuthenticationMonths] [int] NULL,[numAgeOfAuthenticationYears] [int] NULL,[datAuthenticationCreationDate] [datetime] NULL,[numConsumerTotal] [int] NULL,[numCustomerOnlyTotal] [int] NULL,[catSSNMatch] [varchar](9) NULL,[catYOBMatch] [varchar](9) NULL,[catDLMatch] [varchar](9) NULL,[catDLSMatch] [varchar](9) NULL,[numAuthenticationScore] [int] NULL,[catAuthenticationScore] [varchar](8) NULL,[ASScoreFactor1] [int] NULL,[ASScoreFactor2] [int] NULL,[ASScoreFactor3] [int] NULL,[ASScoreFactor4] [int] NULL,[numScoreFactorCount] [int] NULL,[flgBlockedFile] [int] NULL,[flgVictimStatement] [int] NULL,[flgConsumerStatement] [int] NULL,[flgLargeFile] [int] NULL,[catAlertMessage] [varchar](8) NULL,[flgFraudAlert] [int] NULL,[flgSecurityAlert] [int] NULL,[catIPCountryMatch] [varchar](10) NULL,[catIPStateMatch] [varchar](12) NULL,[txtIPState] [varchar](50) NULL,[catIPCityMatch] [varchar](12) NULL,[txtIPCity] [varchar](50) NULL,[catIPZipMatch] [varchar](12) NULL,[txtIPZip] [varchar](5) NULL,[catAcceptReferCode] [varchar](6) NULL,[catOutputTypeCode] [varchar](10) NULL,[numInWalletScore] [int] NULL,[catInWalletScore] [varchar](8) NULL,[flgFS01] [int] NULL,[flgFS02] [int] NULL,[flgFS03] [int] NULL,[flgFS04] [int] NULL,[flgFS05] [int] NULL,[flgFS06] [int] NULL,[flgFS07] [int] NULL,[flgFS08] [int] NULL,[flgFS09] [int] NULL,[flgFS10] [int] NULL,[flgFS11] [int] NULL,[flgFS12] [int] NULL,[flgFS13] [int] NULL,[flgFS14] [int] NULL,[flgFS15] [int] NULL,[flgFS16] [int] NULL,[flgFS17] [int] NULL,[flgFS18] [int] NULL,[flgFS19] [int] NULL,[flgFS20] [int] NULL,[flgFS21] [int] NULL,[flgFS22] [int] NULL,[flgFS25] [int] NULL,[flgFS26] [int] NULL,[flgFS27] [int] NULL,[numCheckpointScore] [int] NULL,[catCheckpointScore] [varchar](8) NULL,[numModelScore] [int] NULL,[catModelScore] [varchar](8) NULL,[catLevelOneDecisionCode] [varchar](6) NULL,[numPrimaryResult] [varchar](10) NULL,[txtAddressVerificationResult] [varchar](10) NULL,[catAddressUnitMismatchResult] [varchar](11) NULL,[txtPhoneVerificationResult] [varchar](10) NULL,[txtPhoneUnitMismatchResult] [varchar](10) NULL,[txtAddressTypeResult] [varchar](10) NULL,[catAddressHighRiskResult] [varchar](7) NULL,[catPhoneHighRiskResult] [varchar](7) NULL,[txtSocialSecurityNumberResult] [varchar](10) NULL,[numResidentialAddressDetailRecords] [int] NULL,[numBusinessAddressDetailRecords] [int] NULL,[numAddressHighRiskDetailRecords] [int] NULL,[numAddressHighRiskDescriptionRecords] [int] NULL,[numResidentialPhoneDetailRecords] [int] NULL,[numBusinessPhoneDetailRecords] [int] NULL,[numPhoneHighRiskDetailRecords] [int] NULL,[numPhoneHighRiskDescriptionRecords] [int] NULL,[numSSNDetailRecords] [int] NULL,[numCOARecords] [int] NULL,[numCOADescriptionRecords] [int] NULL,[txtDLResultCode] [varchar](10) NULL,[catDateOfBirthMatch] [varchar](9) NULL,[txtSSNIssueState] [varchar](5) NULL,[flgSSNDeceased] [int] NULL,[txtSSNFormat] [varchar](10) NULL,[numSSNIssueStartRange] [int] NULL,[catSSNIssueStartRange] [varchar](7) NULL,[numSSNIssueEndRange] [int] NULL,[txtSSNIssueResultCode] [varchar](10) NULL,[catOFACValidationResult] [varchar](7) NULL,[sID] [int] NULL,[sUserID] [int] NULL,[numALL001] [int] NULL,[numALL021] [int] NULL,[numALL026] [int] NULL,[numALL051] [int] NULL,[numALL052] [int] NULL,[numALL062] [int] NULL,[numALL064] [int] NULL,[numALL081] [int] NULL,[numALL084] [int] NULL,[numALL141] [int] NULL,[numALL142] [int] NULL,[numALL201] [int] NULL,[numALL207] [int] NULL,[numALL208] [int] NULL,[numALL701] [int] NULL,[numALL806] [int] NULL,[numALL901] [int] NULL,[numBAC026] [int] NULL,[numBAC401] [int] NULL,[numBAC403] [int] NULL,[numILN201] [int] NULL,[numREF001] [int] NULL,[numREP002] [int] NULL,[numREV201] [int] NULL,[numREV023] [int] NULL,[numILN005] [int] NULL,[numILN108] [int] NULL, numILN002 [int] NULL, numALL803[int] NULL, numALE007 [int] NULL, numILN007 [int] NULL, numALL002 [int] NULL, numRTR751 [int] NULL, [dID] [int] NULL,[dUserID] [int] NULL,[numDistinctDups] [int] NULL,[flgDupExistence] [int] NULL,[flgDupTypeCookie] [int] NULL,[flgDupTypePhone] [int] NULL,[flgDupTypeAddress] [int] NULL,[flgDupTypeBank] [int] NULL,[numMaxDupDateOfBirthDiff] [int] NULL,[numAvgDupDateOfBirthDiff] [int] NULL,[numMinDupCreditScore] [int] NULL,[numAvgDupCreditScore] [int] NULL,[flgMaxDupUserStatusNotActive] [int] NULL,[flgMaxDupUserStatusSuspendedTerminated] [int] NULL,[flgMaxDupCreditScoreLow] [int] NULL,[flgMaxDupCreditScoreMissing] [int] NULL,[flgMaxDupLoanChargeoffDefault] [int] NULL,[flgMaxDupLoanDPD0Greater] [int] NULL,[flgMaxDupLoanDPD30Greater] [int] NULL,[flgMaxDupLoanDPD60Greater] [int] NULL,[flgMaxDupLoanDPD90Greater] [int] NULL,[flgMaxDupLoanDPD120Greater] [int] NULL,[flgMaxDupLoanActive] [int] NULL,[flgMaxDupLoanPaid] [int] NULL,[flgMaxDupNoLoan] [int] NULL,[flgMaxDupRecentLoan] [int] NULL,[flgMaxDupRecentListing] [int] NULL,[flgMaxDupManualPayment] [int] NULL,[flgMaxDupHighPayment] [int] NULL,[flgMaxDupLatePayment] [int] NULL,[flgMaxDupFailedPayment] [int] NULL,[flgMaxDupBusinessAddress] [int] NULL,[flgMaxDupSameName] [int] NULL,[flgMaxDupSameFirstName] [int] NULL,[flgMaxDupSameLastName] [int] NULL,[flgMaxDupHighAgeDiff] [int] NULL,[uID] [int] NULL,[uUserID] [int] NULL,[numUserListingAmount] [money] NULL,[flgUserMultiBankAccounts] [int] NULL,[flgUserMultiBankHolders] [int] NULL,[flgUserPrePaidBankAccount] [int] NULL,[flgUserSuspicion] [int] NULL,[ListingTitle] [varchar](50) NULL,[flgUserTitleConsolidation] [int] NULL,[flgUserTitleDebt] [int] NULL,[numUser30DBankChange] [int] NULL,[numUser30DBankCount] [int] NULL,[numUserAgeYears] [int] NULL,[numUserBankCount] [int] NULL,[numUserEmploymentLengthMonths] [int] NULL,[numUserFailLogins] [int] NULL,[numUserLogins] [int] NULL,[numUserPercentFailLogins] [numeric](33, 17) NULL,[numUserRegistrationAgeMonths] [int] NULL,[numUserSuccessLogins] [int] NULL,[numUserTitleLength] [int] NULL,[numPrevCancellations] [int] NULL,[numPrevOriginations] [int] NULL,[numPrevWithdrawals] [int] NULL,[flgFraudScreen] [int] NOT NULL,[FSSUM] [int] NULL,[flgChannelDM] [int] NOT NULL, [channel] [varchar] (5), [AggregateBalanceCreditRatioOnAllTrades6Months] [decimal](15, 5) NULL,[OpenPaidClosedInactiveInstallTrades] [int] NULL, [catDecisionGroup] [varchar](3) NULL, [probScore] [numeric] (20,6) null, [probDollars] [numeric] (20,6) null, [AdvElement1Variable] [varchar](40) null, [AdvElement2Variable] [varchar](40) null, [flgSuspicionCriterion] [int] NOT NULL, [1401] [int] NOT NULL,[1601] [int] NOT NULL,[1801] [int] NOT NULL,[2001] [int] NOT NULL,[2201] [int] NOT NULL,[3101] [int] NOT NULL,[3501] [int] NOT NULL, [IncomeVerStrategyCode] [varchar](20) NULL, [IncomeVerTreatmentCode] [varchar](70) NULL, [IncVerRandNum1to100] [int] NULL, [PVFScoreVersion] [varchar](15) NULL, [PVFScoreValue] [numeric](20,10) NULL, [3601] [int] NOT NULL,[3801] [int] NOT NULL,[4401] [int] NOT NULL
  65. )
  66.  
  67. DECLARE @InitialVerificationStaging TABLE (
  68. [ID] [int] NOT NULL,[UserID] [int] NOT NULL,[PendingStartDate] [datetime] NULL,[MissingData] [int] NOT NULL,[eID] [int] NULL,[eUserID] [int] NULL,[numAgeOfAuthenticationMonths] [int] NULL,[numAgeOfAuthenticationYears] [int] NULL,[datAuthenticationCreationDate] [datetime] NULL,[numConsumerTotal] [int] NULL,[numCustomerOnlyTotal] [int] NULL,[catSSNMatch] [varchar](9) NULL,[catYOBMatch] [varchar](9) NULL,[catDLMatch] [varchar](9) NULL,[catDLSMatch] [varchar](9) NULL,[numAuthenticationScore] [int] NULL,[catAuthenticationScore] [varchar](8) NULL,[ASScoreFactor1] [int] NULL,[ASScoreFactor2] [int] NULL,[ASScoreFactor3] [int] NULL,[ASScoreFactor4] [int] NULL,[numScoreFactorCount] [int] NULL,[flgBlockedFile] [int] NULL,[flgVictimStatement] [int] NULL,[flgConsumerStatement] [int] NULL,[flgLargeFile] [int] NULL,[catAlertMessage] [varchar](8) NULL,[flgFraudAlert] [int] NULL,[flgSecurityAlert] [int] NULL,[catIPCountryMatch] [varchar](10) NULL,[catIPStateMatch] [varchar](12) NULL,[txtIPState] [varchar](50) NULL,[catIPCityMatch] [varchar](12) NULL,[txtIPCity] [varchar](50) NULL,[catIPZipMatch] [varchar](12) NULL,[txtIPZip] [varchar](5) NULL,[catAcceptReferCode] [varchar](6) NULL,[catOutputTypeCode] [varchar](10) NULL,[numInWalletScore] [int] NULL,[catInWalletScore] [varchar](8) NULL,[flgFS01] [int] NULL,[flgFS02] [int] NULL,[flgFS03] [int] NULL,[flgFS04] [int] NULL,[flgFS05] [int] NULL,[flgFS06] [int] NULL,[flgFS07] [int] NULL,[flgFS08] [int] NULL,[flgFS09] [int] NULL,[flgFS10] [int] NULL,[flgFS11] [int] NULL,[flgFS12] [int] NULL,[flgFS13] [int] NULL,[flgFS14] [int] NULL,[flgFS15] [int] NULL,[flgFS16] [int] NULL,[flgFS17] [int] NULL,[flgFS18] [int] NULL,[flgFS19] [int] NULL,[flgFS20] [int] NULL,[flgFS21] [int] NULL,[flgFS22] [int] NULL,[flgFS25] [int] NULL,[flgFS26] [int] NULL,[flgFS27] [int] NULL,[numCheckpointScore] [int] NULL,[catCheckpointScore] [varchar](8) NULL,[numModelScore] [int] NULL,[catModelScore] [varchar](8) NULL,[catLevelOneDecisionCode] [varchar](6) NULL,[numPrimaryResult] [varchar](10) NULL,[txtAddressVerificationResult] [varchar](10) NULL,[catAddressUnitMismatchResult] [varchar](11) NULL,[txtPhoneVerificationResult] [varchar](10) NULL,[txtPhoneUnitMismatchResult] [varchar](10) NULL,[txtAddressTypeResult] [varchar](10) NULL,[catAddressHighRiskResult] [varchar](7) NULL,[catPhoneHighRiskResult] [varchar](7) NULL,[txtSocialSecurityNumberResult] [varchar](10) NULL,[numResidentialAddressDetailRecords] [int] NULL,[numBusinessAddressDetailRecords] [int] NULL,[numAddressHighRiskDetailRecords] [int] NULL,[numAddressHighRiskDescriptionRecords] [int] NULL,[numResidentialPhoneDetailRecords] [int] NULL,[numBusinessPhoneDetailRecords] [int] NULL,[numPhoneHighRiskDetailRecords] [int] NULL,[numPhoneHighRiskDescriptionRecords] [int] NULL,[numSSNDetailRecords] [int] NULL,[numCOARecords] [int] NULL,[numCOADescriptionRecords] [int] NULL,[txtDLResultCode] [varchar](10) NULL,[catDateOfBirthMatch] [varchar](9) NULL,[txtSSNIssueState] [varchar](5) NULL,[flgSSNDeceased] [int] NULL,[txtSSNFormat] [varchar](10) NULL,[numSSNIssueStartRange] [int] NULL,[catSSNIssueStartRange] [varchar](7) NULL,[numSSNIssueEndRange] [int] NULL,[txtSSNIssueResultCode] [varchar](10) NULL,[catOFACValidationResult] [varchar](7) NULL,[sID] [int] NULL,[sUserID] [int] NULL,[numALL001] [int] NULL,[numALL021] [int] NULL,[numALL026] [int] NULL,[numALL051] [int] NULL,[numALL052] [int] NULL,[numALL062] [int] NULL,[numALL064] [int] NULL,[numALL081] [int] NULL,[numALL084] [int] NULL,[numALL141] [int] NULL,[numALL142] [int] NULL,[numALL201] [int] NULL,[numALL207] [int] NULL,[numALL208] [int] NULL,[numALL701] [int] NULL,[numALL806] [int] NULL,[numALL901] [int] NULL,[numBAC026] [int] NULL,[numBAC401] [int] NULL,[numBAC403] [int] NULL,[numILN201] [int] NULL,[numREF001] [int] NULL,[numREP002] [int] NULL,[numREV201] [int] NULL,[numREV023] [int] NULL,[numILN005] [int] NULL,[numILN108] [int] NULL, numILN002 [int] NULL, numALL803[int] NULL, numALE007 [int] NULL, numILN007 [int] NULL, numALL002 [int] NULL, numRTR751 [int] NULL, [dID] [int] NULL,[dUserID] [int] NULL,[numDistinctDups] [int] NULL,[flgDupExistence] [int] NULL,[flgDupTypeCookie] [int] NULL,[flgDupTypePhone] [int] NULL,[flgDupTypeAddress] [int] NULL,[flgDupTypeBank] [int] NULL,[numMaxDupDateOfBirthDiff] [int] NULL,[numAvgDupDateOfBirthDiff] [int] NULL,[numMinDupCreditScore] [int] NULL,[numAvgDupCreditScore] [int] NULL,[flgMaxDupUserStatusNotActive] [int] NULL,[flgMaxDupUserStatusSuspendedTerminated] [int] NULL,[flgMaxDupCreditScoreLow] [int] NULL,[flgMaxDupCreditScoreMissing] [int] NULL,[flgMaxDupLoanChargeoffDefault] [int] NULL,[flgMaxDupLoanDPD0Greater] [int] NULL,[flgMaxDupLoanDPD30Greater] [int] NULL,[flgMaxDupLoanDPD60Greater] [int] NULL,[flgMaxDupLoanDPD90Greater] [int] NULL,[flgMaxDupLoanDPD120Greater] [int] NULL,[flgMaxDupLoanActive] [int] NULL,[flgMaxDupLoanPaid] [int] NULL,[flgMaxDupNoLoan] [int] NULL,[flgMaxDupRecentLoan] [int] NULL,[flgMaxDupRecentListing] [int] NULL,[flgMaxDupManualPayment] [int] NULL,[flgMaxDupHighPayment] [int] NULL,[flgMaxDupLatePayment] [int] NULL,[flgMaxDupFailedPayment] [int] NULL,[flgMaxDupBusinessAddress] [int] NULL,[flgMaxDupSameName] [int] NULL,[flgMaxDupSameFirstName] [int] NULL,[flgMaxDupSameLastName] [int] NULL,[flgMaxDupHighAgeDiff] [int] NULL,[uID] [int] NULL,[uUserID] [int] NULL,[numUserListingAmount] [money] NULL,[flgUserMultiBankAccounts] [int] NULL,[flgUserMultiBankHolders] [int] NULL,[flgUserPrePaidBankAccount] [int] NULL,[flgUserSuspicion] [int] NULL,[ListingTitle] [varchar](50) NULL,[flgUserTitleConsolidation] [int] NULL,[flgUserTitleDebt] [int] NULL,[numUser30DBankChange] [int] NULL,[numUser30DBankCount] [int] NULL,[numUserAgeYears] [int] NULL,[numUserBankCount] [int] NULL,[numUserEmploymentLengthMonths] [int] NULL,[numUserFailLogins] [int] NULL,[numUserLogins] [int] NULL,[numUserPercentFailLogins] [numeric](33, 17) NULL,[numUserRegistrationAgeMonths] [int] NULL,[numUserSuccessLogins] [int] NULL,[numUserTitleLength] [int] NULL,[numPrevCancellations] [int] NULL,[numPrevOriginations] [int] NULL,[numPrevWithdrawals] [int] NULL,[flgFraudScreen] [int] NOT NULL,[FSSUM] [int] NULL,[flgChannelDM] [int] NOT NULL, [channel] [varchar] (5), [AggregateBalanceCreditRatioOnAllTrades6Months] [decimal](15, 5) NULL,[OpenPaidClosedInactiveInstallTrades] [int] NULL, [flgSuspicionCriterion] [int] NOT NULL, [flgFraudScreenEmail] [int] NULL, [UserCreditProfileID] [int] NOT NULL, [InitialListingAmount] [money] NOT NULL, [Income] [decimal](18,0) NULL, [HasPriorLoanFlag] [smallint] NOT NULL, [OccupationName] [varchar](50) NULL, [EmploymentDescription] [varchar](32) NULL, [MonthsEmployed] [int] NULL, [DTIChg20k36MoLoan] [numeric](15,6) NULL, [DTIwoProspLoan] [numeric](15,6) NULL, MonthlyDebt [money] NULL, [ALL801] [int] NULL, [ILN701] [int] NULL, [REV702] [int] NULL, [ListCancelLast30] [int] NULL, [MRProspModIncomeDate] [datetime] NULL, [IncomeLELastProspMod] [int] NULL, [EmpStatusChgFlag] [int] NULL, [UserLoanHistorySnapshotID] [int] NULL, [P1401] [int] NULL, [P1601] [int] NULL, [P1801] [int] NULL, [flgFraudVictimXML] [int] NULL, [flgSecurityAlertXML] [int] NULL, [flgActiveDutyXML] [int] NULL, [BovStatusID] [int] NULL, [MilStatusID] [int] NULL, [AdrStatusID] [int] NULL
  69. )
  70.  
  71. DECLARE @PendingListings TABLE (
  72. [ID] [int] NOT NULL,[UserID] [int] NOT NULL,[PendingStartDate] [datetime] NULL,[MissingData] [int] NOT NULL
  73. )
  74.  
  75. DECLARE @Stagg TABLE (
  76. [sID] [int] NOT NULL,[sUserID] [int] NOT NULL,[numALL001] [int] NULL,[numALL021] [int] NULL,[numALL026] [int] NULL,[numALL051] [int] NULL,[numALL052] [int] NULL,[numALL062] [int] NULL,[numALL064] [int] NULL,[numALL081] [int] NULL,[numALL084] [int] NULL,[numALL141] [int] NULL,[numALL142] [int] NULL,[numALL201] [int] NULL,[numALL207] [int] NULL,[numALL208] [int] NULL,[numALL701] [int] NULL,[numALL806] [int] NULL,[numALL901] [int] NULL,[numBAC026] [int] NULL,[numBAC401] [int] NULL,[numBAC403] [int] NULL,[numILN201] [int] NULL,[numREF001] [int] NULL,[numREP002] [int] NULL,[numREV201] [int] NULL,[numREV023] [int] NULL,[numILN005] [int] NULL,[numILN108] [int] NULL, numILN002 [int] NULL, numALL803[int] NULL, numALE007 [int] NULL, numILN007 [int] NULL, numALL002 [int] NULL, numRTR751 [int] NULL
  77. )
  78.  
  79. DECLARE @User TABLE (
  80. [uID] [int] NOT NULL,[uUserID] [int] NOT NULL,[numUserListingAmount] [money] NULL,[flgUserMultiBankAccounts] [int] NOT NULL,[flgUserMultiBankHolders] [int] NOT NULL,[flgUserPrePaidBankAccount] [int] NOT NULL,[flgUserSuspicion] [int] NOT NULL,[ListingTitle] [varchar](50) NOT NULL,[flgUserTitleConsolidation] [int] NOT NULL,[flgUserTitleDebt] [int] NOT NULL,[numUser30DBankChange] [int] NOT NULL,[numUser30DBankCount] [int] NOT NULL,[numUserAgeYears] [int] NOT NULL,[numUserBankCount] [int] NOT NULL,[numUserEmploymentLengthMonths] [int] NOT NULL,[numUserFailLogins] [int] NOT NULL,[numUserLogins] [int] NOT NULL,[numUserPercentFailLogins] [numeric](33, 17) NOT NULL,[numUserRegistrationAgeMonths] [int] NOT NULL,[numUserSuccessLogins] [int] NOT NULL,[numUserTitleLength] [int] NOT NULL,[numPrevCancellations] [int] NOT NULL,[numPrevOriginations] [int] NOT NULL,[numPrevWithdrawals] [int] NOT NULL
  81. )
  82.  
  83. DECLARE @IncVerIn TABLE (
  84. [ListingID] [int] NOT NULL, [UserID] [int] NOT NULL, [UserCreditProfileID] [int] NOT NULL, [InitialListingAmount] [money] NOT NULL, [Income] [decimal](18,0) NULL, [HasPriorLoanFlag] [smallint] NOT NULL, [OccupationName] [varchar](50) NULL, [EmploymentDescription] [varchar](32) NULL, [MonthsEmployed] [int] NULL, [DTIChg20k36MoLoan] [numeric](15,6) NULL, [DTIwoProspLoan] [numeric](15,6) NULL, MonthlyDebt [money] NULL, [ALL801] [int] NULL, [ILN701] [int] NULL, [REV702] [int] NULL
  85. )
  86.  
  87. DECLARE @ListCancelLast30 TABLE (
  88. [ListingID] [int] NOT NULL, [ListCancelLast30] [int] NULL
  89. )
  90.  
  91. DECLARE @LastProspIncUpdateDate TABLE (
  92. [UserID] [int] NOT NULL, [ListingID] [int] NOT NULL, [MRProspModIncomeDate] [datetime] NULL
  93. )
  94.  
  95. DECLARE @IncModifyGroup TABLE (
  96. [ListingID] [int] NULL, [IncomeLELastProspMod] [int] NULL, [EmpStatusChgFlag] [int] NULL
  97. )
  98.  
  99. DECLARE @UserDupes TABLE (UserID int,DupUserID int,UserLoginCount int,DupUserLoginCount int)
  100.  
  101. DECLARE @IDVAggregateLogins table(UserID int, numUserLogins int, numUserSuccessLogins INT, numUserFailLogins INT, numUserPercentFailLogins FLOAT)
  102.  
  103. --1. get pending listings
  104. PRINT 'AllPendingListings: ' + CONVERT(VARCHAR(25),GETDATE() ,121)
  105. INSERT INTO @AllPendingListings
  106. select top 1 li.ID, li.UserID, ls.CreatedDate 'PendingStartDate'
  107. from c1.dbo.listings li (nolock)
  108. left join c1.dbo.ListingStatus ls (nolock) on li.ID = ls.ListingID and ls.ListingStatusTypeID = 1 and ls.VersionEndDate is null and ls.VersionValidBit = 1
  109. where li.ID = @ListingID
  110.  
  111. --2 removed because it was deleted out on delivery
  112.  
  113. --3. filter out listings missing level 3 response
  114. PRINT 'PendingListings: ' + CONVERT(VARCHAR(25),GETDATE() ,121)
  115. INSERT INTO @PendingListings
  116. select li.*,
  117. case
  118. when ucp.ExperianDocumentID in (select ExperianDocumentID from c1.dbo.ExperianCreditProfileResponse)
  119. and li.UserID in (select UserID from c1.dbo.Level3_Inwallet_Response)
  120. then 0 else 1 end as 'MissingData'
  121. from @AllPendingListings li
  122. left join c1.dbo.Listings l (nolock) on l.ID = li.ID
  123. left join c1.dbo.UserCreditProfiles ucp (nolock) on l.UserCreditProfileID = ucp.UserCreditProfileID
  124. --4. pull experian variables
  125. --4a. prepare staging table
  126. PRINT 'ExperianStaging: ' + CONVERT(VARCHAR(25),GETDATE() ,121)
  127. INSERT INTO @ExperianStaging
  128. select ListingID = pl.ID, l3rp.*
  129. from @PendingListings pl
  130. left join c1.dbo.Listings li (nolock) on li.id = pl.ID
  131. left join c1.dbo.Level3_Inwallet_Response L3rp (nolock) on L3rp.userid = li.userid
  132. from c1.dbo.Level3_Inwallet_Response (nolock)
  133. where userid = l3rp.userid)
  134.  
  135. --4b. create variable table
  136. PRINT 'Experian: ' + CONVERT(VARCHAR(25),GETDATE() ,121)
  137. INSERT INTO @Experian
  138. select eID = pl.ID, eUserID = pl.UserID, numAgeOfAuthenticationMonths = datediff(mm,l3rp.CreationDate,li.StartTime),
  139. numAgeOfAuthenticationYears = datediff(yy,l3rp.CreationDate,li.StartTime),
  140. datAuthenticationCreationDate = l3rp.CreationDate, numConsumerTotal = l3rp.ConsumerTotal,
  141. numCustomerOnlyTotal = l3rp.CustomerOnlyTotal,
  142. catSSNMatch = case l3rp.SSNMatch
  143. when 1 then 'match'
  144. when 2 then 'partial'
  145. when 3 then 'nomatch'
  146. when 4 then 'notonfile' end,
  147. catYOBMatch = case l3rp.YOBMatch
  148. when 1 then 'match'
  149. when 2 then 'partial'
  150. when 3 then 'nomatch'
  151. when 4 then 'notonfile' end,
  152. catDLMatch = case l3rp.DLMatch
  153. when 1 then 'match'
  154. when 3 then 'nomatch'
  155. when 4 then 'notonfile' else 'missing' end,
  156. catDLSMatch = case l3rp.DLSMatch
  157. when 1 then 'match'
  158. when 3 then 'nomatch'
  159. when 4 then 'notonfile' else 'missing' end,
  160. numAuthenticationScore = case
  161. when l3rp.AuthenticationScore between 1 and 999 then l3rp.AuthenticationScore
  162. else (select avg(AuthenticationScore)
  163. from @ExperianStaging
  164. where AuthenticationScore between 1 and 999) end,
  165. catAuthenticationScore = case
  166. when l3rp.AuthenticationScore = 9001 then 'deceased'
  167. when l3rp.AuthenticationScore = 9012 then 'alert'
  168. when l3rp.AuthenticationScore between 1 and 999 then 'valid' else 'other' end
  169. ,l3rp.ASScoreFactor1
  170. ,l3rp.ASScoreFactor2
  171. ,l3rp.ASScoreFactor3
  172. ,l3rp.ASScoreFactor4
  173. ,numScoreFactorCount = case when l3rp.ASScoreFactor1 <> 0 then 1 else 0 end
  174. + case when l3rp.ASScoreFactor2 <> 0 then 1 else 0 end
  175. + case when l3rp.ASScoreFactor3 <> 0 then 1 else 0 end
  176. + case when l3rp.ASScoreFactor4 <> 0 then 1 else 0 end
  177. ,flgBlockedFile = case when l3rp.BlockedFileFlag = 'Y' then 1 else 0 end
  178. ,flgVictimStatement = case when l3rp.VictimStmtFlag = 'Y' then 1 else 0 end
  179. ,flgConsumerStatement = case when l3rp.ConsumerStatementFlag = 'Y' then 1 else 0 end
  180. ,flgLargeFile = case when l3rp.LargeFileFlag = 'Y' then 1 else 0 end
  181. ,catAlertMessage = case when left(l3rp.VictimStatementMessage,6) in ('ID FRA','FRAUD ','MY IDE') then 'fraud'
  182. when left(l3rp.VictimStatementMessage,6) IN ('ID SEC', 'ACTIVE', 'FRAUDU') then 'security'
  183. else 'none' end
  184. ,flgFraudAlert = case when left(l3rp.VictimStatementMessage,6) in ('ID FRA','FRAUD ','MY IDE') then 1 else 0 end
  185. ,flgSecurityAlert = case when left(l3rp.VictimStatementMessage,6) IN ('ID SEC', 'ACTIVE', 'FRAUDU') then 1 else 0 end
  186. ,catIPCountryMatch = case l3rp.IPCountryMatch
  187. when 1 then 'matchUS'
  188. when 2 then 'matchnotUS'
  189. when 3 then 'nomatch'
  190. when 4 then 'ipnotavail'
  191. when 5 then 'private'
  192. when 6 then 'unknown'
  193. when null then 'missing'
  194. else 'check' end
  195. ,catIPStateMatch = case l3rp.IPStateMatch
  196. when 1 then 'matchinput'
  197. when 2 then 'nomatchinput'
  198. when 3 then 'nomatch'
  199. when 4 then 'ipnotavail'
  200. when 5 then 'statenotapp'
  201. when null then 'missing'
  202. else 'check' end
  203. ,txtIPState = isnull(case when l3rp.IPState in ('?','***') then null else l3rp.IPState end,'missing')
  204. ,catIPCityMatch = case l3rp.IPCityMatch
  205. when 1 then 'matchinput'
  206. when 2 then 'nomatchinput'
  207. when 3 then 'nomatch'
  208. when 4 then 'ipnotavail'
  209. when null then 'missing'
  210. else 'check' end
  211. ,txtIPCity = isnull(case when l3rp.IPCity = '?' then null else l3rp.IPCity end,'missing')
  212. ,catIPZipMatch = case l3rp.IPZipMatch
  213. when 1 then 'matchinput'
  214. when 2 then 'nomatchinput'
  215. when 3 then 'nomatch'
  216. when 4 then 'ipnotavail'
  217. when 5 then 'zipnotapp'
  218. when null then 'missing'
  219. else 'check' end
  220. ,txtIPZip = isnull(right('00000'+cast(l3rp.IPZip as varchar),5),'00000')
  221. ,catAcceptReferCode = case l3rp.AcceptReferCode
  222. when 'A' then 'accept'
  223. when 'R' then 'refer'
  224. when 'D' then 'deny' end
  225. ,catOutputTypeCode = l3rp.OutputTypeCode
  226. ,numInWalletScore = case when l3rp.InWalletScore between 1 and 999 then l3rp.InWalletScore
  227. else (select avg(InWalletScore) from @ExperianStaging where InWalletScore between 1 and 999) end
  228. ,catInWalletScore = case when l3rp.InWalletScore = 9001 then 'deceased'
  229. when l3rp.InWalletScore = 9012 then 'alert'
  230. when l3rp.InWalletScore between 1 and 999 then 'valid' else 'other' end
  231. ,flgFS01 = case when l3rp.FS01 = 'Y' then 1 else 0 end
  232. ,flgFS02 = case when l3rp.FS02 = 'Y' then 1 else 0 end
  233. ,flgFS03 = case when l3rp.FS03 = 'Y' then 1 else 0 end
  234. ,flgFS04 = case when l3rp.FS04 = 'Y' then 1 else 0 end
  235. ,flgFS05 = case when l3rp.FS05 = 'Y' then 1 else 0 end
  236. ,flgFS06 = case when l3rp.FS06 = 'Y' then 1 else 0 end
  237. ,flgFS07 = case when l3rp.FS07 = 'Y' then 1 else 0 end
  238. ,flgFS08 = case when l3rp.FS08 = 'Y' then 1 else 0 end
  239. ,flgFS09 = case when l3rp.FS09 = 'Y' then 1 else 0 end
  240. ,flgFS10 = case when l3rp.FS10 = 'Y' then 1 else 0 end
  241. ,flgFS11 = case when l3rp.FS11 = 'Y' then 1 else 0 end
  242. ,flgFS12 = case when l3rp.FS12 = 'Y' then 1 else 0 end
  243. ,flgFS13 = case when l3rp.FS13 = 'Y' then 1 else 0 end
  244. ,flgFS14 = case when l3rp.FS14 = 'Y' then 1 else 0 end
  245. ,flgFS15 = case when l3rp.FS15 = 'Y' then 1 else 0 end
  246. ,flgFS16 = case when l3rp.FS16 = 'Y' then 1 else 0 end
  247. ,flgFS17 = case when l3rp.FS17 = 'Y' then 1 else 0 end
  248. ,flgFS18 = case when l3rp.FS18 = 'Y' then 1 else 0 end
  249. ,flgFS19 = case when l3rp.FS19 = 'Y' then 1 else 0 end
  250. ,flgFS20 = case when l3rp.FS20 = 'Y' then 1 else 0 end
  251. ,flgFS21 = case when l3rp.FS21 = 'Y' then 1 else 0 end
  252. ,flgFS22 = case when l3rp.FS22 = 'Y' then 1 else 0 end
  253. ,flgFS25 = case when l3rp.FS25 = 'Y' then 1 else 0 end
  254. ,flgFS26 = case when l3rp.FS26 = 'Y' then 1 else 0 end
  255. ,flgFS27 = case when l3rp.FS27 = 'Y' then 1 else 0 end
  256. ,numCheckpointScore = case when l3rp.CheckpointScore between 1 and 999 then l3rp.CheckpointScore
  257. else (select avg(CheckpointScore) from @ExperianStaging where CheckpointScore between 1 and 999) end
  258. ,catCheckpointScore = case when l3rp.CheckpointScore = 9001 then 'deceased'
  259. when l3rp.CheckpointScore = 9012 then 'alert'
  260. when l3rp.CheckpointScore between 1 and 999 then 'valid' else 'other' end
  261. ,numModelScore = case when l3rp.ModelScore between 1 and 999 then l3rp.ModelScore
  262. else (select avg(ModelScore) from @ExperianStaging where ModelScore between 1 and 999) end
  263. ,catModelScore = case when l3rp.ModelScore = 9001 then 'deceased'
  264. when l3rp.ModelScore = 9012 then 'alert'
  265. when l3rp.ModelScore between 1 and 999 then 'valid' else 'other' end
  266. ,catLevelOneDecisionCode = case l3rp.LevelOneDecisionCode
  267. when 'A' then 'accept'
  268. when 'R' then 'refer'
  269. when 'D' then 'deny' end
  270. ,numPrimaryResult = l3rp.PrimaryResult
  271. ,txtAddressVerificationResult = l3rp.AddressVerificationResult
  272. ,catAddressUnitMismatchResult = case l3rp.AddressUnitMismatchResult
  273. when 'MU' then 'missingunit'
  274. when 'EU' then 'extraunit'
  275. when 'WU' then 'wrongunit'
  276. when '' then 'missing' end
  277. ,txtPhoneVerificationResult = l3rp.PhoneVerificationResult
  278. ,txtPhoneUnitMismatchResult = isnull(l3rp.PhoneUnitMismatchResult,'missing')
  279. ,txtAddressTypeResult = l3rp.AddressTypeResult
  280. ,catAddressHighRiskResult = case l3rp.AddressHighRiskResult
  281. when 'N' then 'none'
  282. when 'YA' then 'found'
  283. when 'NP' then 'notest'
  284. else 'missing' end
  285. ,catPhoneHighRiskResult = case l3rp.PhoneHighRiskResult
  286. when 'N' then 'none'
  287. when 'YP' then 'found'
  288. when 'NP' then 'notest'
  289. else 'missing' end
  290. ,txtSocialSecurityNumberResult = l3rp.SocialSecurityNumberResult
  291. ,numResidentialAddressDetailRecords = l3rp.NumResidentialAddressDetailRecords
  292. ,numBusinessAddressDetailRecords = l3rp.NumBusinessAddressDetailRecords
  293. ,numAddressHighRiskDetailRecords = l3rp.NumAddressHighRiskDetailRecords
  294. ,numAddressHighRiskDescriptionRecords = l3rp.NumAddressHighRiskDescriptionRecords
  295. ,numResidentialPhoneDetailRecords = l3rp.NumResidentialPhoneDetailRecords
  296. ,numBusinessPhoneDetailRecords = l3rp.NumBusinessPhoneDetailRecords
  297. ,numPhoneHighRiskDetailRecords = l3rp.NumPhoneHighRiskDetailRecords
  298. ,numPhoneHighRiskDescriptionRecords = l3rp.NumPhoneHighRiskDescriptionRecords
  299. ,numSSNDetailRecords = l3rp.NumSSNDetailRecords
  300. ,numCOARecords = l3rp.NumCOARecords
  301. ,numCOADescriptionRecords = l3rp.NumCOADescriptionRecords
  302. ,txtDLResultCode = l3rp.DLResultCode
  303. ,catDateOfBirthMatch = case l3rp.DateOfBirthMatch
  304. when 1 then 'match'
  305. when 2 then 'partial'
  306. when 3 then 'nomatch'
  307. when 4 then 'nofile'
  308. when 5 then 'ssnnofile' end
  309. ,txtSSNIssueState = l3rp.SSNIssueState
  310. ,flgSSNDeceased = case when l3rp.SSNDeceased = 'Y' then 1 else 0 end
  311. ,txtSSNFormat = l3rp.SSNFormat
  312. ,numSSNIssueStartRange = case when l3rp.SSNIssueStartRange <> 0 then l3rp.SSNIssueStartRange
  313. else (select avg(SSNIssueStartRange) from @ExperianStaging where SSNIssueStartRange <> 0) end
  314. ,catSSNIssueStartRange = case when l3rp.SSNIssueStartRange <> 0 then 'valid' else 'unknown' end
  315. ,numSSNIssueEndRange = case when l3rp.SSNIssueEndRange <> 0 then l3rp.SSNIssueEndRange
  316. else (select avg(SSNIssueEndRange) from @ExperianStaging where SSNIssueEndRange <> 0) end
  317. ,txtSSNIssueResultCode = l3rp.SSNIssueResultCode
  318. ,catOFACValidationResult = case l3rp.OFACValidationResult
  319. when 1 then 'nomatch'
  320. when 2 then 'full'
  321. when 14 then 'surname' end
  322. --into @Experian --drop table @ExperianFlags
  323. from @PendingListings pl
  324. left join c1.dbo.Listings li (nolock) on li.ID= pl.ID
  325. left join @ExperianStaging L3rp on l3rp.ListingID = pl.ID
  326. --select * from @Experian
  327.  
  328.  
  329. --4c. create stagg table
  330. PRINT 'Stagg: ' + CONVERT(VARCHAR(25),GETDATE() ,121)
  331. INSERT INTO @Stagg
  332. select [sID] = ID
  333. ,sUserID = UserID
  334. ,numALL001 = ALL001
  335. ,numALL021 = ALL021
  336. ,numALL026 = ALL026
  337. ,numALL051 = ALL051
  338. ,numALL052 = ALL052
  339. ,numALL062 = ALL062
  340. ,numALL064 = ALL064
  341. ,numALL081 = ALL081
  342. ,numALL084 = ALL084
  343. ,numALL141 = ALL141
  344. ,numALL142 = ALL142
  345. ,numALL201 = ALL201
  346. ,numALL207 = ALL207
  347. ,numALL208 = ALL208
  348. ,numALL701 = ALL701
  349. ,numALL806 = ALL806
  350. ,numALL901 = ALL901
  351. ,numBAC026 = BAC026
  352. ,numBAC401 = BAC401
  353. ,numBAC403 = BAC403
  354. ,numILN201 = ILN201
  355. ,numREF001 = REF001
  356. ,numREP002 = REP002
  357. ,numREV201 = REV201
  358. ,numREV023 = REV023
  359. ,numILN005 = ILN005
  360. ,numILN108 = ILN108
  361. ,numILN002 = ILN002 -- New model
  362. ,numALL803 = ALL803 -- New model
  363. ,numALE007 = ALE007 -- New model
  364. ,numILN007 = ILN007 -- New model
  365. ,numALL002 = ALL002 -- New model
  366. ,numRTR751 = RTR751 -- New model
  367. --into @Stagg
  368. from (
  369. select up.ID, up.UserID, s.AttributeID, cast(s.AttributeValue as int) 'AttributeValue'
  370. from @PendingListings up
  371. left join c1.dbo.Listings li (nolock) on up.ID = li.ID --change to c1
  372. left join c1.dbo.UserCreditProfiles ucp (nolock) on li.UserCreditProfileID = ucp.UserCreditProfileID
  373. left join c1.dbo.ExperianCreditProfileResponse ecpr (nolock) on ecpr.ExperianDocumentID = ucp.ExperianDocumentID
  374. left join c1.dbo.ExperianCreditProfileStaggData s (nolock) on s.ExperianCreditProfileResponseID = ecpr.ExperianCreditProfileResponseID
  375. ) as src
  376. pivot (sum(AttributeValue) for AttributeID in
  377. ([ALL001],[ALL021],[ALL026],[ALL051],[ALL052],[ALL062],[ALL064],[ALL081]
  378. ,[ALL084],[ALL141],[ALL142],[ALL201],[ALL207],[ALL208],[ALL701],[ALL806]
  379. ,[ALL901],[BAC026],[BAC401],[BAC403],[ILN201],[REF001],[REP002],[REV201]
  380. ,[REV023],[ILN005],[ILN108]
  381. ,[ILN002],[ALL803],[ALE007],[ILN007],[ALL002],[RTR751])) as pvt
  382. --select * from @Stagg
  383.  
  384.  
  385. PRINT 'DuplicateUsers: ' + CONVERT(VARCHAR(25),GETDATE() ,121)
  386. Insert into @UserDupes
  387. Exec SingleListingLPR_GetCookieDupes @userid
  388.  
  389. PRINT ' Cookie: ' + CONVERT(VARCHAR(25),GETDATE() ,121)
  390. INSERT INTO @DuplicateUsers
  391. select distinct li.ID, li.UserID, ud.DupUserID as 'DupUserID', 'Cookie' as 'DupType'
  392. from @PendingListings li
  393. left join @UserDupes ud on li.userID=ud.userID
  394.  
  395.  
  396.  
  397. PRINT ' Address: ' + CONVERT(VARCHAR(25),GETDATE() ,121)
  398. INSERT INTO @DuplicateUsers
  399. select distinct li.ID, li.UserID, u.id as 'DupUserID', 'Address' as 'DupType'
  400. from @PendingListings li
  401. left join c1.dbo.usertoaddress uta (nolock) on uta.userid = li.UserID
  402. left join c1.dbo.address a (nolock) on a.addressid = uta.addressid
  403. left join c1.dbo.usertoaddress utad (nolock) on utad.addressid = a.addressid
  404. left join c1.dbo.users u (nolock) on u.id = utad.userid
  405. and u.status <> 6 and u.id > 0 and u.id <> li.userid
  406. where u.id is not null
  407.  
  408.  
  409. PRINT ' Phone: ' + CONVERT(VARCHAR(25),GETDATE() ,121)
  410. INSERT INTO @DuplicateUsers
  411. select distinct li.ID, li.UserID, u.id as 'DupUserID', 'Phone' as 'DupType'
  412. from @PendingListings li
  413. left join
  414. (
  415. select distinct li.UserID, p.areacode as Areacode, p.phonenumber, upt.Name [PhoneType]
  416. from @PendingListings li
  417. join c1.dbo.usertophone utp (nolock) on utp.userid = li.userid
  418. left join circleone.dbo.userphonetype upt (nolock) on upt.UserPhoneTypeID = utp.UserPhoneTypeID
  419. join c1.dbo.phone p (nolock) on p.phoneid = utp.phoneid
  420. where p.phonenumber <> ''
  421. union
  422. select distinct li.UserID, p.areacode as Areacode, p.phonenumber, 'Work' [PhoneType]
  423. from @PendingListings li
  424. join c1.dbo.useremploymentdetail ued (nolock) on ued.userid = li.UserID
  425. join c1.dbo.phone p (nolock) on p.phoneid = ued.employerphoneid
  426. where p.phonenumber <> ''
  427. ) d on li.UserID = d.UserID
  428. left join c1.dbo.phone p (nolock) on p.areacode = d.Areacode and p.phonenumber = d.phonenumber
  429. left join
  430. (
  431. select distinct utp.userid, utp.phoneid, upt.Name [PhoneType], utp.VersionStartDate
  432. from c1.dbo.usertophone utp (nolock)
  433. left join circleone.dbo.userphonetype upt (nolock) on upt.UserPhoneTypeID = utp.UserPhoneTypeID
  434. union
  435. select distinct ued.userid, ued.employerphoneid [phoneid], 'Work' [PhoneType], ued.VersionStartDate
  436. from c1.dbo.useremploymentdetail ued (nolock)
  437. ) utp on utp.phoneid = p.phoneid and not (utp.PhoneType = 'Work' and d.PhoneType = 'Work')
  438. left join c1.dbo.users u (nolock) on u.id = utp.userid and u.status <> 6 and u.id > 0 and u.id <> li.userid
  439. where u.id is not null
  440.  
  441.  
  442. PRINT ' Bank: ' + CONVERT(VARCHAR(25),GETDATE() ,121)
  443. INSERT INTO @DuplicateUsers
  444. select distinct li.ID, li.UserID, u.id as 'DupUserID', 'Bank' as 'DupType'
  445. from @PendingListings li
  446. left join (
  447. select distinct li.UserID, circleone_dw.dbo.fndecrypt(ba.enaccountnumber) [acctnum], ba.routingnumber
  448. from @PendingListings li
  449. left join c1.dbo.accounts a (nolock) on a.userid = li.UserID
  450. left join c1.dbo.bankaccounts ba (nolock) on ba.accountid = a.id
  451. where ba.routingnumber is not null
  452. ) t on t.UserID = li.UserID
  453. cross apply circleone.dbo.fnaccountgetbynumber (t.acctnum, t.routingnumber) b
  454. left join c1.dbo.accounts ad on ad.id = b.acctid
  455. left join c1.dbo.users u on u.id = ad.userid
  456. and u.status <> 6 and u.id > 0 and u.id <> li.UserID
  457. where u.id is not null
  458.  
  459. --5b. prepare DOB, COD, PMT, ADR, IDV, EMP
  460. PRINT 'DuplicateUsersDOBStg: ' + CONVERT(VARCHAR(25),GETDATE() ,121)
  461.  
  462. INSERT INTO @DuplicateUsersDOBStg
  463. select du.ID
  464. , du.UserID
  465. , du.DupUserID
  466. , du.DupType
  467. , datDupDateOfBirth = convert(datetime,circleone_dw.dbo.fndecrypt(u.enDateOfBirth),100)
  468. , datUserDateOfBirth = convert(datetime,circleone_dw.dbo.fndecrypt(ou.enDateOfBirth),100)
  469. , numDateOfBirthDiff = abs(datepart(yy,circleone_dw.dbo.fndecrypt(ou.enDateOfBirth))
  470. -datepart(yy,circleone_dw.dbo.fndecrypt(u.enDateOfBirth)))
  471. --into @DuplicateUsersDOBStg
  472. from @DuplicateUsers du
  473. left join c1.dbo.users u on u.ID = du.DupUserID
  474. left join c1.dbo.users ou on ou.ID = du.UserID
  475.  
  476.  
  477. PRINT 'DuplicateUsersCODStg: ' + CONVERT(VARCHAR(25),GETDATE() ,121)
  478. INSERT INTO @DuplicateUsersCODStg
  479. select up.ID
  480. ,up.UserID
  481. ,du.DupUserID
  482. ,du.DupType
  483. ,sum(case when lst.Name in ('CHARGEOFF','DEFAULTED') then 1 else 0 end) as 'numDupLoanChargeoffDefaulted'
  484. ,sum(case when ld.dpd > 0 then 1 else 0 end) as 'numDupLoanDPD0Greater'
  485. ,sum(case when ld.dpd > 30 then 1 else 0 end) as 'numDupLoanDPD30Greater'
  486. ,sum(case when ld.dpd > 60 then 1 else 0 end) as 'numDupLoanDPD60Greater'
  487. ,sum(case when ld.dpd > 90 then 1 else 0 end) as 'numDupLoanDPD90Greater'
  488. ,sum(case when ld.dpd > 120 then 1 else 0 end) as 'numDupLoanDPD120Greater'
  489. ,sum(case when lst.Name in ('ACTIVE','FINALPAYMENTINPROGRESS') then 1 else 0 end) as 'numDupLoanActive'
  490. ,sum(case when lst.Name in ('COMPLETED') then 1 else 0 end) as 'numDupLoanPaid'
  491. ,sum(case when lo.loanid is null then 1 else 0 end) as 'flgDupNoLoan'
  492. ,sum(case when datediff(mm,lo.originationdate,GETDATE()) <= 3 then 1 else 0 end) as 'numDupRecentLoan'
  493. --into @DuplicateUsersCODStg
  494. from @PendingListings up
  495. left join @DuplicateUsers du on du.UserID = up.UserID and up.ID = du.ID
  496. left join c1.dbo.Loans lo on du.dupuserid = lo.borrowerid
  497. left join c1.dbo.loandetail ld on ld.loanid = lo.loanid
  498. and ld.versionenddate is null and ld.versionvalidbit = 1
  499. left join c1.dbo.LoanStatusTypes lst on lst.ID = ld.LoanStatusTypesID
  500. group by up.ID
  501. ,up.UserID
  502. ,du.DupUserID
  503. ,du.DupType
  504. --select * from @DuplicateUsersCODStg
  505.  
  506. PRINT 'DuplicateUsersPMTStg: ' + CONVERT(VARCHAR(25),GETDATE() ,121)
  507. --INSERT INTO @DuplicateUsersPMTStg
  508. select up.ID
  509. ,up.UserID
  510. ,du.DupUserID
  511. ,du.DupType
  512. ,sum(case when isnull(lp.ismanualpayment,0) = 1 then 1 else 0 end) 'numDupManualPayment'
  513. ,sum(case when isnull(lp.Amount,0) >= 3*isnull(li.MonthlyPayment,0) then 1 else 0 end) 'numDupHighPayment'
  514. ,sum(case when isnull(lp.predpd,0) > 0 then 1 else 0 end) 'numDupLatePayment'
  515. ,sum(case when isnull(lp.OntarioPreviousSplitID,0) is not null then 1 else 0 end) 'numDupFailedPayment'
  516. ,count(lp.loanpaymentid) 'numDupLoanPayments'
  517. --into @DuplicateUsersPMTStg
  518. from @PendingListings up
  519. left join @DuplicateUsers du on du.userid = up.userid and du.ID = up.ID
  520. left join c1.dbo.Loans lo on du.dupuserid = lo.borrowerid
  521. left join c1.dbo.listings li on li.loanid = lo.loanid
  522. left join c1.dbo.LoanPayment lp on lp.loanid = lo.loanid
  523. and lp.amount > 0
  524. group by up.UserID
  525. ,up.ID
  526. ,du.DupUserID
  527. ,du.DupType
  528.  
  529. PRINT 'DuplicateUsersADRStg: ' + CONVERT(VARCHAR(25),GETDATE() ,121)
  530. INSERT INTO @DuplicateUsersADRStg
  531. select up.ID
  532. ,up.UserID
  533. ,du.DupUserID
  534. ,du.DupType
  535. ,flgDupBusinessAddress = case when at.ID in (3,4) then 1 else 0 end
  536. ,flgDupSameLastName = case when isnull(undl.LastName,undp.LastName) = isnull(undl2.LastName,undp2.LastName) then 1 else 0 end
  537. ,flgDupSameFirstName = case when isnull(undl.FirstName,undp.FirstName) = isnull(undl2.FirstName,undp2.FirstName) then 1 else 0 end
  538. ,flgDupSameName = case when (isnull(undl.FirstName,undp.FirstName) = isnull(undl2.FirstName,undp2.FirstName)
  539. and isnull(undl.LastName,undp.LastName) = isnull(undl2.LastName,undp2.LastName)) then 1 else 0 end
  540. --into @DuplicateUsersADRStg
  541. from @PendingListings up
  542. left join @DuplicateUsers du on du.userid = up.userid and up.ID = du.ID
  543. left join c1.dbo.usertoaddress uta (nolock) on uta.userid = up.userid
  544. and uta.versionenddate is null and uta.versionvalidbit = 1
  545. and uta.islegaladdress = 1
  546. and uta.usertoaddressid in (select max(usertoaddressid)
  547. from c1.dbo.usertoaddress
  548. where userid = up.UserID
  549. and versionenddate is null and versionvalidbit = 1
  550. and islegaladdress = 1)
  551. left join c1.dbo.usertoaddress uta2 (nolock) on uta2.userid = du.dupuserid
  552. and uta2.versionenddate is null and uta2.versionvalidbit = 1
  553. and uta2.ispreferredmailing = 1
  554. and uta2.usertoaddressid in (select max(usertoaddressid)
  555. from c1.dbo.usertoaddress
  556. where userid = du.dupuserid
  557. and versionenddate is null and versionvalidbit = 1
  558. and ispreferredmailing = 1)
  559. left join circleone.dbo.AddressTypes at on at.ID = uta2.UserAddressTypeID
  560. left join c1.dbo.UserNameDetail undl on undl.userid = du.userid
  561. and undl.versionenddate is null and undl.versionvalidbit = 1
  562. and undl.UserNameTypeID = 3
  563. left join c1.dbo.UserNameDetail undl2 on undl2.userid = du.dupuserid
  564. and undl2.versionenddate is null and undl2.versionvalidbit = 1
  565. and undl2.UserNameTypeID = 3
  566. left join c1.dbo.UserNameDetail undp on undp.userid = du.userid
  567. and undp.versionenddate is null and undp.versionvalidbit = 1
  568. and undp.UserNameTypeID = 2
  569. left join c1.dbo.UserNameDetail undp2 on undp2.userid = du.dupuserid
  570. and undp2.versionenddate is null and undp2.versionvalidbit = 1
  571. and undp2.UserNameTypeID = 2
  572.  
  573. PRINT 'DuplicateUsersEMPStg: ' + CONVERT(VARCHAR(25),GETDATE() ,121)
  574. INSERT INTO @DuplicateUsersEMPStg
  575. select up.ID
  576. ,up.UserID
  577. ,du.DupUserID
  578. ,du.DupType
  579. --into @DuplicateUsersEMPStg
  580. from @PendingListings up
  581. left join @DuplicateUsers du on du.userid = up.userid and up.ID = du.ID
  582. left join c1.dbo.UserEmploymentDetail ued on ued.userid = du.dupuserid
  583. and ued.versionenddate is null and ued.versionvalidbit = 1
  584. left join c1.dbo.EmploymentStatus es on es.EmploymentStatusID = ued.EmploymentStatusID
  585.  
  586. --5c. prepare duplicate user staging table
  587. PRINT 'DuplicatesStg: ' + CONVERT(VARCHAR(25),GETDATE() ,121)
  588. INSERT INTO @DuplicatesStg
  589. select up.ID
  590. , up.UserID
  591. , aaaDupUserID = du.DupUserID
  592. , catDupType = du.DupType
  593. , flgDupSuspicious = case when s.suspicionid is not null then 1 else 0 end
  594. , dbo.datDupDateOfBirth
  595. , dbo.datUserDateOfBirth
  596. , dbo.numDateOfBirthDiff
  597. , catDupUserStatus = isnull(us.Description,us2.Description)
  598. , flgDupUserStatusNotActive = case when isnull(us.Description,us2.Description) <> 'ACTIVE' then 1 else 0 end
  599. , flgDupUserStatusSuspendedTerminated = case when isnull(us.Description,us2.Description) in ('SUSPENDED','TERMINATED') then 1 else 0 end
  600. , numDupCreditScore = case when ucp.Score between 300 and 900 then ucp.Score else 640 end
  601. , flgDupCreditScoreLow = case when ucp.Score between 300 and 640 then 1 else 0 end
  602. , flgDupCreditScoreMissing = case when ucp.Score is null or ucp.Score = -1 then 1 else 0 end
  603. , flgDupLoanChargeoffDefault = case when isnull(cod.numDupLoanChargeoffDefaulted,0) > 0 then 1 else 0 end
  604. , flgDupLoanDPD0Greater = case when isnull(cod.numDupLoanDPD0Greater,0) > 0 then 1 else 0 end
  605. , flgDupLoanDPD30Greater = case when isnull(cod.numDupLoanDPD30Greater,0) > 0 then 1 else 0 end
  606. , flgDupLoanDPD60Greater = case when isnull(cod.numDupLoanDPD60Greater,0) > 0 then 1 else 0 end
  607. , flgDupLoanDPD90Greater = case when isnull(cod.numDupLoanDPD90Greater,0) > 0 then 1 else 0 end
  608. , flgDupLoanDPD120Greater = case when isnull(cod.numDupLoanDPD120Greater,0) > 0 then 1 else 0 end
  609. , flgDupLoanActive = case when isnull(cod.numDupLoanActive,0) > 0 then 1 else 0 end
  610. , flgDupLoanPaid = case when isnull(cod.numDupLoanPaid,0) > 0 then 1 else 0 end
  611. , flgDupNoLoan = case when isnull(cod.flgDupNoLoan,1) > 0 then 1 else 0 end
  612. , flgDupRecentLoan = case when isnull(cod.numDupRecentLoan,0) > 0 then 1 else 0 end
  613. , flgDupRecentListing = case when datediff(dd,li.starttime,GETDATE()) <= 14 then 1 else 0 end
  614. , flgDupManualPayment = case when isnull(pmt.numDupManualPayment,0) > 0 then 1 else 0 end
  615. , flgDupHighPayment = case when isnull(pmt.numDupHighPayment,0) > 0 then 1 else 0 end
  616. , flgDupLatePayment = case when isnull(pmt.numDupLatePayment,0) > 0 then 1 else 0 end
  617. , flgDupFailedPayment = case when isnull(pmt.numDupFailedPayment,0) > 0 then 1 else 0 end
  618. , flgDupBusinessAddress = isnull(adr.flgDupBusinessAddress,0)
  619. , flgDupSameName = isnull(adr.flgDupSameName,0)
  620. , flgDupSameFirstName = isnull(adr.flgDupSameFirstName,0)
  621. , flgDupSameLastName = isnull(adr.flgDupSameLastName,0)
  622. --into @DuplicatesStg
  623. from @PendingListings up
  624. left join @DuplicateUsers du on du.userid = up.userid and up.ID = du.ID
  625. left join c1.dbo.suspicion s (nolock) on s.userid = du.dupuserid
  626. and s.createddate in (select max(createddate)
  627. from c1.dbo.suspicion (nolock)
  628. where userid = du.dupuserid)
  629. left join @DuplicateUsersDOBStg dbo on dbo.UserID = du.UserID
  630. and dbo.DupUserID = du.DupUserID
  631. and dbo.DupType = du.DupType
  632. left join CircleOne.dbo.UserToStatus uts on uts.UserID = du.DupUserID --change back to c1
  633. and uts.CreationDate in (select max(CreationDate) --select * from c1.dbo.UserToStatus
  634. from CircleOne.dbo.UserToStatus
  635. where UserID = du.DupUserID)
  636. left join c1.dbo.UserStatuses us on us.ID = uts.UserStatusID
  637. left join c1.dbo.Users u on u.id = du.dupuserid
  638. left join c1.dbo.UserStatuses us2 on us2.ID = u.status
  639. left join c1.dbo.UserCreditProfiles ucp on ucp.UserID = du.dupUserID
  640. and ucp.VersionEndDate is null and ucp.VersionValidBit = 1
  641. and ucp.UserCreditProfileID in (select max(UserCreditProfileID)
  642. from c1.dbo.UserCreditProfiles
  643. where userID = du.dupUserID
  644. and VersionEndDate is null and VersionValidBit = 1)
  645. left join @DuplicateUsersCODStg cod on cod.UserID = du.UserID and cod.ID = du.ID
  646. and cod.DupUserID = du.DupUserID
  647. and cod.DupType = du.DupType
  648. left join c1.dbo.listings li on li.userid = du.dupUserID
  649. and li.status between 2 and 8
  650. and li.starttime in (select max(starttime)
  651. from c1.dbo.listings
  652. where userid = du.dupUserID
  653. and [status] between 2 and 8)
  654. left join @DuplicateUsersPMTStg pmt on pmt.UserID = du.UserID and pmt.ID = du.ID
  655. and pmt.DupUserID = du.DupUserID
  656. and pmt.DupType = du.DupType
  657. left join @DuplicateUsersADRStg adr on adr.UserID = du.UserID and adr.ID = du.ID
  658. and adr.DupUserID = du.DupUserID
  659. and adr.DupType = du.DupType
  660. left join @DuplicateUsersEMPStg emp on emp.UserID = du.UserID and emp.ID = du.ID
  661. and emp.DupUserID = du.DupUserID
  662. and emp.DupType = du.DupType
  663.  
  664. --5d. create summary duplicate data table
  665. PRINT 'Duplicates: ' + CONVERT(VARCHAR(25),GETDATE() ,121)
  666. INSERT INTO @Duplicates
  667. select dID = ID
  668. ,dUserID = UserID
  669. ,numDistinctDups = count(distinct d.aaaDupUserID)
  670. ,flgDupExistence = case when count(distinct d.aaaDupUserID) > 0 then 1 else 0 end
  671. ,flgDupTypeCookie = sum(case when d.catDupType = 'Cookie' then 1 else 0 end)
  672. ,flgDupTypePhone = sum(case when d.catDupType = 'Phone' then 1 else 0 end)
  673. ,flgDupTypeAddress = sum(case when d.catDupType = 'Address' then 1 else 0 end)
  674. ,flgDupTypeBank = sum(case when d.catDupType = 'Bank' then 1 else 0 end)
  675. ,numMaxDupDateOfBirthDiff = isnull(max(numDateOfBirthDiff),0)
  676. ,numAvgDupDateOfBirthDiff = isnull(avg(numDateOfBirthDiff),0)
  677. ,numMinDupCreditScore = isnull(min(numDupCreditScore),640)
  678. ,numAvgDupCreditScore = isnull(avg(numDupCreditScore),640)
  679. ,flgMaxDupUserStatusNotActive = max(flgDupUserStatusNotActive)
  680. ,flgMaxDupUserStatusSuspendedTerminated = max(flgDupUserStatusSuspendedTerminated)
  681. ,flgMaxDupCreditScoreLow = max(flgDupCreditScoreLow)
  682. ,flgMaxDupCreditScoreMissing = max(flgDupCreditScoreMissing)
  683. ,flgMaxDupLoanChargeoffDefault = max(flgDupLoanChargeoffDefault)
  684. ,flgMaxDupLoanDPD0Greater = max(flgDupLoanDPD0Greater)
  685. ,flgMaxDupLoanDPD30Greater = max(flgDupLoanDPD30Greater)
  686. ,flgMaxDupLoanDPD60Greater = max(flgDupLoanDPD60Greater)
  687. ,flgMaxDupLoanDPD90Greater = max(flgDupLoanDPD90Greater)
  688. ,flgMaxDupLoanDPD120Greater = max(flgDupLoanDPD120Greater)
  689. ,flgMaxDupLoanActive = max(flgDupLoanActive)
  690. ,flgMaxDupLoanPaid = max(flgDupLoanPaid)
  691. ,flgMaxDupNoLoan = max(flgDupNoLoan)
  692. ,flgMaxDupRecentLoan = max(flgDupRecentLoan)
  693. ,flgMaxDupRecentListing = max(flgDupRecentListing)
  694. ,flgMaxDupManualPayment = max(flgDupManualPayment)
  695. ,flgMaxDupHighPayment = max(flgDupHighPayment)
  696. ,flgMaxDupLatePayment = max(flgDupLatePayment)
  697. ,flgMaxDupFailedPayment = max(flgDupFailedPayment)
  698. ,flgMaxDupBusinessAddress = max(flgDupBusinessAddress)
  699. ,flgMaxDupSameName = max(flgDupSameName)
  700. ,flgMaxDupSameFirstName = max(flgDupSameFirstName)
  701. ,flgMaxDupSameLastName = max(flgDupSameLastName)
  702. ,flgMaxDupHighAgeDiff = case when isnull(max(numDateOfBirthDiff),0) > 10 then 1 else 0 end
  703. --into @Duplicates
  704. from @DuplicatesStg d
  705. group by ID,UserID
  706.  
  707. Insert into @IDVAggregateLogins
  708. Exec SingleListing_IDVAggregateLogins @userid
  709.  
  710. PRINT 'User: ' + CONVERT(VARCHAR(25),GETDATE() ,121)
  711. INSERT INTO @User
  712. select [uID] = up.ID
  713. ,uUserID = up.UserID
  714. ,numUserListingAmount = li.Amount
  715. ,flgUserMultiBankAccounts = isnull(flgUserMultiBankAccounts,0)
  716. ,flgUserMultiBankHolders = isnull(flgUserMultiBankHolders,0)
  717. ,flgUserPrePaidBankAccount = isnull(flgUserPrePaidBankAccount,0)
  718. ,flgUserSuspicion = isnull(flgUserSuspicion,0)
  719. ,ListingTitle = li.Title
  720. ,flgUserTitleConsolidation = isnull(case when patindex('%consolidation%',LOWER(li.Title)) > 0 then 1 else 0 end,0)
  721. ,flgUserTitleDebt = isnull(case when patindex('%debt%',LOWER(li.Title)) > 0 then 1 else 0 end,0)
  722. ,numUser30DBankChange = isnull(numUser30DBankChange,0)
  723. ,numUser30DBankCount = isnull(numUser30DBankCount,0)
  724. ,numUserAgeYears = isnull(Year(GETDATE()) - YEAR(circleone_dw.dbo.fndecrypt(u.enDateofBirth)),0)
  725. ,numUserBankCount = isnull(numUserBankCount,0)
  726. ,numUserEmploymentLengthMonths = isnull((YEAR(GETDATE())*12 + MONTH(GETDATE()))
  727. - (ued.StartYear*12 + ued.StartMonth),0)
  728. ,numUserFailLogins = isnull(numUserFailLogins,0)
  729. ,numUserLogins = isnull(numUserLogins,0)
  730. ,numUserPercentFailLogins = isnull(numUserPercentFailLogins,0)
  731. ,numUserRegistrationAgeMonths = isnull(numUserRegistrationAgeMonths,0)
  732. ,numUserSuccessLogins = isnull(numUserSuccessLogins,0)
  733. ,numUserTitleLength = isnull(len(li.Title),0)
  734. ,numPrevCancellations = ISNULL(numPrevCancellations,0)
  735. ,numPrevOriginations = ISNULL(numPrevOriginations,0)
  736. ,numPrevWithdrawals = ISNULL(numPrevWithdrawals,0)
  737. --into @User
  738. from @PendingListings up --select count(*) from @@UserPopulation
  739. left join c1.dbo.listings li on li.id = up.ID
  740. left join c1.dbo.Users u on up.UserID = u.ID
  741. left join c1.dbo.UserEmploymentDetail ued on up.UserID = ued.UserID
  742. and ued.VersionEndDate is null and VersionValidBit = 1
  743. left join (select up2.UserID, up2.ID, datediff(mm,min(CreationDate),GETDATE()) [numUserRegistrationAgeMonths]
  744. from @PendingListings up2
  745. left join CircleOne.dbo.UsertoStatus us on up2.UserID = us.UserID --change this to c1
  746. where us.UserStatusID = 2 and us.UserStatusChangeReasonID in (10,12)
  747. group by up2.UserID, up2.ID
  748. ) uts on up.UserID = uts.UserID and up.ID = uts.ID
  749. --Number of Logins
  750. left join @IDVAggregateLogins logs on up.UserID = logs.UserID
  751. --Multiple Bank Account Flags
  752. left join (select up2.UserID, up2.ID, count(ac.UserID) [numUserBankCount], count(ac2.UserID) [numUser30DBankCount]
  753. ,case when count(ac.UserID) > 1 then 1 else 0 end [flgUserMultiBankAccounts]
  754. ,case when count(ac.UserID) - count(ac2.UserID) not in (count(ac.UserID),0) then 1 else 0 end [numUser30DBankChange]
  755. ,case when sum(case when ba.SecondAccountHolderName is not null
  756. then 1 else 0 end) > 0 then 1 else 0 end [flgUserMultiBankHolders]
  757. ,case when sum(case when len(circleone_dw.dbo.fndecrypt(enAccountNumber)) = 16 then 1 else 0 end) > 0
  758. then 1 else 0 end [flgUserPrePaidBankAccount] -- 16 digit Acc @ is Prepaid
  759. from @PendingListings up2
  760. left join c1.dbo.Accounts ac on up2.UserID = ac.UserID
  761. and ac.AccountTypeCode = 'BANK'
  762. left join c1.dbo.BankAccounts ba on ac.ID = ba.AccountID
  763. left join c1.dbo.Accounts ac2 on up2.UserID = ac2.UserID
  764. and ac.CreationDate between dateadd(d,-30,GETDATE()) and GETDATE()
  765. and ac2.AccountTypeCode = 'BANK'
  766. group by up2.UserID, up2.ID
  767. ) mba on up.UserID = mba.UserID and up.ID = mba.ID
  768. --User Suspicion
  769. left join (select up2.UserID, up2.ID
  770. ,case when s.suspicionID is not null then 1 else 0 end [flgUserSuspicion]
  771. from @PendingListings up2
  772. left join c1.dbo.suspicion s (nolock) on s.userid = up2.userid
  773. and s.createddate in (select max(createddate)
  774. from c1.dbo.suspicion (nolock)
  775. where userid = up2.userid)
  776. ) sus on sus.UserID = up.UserID and sus.ID = up.ID
  777. left join (select up2.UserID, up2.ID
  778. , SUM(case when li.status = 7 then 1 else 0 end) 'numPrevCancellations'
  779. , SUM(case when li.status = 6 then 1 else 0 end) 'numPrevOriginations'
  780. , SUM(case when li.status = 4 then 1 else 0 end) 'numPrevWithdrawals'
  781. from @PendingListings up2
  782. left join c1.dbo.Listings li (nolock) on li.UserID = up2.UserID
  783. group by up2.UserID, up2.ID
  784. ) prc on prc.UserID = up.UserID and prc.ID = up.ID
  785.  
  786.  
  787. -- Prospect
  788. DECLARE @ProspectListingData LPRProspectSearchType
  789.  
  790. DECLARE @ChannelAttributionFromProspect TABLE (
  791. [ListingID] [int] NOT NULL
  792. , Campaign varchar(255)
  793. , CampaignChannel varchar(255)
  794. )
  795.  
  796. INSERT INTO @ProspectListingData
  797. select l.id,l.userid,l.termsapprovaldate,match.value
  798. from c1..listings l
  799. LEFT JOIN C1..ListingOffersSelected [select](NOLOCK) ON [select].ListingID = l.ID
  800. AND [select].versionenddate is null and [select].versionvalidbit = 1
  801. LEFT JOIN C1..LoanOffer offer(NOLOCK) ON offer.LoanOfferID = [select].LoanOfferID
  802. LEFT JOIN C1..tblLoanOfferScore score(NOLOCK) ON score.ListingScoreID = offer.ListingScoreID
  803. AND score.versionenddate is null and score.versionvalidbit = 1
  804. LEFT JOIN C1..tblLoanOfferScoreDetail pship(NOLOCK) ON pship.ListingScoreID = score.ListingScoreID
  805. AND pship.VariableID = (
  806. SELECT VariableID
  807. FROM C1..tblScoreVariables(NOLOCK)
  808. WHERE VariableName = 'PartnershipBin'
  809. )
  810. LEFT JOIN C1..tblLoanOfferScoreDetail match ON match.ListingScoreID = score.ListingScoreID
  811. AND match.VariableID = (
  812. SELECT VariableID
  813. FROM C1..tblScoreVariables
  814. WHERE VariableName = 'ReferralCode'
  815. )
  816. WHERE l.ID = @listingid
  817.  
  818.  
  819.  
  820. INSERT INTO @ChannelAttributionFromProspect
  821. EXEC [dbo].[SingleListingLPR_GetMostRecentProspectData] @ProspectListingData
  822.  
  823. PRINT 'ChannelAttribution: ' + CONVERT(VARCHAR(25), GETDATE(), 121)
  824.  
  825. INSERT INTO @ChannelAttribution
  826. SELECT l.ID 'ListingID'
  827. , ucp.CreditPullDate
  828. , score.ListingScoreID
  829. , l.TermsApprovalDate
  830. , pship.Value 'PartnershipBin'
  831. , CASE
  832. WHEN pship.Value LIKE '%DirectMail%'
  833. OR p.CampaignChannel IN ('DirectMail')
  834. THEN 7
  835. WHEN pship.Value LIKE '%BestQpSegment%'
  836. OR p.CampaignChannel IN ('QualifiedPartners')
  837. THEN 5
  838. WHEN pship.Value LIKE '||'
  839. AND p.CampaignChannel = 'Affiliates'
  840. AND p.Campaign = 'NQA-YUEJO'
  841. THEN 1
  842. WHEN pship.Value LIKE '||'
  843. AND p.CampaignChannel = 'Affiliates'
  844. AND p.Campaign IN ('NQA-ir', 'NQA-SAS', 'NQA-cj', 'NQA-Adworkz')
  845. THEN 3
  846. WHEN pship.Value LIKE '||'
  847. AND p.CampaignChannel IN ('Affiliates', 'DataExchange')
  848. THEN 5
  849. ELSE 4
  850. END 'Channel'
  851. --into @ChannelAttribution
  852. FROM C1..Listings l(NOLOCK)
  853. join @ChannelAttributionFromProspect p on p.ListingID = l.ID
  854. LEFT JOIN C1..ListingOffersSelected [select](NOLOCK) ON [select].ListingID = l.ID
  855. AND [select].versionenddate is null and [select].versionvalidbit = 1
  856. LEFT JOIN C1..LoanOffer offer(NOLOCK) ON offer.LoanOfferID = [select].LoanOfferID
  857. LEFT JOIN C1..UserCreditProfiles ucp(NOLOCK) ON ucp.UserCreditProfileID = l.UserCreditProfileID
  858. LEFT JOIN C1..tblLoanOfferScore score(NOLOCK) ON score.ListingScoreID = offer.ListingScoreID
  859. AND score.versionenddate is null and score.versionvalidbit = 1
  860. LEFT JOIN C1..tblLoanOfferScoreDetail pship(NOLOCK) ON pship.ListingScoreID = score.ListingScoreID
  861. AND pship.VariableID = (
  862. SELECT VariableID
  863. FROM C1..tblScoreVariables(NOLOCK)
  864. WHERE VariableName = 'PartnershipBin'
  865. )
  866. LEFT JOIN C1..tblLoanOfferScoreDetail match ON match.ListingScoreID = score.ListingScoreID
  867. AND match.VariableID = (
  868. SELECT VariableID
  869. FROM C1..tblScoreVariables
  870. WHERE VariableName = 'ReferralCode'
  871. )
  872.  
  873. WHERE l.TermsApprovalDate >= '8/1/14' /*AND pship.Value LIKE '%DirectMail%'*/
  874. AND l.ID IN (
  875. SELECT ID
  876. FROM @PendingListings
  877. ) /*AND pship.Value LIKE '%DirectMail%'*/
  878. ORDER BY CreditPullDate DESC
  879.  
  880. -- aggregate and apend variables
  881. PRINT 'Aggregate: ' + CONVERT(VARCHAR(25),GETDATE() ,121)
  882. INSERT INTO @Aggregate
  883. select p.*
  884. ,e.*
  885. ,s.*
  886. ,d.*
  887. ,u.*
  888. ,0 'flgFraudScreen'
  889. ,flgFS01 + flgFS02 + flgFS04 + flgFS05 + flgFS06 + flgFS07 + flgFS08 + flgFS09 + flgFS10 + flgFS11 + flgFS13 + flgFS14 + flgFS15 + flgFS16 + flgFS17 + flgFS18 + flgFS19 + flgFS20 + flgFS21 + flgFS22 + flgFS25 + flgFS26 + flgFS27 'FSSUM'
  890. ,case when ca.PartnershipBin LIKE '%DirectMail%' then 1 else 0 end 'flgChannelDM'
  891. ,ca.Channel
  892. ,ucp.AggregateBalanceCreditRatioOnAllTrades6Months
  893. ,ucp.OpenPaidClosedInactiveInstallTrades
  894. --into @Aggregate
  895. from @PendingListings p
  896. left join @Experian e on p.UserID = e.eUserID and p.ID = e.eID
  897. left join @Stagg s on p.UserID = s.sUserID and p.ID = s.sID
  898. left join @Duplicates d on p.UserID = d.dUserID and p.ID = d.dID
  899. left join @User u on p.UserID = u.uUserID and p.ID = u.uID --rerun this
  900. left join c1.dbo.Listings (nolock) li on p.ID = li.ID
  901. left join c1.dbo.UserCreditProfiles (nolock) ucp on li.UserCreditProfileID = ucp.UserCreditProfileID
  902. left join @ChannelAttribution ca on p.ID = ca.ListingID
  903. where case when MissingData = 1 and DATEDIFF(dd,PendingStartDate,getdate()) <= 2 then 1 else 0 end = 0 -- set where clause here rather than bottom
  904.  
  905. PRINT 'AggregateFraud: ' + CONVERT(VARCHAR(25),GETDATE() ,121)
  906. INSERT INTO @AggregateFraud
  907. select pl.*
  908. ,case when (s.createddate is not null and gs.loanid is null and (lo.originationdate is null or s.createddate > lo.originationdate)) then 1 else 0 end as 'flgSuspicionCriterion'
  909. ,case when exists (select 1 from CircleOneProcessing.dbo.VS_InitialListingsWorkflowsFraudScreenEmaiL where EmailDomain = substring(ue.Email,charindex('@',ue.Email)+1,100)) then 1 else 0 end 'flgFraudScreenEmail'
  910. --into @AggregateFraud
  911. from @Aggregate pl
  912. left join c1.dbo.Suspicion S (nolock) on S.UserID = pl.UserID and S.CreatedDate in
  913. (Select Max(CreatedDate) from c1.dbo.Suspicion(nolock) where UserID = pl.UserID)
  914. left join c1.dbo.loans lo (nolock) on pl.userid = lo.borrowerid
  915. and lo.originationdate < pl.PendingStartDate
  916. and lo.originationdate in (select max(originationdate)
  917. from c1.dbo.loans
  918. where borrowerid = lo.borrowerid
  919. and originationdate < pl.PendingStartDate
  920. )
  921. left join c1.dbo.loans gs (nolock) on gs.borrowerid = s.userid
  922. and datediff(mm,lo.originationdate,s.createddate) >= 12
  923. and lo.loanid not in (
  924. select loanid
  925. from c1.dbo.loandetail(nolock)
  926. where loanid = lo.loanid
  927. and dpd > 30)
  928. left join c1.dbo.UserEmails ue on pl.UserID = ue.UserID
  929. and ue.VersionEndDate is null
  930. and ue.VersionValidBit = 1
  931. and ue.IsSignIn = 1
  932.  
  933.  
  934. /*##########################################################################################
  935. ###Prior to appending other holds, create the tmp tables for POI/POE Update 201405v3 policy#
  936. ###########################################################################################*/
  937.  
  938. --Create a table with the Verificaiton Score and strategy components
  939.  
  940. PRINT 'IncVerIn: ' + CONVERT(VARCHAR(25),GETDATE() ,121)
  941. INSERT INTO @IncVerIn
  942. SELECT aggf.ID AS ListingID
  943. , aggf.UserID
  944. , li.UserCreditProfileID
  945. , li.Amount AS InitialListingAmount
  946. , uinc.Income
  947. , CASE WHEN priorLoan.PriorLoanCount > 0 THEN 1 ELSE 0 END AS HasPriorLoanFlag
  948. , occ.OccupationName
  949. , es.[Description] AS EmploymentDescription
  950. , 12*(YEAR(li.TermsApprovalDate) - ued.StartYear) + (MONTH(li.TermsApprovalDate) - ued.StartMonth) AS MonthsEmployed
  951. , CASE WHEN es.[Description] = 'Self-employed' THEN -98
  952. WHEN uinc.Income IS NULL OR uinc.Income = 0 OR uinc.IsVerifiable = 0 THEN -99
  953. WHEN ucp.CreditPullDate >= '12/28/12' THEN ROUND(((ucp.MonthlyDebt + 692.0)/(uinc.Income/12.0)) - (ucp.MonthlyDebt/(uinc.Income/12.0)),2) --<<-- Corrects for 12/28/2012 rounding change
  954. ELSE ROUND((ucp.MonthlyDebt + 692.0)/(uinc.Income/12.0),2) - ROUND(ucp.MonthlyDebt/(uinc.Income/12.0),2)
  955. END AS DTIChg20k36MoLoan
  956. , CASE WHEN es.[Description] = 'Self-employed' THEN -98
  957. WHEN uinc.Income IS NULL OR uinc.Income = 0 OR uinc.Income = 0 THEN -99
  958. ELSE ROUND(ucp.MonthlyDebt/(uinc.Income/12.0),2)
  959. END AS DTIwoProspLoan
  960. , ucp.MonthlyDebt
  961. , stagg.ALL801
  962. , stagg.ILN701
  963. , stagg.REV702
  964. FROM @AggregateFraud aggf
  965. LEFT JOIN C1.dbo.Listings li
  966. ON li.ID = aggf.ID
  967. LEFT JOIN C1.dbo.UserCreditProfiles ucp
  968. ON ucp.UserCreditProfileID = li.UserCreditProfileID
  969. LEFT JOIN (
  970. SELECT aggf.ID,
  971. aggf.UserID,
  972. COUNT(DISTINCT lo.LoanID) AS PriorLoanCount
  973. FROM @AggregateFraud aggf
  974. LEFT JOIN C1.dbo.Listings li ON li.UserID = aggf.UserID
  975. LEFT JOIN C1.dbo.Loans lo ON lo.LoanID = li.LoanID
  976. AND lo.OriginationDate < GETDATE()
  977. GROUP BY aggf.ID,aggf.UserID
  978. ) priorLoan
  979. ON priorLoan.id = aggf.ID
  980. LEFT JOIN C1.dbo.UserEmploymentDetail ued
  981. ON ued.UserEmploymentDetailID = li.UserEmploymentDetailID
  982. AND c1.dbo.fnisrecordcurrentandvalid(li.TermsApprovalDate,ued.VersionStartDate,ued.VersionEndDate,ued.VersionValidBit) = 1
  983. LEFT JOIN C1.dbo.EmploymentStatus es ON es.EmploymentStatusID = ued.EmploymentStatusID
  984. LEFT JOIN C1.dbo.Occupations occ
  985. ON occ.ID = ued.OccupationID
  986. LEFT JOIN C1.dbo.UserIncome uinc
  987. ON uinc.UserID = li.UserID
  988. AND c1.dbo.fnisrecordcurrentandvalid(li.TermsApprovalDate,uinc.VersionStartDate,uinc.VersionEndDate,uinc.VersionValidBit) = 1
  989. LEFT JOIN C1.dbo.ExperianCreditProfileResponse ecpr
  990. ON ecpr.ExperianDocumentID = ucp.ExperianDocumentID
  991. LEFT JOIN (
  992. SELECT [ExperianCreditProfileResponseID],[AttributeID],[AttributeValue] = CAST([AttributeValue] as INT)
  993. FROM C1.dbo.ExperianCreditProfileStaggData
  994. ) as sourcetable
  995. PIVOT
  996. (
  997. MAX([AttributeValue]) --there should only be 1 value in this aggregate but if it gets doubled up some how, avg, min, or max is pretty safe. don't use sum!
  998. FOR
  999. [AttributeID]
  1000. IN ([ALL801],
  1001. [ILN701],
  1002. [REV702]
  1003. )
  1004. ) AS stagg on stagg.[ExperianCreditProfileResponseID] = ecpr.[ExperianCreditProfileResponseID]
  1005. WHERE li.UserCreditProfileID IS NOT NULL
  1006.  
  1007. PRINT 'ListCancelLast30: ' + CONVERT(VARCHAR(25),GETDATE() ,121)
  1008. INSERT INTO @ListCancelLast30
  1009. SELECT incin.ListingID
  1010. , COUNT(*) AS ListCancelLast30
  1011. FROM @IncVerIn incin
  1012. LEFT JOIN C1.dbo.Listings li
  1013. ON li.ID = incin.ListingID
  1014. LEFT JOIN C1.dbo.Listings li2
  1015. ON li.UserID = li2.UserID
  1016. AND li2.Status = 7
  1017. AND li2.TermsApprovalDate < li.TermsApprovalDate
  1018. AND DATEDIFF(dd,li2.TermsApprovalDate,li.TermsApprovalDate) <= 30
  1019. WHERE li2.Status = 7
  1020. AND li2.TermsApprovalDate < li.TermsApprovalDate
  1021. AND DATEDIFF(dd,li2.TermsApprovalDate,li.TermsApprovalDate) <= 30
  1022. GROUP BY incin.ListingID
  1023.  
  1024.  
  1025. PRINT 'LastProspIncUpdateDate: ' + CONVERT(VARCHAR(25),GETDATE() ,121)
  1026. INSERT INTO @LastProspIncUpdateDate
  1027. SELECT uinc.UserID
  1028. , incin.ListingID
  1029. , MAX(uinc.CreatedDate) AS MRProspModIncomeDate
  1030. FROM C1.dbo.UserIncome uinc
  1031. LEFT JOIN @IncVerIn incin
  1032. ON incin.UserID = uinc.UserID
  1033. LEFT JOIN @ListCancelLast30 lcl30
  1034. ON lcl30.ListingID = incin.ListingID
  1035. LEFT JOIN C1.dbo.Listings li
  1036. ON li.ID = incin.ListingID
  1037. WHERE incin.UserID IS NOT NULL
  1038. AND uinc.CreatedDate < li.TermsApprovalDate
  1039. AND lcl30.ListCancelLast30 >= 1
  1040. AND uinc.CreatedUser > 0 AND uinc.CreatedUser < uinc.UserID
  1041. GROUP BY uinc.UserID
  1042. , incin.ListingID
  1043.  
  1044.  
  1045. PRINT 'IncModifyGroup: ' + CONVERT(VARCHAR(25),GETDATE() ,121)
  1046. INSERT INTO @IncModifyGroup
  1047. SELECT incin.ListingID
  1048. , CASE WHEN incin.Income <= uinc.Income THEN 1 ELSE 0 END IncomeLELastProspMod
  1049. , CASE WHEN incin.EmploymentDescription IN ('Employed', 'Other')
  1050. AND uinc.IsVerifiable = 0 THEN 1 ELSE 0 END EmpStatusChgFlag
  1051. FROM @LastProspIncUpdateDate lpind
  1052. LEFT JOIN C1.dbo.UserIncome uinc
  1053. ON uinc.UserID = lpind.UserID
  1054. AND uinc.CreatedDate = lpind.MRProspModIncomeDate
  1055. LEFT JOIN @IncVerIn incin
  1056. ON incin.ListingID = lpind.ListingID
  1057.  
  1058.  
  1059. --append other holds
  1060. PRINT 'InitialVerificationStaging: ' + CONVERT(VARCHAR(25),GETDATE() ,121)
  1061. INSERT INTO @InitialVerificationStaging
  1062. select pl.*
  1063. ,invin.UserCreditProfileID
  1064. ,invin.InitialListingAmount
  1065. ,invin.Income
  1066. ,invin.HasPriorLoanFlag
  1067. ,invin.OccupationName
  1068. ,invin.EmploymentDescription
  1069. ,invin.MonthsEmployed
  1070. ,invin.DTIChg20k36MoLoan
  1071. ,invin.DTIwoProspLoan
  1072. ,invin.MonthlyDebt
  1073. ,invin.ALL801
  1074. ,invin.ILN701
  1075. ,invin.REV702
  1076. ,lcl30.ListCancelLast30
  1077. ,lpiud.MRProspModIncomeDate
  1078. ,img.IncomeLELastProspMod
  1079. ,img.EmpStatusChgFlag
  1080. ,li.UserLoanHistorySnapshotID
  1081. ,P1401 = excp1401.UserID
  1082. ,P1601 = excp1601.UserID
  1083. ,P1801 = excp1801.UserID
  1084. ,flgFraudVictimXML = isnull(PATINDEX('%Fraud Victim Alert%',ed.rawXML),0)
  1085. ,flgSecurityAlertXML = isnull(PATINDEX('%Security Alert%',ed.rawXML),0)
  1086. ,flgActiveDutyXML = isnull(PATINDEX('%Active Military Duty%',ed.rawXML),0)
  1087. ,BovStatusID = bov.StatusID
  1088. ,MilStatusID = mil.StatusID
  1089. ,AdrStatusID = adr.StatusID
  1090. --into @InitialVerificationStaging
  1091. from @AggregateFraud pl
  1092. left join @IncVerIn invin on pl.ID = invin.ListingID
  1093. left join @ListCancelLast30 lcl30 on pl.ID = lcl30.ListingID
  1094. left join @LastProspIncUpdateDate lpiud on pl.ID = lpiud.ListingID
  1095. left join @IncModifyGroup img on pl.ID = img.ListingID
  1096. left join c1.dbo.Listings li on pl.ID = li.ID
  1097. --look up victim statement in most recent ExperianDocument
  1098. left join c1.dbo.ExperianDocuments ed on pl.UserID = ed.UserID
  1099. and ed.documentType = 'Response Credit Profile'
  1100. and ed.CreationDate = (select MAX(CreationDate) from c1.dbo.ExperianDocuments where ed.UserID = UserID and documentType = 'Response Credit Profile')
  1101. --check holds
  1102. left join c1.dbo.listingqueue bov (nolock) on bov.listingid = pl.id
  1103. and bov.typeid = 9 and bov.statusid = 2
  1104. and bov.versionenddate is null and bov.versionvalidbit = 1
  1105. left join c1.dbo.listingqueue mil (nolock) on mil.listingid = pl.id
  1106. and mil.typeid = 8 and mil.statusid = 2
  1107. and mil.versionenddate is null and mil.versionvalidbit = 1
  1108. left join c1.dbo.listingqueue adr (nolock) on adr.listingid = pl.id
  1109. and adr.typeid = 7 and adr.statusid = 2
  1110. and adr.versionenddate is null and adr.versionvalidbit = 1
  1111. --check for previously matched 1401, 1601, 1801
  1112. left join (select wf.UserID
  1113. ,w.WorkflowTypeID
  1114. from @PendingListings wf
  1115. join c1.dbo.Workflow w on wf.UserID = w.UserID
  1116. and w.WorkflowTypeID = 1401
  1117. join c1.dbo.VerificationWorkflowResultDetail wr on w.workflowid = wr.workflowid
  1118. and wr.VerificationWorkflowResultTypeID = 1
  1119. left join c1.dbo.VerificationWorkflowResultType wrt on wrt.Verificationworkflowresulttypeid = wr.Verificationworkflowresulttypeid
  1120. ) excp1401 on excp1401.UserID = pl.UserID
  1121. left join (select wf.UserID
  1122. ,w.WorkflowTypeID
  1123. from @PendingListings wf
  1124. join c1.dbo.Workflow w on wf.UserID = w.UserID
  1125. and w.WorkflowTypeID = 1601
  1126. join c1.dbo.VerificationWorkflowResultDetail wr on w.workflowid = wr.workflowid
  1127. and wr.VerificationWorkflowResultTypeID = 1
  1128. left join c1.dbo.VerificationWorkflowResultType wrt on wrt.Verificationworkflowresulttypeid = wr.Verificationworkflowresulttypeid
  1129. ) excp1601 on excp1601.UserID = pl.UserID
  1130. left join (select wf.UserID
  1131. ,w.WorkflowTypeID
  1132. from @PendingListings wf
  1133. join c1.dbo.Workflow w on wf.UserID = w.UserID
  1134. and w.WorkflowTypeID = 1801
  1135. join c1.dbo.VerificationWorkflowResultDetail wr on w.workflowid = wr.workflowid
  1136. and wr.VerificationWorkflowResultTypeID = 1
  1137. left join c1.dbo.VerificationWorkflowResultType wrt on wrt.Verificationworkflowresulttypeid = wr.Verificationworkflowresulttypeid
  1138. ) excp1801 on excp1801.UserID = pl.UserID
  1139.  
  1140. select * from @InitialVerificationStaging
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!