Settlement Agent/SellingAgentBrokers

Discussions concerning general integration topics.

Moderator: Phil Barton

Post Reply
dlerickson
Posts: 80
Joined: Tue Jan 21, 2014 11:35 am
Location: Austin, TX

Settlement Agent/SellingAgentBrokers

Post by dlerickson »

I'm trying to get SQL join data from an Order to both the SettlementAgent and SellingAgentBroker data. None of the key relationships in SQL seem to be mapping a clear path to this data. Here's a list of the Field Code Browser equivalents of what I need for each:

SettlementAgent:
Order.SettlementAgents.Name
Order.SettlementAgents.Address.Address1
Order.SettlementAgents.Address.Address2
Order.SettlementAgents.Address.City
Order.SettlementAgents.Address.State
Order.SettlementAgents.Address.Zip
Order.SettlementAgents.LicenseNumber

Order.SettlementAgents.MainPerson.FirstName
Order.SettlementAgents.MainPerson.LastName
Order.SettlementAgents.MainPerson.LicenseNumber


SellingAgentBroker:
Order.SellingAgentBrokers.Name
Order.SellingAgentBrokers.Address.Address1
Order.SellingAgentBrokers.Address.Address2
Order.SellingAgentBrokers.Address.City
Order.SellingAgentBrokers.Address.State
Order.SellingAgentBrokers.Address.Zip
Order.SellingAgentBrokers.LicenseNumber

Order.SellingAgentBrokers.MainPerson.FirstName
Order.SellingAgentBrokers.MainPerson.LastName
Order.SellingAgentBrokers.MainPerson.LicenseNumber


The fields are the same for each path, so I'm hoping that the difference between these queries is just swapping out a couple intermediate tables from one query to the other. Thanks!
Lisa Ennis
Posts: 84
Joined: Thu Sep 11, 2008 1:57 pm

Re: Settlement Agent/SellingAgentBrokers

Post by Lisa Ennis »

This gets the data based on the contact types.

Code: Select all

SELECT 
Name,
C.Address#Address1 AS Address1,
C.Address#Address2 AS Address2,
C.Address#City AS City,
C.Address#StateID,
zS.Code AS [State],
C.Address#Zip,
C.LicenseNumber,
P.FirstName AS MainPersonFirstName,
P.LastName AS MainPersonLastName,
P.LicenseNumber AS MainPersonLastNumber
FROM pfm.Contact AS C
LEFT OUTER JOIN zref.[State] AS zS ON C.Address#StateID=zS.ID
LEFT OUTER JOIN pfm.Person AS P ON C.RootId#=P.RootId# AND P.Id# BETWEEN C.Id# AND C.LastID# AND P.IsMainPerson=1
WHERE
C.ContactType=22 OR C.ContactType=21  --22=Settlement Agent, 21=Selling Agent
If you are needing the settlement agent and selling agent that is selected on Page 5 of the CDF, use the pfm.PointOfContact table

Code: Select all

SELECT 
Name,
C.Address#Address1 AS Address1,
C.Address#Address2 AS Address2,
C.Address#City AS City,
C.Address#StateID,
zS.Code AS [State],
C.Address#Zip,
C.LicenseNumber,
P.FirstName AS MainPersonFirstName,
P.LastName AS MainPersonLastName,
P.LicenseNumber AS MainPersonLastNumber
FROM 
(SELECT 
	PC.RootId#,
	PC.Id#,
	PC.ContactId$,
	PC.PersonId$,
	PC.Position, --1=Buyer's Agent, 2=Seller's Agent, 3=Settlement Agent,
    CDF.Id# AS CDF_Id#
    FROM	 
	pfm.PointOfContact AS PC WITH (NOLOCK) 
	INNER JOIN pfm.CDF AS CDF WITH (NOLOCK) ON PC.RootId#=CDF.RootId# AND PC.Id# BETWEEN CDF.Id# AND CDF.LastId#
    WHERE 
    PC.Type#=(SELECT ID FROM pf.OrderItemType WHERE Name='CDFPointOfContact')
    AND PC.Position IN (2, 3)) AS POC
INNER JOIN pfm.Contact AS C ON POC.RootId#=C.RootId# AND POC.ContactId$=C.Id#
LEFT OUTER JOIN pfm.Person AS P ON POC.RootId#=P.RootId# AND POC.PersonId$=P.Id#
LEFT OUTER JOIN zref.[State] AS zS ON C.Address#StateID=zS.ID
Hope this helps!
Lisa Ennis
Senior Report Developer
SoftPro
dlerickson
Posts: 80
Joined: Tue Jan 21, 2014 11:35 am
Location: Austin, TX

Re: Settlement Agent/SellingAgentBrokers

Post by dlerickson »

Thanks...so far, so good. One further question, is this the proper way to tie it back to the Order? Doing a straight join on the Id# fields does not seem to work properly.

Code: Select all

where contact.RootId# = [Order].RootId# 
and (contact.Id# between [Order].Id# and [Order].LastId#)
and contact.ContactType = 22 -- Settlement Agent
Lisa Ennis
Posts: 84
Joined: Thu Sep 11, 2008 1:57 pm

Re: Settlement Agent/SellingAgentBrokers

Post by Lisa Ennis »

The field RootId# that exists in most of the pfm schema tables is the order id. As you traverse down through the tables, you also need the between joins of Id# and LastId# from the table above it in hierarchy.
Lisa Ennis
Senior Report Developer
SoftPro
Post Reply