Useful SSMS Queries
Useful Queries
Stuck in Funding Queries
select * --stuck in funding Query for loans in status longer than 10 min
from loanorigination..loanoriginationmaster lom --table pulling from
where ReferenceStatusTypeId = 6 --funding status
AND lom.FundDateTime < DATEADD(minute, -5, GETDATE()) --been in funding for more than 10 mins
Order by UpdatedDateTime DESC
select * --Query to see why stuck
from loanorigination.dbo.loanoriginationfunding f
join loanorigination.dbo.[ReferenceFundingStep] rs on rs.[ReferenceFundingStepid] = f.[ReferenceFundingStepid]
where loanoriginationmasterID = '130465'
--Duplicate payment account **Check Unicorn for notes that verify Bank Info, Correct loanpro if they are there**
--Nothing Pulls **Cust requested a check payment. check account notes and if verified that they want a check, push through**
-- Cust found by SSN but name is different **Check profile in Unicorn and Loanpro for differences.
-- **If only missing a space, push through, other wise have agent verify name with cust**
select top 100 *
from OLPS.dbo.Application
where ApplicationStatusID = -1
Order by DatetimeApplication DESC
select *
from loanorigination..loanoriginationmaster lom
where ReferenceStatusTypeId = 7 --funded loans
Order by UpdatedDateTime DESC
select * --list of app by status
from loanorigination..loanoriginationmaster lom
where ReferenceStatusTypeId = 3 --1=new 2=pending 3=Ready to Work 4=Withdrawn 5=Declined 6=funding 7=funded
Order by LoanOriginationMasterId DESC --UpdatedDateTime
--check push status
select top 100 *
from LoanOrigination..LoanOriginationMaster
where LoanOriginationMasterId = 1187173
--Push Column
-- 0=non-push
-- 1=push
--verify apps are funding
select top 100 *
from loanorigination..loanoriginationmaster lom
where ReferenceStatusTypeId = 7
-- AND lom.FundDateTime < DATEADD(minute, -10, GETDATE())
Order by UpdatedDateTime DESC
--Query to check purchased leads:
select *
from OLPS.dbo.Application
where ApplicationStatusID = -1
Order by DatetimeApplication DESC
--Get approval codes
select top 25 *
from AmericanWebLoan..DirectMailCustomers
where (StartDate < GETDATE() and EndDate > GETDATE())
and DirectMailApplicationStatusID in (0,1)
and LeadGuid is null
--get unicorn app # from GP loan id
Select *
From loanorigination..LoanOriginationMaster LOM
Where LOM.FundingTrackingId = '100451847'
--See what loan source is
select s.loanoriginationMasterID, ct.campaigntypename, case WHEN pc.entityproviderID = 118 THEN 'INTERNAL' ELSE 'AFFILIATE' END as source
from loanorigination..loanoriginationsource s
join olps_archive.dbo.application app with (nolock) on app.applicationguid = s.trackingguid
join olps.dbo.providercampaign pc with (nolock) on pc.providercampaignID = app.providercampaignID
join olps.dbo.campaigntype ct on ct.campaigntypeID = pc.campaigntypeID
where loanoriginationmasterID = 1466907
--pull old CIM files for given App ID
select ld.LoanSystemLoanID
, '\\KSOLA1ESIG01\GoldPoint\' + ld.DocumentPath AS FullPath
, '\\KSOLA1ESIG01\GoldPoint\' + SUBSTRING(ld.DocumentPath, 0, CHARINDEX('0' + CAST(ld.LoanSystemLoanID AS VARCHAR(MAX)), ld.DocumentPath)) AS Directory
from LMS..LoanDocument ld
where ld.LoanSystemLoanID = '0100268595'
--Search based off bank info
select top 100 *
from loanorigination..LoanOriginationPayment p
JOIN loanorigination..LoanOriginationMaster m on p.LoanOriginationMasterID = m.LoanOriginationMasterID
where (RoutingNumber = '104902363' and AccountNumber = '10202001')
--and m.ReferenceStatusTypeId in (1,2,3) -- (1 = new, 2 = pending, 3 = ready to work)
--find apps with due dates off from End of Month
select * from loanorigination.dbo.LoanOriginationPayment p
join loanorigination.dbo.LoanOriginationMaster l on l.LoanOriginationMasterId = p.LoanOriginationMasterId
where p.referencepaydayId = 45 --last day of month
and l.ReferenceStatusTypeId = 7 --funded
and DAY(p.FirstDueDate) = 30 --first due date = day # 30 of month
and ReferencePayFrequencyId = 3 --monthly
order by 1 desc
--List of Status and substatus types
select * from loanorigination.dbo.referencestatustype
select * from loanorigination.dbo.referencesubstatustype
select * from loanorigination.dbo.referencepayfrequency
select * from loanorigination.dbo.loanoriginationfunding f with (nolock)
where ReferenceFundingStepId = 14
and ReferenceFundingStepStatusId = 2
and CreatedDateTime > '8/4/2021'
order by 1 desc
--find documenDelete from loanorigination..loanoriginationdocument where loanoriginationdocumentid = 1774750t ID
select * from loanorigination..loanoriginationdocument with (nolock)
where loanoriginationmasterID = 37786 --LOMID
--check if the docs in question exist
--\\KSOLA1ESIG01\Server\Production\ <doc path from loanoriginationdocument table>
--Code to Delete bad docs
Delete from loanorigination..loanoriginationdocument where loanoriginationdocumentid = 35801 --from step 1
--Query for count of stuck emails
select count(*) from communicator.dbo.PostMessage
where StatusID = 1
order by 1 desc
--Got DisplayID from LoanID
select top 100
display_id
,*
from LoanPro..Loan_entity
where ID = 15792
My Portal Queries
--Has a customer created a portal account:
select*from loanpro.dbo.loan_entity le
left join loanpro.dbo.loan__customer lc on lc.loan_id = le.id
left join lms.dbo.customerlink cl on cl.LoanSystemCustomerId = lc.customer_id
left join lms.dbo.customerportal cp on cp.customerLinkID = cl.customerLinkID
where le.display_id ='3-55470'
--PORTAL cp.CreatedDateTime = date they created a protal account
--PORTAL cp.Username = portal username
--Find actions done on portal account
select * from lms.dbo.customerPortalaction
join lms.[dbo].[ReferenceActionType] on [ReferenceActionType].[ReferenceActionTypeid] = customerPortalaction.[ReferenceActionTypeid]
where customerportalId = 10708
order by lms.dbo.CustomerPortalAction.CreatedDateTime DESC
--Find accounts that have specific Portal Action Types
select * from lms.dbo.customerPortalaction
where ReferenceActionTypeId = 18 -- and CreatedDateTime < '2020-02-26 13:52:19.107' --addes date range to query
order by lms.dbo.CustomerPortalAction.CreatedDateTime DESC
--get loan # with Portal ID
select*from loanpro.dbo.loan_entity le
left join loanpro.dbo.loan__customer lc on lc.loan_id = le.id
left join lms.dbo.customerlink cl on cl.LoanSystemCustomerId = lc.customer_id
left join lms.dbo.customerportal cp on cp.customerLinkID = cl.customerLinkID
where CustomerPortalId=151235
--How to get the IP:
select top 50 * from LMS.[dbo].[LMSAPILogging]
where customerportalId = 261578
order by 1 desc
--PORTAL Run to get IP then run below
--See when the customer logged in from the IP gathered in above query:
select top 50 * from LMS.[dbo].[LMSAPILogging]
where IPaddress = '72.234.226.150'
order by 1 desc
--PORTAL Portal logins additional log
--Get the Cust MyAccount access code (primaraly for PreProd)
select top 10 * from lms.[dbo].[CustomerPortalSetupAuth] order by 1 desc
--get customer info
select * from loanorigination.dbo.loanoriginationcustomer where loanoriginationmasteRId = 55327 --Loan Number
--See if code was sent out
select * from lms.[dbo].[CustomerPortalSetupAuth] where EmailAddress = 'eloybarrerajr1@gmail.com' --Cust email from above querie
--What emails have been sent to cust email address
select * from communicator.dbo.PostMessage where ToAddress = 'FABIANYBARRA98@GMAIL.COM' --Check Email on LP customer profile
order by DatetimePost DESC
--Selective Columns for email search
select [ToAddress], [FromAddress], [MessageHeading], [DateTimePost]
from communicator.dbo.PostMessage where ToAddress = 'FABIANYBARRA98@GMAIL.COM' --Check Email on LP customer profile
order by DatetimePost DESC
--Find if emails were recieved and click on links
select * from communicator.[dbo].[SendGridLoad] where email = 'eloybarrerajr1@gmail.com' --Check Email on LP customer profile
order by CreatedDate DESC
--See what pages a customer visited
select *
from americanwebloan..LoggedPages
where applicationid = '1609675'
order by DateVisited desc
--check if has portal or not
select top 100
le.display_id
, cp.UserName
, cp.CustomerPortalId
, le.id
, cp.CreatedDateTime as PortalCreate
,*
from LMs..CustomerLink cl
LEFT JOIN Lms..CustomerPortal cp on cl.CustomerLinkId = cp.CustomerLinkId
JOIN Loanpro..customer_entity ce on cl.LoanSystemCustomerId = ce.ID
JOIN LoanPro..Loan__Customer lc on ce.id = lc.Customer_ID
JOIN LoanPro..Loan_entity le on lc.loan_id = le.id
--where (ce.first_name ='john' and ce.last_name = 'beck')
where display_id like '%2-1493736%' or display_id like '%1493736%' --OR ce.email = 'Kris2004yas@hotmail.com'
Order by cl.LoanSystemCustomerId desc
--Find if emails were recieved and click on links
select [EMail], [Event], [Subject], [CreatedDate] from communicator.[dbo].[SendGridLoad] where email = 'FABIANYBARRA98@GMAIL.COM' --Check Email on LP customer profile
order by CreatedDate DESC